@eggjs/rds (英语)
一个RDS客户端. RDS,关系数据库服务. 与亚马逊相当 RDS 软件。 。 。 .
支助 MySQL 只有协议.
使用量
创建 RDS 实例
import { RDSClient } from '@eggjs/rds';
const db = new RDSClient({
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,
// Set asyncLocalStorage manually for transaction
// connectionStorage: new AsyncLocalStorage(),
// If create multiple RDSClient instances with the same connectionStorage, use this key to distinguish between the instances
// connectionStorageKey: 'datasource',
// The timeout for connecting to the MySQL server. (Default: 500 milliseconds)
// connectTimeout: 500,
// The timeout for waiting for a connection from the connection pool. (Default: 500 milliseconds)
// So max timeout for get a connection is (connectTimeout + poolWaitTimeout)
// poolWaitTimeout: 500,
});
插入
- 插入一行
const row = {
name: 'fengmk2',
otherField: 'other field value',
createdAt: db.literals.now, // `now()` on db server
// ...
};
const result = await db.insert('table-name', row);
console.log(result);
{ fieldCount: 0,
affectedRows: 1,
insertId: 3710,
serverStatus: 2,
warningCount: 2,
message: '',
protocol41: true,
changedRows: 0 }
- 插入多行
将在交易和自动执行下执行.
const 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
// ...
},
// ...
];
const results = await 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
const row = {
id: 123,
name: 'fengmk2',
otherField: 'other field value',
modifiedAt: db.literals.now, // `now()` on db server
};
const result = await 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
const row = {
name: 'fengmk2',
otherField: 'other field value',
modifiedAt: db.literals.now, // `now()` on db server
};
const result = await 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
const 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: 'm@fengmk2_2.com',
otherField: 'other field value 2',
modifiedAt: db.literals.now, // `now()` on db server
}]
const result = await 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属性
const options = [{
row: {
email: '[email protected]',
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',
}
}]
const result = await 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 }
获取
- 排队
const row = await db.get('table-name', { name: 'fengmk2' });
=> SELECT * FROM `table-name` WHERE `name` = 'fengmk2'
选择
- 选择所有行
const rows = await db.select('table-name');
=> SELECT * FROM `table-name`
- 选择带有条件的行
const rows = await 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
删除
- 以条件删除
const result = await db.delete('table-name', {
name: 'fengmk2'
});
=> DELETE FROM `table-name` WHERE `name` = 'fengmk2'
计数
- 从表格中计数到条件
const count = await db.count('table-name', {
type: 'javascript'
});
=> SELECT COUNT(*) AS count FROM `table-name` WHERE `type` = 'javascript';
交易
开始交易、 承诺或回滚
const tran = await db.beginTransaction();
try {
await tran.insert(table, row1);
await tran.update(table, row2);
await tran.commit();
} catch (err) {
// error, rollback
await tran.rollback(); // rollback call won't throw err
throw err;
}
范围交易
API : (英语) async beginTransactionScope(scope)
在范围中运行的所有查询都将在同一交易下进行. 我们会自动为您承诺或退后.
const result = await db.beginTransactionScope(async conn => {
// don't commit or rollback by yourself
await conn.insert(table, row1);
await conn.update(table, row2);
return { success: true };
});
// if error throw on scope, will auto rollback
单位 Promise.all 实例中,并行开始交易程序将创建孤立的交易.
const result = await Promise.all([
db.beginTransactionScope(async conn => {
// commit and success
}),
db.beginTransactionScope(async conn => {
// throw err and rollback
}),
])
原始查询
- 无参数查询
const rows = await db.query('SELECT * FROM your_table LIMIT 100');
console.log(rows);
- 查询数组参数
const rows = await db.query('SELECT * FROM your_table WHERE id=?', [ 123 ]);
console.log(rows);
- 用对象参数查询
const rows = await db.query('SELECT * FROM your_table WHERE id=:id', { id: 123 });
console.log(rows);
自定义查询生命圈
db.beforeQuery((sql: string) => {
// change sql string
return `/* add custom format here */ ${sql}`;
});
db.afterQuery((sql: string, result: any, execDuration: number, err?: Error) => {
// handle logger here
});
APIs 密码
*表示此函数可以生成 .
IO 查询
- aync 查询( sql[, 值)
- aync 查询 One( sql) [, 值]
- aync 选择(表,选项)
- async 获取(表格,在哪里,选项)
- aync 插入(表,行,选项)
- aync 更新(表、行、选项)
- aync 更新 Rows(表,选项)
- 自动删除( 表格, 其中)
- async 计数(表,在哪里)
交易助手
- async 开始交易( )
- aync 开始交易范围( 镜片)
用户
- 出局( 值, 字符串对象, 时间区)
- caveId( 值, 禁止合格)
- 格式( sql, 值, 字符串对象, 时区)
文学
await db.insert('user', {
name: 'fengmk2',
createdAt: db.literals.now,
});
=>
INSERT INTO `user` SET `name` = 'fengmk2', `createdAt` = now()
自定义字元
const session = new db.literals.Literal('session()');
类别关系
+-----------+ +----------------+
| RDSClient +-- beginTransaction() --> + RDSTransaction |
+--+----+---+ +----+----+------+
| | getConnection() .conn | |
| | +---------------+ | |
| +-------->+ RDSConnection +<--------+ |
| +-------+-------+ |
| | extends |
| v |
| extends +-------+-------+ extends |
+------------->+ Operator +<-------------+
| query() |
+---------------+
给当地的 def
运行套接字来开始测试 Mysql 服务
docker compose -f docker-compose.yml up -d
# if you run the first time, should wait for ~20s to let mysql service init started
运行单元测试
npm test
停止测试 Mysql 服务
docker compose -f docker-compose.yml down
许可证
贡献者
制造的 贡献者-img。 。 。 .