Ali-rds-x NPM | npm.io

ali-rds-x

Aliyun RDS client. Sub module of ali-sdk.

clone from https://github.com/SimbaZZX/ali-rds

增加like模糊查询等，用于npm发布

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: '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 }

Update multiple rows with row and where properties

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 }

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

复杂查询
如果你看到这里说明你和我遇到了一样的问题，egg/mysql 缺少一些实用方法，这里做了一些扩展，可能在你的使用过程中，还会存在 bug，请参考着继续完善吧。单层CUSTOM自定义查询语句

        let rows = yield db.select('table-name', {
            where: {
                sender: { op: 'like', value: `%111%` },
                receiver: { op: 'like', value: `%222%` },
                cn_orderno_list: { op: 'like', value: `%333%` },
                OR: {
                    sender11: { op: 'like', value: `%111%` },
                    receiver22: { op: 'like', value: `%222%` },
                },
                a: 1,
                b: [11, 22],
                AND: {
                    orderno: null,
                    id: 1,
                    role: ['admin', 'guest'],
                    OR: {
                        realname: { op: 'like', value: 'simba' },
                        age: { op: '>', value: '20' },
                        nickname: { op: 'like', value: 'ace' }
                    },
                    createtime: [
                        { op: '>', value: '2020-10-13' },
                        { op: '<', value: '2020-11-22' },
                    ]
                },
            },
            columns: ['author', 'title'],
            orders: [['id', 'desc']]
        });

=> SELECT *  FROM `t_order`
    WHERE `sender` like '%111%'
    AND `receiver` like '%222%'
    AND `cn_orderno_list` like '%333%'
    AND (`sender11` like '%111%' OR `receiver22` like '%222%')
    AND `a` = 1
    AND `b` IN (11, 22)
    AND (`orderno` IS NULL AND `id` = 1
          AND `role` IN ('admin', 'guest')
          AND (`realname` like 'simba' OR `age` > '20' OR `nickname` like 'ace')
          AND (`createtime` > '2020-10-13' AND `createtime` < '2020-11-22')
        )

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, rows, 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

MySQL
- Pool
- Cluster
SQL Server
PostgreSQL

License

MIT

ali-rds ali-rds-x

co-wrap-all debug mysql pify

@everything-registry/sub-chunk-1118 @zalastax/nolb-ali-egg-mysql-x

3.4.1

3 years ago

3.4.0

3 years ago