eggjs/mysql

npm i @eggjs/mysql

export default {
  mysql: {
    enable: true,
    package: '@eggjs/mysql',
  },
};

export default {
  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 `true`
    app: true,
    // load into agent, default is `false`
    agent: false,
  },
};

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

export default {
  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,
  },
};

const client1 = app.mysqls.getSingletonInstance('db1');
await client1.query(sql, values);

const client2 = app.mysqls.getSingletonInstance('db2');
await client2.query(sql, values);

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

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

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

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

const conn = await app.mysql.beginTransaction();

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

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

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

await app.mysql.insert(table, {
  create_time: app.mysql.literals.now,
});

// INSERT INTO `$table`(`create_time`) VALUES(NOW())

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

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

docker compose -f docker-compose.yml up -d
# if you run the first time, should wait for ~20s to let mysql service init started

npm test

docker compose -f docker-compose.yml down