NPM version CI Test coverage npm download Node.js Version PRs Welcome CodeRabbit Pull Request Reviews
MySQL plugin for Egg.js
npm i @eggjs/mysql
MySQL Plugin for egg@4, support egg application access to MySQL database.
If you're using
egg@3, please useegg-mysql@5instead.
This plugin based on @eggjs/rds, if you want to know specific usage, you should refer to the document of @eggjs/rds.
Change ${app_root}/config/plugin.ts to enable MySQL plugin:
export default { mysql: { enable: true, package: '@eggjs/mysql', }, };
Configure database information in ${app_root}/config/config.default.ts:
export default { mysql: { // database configuration client: { // host host: 'mysql.com', // port port: '3306', // username user: 'test_user', // password password: 'test_password', // database database: 'test', }, // load into app, default is `true` app: true, // load into agent, default is `false` agent: false, }, };
Usage:
await app.mysql.query(sql, values); // you can access to simple database instance by using app.mysql.
export default { mysql: { clients: { // clientId, access the client instance by app.mysql.get('clientId') db1: { // host host: 'mysql.com', // port port: '3306', // username user: 'test_user', // password password: 'test_password', // database database: 'test', }, // ... }, // default configuration for all databases default: {}, // load into app, default is open app: true, // load into agent, default is close agent: false, }, };
Usage:
const client1 = app.mysqls.getSingletonInstance('db1'); await client1.query(sql, values); const client2 = app.mysqls.getSingletonInstance('db2'); await client2.query(sql, values);
// insert const result = await app.mysql.insert('posts', { title: 'Hello World' }); const insertSuccess = result.affectedRows === 1;
// get const post = await app.mysql.get('posts', { id: 12 }); // query const results = await app.mysql.select('posts', { where: { status: 'draft' }, orders: [ ['created_at', 'desc'], ['id', 'desc'], ], limit: 10, offset: 0, });
// update by primary key ID, and refresh const row = { id: 123, name: 'fengmk2', otherField: 'other field value', modifiedAt: app.mysql.literals.now, // `now()` on db server }; const result = await app.mysql.update('posts', row); const updateSuccess = result.affectedRows === 1;
const result = await app.mysql.delete('table-name', { name: 'fengmk2', });
- adventage:
beginTransaction,commitorrollbackcan be completely under control by developer - disadventage: more handwritten code, Forgot catching error or cleanup will lead to serious bug.
const conn = await app.mysql.beginTransaction(); try { await conn.insert(table, row1); await conn.update(table, row2); await conn.commit(); } catch (err) { // error, rollback await conn.rollback(); // rollback call won't throw err throw err; }
- API:
async beginTransactionScope(scope, ctx)scope: A generatorFunction which will execute all sqls of this transaction.ctx: The context object of current request, it will ensures that even in the case of a nested transaction, there is only one active transaction in a request at the same time.
- adventage: easy to use, as if there is no transaction in your code.
- disadvantage: all transation will be successful or failed, cannot control precisely
const result = await app.mysql.beginTransactionScope(async conn => { // don't commit or rollback by yourself await conn.insert(table, row1); await conn.update(table, row2); return { success: true }; }, ctx); // ctx is the context of current request, access by `this.ctx`. // if error throw on scope, will auto rollback
const results = await app.mysql.query( 'update posts set hits = (hits + ?) where id = ?', [1, postId] );
If you want to call literals or functions in mysql , you can use Literal.
- NOW(): The database system time, you can obtain by
app.mysql.literals.now.
await app.mysql.insert(table, { create_time: app.mysql.literals.now, }); // INSERT INTO `$table`(`create_time`) VALUES(NOW())
The following demo showed how to call CONCAT(s1, ...sn) function in mysql to do string splicing.
const Literal = app.mysql.literals.Literal; const first = 'James'; const last = 'Bond'; await app.mysql.insert(table, { id: 123, fullname: new Literal(`CONCAT("${first}", "${last}"`), }); // INSERT INTO `$table`(`id`, `fullname`) VALUES(123, CONCAT("James", "Bond"))
Run docker compose to start test mysql service
docker compose -f docker-compose.yml up -d
# if you run the first time, should wait for ~20s to let mysql service init startedRun the unit tests
npm testStop test mysql service
docker compose -f docker-compose.yml down
Please open an issue here.
Made with contributors-img.