egg-mysql
MySQL plugin for egg
Last updated 8 months ago by dead_horse .
MIT · Repository · Bugs · Original npm · Tarball
$ cnpm install egg-mysql 
SYNC missed versions from official npm registry.

egg-mysql

NPM version build status Test coverage David deps Known Vulnerabilities npm download

Aliyun rds client(support mysql portocal) for egg framework

Install

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

exports.mysql = {
  enable: true,
  package: 'egg-mysql',
};

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

Simple database instance

exports.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 open
  app: true,
  // load into agent, default is close
  agent: false,
};

Usage:

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

Multiple database instance

exports.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.mysql.get('db1');
client1.query(sql, values);

const client2 = app.mysql.get('db2');
client2.query(sql, values);

CRUD user guide

Create

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

Read

// get
const post = yield app.mysql.get('posts', { id: 12 });
// query
const results = yield app.mysql.select('posts',{
  where: { status: 'draft' },
  orders: [['created_at','desc'], ['id','desc']],
  limit: 10,
  offset: 0
});

Update

// 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 = yield app.mysql.update('posts', row);
const updateSuccess = result.affectedRows === 1;

Delete

const result = yield app.mysql.delete('table-name', {
  name: 'fengmk2'
});

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.
const conn = yield app.mysql.beginTransaction();

try {
  yield conn.insert(table, row1);
  yield conn.update(table, row2);
  yield conn.commit();
} catch (err) {
  // error, rollback
  yield conn.rollback(); // rollback call won't throw err
  throw err;
}

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

Advance

Custom SQL splicing

const 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.
yield app.mysql.insert(table, {
  create_time: app.mysql.literals.now
});

// 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.

const Literal = app.mysql.literals.Literal;
const first = 'James';
const last = 'Bond';
yield app.mysql.insert(table, {
  id: 123,
  fullname: new Literal(`CONCAT("${first}", "${last}"`),
});

// INSERT INTO `$table`(`id`, `fullname`) VALUES(123, CONCAT("James", "Bond"))

Questions & Suggestions

Please open an issue here.

License

MIT

Downloads
Today 41
This Week 41
This Month 344
Last Day 15
Last Week 249
Last Month 1,267
Dependencies (1)
Dev Dependencies (9)
Dependents (2)

Copyright 2014 - 2017 © taobao.org |