mysql-plus

v0.16.2

Published

2 years ago

MySQL client for Node.js with methods for defining tables with auto-migration and making basic queries

Downloads

0High
0Medium
0Low

nwoltman

mysql plus database auto automatic migration schema table define sql query

mysql-plus

A MySQL client for Node.js that makes defining tables easy and automatically migrates table schemas.

This module extends the popular mysql module, so it is recommended that you read the mysql documentation, especially the sections on connection options, performing queries, escaping query values, and escaping query identifiers.

Installation
Usage Example
API
- Modules
  - mysql-plus
- Classes
- Info

Installation

npm install mysql-plus
# or
yarn add mysql-plus

Usage Example

db.js

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 module
module.exports = pool;

User.js

const db = require('./db');

const userTable = db.defineTable('user', {
  columns: {
    id: db.ColTypes.bigint().unsigned().notNull().primaryKey().autoIncrement(),
    email: db.ColTypes.varchar(255).notNull().unique(),
    name: db.ColTypes.varchar(63).notNull(),
  },
});

const User = {
  async insertAndSelectExample() {
    const result = await userTable.insert({email: '[email protected]', name: 'newuser'})
    const rows = await userTable.select('*', 'WHERE `id` = ?', [result.insertId]))
    console.log(rows); // [ { id: 1, email: '[email protected]', name: 'newuser' } ]
    return rows[0];
  }
};

module.exports = User;

app.js (Express example)

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 database
db.sync((err) => {
  if (err) throw err;
  // Now the server can be safely started
  app.listen(/*...*/);
});

API

Modules

Classes

Info

mysql-plus ⇐ mysql

This module.

Extends: mysql See: mysql

mysql-plus ⇐ mysql
- ~ColTypes
- ~KeyTypes
- ~createPool(config) ⇒ PoolPlus
- ~queryCallback : function

mysql-plus~ColTypes

A namespace that provides the column type methods used to define columns.

See: Column Types

Example:

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

Example:

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

Just like the original mysql.createPool() method except it returns a PoolPlus instance and accepts more options.

Returns: PoolPlus - A new PoolPlus instance.

Example:

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.

See: 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

PoolPlus ⇐ Pool
- instance
  - .ColTypes
  - .KeyTypes
  - .raw(sql) ⇒ Object
  - .basicTable(name) ⇒ MySQLTable
  - .defineTable(name, schema, [migrationStrategy]) ⇒ MySQLTable
  - .sync([cb]) ⇒ Promise
  - .pquery(sql, [values], [cb]) ⇒ Promise
  - .transaction(trxnHandler) ⇒ Promise
- inner
  - ~transactionHandler ⇒ Promise

poolPlus.ColTypes

A namespace that provides the column type methods used to define columns. The exact same thing as mysqlPlus.ColTypes. Just here for convenience.

See: Column Types

Example:

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. Just here for convenience.

See: Key Types

Example:

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().)

| 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

Example: Inserting a geometry Point

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

Simply returns an instance of MySQLTable for querying the table with the given name.

Returns: MySQLTable - A MySQLTable instance.

poolPlus.defineTable(name, schema, [migrationStrategy]) ⇒ MySQLTable

Defines a table to be created or updated in the database.

Returns: MySQLTable - A MySQLTable instance that has methods for performing queries on the table. See: Defining Table Schemas

Example:

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

pool.sync((err) => {
  if (err) throw err;
  // Now do something such as start an HTTP server
});

Example: With a promise

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.

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

Example:

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.

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.

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 argument
done(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 argument
done(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

The mysql module's Connection class extended with one extra method. Returned by mysql.createConnection() and pool.getConnection() and passed to 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.

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

Example:

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.To create an instance, use poolPlus.defineTable() or poolPlus.basicTable().

See: https://github.com/mysqljs/mysql#performing-queries

MySQLTable
- .name : string
- .schema : Object
- .pool : PoolPlus
- .trxn : ?Connection
- .select(columns, [sqlString], [values], [cb]) ⇒ Promise
- .exists(sqlString, [values], [cb]) ⇒ Promise
- .insert([data], [sqlString], [values], [cb]) ⇒ Promise
- .insertIfNotExists(data, keyColumns, [cb]) ⇒ Promise
- .update([data], [sqlString], [values], [cb]) ⇒ Promise
- .delete([sqlString], [values], [cb]) ⇒ Promise
- .query() ⇒ Promise
- .transacting(trxn) ⇒ MySQLTable

mySQLTable.name : string

The table's name (as passed to poolPlus.defineTable()).

mySQLTable.schema : Object

The table's schema (as passed to poolPlus.defineTable()).

mySQLTable.pool : PoolPlus

The PoolPlus instance that created this table.

mySQLTable.trxn : ?Connection

The transaction connection that created this table from a call to table.transacting(trxn).

mySQLTable.select(columns, [sqlString], [values], [cb]) ⇒ Promise

Selects data from the table.

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

userTable.select('*', (err, rows) => {
  if (err) throw err;
  // rows contains all data for all users
});

// SELECT * FROM `user`;

Example: Select specific columns

userTable.select(['email', 'name'], 'WHERE `points` > 10000', (err, rows) => {
  if (err) throw err;
  console.log(rows); // -> [{email: '[email protected]', name: 'John Doe'}, ...]
});

// SELECT `email`, `name` FROM `user` WHERE `points` > 10000;

Example: Select with placeholders

userTable.select(['email'], 'WHERE `id` = ?', [5])
  .then(rows => console.log(rows)); // -> [{email: '[email protected]'}]

// SELECT `email` FROM `user` WHERE `id` = 5;


userTable.select('??', 'WHERE ?', ['email', {id: 5}])
  .then(rows => console.log(rows)); // -> [{email: '[email protected]'}]

// SELECT `email` FROM `user` WHERE `id` = 5;

Example: Select columns with aliases

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

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.

Returns: ?Promise - If the cb parameter is omitted, a promise that will resolve with either true or false is returned.

Example: Using a promise

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

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.

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

userTable.insert({email: '[email protected]', name: 'John Doe'})
  .then(result => result.affectedRows); // 1

// INSERT INTO `user`
// SET `email` = '[email protected]', `name` = 'John Doe';

Example: Insert or update

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

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

const users = [
  [1, '[email protected]', 'John Doe'],
  [2, '[email protected]', 'Jane Brown'],
];
userTable.insert([users])
  .then(result => result.insertId); // 2 (ID of the last inserted row)

// INSERT INTO `user` VALUES
// (1, '[email protected]', 'John Doe'),
// (2, '[email protected]', 'Jane Brown');

Example: Bulk insert with specified columns

const users = [
  ['[email protected]', 'John Doe'],
  ['[email protected]', 'Jane Brown'],
];
userTable.insert([['email', 'name'], users])
  .then(result => result.affectedRows); // 2

// INSERT INTO `user` (`email`, `name`) VALUES
// ('[email protected]', 'John Doe'),
// ('[email protected]', '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).

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 protected]', name: 'John Doe'}, ['email'])
  .then(result => result.affectedRows);
  // 0 - If there was a row with `email` = '[email protected]' (row not inserted)
  // 1 - If there wasn't (row was inserted)

// INSERT INTO `user` (`email`, `name`)
// SELECT '[email protected]', 'John Doe'
// FROM DUAL WHERE NOT EXISTS (
//   SELECT 1 FROM `user`
//   WHERE `email` = '[email protected]' 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
// );

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.

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

userTable.update({email: '[email protected]'}, 'WHERE `id` = ?', [5])
  .then(result => result.changedRows); // 1

// UPDATE `user` SET `email` = '[email protected]'
// WHERE `id` = 5;

Example: With only the sqlString argument

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)

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.

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

mySQLTable.query() ⇒ Promise

Exactly the same as pool.pquery().

mySQLTable.transacting(trxn) ⇒ MySQLTable

Returns a new MySQLTable instance that will perform queries using the provided transaction connection.

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

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.

Example:

{
  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 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.

{
  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.

{
  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.

{
  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:

{
  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 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:

{
  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

mysql.ColTypes and pool.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 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

Compatible types:

geometry
point
linestring
polygon
multipoint
multilinestring
multipolygon
geometrycollection

Key Types

mysql.KeyTypes and pool.KeyTypes both expose the following methods for defining table keys:

index(keyPart [, ...otherKeyParts]) - Creates a regular index
uniqueIndex(keyPart [, ...otherKeyParts]) - Creates a unique index
spatialIndex(columnName) - Creates a spatial index
fulltextIndex(columnName, [...otherColumns]) - Creates a fulltext index
foreignKey(columnName [, ...otherColumns]) - Creates a foreign key constraint

Example:

{
  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 in a query):

{
  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<string>)
onDelete(action) - Sets the foreign key’s ON DELETE action, where action is one of: RESTRICT, CASCADE, SET NULL, NO ACTION
onUpdate(action) - Sets the foreign key’s ON UPDATE action (with the same options as onDelete)
cascade() - Short for: .onDelete('CASCADE').onUpdate('CASCADE')

Example:

{
  columns: {
    id: /* ... */,
    uid: /* ... */,
    userID: /* ... */,
    thingOne: /* ... */,
    thingTwo: /* ... */,
  },
  keys: [
    KeyTypes.foreignKey('id').references('other_table', 'id'),
    KeyTypes.foreignKey('uid').references('other_table', 'uid').cascade(),
    KeyTypes.foreignKey('userID').references('user', 'id').onDelete('CASCADE').onUpdate('SET NULL'),
    KeyTypes.foreignKey('thingOne', 'thingTwo').references('thing_table', ['one', 'two']),
  ]
}

Note: Foreign keys don't define indexes, but constraints. When defining foreign keys, the columns used in the key should also have an index.

Indexes required for the example above:

{
  primaryKey: 'id',
  keys: [
    KeyTypes.uniqueIndex('uid'),
    KeyTypes.uniqueIndex('userID'),
    KeyTypes.index('thingOne', 'thingTwo'),
  ]
}

Prefix Lengths

PRIMARY, INDEX, and UNIQUE keys on char, varchar, binary, varbinary, blob, and text columns may include a key prefix length.

{
  columns: {
    id: ColTypes.char(50).notNull(),
    uid: ColTypes.varchar(100).notNull(),
    description: ColTypes.text(),
  },
  primaryKey: 'id(10)',
  keys: [
    KeyTypes.uniqueIndex('uid(30)'),
    KeyTypes.index('description(50)'),
  ]

Published

Vulnerabilities

Links

Maintainers

Keywords

Readme

mysql-plus

Table of Contents

Installation

Usage Example

db.js

User.js

app.js (Express example)

API

Modules

Classes

Info

mysql-plus ⇐ mysql

mysql-plus~ColTypes

mysql-plus~KeyTypes

mysql-plus~createPool(config) ⇒ PoolPlus

mysql-plus~queryCallback : function

PoolPlus ⇐ Pool

poolPlus.ColTypes

poolPlus.KeyTypes

poolPlus.raw(sql) ⇒ Object

poolPlus.basicTable(name) ⇒ MySQLTable

poolPlus.defineTable(name, schema, [migrationStrategy]) ⇒ MySQLTable

poolPlus.sync([cb]) ⇒ Promise

poolPlus.pquery(sql, [values], [cb]) ⇒ Promise

poolPlus.transaction(trxnHandler) ⇒ Promise

PoolPlus~transactionHandler ⇒ Promise

Connection

connection.pquery(sql, [values], [cb]) ⇒ Promise

MySQLTable

mySQLTable.name : string

mySQLTable.schema : Object

mySQLTable.pool : PoolPlus

mySQLTable.trxn : ?Connection

mySQLTable.select(columns, [sqlString], [values], [cb]) ⇒ Promise

mySQLTable.exists(sqlString, [values], [cb]) ⇒ Promise

mySQLTable.insert([data], [sqlString], [values], [cb]) ⇒ Promise

mySQLTable.insertIfNotExists(data, keyColumns, [cb]) ⇒ Promise

mySQLTable.update([data], [sqlString], [values], [cb]) ⇒ Promise

mySQLTable.delete([sqlString], [values], [cb]) ⇒ Promise

mySQLTable.query() ⇒ Promise

mySQLTable.transacting(trxn) ⇒ MySQLTable

Migration Strategies

safe

alter

Known Migrations That Will Not Work

drop

Defining Table Schemas

Columns

Primary Key

Keys

Table Options

Column Types

ColumnDefinition

NumericColumnDefinition

TextColumnDefinition

UpdatableTimeColumnDefinition

TimestampColumnDefinition

NULL Timestamps

Timestamps' DEFAULT value

GeometryColumnDefinition

Key Types

Foreign Keys

Prefix Lengths