const mysql = require('mysql-plus');const pool = mysql.createPool({ host: 'localhost', user: 'username', password: 'secret', database: 'my_db',});// Both `mysql` and `pool` are 100% compatible with the mysql modulemodule.exports = pool;
const db = require('./db');const User = require('./User');const express = require('express');const app = express();app.get('/user', async (req, res, next) => { try { const user = await User.insertAndSelectExample(); res.send(user); } catch (err) { next(err) }})// Sync the table schemas to the databasedb.sync((err) => { if (err) throw err; // Now the server can be safely started app.listen(/*...*/);});
Just like the original mysql.createPool()
method except it returns a 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, this object may also have a plusOptions property to configure the 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. 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.
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().)
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
One of safe, alter, or drop. This will override the migrationStrategy value from the config (but is still subject to the same restrictions in production environments).
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
pool.sync((err) => { if (err) throw err; // Now do something such as start an HTTP server});
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.
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.
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
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
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.
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()
Example: To fail a transaction using the done callback
// Call the `done` callback with a truthy value as the first argumentdone(error);
Example: To complete a transaction using the done callback
// Call the `done` callback with a falsy value as the first argument// and pass the results of the transaction as the second argumentdone(null, results);done(); // Passing results is not required
Example: Full example using the done callback
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.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 });
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".
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.
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
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
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// );
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
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)
userTable.delete((err, result) => { if (err) throw err; // all rows deleted :(});// DELETE FROM `user`;
The transaction connection that will be used to perform queries.
Returns: 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()
Example:
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.
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 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 are defined using the column property which is an object where the keys are column names and the values are column definitions of a certain type.
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.
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 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:
{ ts: ColTypes.timestamp(),}
would define a column with this 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:
{ ts: ColTypes.timestamp().notNull(), // equivalent to `ColTypes.timestamp().notNull().defaultCurrentTimestamp()`}
would define a column with this 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 for the column