mysqlThis module.
PoolA class that extends the mysql module's Pool class with the ability to define tables
and perform queries and transactions using promises.
The mysql module's Connection class extended with one extra method. Returned by
mysql.createConnection()
and pool.getConnection() and
passed to transactionHandler.
A class that provides convenient methods for performing queries.
To create
an instance, use poolPlus.defineTable() or
poolPlus.basicTable().
mysql
This module.
**Extends**: mysql
**See**: [mysql](https://github.com/mysqljs/mysql#mysql)
* [mysql-plus](#module_mysql-plus) ⇐ mysql
* [~ColTypes](#module_mysql-plus..ColTypes)
* [~KeyTypes](#module_mysql-plus..KeyTypes)
* [~createPool(config)](#module_mysql-plus..createPool) ⇒ [PoolPlus](#PoolPlus)
* [~queryCallback](#module_mysql-plus..queryCallback) : function
---
### mysql-plus~ColTypes
A namespace that provides the column type methods used to define columns.
**See**: [Column Types](#column-types)
**Example**:
```js
const mysql = require('mysql-plus');
const pool = mysql.createPool(config);
const userTable = pool.defineTable('user', {
columns: {
id: mysql.ColTypes.bigint().unsigned().notNull().primaryKey(),
created: mysql.ColTypes.datetime(),
}
});
```
---
### mysql-plus~KeyTypes
A namespace that provides the key type methods used to define keys.
**See**: [Key Types](#key-types)
**Example**:
```js
const mysql = require('mysql-plus');
const pool = mysql.createPool(config);
const userTable = pool.defineTable('user', {
columns: {
id: mysql.ColTypes.bigint().unsigned().notNull().primaryKey(),
uid: mysql.ColTypes.varchar(32).notNull(),
created: mysql.ColTypes.datetime(),
},
keys: [
mysql.KeyTypes.uniqueIndex('uid'),
mysql.KeyTypes.index('created'),
],
});
```
---
### mysql-plus~createPool(config) ⇒ [PoolPlus](#PoolPlus)
Just like the original [`mysql.createPool()`](https://github.com/mysqljs/mysql#pooling-connections)
method except it returns a [`PoolPlus`](#PoolPlus) instance and accepts more options.
| Param | Type | Default | Description |
|:--- |:--- |:--- |:--- |
| config | Object | | A configuration object defining MySQL connection options. In addition to the, possible [mysql connection options](https://github.com/mysqljs/mysql#connection-options), this object may also have a `plusOptions` property to configure the [`PoolPlus`](#PoolPlus) instance, it returns. |
| [config.plusOptions] | Object | | An optional configuration object that may have the following properties: |
| [config.plusOptions.migrationStrategy] | string | | One of `safe`, `alter`, or `drop`. Please see the migration strategies documentation [here](#migration-strategies). Defaults to `safe` in production and `alter` everywhere else. |
| [config.plusOptions.allowAlterInProduction] | boolean | false | Setting this to `true` will allow `alter` to be used as a migration strategy in production environments. |
| [config.plusOptions.debug] | boolean | false | If set to `true`, all of the SQL operations that will be performed will be printed to the console. |
**Returns**: [PoolPlus](#PoolPlus) - A new `PoolPlus` instance.
**Example**:
```js
const mysql = require('mysql-plus');
const pool = mysql.createPool({
host: 'example.org',
user: 'me',
password: 'secret',
plusOptions: {
migrationStrategy: 'safe',
allowAlterInProduction: true,
debug: true,
},
});
```
---
### mysql-plus~queryCallback : function
A function called with the results of a query.
| Param | Type | Description |
|:--- |:--- |:--- |
| error | ?Error | An `Error` object if an error occurred; `null` otherwise. |
| results | Array | Object | The results of the query. |
| fields | Array.<Object> | Information about the returned results' fields (if any). |
**See**: [https://github.com/mysqljs/mysql#performing-queries](https://github.com/mysqljs/mysql#performing-queries)
---
## PoolPlus ⇐ Pool
A class that extends the `mysql` module's `Pool` class with the ability to define tables
and perform queries and transactions using promises.
**Extends**: Pool
**See**: [Pool](https://github.com/mysqljs/mysql#pooling-connections)
* [PoolPlus](#PoolPlus) ⇐ Pool
* _instance_
* [.ColTypes](#PoolPlus+ColTypes)
* [.KeyTypes](#PoolPlus+KeyTypes)
* [.raw(sql)](#PoolPlus+raw) ⇒ Object
* [.basicTable(name)](#PoolPlus+basicTable) ⇒ [MySQLTable](#MySQLTable)
* [.defineTable(name, schema, [migrationStrategy])](#PoolPlus+defineTable) ⇒ [MySQLTable](#MySQLTable)
* [.sync([cb])](#PoolPlus+sync) ⇒ Promise
* [.pquery(sql, [values], [cb])](#PoolPlus+pquery) ⇒ Promise
* [.transaction(trxnHandler)](#PoolPlus+transaction) ⇒ Promise
* _inner_
* [~transactionHandler](#PoolPlus..transactionHandler) ⇒ Promise
---
### poolPlus.ColTypes
A namespace that provides the column type methods used to define columns.
The exact same thing as [`mysqlPlus.ColTypes`](#module_mysql-plus..ColTypes).
Just here for convenience.
**See**: [Column Types](#column-types)
**Example**:
```js
const pool = mysql.createPool(config);
const ColTypes = pool.ColTypes;
const userTable = pool.defineTable('user', {
columns: {
id: ColTypes.bigint().unsigned().notNull().primaryKey(),
created: ColTypes.datetime(),
}
});
```
---
### poolPlus.KeyTypes
A namespace that provides the column type methods used to define keys.
The exact same thing as [`mysqlPlus.KeyTypes`](#module_mysql-plus..KeyTypes).
Just here for convenience.
**See**: [Key Types](#key-types)
**Example**:
```js
const pool = mysql.createPool(config);
const {ColTypes, KeyTypes} = pool;
const userTable = pool.defineTable('user', {
columns: {
id: ColTypes.bigint().unsigned().notNull().primaryKey(),
uid: ColTypes.varchar(32).notNull(),
created: ColTypes.datetime(),
},
keys: [
KeyTypes.uniqueIndex('uid'),
KeyTypes.index('created'),
],
});
```
---
### poolPlus.raw(sql) ⇒ Object
Wraps the provided SQL string in an object that will prevent the string from being escaped
when it is used as a data-object value or `?` placeholder replacement.
(The same as [`mysql.raw()`](https://github.com/mysqljs/mysql#escaping-query-values).)
| Param | Type | Description |
|:--- |:--- |:--- |
| sql | string | SQL that should not be escaped. |
**Returns**: Object - An object that is turned into the provided `sql` string when `mysql` attempts to escape it.
**See**: [(mysql) Escaping query values](https://github.com/mysqljs/mysql#escaping-query-values)
**Example**: Inserting a geometry Point
```js
placeTable.insert({
placeId: 'ChIJK2f',
coordinates: pool.raw('POINT(-80.5204, 43.4642)'),
});
// or
placeTable.insert(
'SET `placeId` = ?, `coordinates` = ?',
['ChIJK2f', pool.raw('POINT(-80.5204, 43.4642)')]
);
// INSERT INTO `place`
// SET `placeId` = 'ChIJK2f', `coordinates` = POINT(-80.5204, 43.4642);
```
---
### poolPlus.basicTable(name) ⇒ [MySQLTable](#MySQLTable)
Simply returns an instance of [`MySQLTable`](#MySQLTable)
for querying the table with the given `name`.
| Param | Type | Description |
|:--- |:--- |:--- |
| name | string | The name of the table. |
**Returns**: [MySQLTable](#MySQLTable) - A `MySQLTable` instance.
---
### poolPlus.defineTable(name, schema, [migrationStrategy]) ⇒ [MySQLTable](#MySQLTable)
Defines a table to be created or updated in the database.
| Param | Type | Description |
|:--- |:--- |:--- |
| name | string | The name of the table. |
| schema | Object | An object that defines the table's schema. See the [Defining Table Schemas](#defining-table-schemas) section. |
| [migrationStrategy] | string | One of `safe`, `alter`, or `drop`. This will override the `migrationStrategy` value from the [`config`](#module_mysql-plus..createPool) (but is still subject to the same restrictions in production environments). |
**Returns**: [MySQLTable](#MySQLTable) - A `MySQLTable` instance that has methods for performing queries on the table.
**See**: [Defining Table Schemas](#defining-table-schemas)
**Example**:
```js
const userTable = pool.defineTable('user', {
columns: {
id: pool.ColTypes.bigint().unsigned().notNull().primaryKey().autoIncrement(),
email: pool.ColTypes.varchar(255).notNull().unique(),
created: pool.ColTypes.datetime(),
}
});
```
---
### poolPlus.sync([cb]) ⇒ Promise
Syncs the defined tables to the database by creating new tables and dropping
or migrating existing tables (depending on the migration setting).
Generally, this should only be called once when starting up a server.
__Warning:__ If an error occurs while syncing, the database will be in an unknown state.
Always keep a backup of your database so you can restore it to the latest working state.
| Param | Type | Description |
|:--- |:--- |:--- |
| [cb] | function | A callback that is called once all defined table schemas have been synced to the database. If an error occured, the first argument passed to the callback will be the error object. |
**Returns**: ?Promise - If `cb` is not provided, a promise will be returned.
**Example**: With a callback
```js
pool.sync((err) => {
if (err) throw err;
// Now do something such as start an HTTP server
});
```
**Example**: With a promise
```js
pool.sync()
.then(() => {
// Success
}, (err) => {
// Error
});
```
---
### poolPlus.pquery(sql, [values], [cb]) ⇒ Promise
The same as the `query` method on the original mysql `Pool` except when not passed a
callback it returns a promise that resolves with the results of the query.
| Param | Type | Description |
|:--- |:--- |:--- |
| sql | string | Object | An SqlString or options object. |
| [values] | Array | Values to replace placeholders in the SqlString. |
| [cb] | [queryCallback](#module_mysql-plus..queryCallback) | An optional callback that gets called with the results of the query. |
**Returns**: ?Promise - If the `cb` parameter is omitted, a promise that will resolve with the results
of the query is returned.
**See**: [https://github.com/mysqljs/mysql#performing-queries](https://github.com/mysqljs/mysql#performing-queries)
**Example**:
```js
pool.pquery('SELECT * FROM `books` WHERE `author` = "David"')
.then((results) => {
// results will contain the results of the query
})
.catch((error) => {
// error will be the Error that occurred during the query
});
```
---
### poolPlus.transaction(trxnHandler) ⇒ Promise
Begins a transaction and provides a connection to use to make queries during the transaction.
__Note:__ Be aware that there are commands in MySQL that can cause an implicit commit, as described
in [the MySQL documentation](http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html).
| Param | Type | Description |
|:--- |:--- |:--- |
| trxnHandler | [transactionHandler](#PoolPlus..transactionHandler) | A function that, given a transaction connection, will make queries and then end the transaction. |
**Returns**: Promise - A promise that is resolved with the results of the transaction (the value
passed to the `done()` callback or the result of the last returned promise) or is
rejected with the error that caused the transaction to fail.
**Example**: Using the `done` callback
```js
pool.transaction((trxn, done) => {
trxn.query('INSERT INTO `animals` VALUES ("dog")', (err, result) => {
if (err) return done(err);
trxn.query(
'INSERT INTO `pets` (`type`,`name`) VALUES (?, "Rover")',
[result.insertId],
done
);
});
}).then(result => {
// result is the result of inserting "Rover" into `pets`
}).catch(err => {
// If this is called then the inserts will have been rolled back
// (so "dog" will not be in the `animals` table)
});
```
**Example**: Returning a promise
```js
pool.transaction((trxn) => {
return trxn.pquery('INSERT INTO `animals` (`type`) VALUES ("dog")')
.then(result => trxn.pquery(
'INSERT INTO `pets` (`typeID`,`name`) VALUES (?, "Rover")',
[result.insertId]
));
}).then(result => {
// result is the result of inserting "Rover" into `pets`
}).catch(err => {
// An error occurred and the inserts have been rolled back
});
```
---
### PoolPlus~transactionHandler ⇒ Promise
A function that will make queries during a transaction.
| Param | Type | Description |
|:--- |:--- |:--- |
| trxn | [Connection](#Connection) | The transaction connection. |
| [done] | function | A callback that can be used to end the transaction. |
**Returns**: ?Promise - If not using the `done` callback, this function must return a promise.
If the promise resolves, the transaction will be committed, and if it rejects, the
transaction will be rolled back. If this function does not return a promise, the
`done` callback must be used or else the transaction will not be committed and
the transaction connection will never be released.
**See**: [`poolPlus.transaction()`](#PoolPlus+transaction)
**Example**: To fail a transaction using the `done` callback
```js
// Call the `done` callback with a truthy value as the first argument
done(error);
```
**Example**: To complete a transaction using the `done` callback
```js
// Call the `done` callback with a falsy value as the first argument
// and pass the results of the transaction as the second argument
done(null, results);
done(); // Passing results is not required
```
**Example**: Full example using the `done` callback
```js
function trxnHandler(trxn, done) {
trxn.query('INSERT INTO `animals` (`type`) VALUES ("dog")', (err, animalsResult) => {
if (err) return done(err);
trxn.query(
'INSERT INTO `pets` (`typeID`,`name`) VALUES (?, "Rover")',
[animalsResult.insertId],
(err, petsResult) => {
if (err) return done(err);
done(null, {animalsResult, petsResult});
}
);
});
}
```
---
## Connection
The `mysql` module's `Connection` class extended with one extra method. Returned by
[`mysql.createConnection()`](https://github.com/mysqljs/mysql#establishing-connections)
and [`pool.getConnection()`](https://github.com/mysqljs/mysql#pooling-connections) and
passed to [`transactionHandler`](#PoolPlus..transactionHandler).
---
### connection.pquery(sql, [values], [cb]) ⇒ Promise
The same as the `query` method except when not passed a callback it returns
a promise that resolves with the results of the query.
| Param | Type | Description |
|:--- |:--- |:--- |
| sql | string | Object | An SqlString or options object. |
| [values] | Array | Values to replace placeholders in the SqlString. |
| [cb] | [queryCallback](#module_mysql-plus..queryCallback) | An optional callback that gets called with the results of the query. |
**Returns**: ?Promise - If the `cb` parameter is omitted, a promise that will resolve with the results
of the query is returned.
**See**: [https://github.com/mysqljs/mysql#performing-queries](https://github.com/mysqljs/mysql#performing-queries)
**Example**:
```js
connection.pquery('SELECT * FROM `books` WHERE `author` = "David"')
.then((results) => {
// results will contain the results of the query
})
.catch((error) => {
// error will be the Error that occurred during the query
});
```
---
## MySQLTable
A class that provides convenient methods for performing queries.string
* [.schema](#MySQLTable+schema) : Object
* [.pool](#MySQLTable+pool) : [PoolPlus](#PoolPlus)
* [.trxn](#MySQLTable+trxn) : ?[Connection](#Connection)
* [.select(columns, [sqlString], [values], [cb])](#MySQLTable+select) ⇒ Promise
* [.exists(sqlString, [values], [cb])](#MySQLTable+exists) ⇒ Promise
* [.insert([data], [sqlString], [values], [cb])](#MySQLTable+insert) ⇒ Promise
* [.insertIfNotExists(data, keyColumns, [cb])](#MySQLTable+insertIfNotExists) ⇒ Promise
* [.update([data], [sqlString], [values], [cb])](#MySQLTable+update) ⇒ Promise
* [.delete([sqlString], [values], [cb])](#MySQLTable+delete) ⇒ Promise
* [.query()](#MySQLTable+query) ⇒ Promise
* [.transacting(trxn)](#MySQLTable+transacting) ⇒ [MySQLTable](#MySQLTable)
---
### mySQLTable.name : string
The table's name (as passed to [`poolPlus.defineTable()`](#PoolPlus+defineTable)).
---
### mySQLTable.schema : Object
The table's schema (as passed to [`poolPlus.defineTable()`](#PoolPlus+defineTable)).
---
### mySQLTable.pool : [PoolPlus](#PoolPlus)
The `PoolPlus` instance that created this table.
---
### mySQLTable.trxn : ?[Connection](#Connection)
The transaction connection that created this table from a call
to [`table.transacting(trxn)`](#MySQLTable+transacting).
---
### mySQLTable.select(columns, [sqlString], [values], [cb]) ⇒ Promise
Selects data from the table.
| Param | Type | Description |
|:--- |:--- |:--- |
| columns | Array.<string> | string | An array of columns to select or a custom `SELECT` string. |
| [sqlString] | string | SQL to be appended to the query after the `FROM table` clause. |
| [values] | Array | Values to replace the placeholders in `sqlString` and `columns`. |
| [cb] | [queryCallback](#module_mysql-plus..queryCallback) | A callback that gets called with the results of the query. |
**Returns**: ?Promise - If the `cb` parameter is omitted, a promise that will resolve with the results
of the query is returned.
**Example**: Select all columns
```js
userTable.select('*', (err, rows) => {
if (err) throw err;
// rows contains all data for all users
});
// SELECT * FROM `user`;
```
**Example**: Select specific columns
```js
userTable.select(['email', 'name'], 'WHERE `points` > 10000', (err, rows) => {
if (err) throw err;
console.log(rows); // -> [{email: 'email@example.com', name: 'John Doe'}, ...]
});
// SELECT `email`, `name` FROM `user` WHERE `points` > 10000;
```
**Example**: Select with placeholders
```js
userTable.select(['email'], 'WHERE `id` = ?', [5])
.then(rows => console.log(rows)); // -> [{email: 'email@example.com'}]
// SELECT `email` FROM `user` WHERE `id` = 5;
userTable.select('??', 'WHERE ?', ['email', {id: 5}])
.then(rows => console.log(rows)); // -> [{email: 'email@example.com'}]
// SELECT `email` FROM `user` WHERE `id` = 5;
```
**Example**: Select columns with aliases
```js
userTable.select('`name` AS `fullName`', 'WHERE `points` > 10000')
.then(rows => console.log(rows)); // -> [{fullName: 'John Doe'}, ...]
// SELECT `name` AS `fullName` FROM `user` WHERE `points` > 10000;
```
**Example**: Select using a function
```js
userTable.select('COUNT(*) AS `highScorers`', 'WHERE `points` > 10000')
.then(rows => console.log(rows)); // -> [{highScorers: 27}]
// SELECT COUNT(*) AS `highScorers` FROM `user` WHERE `points` > 10000;
```
---
### mySQLTable.exists(sqlString, [values], [cb]) ⇒ Promise
Checks if rows in the table exist.
| Param | Type | Description |
|:--- |:--- |:--- |
| sqlString | string | SQL that specifies rows to check for existence.Array | Values to replace the placeholders in `sqlString`. |
| [cb] | [queryCallback](#module_mysql-plus..queryCallback) | A callback that gets called with the results of the query where the `results` will be either `true` or `false`. |
**Returns**: ?Promise - If the `cb` parameter is omitted, a promise that will
resolve with either `true` or `false` is returned.
**Example**: Using a promise
```js
userTable.exists('WHERE `id` > 10')
.then(exists => console.log(exists)); // true or false
// SELECT EXISTS (
// SELECT 1 FROM `user`
// WHERE `id` > 10 # This is where `sqlString` gets inserted
// LIMIT 1
// )
```
**Example**: Using a callback and the `values` argument
```js
userTable.exists('WHERE `id` = ?', [10], (err, exists) => {
if (err) throw err;
console.log(exists); // true or false
});
```
---
### mySQLTable.insert([data], [sqlString], [values], [cb]) ⇒ Promise
Inserts data into a new row in the table.
__Note:__ The `data` and `sqlString` arguments are individually
optional but at least one of them must be specified.
| Param | Type | Description |
|:--- |:--- |:--- |
| [data] | Object | Array | An object of (column name)-(data value) pairs or an array containing either 1) an array of arrays of data values or 2) an array of column names and the data array from 1). |
| [sqlString] | string | SQL to be appended to the query. If `data` is provided, it is appended directly after the formatted data, otherwise it is appended after `"INSERT INTO tableName"`. |
| [values] | Array | Values to replace the placeholders in `sqlString`. |
| [cb] | [queryCallback](#module_mysql-plus..queryCallback) | A callback that gets called with the results of the query. |
**Returns**: ?Promise - If the `cb` parameter is omitted, a promise that will resolve with the results
of the query is returned.
**Example**: Insert a new user
```js
userTable.insert({email: 'email@example.com', name: 'John Doe'})
.then(result => result.affectedRows); // 1
// INSERT INTO `user`
// SET `email` = 'email@example.com', `name` = 'John Doe';
```
**Example**: Insert or update
```js
const data = {id: 5, points: 100};
// If duplicate key (id), add the points
const onDuplicateKeySQL = 'ON DUPLICATE KEY UPDATE `points` = `points` + ?';
userTable.insert(data, onDuplicateKeySQL, [data.points])
.then(result => result.affectedRows); // 1 if inserted, 2 if updated
// INSERT INTO `user` SET `id` = 5, `points` = 100
// ON DUPLICATE KEY UPDATE `points` = `points` + 100;
```
**Example**: With only the `sqlString` argument
```js
placeTable.insert('SET `location` = POINT(0, 0)');
// INSERT INTO `place` SET `location` = POINT(0, 0);
placeTable.insert('(`location`) VALUES (POINT(?, ?))', [8, 2]);
// INSERT INTO `place` (`location`) VALUES (POINT(8, 2));
```
**Example**: Bulk insert
```js
const users = [
[1, 'john@email.com', 'John Doe'],
[2, 'jane@email.com', 'Jane Brown'],
];
userTable.insert([users])
.then(result => result.insertId); // 2 (ID of the last inserted row)
// INSERT INTO `user` VALUES
// (1, 'john@email.com', 'John Doe'),
// (2, 'jane@email.com', 'Jane Brown');
```
**Example**: Bulk insert with specified columns
```js
const users = [
['john@email.com', 'John Doe'],
['jane@email.com', 'Jane Brown'],
];
userTable.insert([['email', 'name'], users])
.then(result => result.affectedRows); // 2
// INSERT INTO `user` (`email`, `name`) VALUES
// ('john@email.com', 'John Doe'),
// ('jane@email.com', 'Jane Brown');
```
---
### mySQLTable.insertIfNotExists(data, keyColumns, [cb]) ⇒ Promise
Inserts a new row into the table if there are no existing rows in
the table that have the same values for the specified columns.
This is useful because if the row is not inserted, the table's
`AUTO_INCREMENT` value is not increased (unlike when an insert
fails because of a unique key constraint).
| Param | Type | Description |
|:--- |:--- |:--- |
| data | Object | An object mapping column names to data values to insert. |
| keyColumns | Array.<string> | The names of columns in the `data` object. If there is already a row in the table with the same values for these columns as the values being inserted, the data will not be inserted. |
| [cb] | [queryCallback](#module_mysql-plus..queryCallback) | A callback that gets called with the results of the query. |
**Returns**: ?Promise - If the `cb` parameter is omitted, a promise that will
resolve with the results of the query is returned.
**Example**: Insert a new user if a user with the same email does not exist
```js
userTable.insertIfNotExists({email: 'email@example.com', name: 'John Doe'}, ['email'])
.then(result => result.affectedRows);
// 0 - If there was a row with `email` = 'email@example.com' (row not inserted)
// 1 - If there wasn't (row was inserted)
// INSERT INTO `user` (`email`, `name`)
// SELECT 'email@example.com', 'John Doe'
// FROM DUAL WHERE NOT EXISTS (
// SELECT 1 FROM `user`
// WHERE `email` = 'email@example.com' LIMIT 1
// );
```
**Example**: Insert without escaping some values
```js
const data = {
placeId: 'ChIJK2f-X1bxK4gRkB0jxyh7AwU',
type: 'city',
location: mysql.raw('POINT(-80.5204096, 43.4642578)'),
};
placeTable.insertIfNotExists(data, ['placeId', 'type'])
.then(result => result.affectedRows);
// 0 - If there was a row with the same `placeId` and `type` (row not inserted)
// 1 - If there wasn't (row was inserted)
// INSERT INTO `place` (`placeId`, `type`, `location`)
// SELECT 'ChIJK2f-X1bxK4gRkB0jxyh7AwU', 'city', POINT(-80.5204096, 43.4642578)
// FROM DUAL WHERE NOT EXISTS (
// SELECT 1 FROM `place`
// WHERE `placeId` = 'ChIJK2f-X1bxK4gRkB0jxyh7AwU' AND `type` = 'city' LIMIT 1
// );
```
---
### mySQLTable.update([data], [sqlString], [values], [cb]) ⇒ Promise
Updates data in the table.
__Note:__ The `data` and `sqlString` arguments are individually
optional but at least one of them must be specified.
| Param | Type | Description |
|:--- |:--- |:--- |
| [data] | Object | An object of (column name)-(data value) pairs that define the new column values. |
| [sqlString] | string | SQL to be appended to the query after the `SET data` clause or immediately after `SET ` if `data` is omitted. |
| [values] | Array | Values to replace the placeholders in `sqlString` (and/or `data`). |
| [cb] | [queryCallback](#module_mysql-plus..queryCallback) | A callback that gets called with the results of the query. |
**Returns**: ?Promise - If the `cb` parameter is omitted, a promise that will resolve with the results
of the query is returned.
**Example**: With both the `data` and `sqlString` arguments
```js
userTable.update({email: 'updated@email.com'}, 'WHERE `id` = ?', [5])
.then(result => result.changedRows); // 1
// UPDATE `user` SET `email` = 'updated@email.com'
// WHERE `id` = 5;
```
**Example**: With only the `sqlString` argument
```js
userTable.update("`word` = CONCAT('prefix', `word`)");
// UPDATE `user` SET `word` = CONCAT('prefix', `word`);
userTable.update('`points` = `points` + ? WHERE `winner` = ?', [10, 1]);
// UPDATE `user` SET `points` = `points` + 10
// WHERE `winner` = 1;
```
**Example**: With only the `data` argument (updates all rows)
```js
userTable.update({points: 1000});
// UPDATE `user` SET `points` = 1000;
userTable.update({points: mysql.raw('`points` + 10')});
// UPDATE `user` SET `points` = `points` + 10;
```
---
### mySQLTable.delete([sqlString], [values], [cb]) ⇒ Promise
Deletes data from the table.
| Param | Type | Description |
|:--- |:--- |:--- |
| [sqlString] | string | SQL to be appended to the query after the `FROM table` clause. |
| [values] | Array | Values to replace the placeholders in `sqlString`. |
| [cb] | [queryCallback](#module_mysql-plus..queryCallback) | A callback that gets called with the results of the query. |
**Returns**: ?Promise - If the `cb` parameter is omitted, a promise that will resolve with the results
of the query is returned.
**Example**: Delete specific rows
```js
userTable.delete('WHERE `spammer` = 1')
.then(result => result.affectedRows); // The number of deleted spammers
// DELETE FROM `user` WHERE `spammer` = 1;
```
**Example**: Delete all rows (you probably don't want to do this)
```js
userTable.delete((err, result) => {
if (err) throw err;
// all rows deleted :(
});
// DELETE FROM `user`;
```
---
### mySQLTable.query() ⇒ Promise
Exactly the same as [`pool.pquery()`](#PoolPlus+pquery).
---
### mySQLTable.transacting(trxn) ⇒ [MySQLTable](#MySQLTable)
Returns a new `MySQLTable` instance that will perform queries using the provided transaction connection.
| Param | Type | Description |
|:--- |:--- |:--- |
| trxn | [Connection](#Connection) | The transaction connection that will be used to perform queries. |
**Returns**: [MySQLTable](#MySQLTable) - A new `MySQLTable` instance that will perform queries using the provided transaction
connection instead of the `PoolPlus` instance that was used to create the original instance.
**See**: [`pool.transaction()`](#PoolPlus+transaction)
**Example**:
```js
const animalsTable = pool.defineTable('animals', schema);
const petsTable = pool.defineTable('pets', schema);
pool.transaction((trxn) => {
return animalsTable.transacting(trxn)
.insert({type: 'dog'})
.then(result =>
petsTable.transacting(trxn)
.insert({typeID: result.insertId, name: 'Rover'})
);
}).then(result => {
// result is the result of inserting "Rover" into the pets table
}).catch(err => {
// An error occurred during the transaction
});
```
---
## Migration Strategies
The possible migration strategies are as follows:
+ `safe` - default in a production environment (`NODE_ENV === 'production'`)
+ `alter` - default in a development environment
+ `drop`
In addition to being the default in a production environment, the `safe` strategy is the only allowed strategy in production. This means that if `alter` or `drop` are used anywhere to configure connections or tables, they will be ignored and `safe` will be used instead. However, it is possible to override this behavior to allow the `alter` strategy in production by setting the `allowAlterInProduction` option to `true` in the [Pool configuration](#mysql-pluscreatepoolconfig--poolplus).
### safe
Only allows newly-defined tables to be created. Existing tables are never changed in any way.
### alter
Specifies that newly-defined tables will be created, existing tables that are no longer defined will be dropped, and existing tables that have a different definition from what is found in the database will be migrated with minimal data-loss.
**To rename table columns**, the column's old name must be specified in the [column definition](#columndefinition) with the `.oldName('name')` method. If it is not, the column will be dropped and all of the data that was in that column will be lost.
**Note:** It is up to you to understand how changes to an existing table might affect the data. For example, changing a DOUBLE column to a FLOAT will cause the precision of the value to be reduced so some significant digits may be lost (i.e. `1.123456789` would be reduced to `1.12346`). Furthermore, some changes to tables cannot be done and will cause an error. An example of this would be adding a column with the `NOT NULL` attribute to a non-empty table without specifying a default value.
##### Known Migrations That Will Not Work
+ Altering a column in a certain way when a different table references that column as a foreign key.
+ Normally this isn't a problem if the column type is being changed (since you'd also need to change the column type in the referencing table and `mysql-plus` can handle this case), but if only the column in the first table needs to change (such as modifying it's `AUTO_INCREMENT` value), the operation will fail because of the foreign key constraint.
+ **Workaround 1:** Manually remove the foreign key constraint from the referencing table (using SQL) before syncing.
+ **Workaround 2:** Remove the foreign key definition from the referencing table schema (i.e. by commenting it out) before syncing the column change, then restore the foreign key definition and re-sync.
### drop
All defined tables will be dropped and recreated.
## Defining Table Schemas
A schema is defined by a JavaScript object with certain properties. For `mysql-plus`, the schema properties can be broken down into four main types:
+ [Columns](#columns)
+ [Primary Key](#primary-key)
+ [Keys](#keys)
+ [Table Options](#table-options)
### Columns
Columns are defined using the `column` property which is an object where the keys are column names and the values are [column definitions](#columndefinition) of a certain [type](#column-types).
**Example:**
```js
{
columns: {
id: pool.ColTypes.bigint().unsigned().notNull().primaryKey().autoIncrement(),
email: pool.ColTypes.varchar(255).notNull().unique(),
points: pool.ColTypes.int().unsigned().default(0),
}
}
```
See the [Column Types](#column-types) section for all possible column types and attributes that can be defined.
### Primary Key
`string | string[]`
The table’s primary key can be defined with the `primaryKey` property.
```js
{
columns: {
id: pool.ColTypes.int().unsigned().notNull(),
name: pool.ColTypes.varchar(255).notNull(),
},
primaryKey: 'id'
}
```
An array can be used to define a multi-column primary key.
```js
{
columns: {
id: pool.ColTypes.int().unsigned().notNull(),
name: pool.ColTypes.varchar(255).notNull(),
},
primaryKey: ['id', 'name']
}
```
Primary keys for string columns may include a key [prefix length](#prefix-lengths).
```js
{
columns: {
id: pool.ColTypes.varchar(100).unsigned().notNull(),
},
primaryKey: 'id(20)'
}
```
### Keys
Keys can be defined with the `keys` property, which is an array of [`KeyTypes`](#key-types):
```js
{
columns: {
id: pool.ColTypes.int().unsigned().notNull(),
accountID: pool.ColTypes.int().unsigned().notNull(),
email: pool.ColTypes.varchar(255).notNull(),
location: pool.ColTypes.point().notNull(),
description: pool.ColTypes.text(),
},
keys: [
pool.KeyTypes.index('accountID'),
pool.KeyTypes.uniqueIndex('email'),
pool.KeyTypes.spatialIndex('location'),
pool.KeyTypes.fulltextIndex('description'),
pool.KeyTypes.foreignKey('accountID').references('account', 'id'),
]
}
```
See the [Key Types](#key-types) section for information on the different types of keys that can be defined.
### Table Options
These schema properties configure table-level options. The options currently supported are as follows:
+ `engine` - Specify the storage engine for the table (such as InnoDB or MyISAM)
+ `autoIncrement` - The initial `AUTO_INCREMENT` value for the table
+ `charset` - Specify a default character set for the table
+ `collate` - Specify a default collation for the table
+ `compression` - The compression algorithm used for page level compression (MySQL 5.7 + InnoDB only)
+ `rowFormat` - Defines the physical format in which the rows are stored
**Example:**
```js
{
columns: {...},
engine: 'MyISAM',
autoIncrement: 5000000000
charset: 'utf8mb4',
collate: 'utf8mb4_unicode_520_ci',
compression: 'LZ4',
rowFormat: 'COMPACT',
}
```
**Note:** After explicitly defining a table option in a schema, if you remove it from the schema and resync your table definitions, the table option will not change in the database. To go back to the default value for the table option, you'll need to explicitly define it on the schema and resync the table (or manually change it on the command line), and then you may remove it from the schema.
## Column Types
+ [ColumnDefinition](#columndefinition)
+ [NumericColumnDefinition](#numericcolumndefinition)
+ [TextColumnDefinition](#textcolumndefinition)
+ [UpdatableTimeColumnDefinition](#updatabletimecolumndefinition)
+ [TimestampColumnDefinition](#timestampcolumndefinition)
+ [GeometryColumnDefinition](#geometrycolumndefinition)
[`mysql.ColTypes`](#module_mysql-plus..ColTypes) and [`pool.ColTypes`](#PoolPlus+ColTypes) both expose the following methods:
+ `tinyint([m])`
+ `smallint([m])`
+ `mediumint([m])`
+ `int([m])`
+ `integer([m])` - synonym for `int`
+ `bigint([m])`
+ `float([m [, d]])`
+ `double([m [, d]])`
+ `decimal([m [, d]])`
+ `dec([m [, d]])` - synonym for `decimal`
+ `numeric([m [, d]])` - synonym for `decimal`
+ `fixed([m [, d]])` - synonym for `decimal`
+ `bit([m])`
+ `bool()` - synonym for `tinyint(1)`
+ `boolean()` - synonym for `tinyint(1)`
+ `date()`
+ `datetime([m])`
+ `timestamp([m])`
+ `time([m])`
+ `year()`
+ `char([m])`
+ `varchar(m)`
+ `text([m])`
+ `tinytext()`
+ `mediumtext()`
+ `longtext()`
+ `binary([m])`
+ `varbinary(m)`
+ `blob([m])`
+ `tinyblob()`
+ `mediumblob()`
+ `longblob()`
+ `enum(...values)`
+ `set(...values)`
+ `json()`
+ `geometry()`
+ `point()`
+ `linestring()`
+ `polygon()`
+ `multipoint()`
+ `multilinestring()`
+ `multipolygon()`
+ `geometrycollection()`
All of these methods return a `ColumnDefinition` class.
#### ColumnDefinition
This class is what is used to define the column's attributes. These attributes can be set using the following methods:
+ `notNull()` - Adds the `NOT NULL` attribute
+ `default(value)` - Sets the column's `DEFAULT` value
+ Examples:
+ `.default('Hello')` produces `DEFAULT 'Hello'`
+ `.default(null)` produces `DEFAULT NULL`
+ __Note:__ `*blob`, `*text`, `json`, and geometry columns cannot be assigned a default value other than `null`.
+ `primaryKey()` - Declares the column to be the table's primary key
+ `unique()` - Creates a unique index for the column
+ `index()` - Creates an index for the column
+ `oldName(name: string)` - The previous/current column name. If a column with this name exists, it will be renamed to the column name associated with the column defintion so that the data in that column will not be lost.
All `ColumnDefinition` methods return the `ColumnDefinition`, so they are chainable.
Additionally, certain column types have type-specific methods. These are as follows:
#### NumericColumnDefinition
Methods:
+ `unsigned()` - Adds the `unsigned` attribute
+ `zerofill()` - Adds the `zerofill` attribute
+ `autoIncrement()` - Adds the `AUTO_INCREMENT` attribute
Compatible types:
+ `tinyint`
+ `smallint`
+ `mediumint`
+ `int`
+ `integer`
+ `bigint`
+ `float`
+ `double`
+ `decimal`
+ `dec`
+ `numeric`
+ `fixed`
#### TextColumnDefinition
Methods:
+ `charset(value)` - Sets the column's character set
+ `collate(value)` - Sets the column's collation
+ `fulltextIndex()` - Creates a [fulltext index](https://dev.mysql.com/doc/en/innodb-fulltext-index.html) for the column (excluding `enum` and `set` columns)
Compatible types:
+ `char`
+ `varchar`
+ `text`
+ `tinytext`
+ `mediumtext`
+ `longtext`
+ `enum`
+ `set`
#### UpdatableTimeColumnDefinition
Methods:
+ `defaultCurrentTimestamp()` - Adds the `DEFAULT CURRENT_TIMESTAMP` attribute
+ `onUpdateCurrentTimestamp()` - Adds the `ON UPDATE CURRENT_TIMESTAMP` attribute
Compatible types:
+ `datetime`
+ `timestamp`
#### TimestampColumnDefinition
Compatible types:
+ `timestamp`
There aren't any extra methods on this type, but there are some things to be aware of with `timestamp` columns:
##### NULL Timestamps
Normally, timestamp columns are `NOT NULL` by default, however, mysql-plus defines timestamp columns to be `NULL` by default to keep column definition semantics consistent. Therefore, the following column definition:
```js
{
ts: ColTypes.timestamp(),
}
```
would define a column with this SQL:
```sql
`ts` timestamp NULL DEFAULT NULL
```
##### Timestamps' DEFAULT value
If a timestamp column is defined with the `notNull()` method, the column's `DEFAULT` value will be set to `CURRENT_TIMESTAMP`. So the following:
```js
{
ts: ColTypes.timestamp().notNull(), // equivalent to `ColTypes.timestamp().notNull().defaultCurrentTimestamp()`
}
```
would define a column with this SQL:
```sql
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
```
Normally if the `DEFAULT` is unspecified, MySQL uses `CURRENT_TIMESTAMP` as the `DEFAULT` value of only the first timestamp column and `'0000-00-00 00:00:00'` for subsequent columns, but `mysql-plus` uses `CURRENT_TIMESTAMP` for all timestamp columns for consistency.
#### GeometryColumnDefinition
Methods:
+ `spatialIndex()` - Creates a [spatial index](https://dev.mysql.com/doc/en/create-index.html#create-index-spatial) for the column
Compatible types:
+ `geometry`
+ `point`
+ `linestring`
+ `polygon`
+ `multipoint`
+ `multilinestring`
+ `multipolygon`
+ `geometrycollection`
## Key Types
[`mysql.KeyTypes`](#module_mysql-plus..KeyTypes) and [`pool.KeyTypes`](#PoolPlus+KeyTypes) both expose the following methods for defining table keys:
+ `index(keyPart [, ...otherKeyParts])` - Creates a regular [index](https://dev.mysql.com/doc/en/create-index.html)
+ `uniqueIndex(keyPart [, ...otherKeyParts])` - Creates a [unique index](https://dev.mysql.com/doc/en/create-index.html#create-index-unique)
+ `spatialIndex(columnName)` - Creates a [spatial index](https://dev.mysql.com/doc/en/create-index.html#create-index-spatial)
+ `fulltextIndex(columnName, [...otherColumns])` - Creates a [fulltext index](https://dev.mysql.com/doc/en/innodb-fulltext-index.html)
+ `foreignKey(columnName [, ...otherColumns])` - Creates a [foreign key constraint](https://dev.mysql.com/doc/en/create-table-foreign-keys.html)
**Example:**
```js
{
keys: [
pool.KeyTypes.index('accountID'),
pool.KeyTypes.uniqueIndex('email'),
pool.KeyTypes.spatialIndex('location'),
pool.KeyTypes.fulltextIndex('description'),
pool.KeyTypes.foreignKey('accountID').references('account', 'id'),
// Multi-column keys
pool.KeyTypes.uniqueIndex('accountID', 'email'),
pool.KeyTypes.foreignKey('userID', 'accountID').references('user', ['id', 'accountID']),
]
}
```
All key types have a `name` method that can be used to customize the key’s name (helpful if you need to use an [index hint](https://dev.mysql.com/doc/en/index-hints.html) in a query):
```js
{
keys: [
pool.KeyTypes.index('accountID').name('account_key'),
pool.KeyTypes.uniqueIndex('email').name('email_key'),
pool.KeyTypes.spatialIndex('location').name('location_key'),
pool.KeyTypes.fulltextIndex('description').name('description_key'),
pool.KeyTypes.foreignKey('accountID').references('account', 'id').name('account_foreign_key'),
]
}
```
### Foreign Keys
Foreign keys have the following additional methods:
+ `references(tableName, columns)` - Sets the name of the reference table (`string`) and the referenced columns (`string|Array