Neverending Package Mountain

    egg-cute-mysql
    TypeScript icon, indicating that this package has built-in type declarations

    1.1.3 • Public • Published

    egg-cute-mysql

    Refer to [ali-rds], [egg-mysql]

    Install

    $ npm i egg-cute-mysql --save

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

    Configuration

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

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

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

    Simple database instance

    exports.mysql = {
      // database configuration
      client: {
        // host
        host: '127.0.0.1',
        // port
        port: '3306',
        // username
        user: 'user',
        // password
        password: 'password',
        // database
        database: 'test',
    
        size: 20,
        // Camelcase,Underline
        selectKey: 'Camelcase',
      },
      // 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: '127.0.0.1',
          // port
          port: '3306',
          // username
          user: 'user',
          // password
          password: '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 = await app.mysql.insert('insert into posts(title) value(:title)', { title: 'Hello World' });
    const insertSuccess = !result.insertId;

    Read

    // get
    const post = await app.mysql.info('select * from posts where id=:id', { id: 12 });
    // query
    const results = await app.mysql.list(`select * from posts ${app.mysql.order([['created_at','desc'], ['id','desc']])} limit :limit`,{
      limit: 2
    });
    
    const results = await app.mysql.page('select * from posts',{
      size: 10,
      pageNum: 1,
    });

    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 = await app.mysql.update('update posts set name=:name,other_field=:other_field,modified_at=:modified_at where id=:id', row);
    const updateSuccess = result.changedRows === 1;

    Delete

    const result = await app.mysql.delete('delete from posts where name=: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 = await app.mysql.beginTransaction();
    
    try {
      await conn.insert(sql, values);
      await conn.update(sql, values);
      await 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 = await app.mysql.beginTransactionScope(async (conn) => {
      // don't commit or rollback by yourself
      await conn.insert(sql, values);
      await conn.update(sql, values);
      return { success: true };
    }, ctx); // ctx is the context of current request, access by `this.ctx`.
    // if error throw on scope, will auto rollback

    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.
    await app.mysql.insert(sql, {
      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';
    await app.mysql.insert(sql, {
      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-cute-mysql

    DownloadsWeekly Downloads

    0

    Version

    1.1.3

    License

    MIT

    Unpacked Size

    39.6 kB

    Total Files

    49

    Last publish

    Collaborators

    • wadecha