egg-mysql

Aliyun rds client(support mysql portocal) for egg framework

本代码是 为了改typescript get传第二个参数是做了修改,其他都是用了egg-mysql

本意是修改bug

Install

1$ npm i zion-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.js to enable MySQL plugin:

1exports.mysql = {
2  enable: true,
3  package: 'zion-egg-mysql',
4};

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

Simple database instance

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

Usage:

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

Multiple database instance

1exports.mysql = {
2  clients: {
3    // clientId, access the client instance by app.mysql.get('clientId')
4    db1: {
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    // ...
17  },
18  // default configuration for all databases
19  default: {
20
21  },
22
23  // load into app, default is open
24  app: true,
25  // load into agent, default is close
26  agent: false,
27};

Usage:

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

CRUD user guide

Create

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

Read

1// get
2const post = yield app.mysql.get('posts', { id: 12 });
3// query
4const results = yield 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 = yield app.mysql.update('posts', row);
9const updateSuccess = result.affectedRows === 1;

Delete

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

Automatic control: Transaction with scope

• API：*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 = yield app.mysql.beginTransactionScope(function* (conn) {
2  // don't commit or rollback by yourself
3  yield conn.insert(table, row1);
4  yield 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 = yield 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.

1yield 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';
4yield 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

jtyjty99999	popomore	atian25	dead-horse	fengmk2	AntiMoron
guoshencheng

This project follows the git-contributor spec, auto updated at Fri Feb 11 2022 15:53:56 GMT+0800.