# @ostai/mysql

> The altered version of ali-rds

Latest version **1.0.7** (published 2019-09-29) · MIT license · 0 weekly downloads

## Install

```sh
npm install @ostai/mysql
pnpm add @ostai/mysql
yarn add @ostai/mysql
bun add @ostai/mysql
```

## Health

**Score 15/100 (F)** — status: abandoned.

Positive: no vulnerabilities.

Warnings: low downloads; no types; no esm support.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 1.0.7 |
| Published | 2019-09-29 |
| First published | 2018-07-05 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | none |
| Module format | CommonJS |
| Node | >= 4.0.0 |
| Dependencies | 4 |
| Unpacked size | 23.8 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 0 |
| Maintainers | kael |
| Keywords | ali-rds, mysql, rds, client |

## Links

- npm: https://www.npmjs.com/package/@ostai/mysql
- Repository: https://github.com/kaelzhang/ostai-mysql
- Issues: https://github.com/kaelzhang/ostai-mysql/issues
- npm.io page: https://npm.io/package/@ostai/mysql

## Dependencies (4)

- [pify](https://npm.io/package/pify.md) ^4.0.1
- [debug](https://npm.io/package/debug.md) ^4.1.1
- [mysql](https://npm.io/package/mysql.md) ^2.17.1
- [co-wrap-all](https://npm.io/package/co-wrap-all.md) ^1.0.0

## Alternatives

- [launchdarkly-js-client-sdk](https://npm.io/package/launchdarkly-js-client-sdk.md) — 2.5M weekly downloads
- [@elastic/elasticsearch](https://npm.io/package/@elastic/elasticsearch.md) — 2.1M weekly downloads
- [@c8y/client](https://npm.io/package/@c8y/client.md) — 15.3K weekly downloads
- [@signaldb/maverickjs](https://npm.io/package/@signaldb/maverickjs.md) — 1.7K weekly downloads
- [@bbc/http-transport-cache](https://npm.io/package/@bbc/http-transport-cache.md) — 1.2K weekly downloads

## Recent versions

- 1.0.7 (latest) — 2019-09-29
- 1.0.6 — 2018-07-31
- 1.0.5 — 2018-07-06
- 1.0.4 — 2018-07-05
- 1.0.3 — 2018-07-05
- 1.0.2 — 2018-07-05
- 1.0.1 — 2018-07-05
- 1.0.0 — 2018-07-05

## README

# @ostai/mysql

[![NPM version][npm-image]][npm-url]
[![build status][travis-image]][travis-url]
[![Test coverage][codecov-image]][codecov-url]
[![David deps][david-image]][david-url]

[npm-image]: https://img.shields.io/npm/v/@ostai/mysql.svg?style=flat-square
[npm-url]: https://npmjs.org/package/@ostai/mysql
[travis-image]: https://img.shields.io/travis/kaelzhang/ostai-mysql.svg?style=flat-square
[travis-url]: https://travis-ci.org/kaelzhang/ostai-mysql
[codecov-image]: https://codecov.io/github/kaelzhang/ostai-mysql/coverage.svg?branch=master
[codecov-url]: https://codecov.io/github/kaelzhang/ostai-mysql?branch=master
[david-image]: https://img.shields.io/david/kaelzhang/ostai-mysql.svg?style=flat-square
[david-url]: https://david-dm.org/kaelzhang/ostai-mysql

The altered version of ali-rds

# What's different

- **Fixes tons of failing tests**
- **Adds a new option**: `options.updates: string | Array<string>` to `db.insert(table, values, options)` which generates the additional `ON DUPLICATE KEY UPDATE` query suffix.

## options.updates `string | Array<string>`

----

# RDS Usage

RDS, Relational Database Service. Equal to well know Amazon [RDS](http://aws.amazon.com/rds/).
Support `MySQL`, `SQL Server` and `PostgreSQL`.

## MySQL Usage

### Create RDS instance

```js
const rds = require('@ostai-mysql');

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

```js
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.

```js
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`

```js
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`

```js
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 }
```

### Get

- Get a row

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

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

### Select

- Select all rows

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

=> SELECT * FROM `table-name`
```

- Select rows with condition

```js
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

```js
let result = yield db.delete('table-name', {
  name: 'fengmk2'
});

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

### Count

- Get count from a table with condition

```js
let count = yield db.count('table-name', {
  type: 'javascript'
});

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

### Transactions

beginTransaction, commit or rollback

```js
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.

```js
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.

```js
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

```js
let rows = yield db.query('SELECT * FROM your_table LIMIT 100');
console.log(rows);
```

- Query with array arguments

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

- Query with object arguments

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

#### Transactions

- *beginTransaction()
- *beginTransactionScope(scope)

### Utils

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

### Literals

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

=>

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

#### Custom Literal

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

## TODO

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

## License

[MIT](LICENSE)

---
_Source: https://npm.io/package/@ostai/mysql · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._