ali-sdk/ali-rds

Name: ali-rds

Owner: ali-sdk

Description: Aliyun RDS client

Created: 2015-02-25 10:07:55.0

Updated: 2018-01-04 01:39:07.0

Pushed: 2017-09-26 03:40:30.0

Homepage: null

Size: 70

Language: JavaScript

GitHub Committers

User	Most Recent Commit	# Commits

Other Committers

User	Email	Most Recent Commit	# Commits

README

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

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

t db = rds({
st: 'your-rds-address.mysql.rds.aliyuncs.com',
rt: 3306,
er: 'your-username',
ssword: 'your-password',
tabase: '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,

Insert

• Insert one row

row = {
me: 'fengmk2',
herField: 'other field value',
eatedAt: db.literals.now, // `now()` on db server
 ...

result = yield db.insert('table-name', row);
ole.log(result);
eldCount: 0,
fectedRows: 1,
sertId: 3710,
rverStatus: 2,
rningCount: 2,
ssage: '',
otocol41: true,
angedRows: 0 }

• Insert multi rows

Will execute under a transaction and auto commit.

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
// ...

 ...


results = yield db.insert('table-name', rows);
ole.log(result);
eldCount: 0,
fectedRows: 2,
sertId: 3840,
rverStatus: 2,
rningCount: 2,
ssage: '&Records: 2  Duplicates: 0  Warnings: 0',
otocol41: true,
angedRows: 0 }

Update

• Update a row with primary key: id

row = {
: 123,
me: 'fengmk2',
herField: 'other field value',
difiedAt: db.literals.now, // `now()` on db server

result = yield db.update('table-name', row);
ole.log(result);
eldCount: 0,
fectedRows: 1,
sertId: 0,
rverStatus: 2,
rningCount: 0,
ssage: '(Rows matched: 1  Changed: 1  Warnings: 0',
otocol41: true,
angedRows: 1 }

• Update a row with options.where and options.columns

row = {
me: 'fengmk2',
herField: 'other field value',
difiedAt: db.literals.now, // `now()` on db server

result = yield db.update('table-name', row, {
ere: { name: row.name },
lumns: [ 'otherField', 'modifiedAt' ]

ole.log(result);
eldCount: 0,
fectedRows: 1,
sertId: 0,
rverStatus: 2,
rningCount: 0,
ssage: '(Rows matched: 1  Changed: 1  Warnings: 0',
otocol41: true,
angedRows: 1 }

Get

• Get a row

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

ELECT * FROM `table-name` WHERE `name` = 'fengmk2'

Select

• Select all rows

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

ELECT * FROM `table-name`

• Select rows with condition

rows = yield db.select('table-name', {
ere: {
type: 'javascript'

lumns: ['author', 'title'],
ders: [['id', 'desc']]


ELECT `author`, `title` FROM `table-name`
RE `type` = 'javascript' ORDER BY `id` DESC

Delete

• Delete with condition

result = yield db.delete('table-name', {
me: 'fengmk2'


ELETE FROM `table-name` WHERE `name` = 'fengmk2'

Count

• Get count from a table with condition

count = yield db.count('table-name', {
pe: 'javascript'


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

Transactions

beginTransaction, commit or rollback

tran = yield db.beginTransaction();

{
eld tran.insert(table, row1);
eld tran.update(table, row2);
eld tran.commit();
tch (err) {
 error, rollback
eld tran.rollback(); // rollback call won't throw err
row 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.

result = yield db.beginTransactionScope(function* (conn) {
 don't commit or rollback by yourself
eld conn.insert(table, row1);
eld conn.update(table, row2);
turn { success: true };

f 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.

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


tion* bar(ctx, data2) {
turn 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);

Raw Queries

• Query with arguments

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

• Query with arguments

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

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)
• *delete(table, where)
• *count(table, where)

Transactions

• *beginTransaction()
• *beginTransactionScope(scope)

Utils

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

Literals

d db.insert('user', {
me: 'fengmk2',
eatedAt: db.literals.now,




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

Custom Literal

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

TODO

• [x] MySQL
• [x] Pool
• [ ] Cluster
• [ ] SQL Server
• [ ] PostgreSQL

License

MIT