ali-rds 阿里云 RDS （关系型数据库服务）客户端

Question

阿里云RDS客户端。ali-sdk 的子模块。RDS 即关系数据库服务。等于熟知亚马逊 RDS。支持 MySQL，SQL Server 和 PostgreSQL。

MySQL 用法

创建 RDS 实例

const rds = require('ali-rds');

const db = rds({
  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,
});

插入

插入一行

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

插入多行

将在事务和自动提交下执行。

let 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
    // ...
  },
  // ...
];

let results = yield 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 }

更新

用主键更新一行： id

let row = {
  id: 123,
  name: 'fengmk2',
  otherField: 'other field value',
  modifiedAt: db.literals.now, // `now()` on db server
};
let result = yield 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 }

用 options.where 和更新一行 options.columns

let row = {
  name: 'fengmk2',
  otherField: 'other field value',
  modifiedAt: db.literals.now, // `now()` on db server
};
let result = yield 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 }

更新多行

使用主键更新多行： id

let 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
}]
let result = yield 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 }

使用 row 和 where 属性更新多行

let 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',
  }
}]
let result = yield 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 }

查询数据

查询一行

let row = yield db.get('table-name', { name: 'fengmk2' });
=> SELECT * FROM `table-name` WHERE `name` = 'fengmk2'

查询所有行

let rows = yield db.select('table-name');
=> SELECT * FROM `table-name`

查询有条件的行

let rows = yield 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

删除

有条件删除

let result = yield db.delete('table-name', {
  name: 'fengmk2'
});
=> DELETE FROM `table-name` WHERE `name` = 'fengmk2'

统计条数

从有条件的表中获取计数

let count = yield db.count('table-name', {
  type: 'javascript'
});
=> SELECT COUNT(*) AS count FROM `table-name` WHERE `type` = 'javascript';

事务

beginTransaction、提交或回滚

let tran = yield db.beginTransaction();

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

有作用域的事务

应用程序接口： *beginTransactionScope(scope)

在范围内运行的所有查询都将在同一个事务下进行。我们将为您自动提交或回滚。

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

在 koa 上使用事务

应用程序接口： *beginTransactionScope(scope, ctx)

使用 koa 的上下文来确保一个 ctx 上只有一个活动事务。

function* foo(ctx, data1) {
  return yield db.beginTransactionScope(function* (conn) {
    yield conn.insert(table1, data1);
    return { success: true };
  }, ctx);
}

function* bar(ctx, data2) {
  return yield db.beginTransactionScope(function* (conn) {
    // execute foo with the same transaction scope
    yield foo(ctx, { foo: 'bar' });
    yield conn.insert(table2, data2);
    return { success: true };
  }, ctx);
}

原始查询

不带参数的查询

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

使用数组参数查询

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

使用对象参数查询

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

APIs

* 这意味着这个函数是异步的。

IO 查询

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

事务

• *beginTransaction()
• *beginTransactionScope(scope)

实用程序

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

Literals 字面量

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

=>

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

自定义 Literal 字面量

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

项目地址：https://github.com/ali-sdk/ali-rds