JavaScript
Need help with ali-rds?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.
ali-sdk

Description

Aliyun RDS client

223 Stars 58 Forks Other 60 Commits 37 Opened issues

Services available

Need anything else?

ali-rds

NPM version build status Test coverage David deps npm download

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 = 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, });

Insert

  • Insert one row
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 }
  • Insert multi rows

Will execute under a transaction and auto commit.

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 }

Update

  • Update a row with primary key:
    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 }
  • Update a row with
    options.where
    and
    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 }

Update multiple rows

  • Update multiple rows with primary key:
    id
let options = [{
  id: 123,
  name: 'fengmk2',
  email: '[email protected]',
  otherField: 'other field value',
  modifiedAt: db.literals.now, // `now()` on db server
}, {
   id: 124,
  name: 'fengmk2_2',
  email: '[email protected]_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 }
  • Update multiple rows with
    row
    and
    where
    properties
let options = [{
  row: {
    email: '[email protected]',
    otherField: 'other field value',
    modifiedAt: db.literals.now, // `now()` on db server
  },
  where: {
    id: 123,
    name: 'fengmk2',
  }
}, {
  row: {
    email: '[email protected]_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 }

Get

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

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

Select

  • Select all rows
let rows = yield db.select('table-name');

=> SELECT * FROM table-name

  • Select rows with condition
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

Delete

  • Delete with condition
let result = yield db.delete('table-name', {
  name: 'fengmk2'
});

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

Count

  • Get count from a table with condition
let count = yield db.count('table-name', {
  type: 'javascript'
});

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

Transactions

beginTransaction, commit or rollback

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; }

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 = 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

Transaction on koa

API:

*beginTransactionScope(scope, ctx)

Use koa's context to make sure only one active transaction on one 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); }

Raw Queries

  • Query without arguments
let rows = yield db.query('SELECT * FROM your_table LIMIT 100');
console.log(rows);
  • Query with array arguments
let rows = yield db.query('SELECT * FROM your_table WHERE id=?', [ 123 ]);
console.log(rows);
  • Query with object arguments
let rows = yield db.query('SELECT * FROM your_table WHERE id=:id', { id: 123 });
console.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)
  • *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

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

=>

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

Custom Literal

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

TODO

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

License

MIT

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.