@mahudas/mariadb

Dependencies

• mahudas^0.1.2
• mariadb^3.1.1

As a plugin

如同一般的plugin，透過npm安裝之後，在Application的plugin.env.js裡設定啟用。

npm i @mahudas/mariadb -s

// config/plugin.deafult.js
module.exports = {
  mariadb: {
    enable: true,
    package: '@mahudas/mariadb',
  },
}

設定

// config/config.default.js
module.exports = {
  mariadb: {
    host: '127.0.0.1',
    port: 3306,
    user: 'user',
    password: 'password',
    database: '',
    charset: 'utf8mb4',
    timezone: '+08:00',
    acquireTimeout: 20000,
  },
}

參數

請參考 Mariadb Pool options

Example

get 和 find, insert, update, delete 是擴充的 function

Get/Find

// controller.js or service.js or middleware.js
await app.mariadb.get('SELECT NOW() AS currentTime;');
// > { currentTime: 2023-03-20T03:11:13.000Z }
await app.mariadb.find('SELECT NOW() AS currentTime;');
// > [{ currentTime: 2023-03-20T03:11:13.000Z }, meta: []]

Insert

const insert_data = {
    foo: 'bar',
}
await app.mariadb.insert('table_name', insert_data);
// > { status: true, insertId: 1 }

Update

const update_data = {
    foo: 'barU',
}
const where = {
    id: 1
}
await app.mariadb.update('table_name', update_data, where);
// > true

Delete

const delete_where = {
    id: 1
}
await app.mariadb.delete('table_name', delete_where);
// > true

Transaction

const transactionConn = await app.mariadb.beginTransaction();
const insert_data = {
    foo: 'bar',
}
await app.mariadb.insert('table_name', insert_data);
await transactionConn.commit();

// 因為 transactionConn 已經 commit，為了重複使用connection，所以用參數的方式帶入現有的 connection
const deleteTransactionConn = await app.mariadb.beginTransaction(transactionConn);
const delete_where = {
    id: 2
}
await app.mariadb.delete('table_name', delete_where);
deleteTransactionConn.rollback();
// > no delete

// 最後記得要手動釋出 connection
deleteTransactionConn.end();

一般使用

// 要先取得 connection，在用 conn 去 query
// 最後記得要 conn.end();
const conn = await app.mariadb.getConnection();
try{
    return await conn.query('SELECT NOW() AS currentTime;');
}catch (e) {
    await conn.end();
    throw e;
}finally {
    await conn.end();
}