egg-mysql-x

    3.0.1 • Public • Published

    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

    Install

    npm i egg-mysql-x

    DownloadsWeekly Downloads

    2

    Version

    3.0.1

    License

    MIT

    Unpacked Size

    32 kB

    Total Files

    44

    Last publish

    Collaborators

    • wellfrog16