@eggjs/rds

A RDS client. RDS, Relational Database Service. Equal to well know Amazon RDS.

Support MySQL protocol only.

Usage

Create RDS instance

import { RDSClient } from '@eggjs/rds';

const db = new RDSClient({
  host: 'your-rds-address.mysql.rds.aliyuncs.com',
  port: 3306,
  user: 'your-username',
  password: 'your-password',
  database: 'your-database-name',
  // optional params
  // The charset for the connection.
  // This is called "collation" in the SQL-level of MySQL (like utf8_general_ci).
  // If a SQL-level charset is specified (like utf8mb4)
  // then the default collation for that charset is used. (Default: 'UTF8_GENERAL_CI')
  // charset: 'utf8_general_ci',
  //
  // The maximum number of connections to create at once. (Default: 10)
  // connectionLimit: 10,
  //
  // The maximum number of connection requests the pool will queue
  // before returning an error from getConnection.
  // If set to 0, there is no limit to the number of queued connection requests. (Default: 0)
  // queueLimit: 0,
  // Set asyncLocalStorage manually for transaction
  // connectionStorage: new AsyncLocalStorage(),
  // If create multiple RDSClient instances with the same connectionStorage, use this key to distinguish between the instances
  // connectionStorageKey: 'datasource',
});

Insert

Insert one row

const row = {
  name: 'fengmk2',
  otherField: 'other field value',
  createdAt: db.literals.now, // `now()` on db server
  // ...
};
const result = await db.insert('table-name', row);
console.log(result);
{ fieldCount: 0,
  affectedRows: 1,
  insertId: 3710,
  serverStatus: 2,
  warningCount: 2,
  message: '',
  protocol41: true,
  changedRows: 0 }

Insert multi rows

Will execute under a transaction and auto commit.

const rows = [
  {
    name: 'fengmk1',
    otherField: 'other field value',
    createdAt: db.literals.now, // `now()` on db server
    // ...
  },
  {
    name: 'fengmk2',
    otherField: 'other field value',
    createdAt: db.literals.now, // `now()` on db server
    // ...
  },
  // ...
];

const results = await db.insert('table-name', rows);
console.log(result);
{ fieldCount: 0,
  affectedRows: 2,
  insertId: 3840,
  serverStatus: 2,
  warningCount: 2,
  message: '&Records: 2  Duplicates: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0 }

Update

Update a row with primary key: id

const row = {
  id: 123,
  name: 'fengmk2',
  otherField: 'other field value',
  modifiedAt: db.literals.now, // `now()` on db server
};
const result = await db.update('table-name', row);
console.log(result);
{ fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1 }

Update a row with options.where and options.columns

const row = {
  name: 'fengmk2',
  otherField: 'other field value',
  modifiedAt: db.literals.now, // `now()` on db server
};
const result = await db.update('table-name', row, {
  where: { name: row.name },
  columns: [ 'otherField', 'modifiedAt' ]
});
console.log(result);
{ fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1 }

Update multiple rows

Update multiple rows with primary key: id

const options = [{
  id: 123,
  name: 'fengmk2',
  email: 'm@fengmk2.com',
  otherField: 'other field value',
  modifiedAt: db.literals.now, // `now()` on db server
}, {
   id: 124,
  name: 'fengmk2_2',
  email: 'm@fengmk2_2.com',
  otherField: 'other field value 2',
  modifiedAt: db.literals.now, // `now()` on db server
}]
const result = await db.updateRows('table-name', options);
console.log(result);
{ fieldCount: 0,
  affectedRows: 2,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 2  Changed: 2  Warnings: 0',
  protocol41: true,
  changedRows: 2 }

Update multiple rows with row and where properties

const options = [{
  row: {
    email: 'm@fengmk2.com',
    otherField: 'other field value',
    modifiedAt: db.literals.now, // `now()` on db server
  },
  where: {
    id: 123,
    name: 'fengmk2',
  }
}, {
  row: {
    email: 'm@fengmk2_2.com',
    otherField: 'other field value2',
    modifiedAt: db.literals.now, // `now()` on db server
  }, 
  where: {
    id: 124,
    name: 'fengmk2_2',
  }
}]
const result = await db.updateRows('table-name', options);
console.log(result);
{ fieldCount: 0,
  affectedRows: 2,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 2  Changed: 2  Warnings: 0',
  protocol41: true,
  changedRows: 2 }

Get

Get a row

const row = await db.get('table-name', { name: 'fengmk2' });

=> SELECT * FROM `table-name` WHERE `name` = 'fengmk2'

Select

Select all rows

const rows = await db.select('table-name');

=> SELECT * FROM `table-name`

Select rows with condition

const rows = await db.select('table-name', {
  where: {
    type: 'javascript'
  },
  columns: ['author', 'title'],
  orders: [['id', 'desc']]
});

=> SELECT `author`, `title` FROM `table-name`
 WHERE `type` = 'javascript' ORDER BY `id` DESC

Delete

Delete with condition

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

=> DELETE FROM `table-name` WHERE `name` = 'fengmk2'

Count

Get count from a table with condition

const count = await db.count('table-name', {
  type: 'javascript'
});

=> SELECT COUNT(*) AS count FROM `table-name` WHERE `type` = 'javascript';

Transactions

beginTransaction, commit or rollback

const tran = await db.beginTransaction();

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

Transaction with scope

API: async beginTransactionScope(scope)

All query run in scope will under a same transaction. We will auto commit or rollback for you.

const result = await db.beginTransactionScope(async conn => {
  // don't commit or rollback by yourself
  await conn.insert(table, row1);
  await conn.update(table, row2);
  return { success: true };
});
// if error throw on scope, will auto rollback

In Promise.all case, Parallel beginTransactionScope will create isolated transactions.

const result = await Promise.all([
  db.beginTransactionScope(async conn => {
    // commit and success
  }),
  db.beginTransactionScope(async conn => {
    // throw err and rollback
  }),
])

Raw Queries

Query without arguments

const rows = await db.query('SELECT * FROM your_table LIMIT 100');
console.log(rows);

Query with array arguments

const rows = await db.query('SELECT * FROM your_table WHERE id=?', [ 123 ]);
console.log(rows);

Query with object arguments

const rows = await db.query('SELECT * FROM your_table WHERE id=:id', { id: 123 });
console.log(rows);

Custom query lifecircle

db.beforeQuery((sql: string) => {
  // change sql string
  return `/* add custom format here */ ${sql}`;
});

db.afterQuery((sql: string, result: any, execDuration: number, err?: Error) => {
  // handle logger here
});

APIs

* Meaning this function is yieldable.

IO queries

async query(sql[, values)
async queryOne(sql[, values)
async select(table, options)
async get(table, where, options)
async insert(table, row[s], options)
async update(table, row, options)
async updateRows(table, options)
async delete(table, where)
async count(table, where)

Transactions Helpers

async beginTransaction()
async beginTransactionScope(scope)

Utils

escape(value, stringifyObjects, timeZone)
escapeId(value, forbidQualified)
format(sql, values, stringifyObjects, timeZone)

Literals

await db.insert('user', {
  name: 'fengmk2',
  createdAt: db.literals.now,
});

=>

INSERT INTO `user` SET `name` = 'fengmk2', `createdAt` = now()

Custom Literal

const session = new db.literals.Literal('session()');

Class Relation

+-----------+                          +----------------+
| RDSClient +-- beginTransaction() --> + RDSTransaction |
+--+----+---+                          +----+----+------+
   |    | getConnection()            .conn  |    |
   |    |         +---------------+         |    |
   |    +-------->+ RDSConnection +<--------+    |
   |              +-------+-------+              |
   |                      | extends              |
   |                      v                      |
   |   extends    +-------+-------+    extends   |
   +------------->+   Operator    +<-------------+
                  |    query()    |
                  +---------------+