egg-mysql

Aliyun rds client(support mysql portocal) for egg framework

Install

1npm i egg-mysql --save

MySQL Plugin for egg, support egg application access to MySQL database.

This plugin based on ali-rds, if you want to know specific usage, you should refer to the document of ali-rds.

Configuration

Change ${app_root}/config/plugin.ts to enable MySQL plugin:

1export default {
2  mysql: {
3    enable: true,
4    package: 'egg-mysql',
5  },
6}

Configure database information in ${app_root}/config/config.default.ts:

Simple database instance

1export default {
2  mysql: {
3    // database configuration
4    client: {
5      // host
6      host: 'mysql.com',
7      // port
8      port: '3306',
9      // username
10      user: 'test_user',
11      // password
12      password: 'test_password',
13      // database
14      database: 'test',
15    },
16    // load into app, default is open
17    app: true,
18    // load into agent, default is close
19    agent: false,
20  },
21}

Usage:

1await app.mysql.query(sql, values); // you can access to simple database instance by using app.mysql.

Multiple database instance

1export default {
2  mysql: {
3    clients: {
4      // clientId, access the client instance by app.mysql.get('clientId')
5      db1: {
6        // host
7        host: 'mysql.com',
8        // port
9        port: '3306',
10        // username
11        user: 'test_user',
12        // password
13        password: 'test_password',
14        // database
15        database: 'test',
16      },
17      // ...
18    },
19    // default configuration for all databases
20    default: {
21
22    },
23    // load into app, default is open
24    app: true,
25    // load into agent, default is close
26    agent: false,
27  },
28}

Usage:

1const client1 = app.mysqls.get('db1');
2await client1.query(sql, values);
3
4const client2 = app.mysqls.get('db2');
5await client2.query(sql, values);

CRUD user guide

Create

1// insert
2const result = await app.mysql.insert('posts', { title: 'Hello World' });
3const insertSuccess = result.affectedRows === 1;

Read

1// get
2const post = await app.mysql.get('posts', { id: 12 });
3// query
4const results = await app.mysql.select('posts',{
5  where: { status: 'draft' },
6  orders: [['created_at','desc'], ['id','desc']],
7  limit: 10,
8  offset: 0
9});

Update

1// update by primary key ID, and refresh
2const row = {
3  id: 123,
4  name: 'fengmk2',
5  otherField: 'other field value',
6  modifiedAt: app.mysql.literals.now, // `now()` on db server
7};
8const result = await app.mysql.update('posts', row);
9const updateSuccess = result.affectedRows === 1;

Delete

1const result = await app.mysql.delete('table-name', {
2  name: 'fengmk2',
3});

Transaction

Manual control

• adventage: beginTransaction, commit or rollback can be completely under control by developer
• disadventage: more handwritten code, Forgot catching error or cleanup will lead to serious bug.

1const conn = await app.mysql.beginTransaction();
2
3try {
4  await conn.insert(table, row1);
5  await conn.update(table, row2);
6  await conn.commit();
7} catch (err) {
8  // error, rollback
9  await conn.rollback(); // rollback call won't throw err
10  throw err;
11}

Automatic control: Transaction with scope

• 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

1const result = await app.mysql.beginTransactionScope(async (conn) => {
2  // don't commit or rollback by yourself
3  await conn.insert(table, row1);
4  await conn.update(table, row2);
5  return { success: true };
6}, ctx); // ctx is the context of current request, access by `this.ctx`.
7// if error throw on scope, will auto rollback

Advance

Custom SQL splicing

1const results = await app.mysql.query('update posts set hits = (hits + ?) where id = ?', [ 1, postId ]);

Literal

If you want to call literals or functions in mysql , you can use Literal.

Inner Literal

• NOW(): The database system time, you can obtain by app.mysql.literals.now.

1await app.mysql.insert(table, {
2  create_time: app.mysql.literals.now,
3});
4
5// INSERT INTO `$table`(`create_time`) VALUES(NOW())

Custom literal

The following demo showed how to call CONCAT(s1, ...sn) funtion in mysql to do string splicing.

1const Literal = app.mysql.literals.Literal;
2const first = 'James';
3const last = 'Bond';
4await app.mysql.insert(table, {
5  id: 123,
6  fullname: new Literal(`CONCAT("${first}", "${last}"`),
7});
8
9// INSERT INTO `$table`(`id`, `fullname`) VALUES(123, CONCAT("James", "Bond"))

Questions & Suggestions

Please open an issue here.

License

MIT

Contributors

fengmk2	jtyjty99999	popomore	atian25	dead-horse	AntiMoron
guoshencheng	cnwangjie	starandtina	shangwenhe

This project follows the git-contributor spec, auto updated at Sat Dec 03 2022 22:52:06 GMT+0800.