ali-rds
Aliyun RDS client. Sub module of ali-sdk.
RDS Usage
RDS, Relational Database Service. Equal to well know Amazon RDS.
Support MySQL
, SQL Server
and PostgreSQL
.
MySQL Usage
Create RDS instance
const rds = ; const db = ;
Insert
- Insert one row
let row = name: 'fengmk2' otherField: 'other field value' createdAt: dbliteralsnow // `now()` on db server // ...;let result = db;console; 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.
let rows = name: 'fengmk1' otherField: 'other field value' createdAt: dbliteralsnow // `now()` on db server // ... name: 'fengmk2' otherField: 'other field value' createdAt: dbliteralsnow // `now()` on db server // ... // ...; let results = db;console; 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
let row = id: 123 name: 'fengmk2' otherField: 'other field value' modifiedAt: dbliteralsnow // `now()` on db server;let result = db;console; 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
andoptions.columns
let row = name: 'fengmk2' otherField: 'other field value' modifiedAt: dbliteralsnow // `now()` on db server;let result = db;console; 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
let options = id: 123 name: 'fengmk2' email: 'm@fengmk2.com' otherField: 'other field value' modifiedAt: dbliteralsnow // `now()` on db server id: 124 name: 'fengmk2_2' email: 'm@fengmk2_2.com' otherField: 'other field value 2' modifiedAt: dbliteralsnow // `now()` on db serverlet result = db;console; 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
andwhere
properties
let options = row: email: 'm@fengmk2.com' otherField: 'other field value' modifiedAt: dbliteralsnow // `now()` on db server where: id: 123 name: 'fengmk2' row: email: 'm@fengmk2_2.com' otherField: 'other field value2' modifiedAt: dbliteralsnow // `now()` on db server where: id: 124 name: 'fengmk2_2' let result = db;console; 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
let row = db; SELECT * FROM `table-name` WHERE `name` = 'fengmk2'
Select
- Select all rows
let rows = db; SELECT * FROM `table-name`
- Select rows with condition
let rows = db; SELECT `author` `title` FROM `table-name` WHERE `type` = 'javascript' ORDER BY `id` DESC
Delete
- Delete with condition
let result = db; DELETE FROM `table-name` WHERE `name` = 'fengmk2'
Count
- Get count from a table with condition
let count = db; SELECT AS count FROM `table-name` WHERE `type` = 'javascript';
Transactions
beginTransaction, commit or rollback
let tran = db; try tran; tran; tran; catch err // error, rollback tran; // rollback call won't throw err throw err;
Transaction with scope
API: *beginTransactionScope(scope)
All query run in scope will under a same transaction. We will auto commit or rollback for you.
var result = db;// if error throw on scope, will auto rollback
Transaction on koa
API: *beginTransactionScope(scope, ctx)
Use koa's context to make sure only one active transaction on one ctx.
{ return db;} { return db;}
Raw Queries
- Query without arguments
let rows = db;console;
- Query with array arguments
let rows = db;console;
- Query with object arguments
let rows = db;console;
SQL Server Usage
TBD
APIs
*
Meaning this function is yieldable.
IO queries
- *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)
Transactions
- *beginTransaction()
- *beginTransactionScope(scope)
Utils
- escape(value, stringifyObjects, timeZone)
- escapeId(value, forbidQualified)
- format(sql, values, stringifyObjects, timeZone)
Literals
db; INSERT INTO `user` SET `name` = 'fengmk2' `createdAt` =
Custom Literal
let session = 'session()';
TODO
- MySQL
- Pool
- Cluster
- SQL Server
- PostgreSQL