Migrations API - SQL

Below are examples of all the different migrations supported by db-migrate. Please note that not all migrations are supported by all databases. For example, SQLite does not support dropping columns.

createTable(tableName, columnSpec, callback)

Creates a new table with the specified columns.

Arguments

  • tableName - the name of the table to create
  • columnSpec - a hash of column definitions
  • callback(err) - callback that will be invoked after table creation

Examples

// with no table options
exports.up = function (db, callback) {
  db.createTable('pets', {
    id: { type: 'int', primaryKey: true, autoIncrement: true },
    name: 'string'  // shorthand notation
  }, callback);
}

// with table options
exports.up = function (db, callback) {
  db.createTable('pets', {
    columns: {
      id: { type: 'int', primaryKey: true, autoIncrement: true },
      name: 'string'  // shorthand notation
    },
    ifNotExists: true
  }, callback);
}

Column Specs

The following options are available on column specs

  • type - the column data type. Supported types can be found in db-migrate-shared/data_type.js
  • length - the column data length, where supported
  • primaryKey - true to set the column as a primary key. Compound primary keys are supported by setting the primaryKey option to true on multiple columns
  • autoIncrement - true to mark the column as auto incrementing
  • notNull - true to mark the column as non-nullable, omit it archive database default behavior and false to mark explicitly as nullable
  • unique - true to add unique constraint to the column
  • defaultValue - set the column default value. To set an expression (eg a function call) as the default value use this syntax: defaultValue: new String('uuid_generate_v4()')
  • foreignKey - set a foreign key to the column

Column ForeignKey Spec Examples

Note: Currently only supported in mysql and pg!

exports.up = function(db, callback) {

  //automatic mapping, the mapping key resolves to the column
  db.createTable( 'product_variant',
  {
      id:
      {
        type: 'int',
        unsigned: true,
        notNull: true,
        primaryKey: true,
        autoIncrement: true,
        length: 10
      },
      product_id:
      {
        type: 'int',
        unsigned: true,
        length: 10,
        notNull: true,
        foreignKey: {
          name: 'product_variant_product_id_fk',
          table: 'product',
          rules: {
            onDelete: 'CASCADE',
            onUpdate: 'RESTRICT'
          },
          mapping: 'id'
        }
      },
  }, callback );
};

exports.up = function(db, callback) {

  //explicit mapping
  db.createTable( 'product_variant',
  {
    id:
    {
      type: 'int',
      unsigned: true,
      notNull: true,
      primaryKey: true,
      autoIncrement: true,
      length: 10
    },
    product_id:
    {
      type: 'int',
      unsigned: true,
      length: 10,
      notNull: true,
      foreignKey: {
        name: 'product_variant_product_id_fk',
        table: 'product',
        rules: {
          onDelete: 'CASCADE',
          onUpdate: 'RESTRICT'
        },
        mapping: {
          product_id: 'id'
        }
      }
    },
  }, callback );
};

dropTable(tableName, [options,] callback)

Drop a database table

Arguments

  • tableName - name of the table to drop
  • options - table options
  • callback(err) - callback that will be invoked after dropping the table

Table Options

  • ifExists - Only drop the table if it already exists

renameTable(tableName, newTableName, callback)

Rename a database table

Arguments

  • tableName - existing table name
  • options - new table name
  • callback(err) - callback that will be invoked after renaming the table

addColumn(tableName, columnName, columnSpec, callback)

Add a column to a database table

Arguments

  • tableName - name of table to add a column to
  • columnName - name of the column to add
  • columnSpec - a hash of column definitions
  • callback(err) - callback that will be invoked after adding the column

Column spec is the same as that described in createTable

removeColumn(tableName, columnName, callback)

Remove a column from an existing database table

  • tableName - name of table to remove a column from
  • columnName - name of the column to remove
  • callback(err) - callback that will be invoked after removing the column

renameColumn(tableName, oldColumnName, newColumnName, callback)

Rename a column

Arguments

  • tableName - table containing column to rename
  • oldColumnName - existing column name
  • newColumnName - new name of the column
  • callback(err) - callback that will be invoked after renaming the column

changeColumn(tableName, columnName, columnSpec, callback)

Change the definition of a column

Arguments

  • tableName - table containing column to change
  • columnName - existing column name
  • columnSpec - a hash containing the column spec
  • callback(err) - callback that will be invoked after changing the column

addIndex(tableName, indexName, columns, [unique], callback)

Add an index

Arguments

  • tableName - table to add the index too
  • indexName - the name of the index
  • columns - an array of column names contained in the index
  • unique - whether the index is unique (optional, default false)
  • callback(err) - callback that will be invoked after adding the index

addForeignKey

Adds a foreign Key

Arguments

  • tableName - table on which the foreign key gets applied
  • referencedTableName - table where the referenced key is located
  • keyName - name of the foreign key
  • fieldMapping - mapping of the foreign key to referenced key
  • rules - ondelete, onupdate constraints
  • callback(err) - callback that will be invoked after adding the foreign key

Example

exports.up = function (db, callback)
{
  db.addForeignKey('module_user', 'modules', 'module_user_module_id_foreign',
  {
    'module_id': 'id'
  },
  {
    onDelete: 'CASCADE',
    onUpdate: 'RESTRICT'
  }, callback);
};

removeForeignKey

Arguments

  • tableName - table in which the foreign key should be deleted
  • keyName - the name of the foreign key
  • options - object of options, see below
  • callback - callback that will be invoked once the foreign key was deleted

Options

  • dropIndex (default: false) - deletes the index with the same name as the foreign key

Examples

//without options object
exports.down = function (db, callback)
{
  db.removeForeignKey('module_user', 'module_user_module_id_foreign', callback);
};

//with options object
exports.down = function (db, callback)
{
  db.removeForeignKey('module_user', 'module_user_module_id_foreign',
  {
    dropIndex: true,
  }, callback);
};

insert(tableName, columnNameArray, valueArray, callback)

Insert an item into a given column

Arguments

  • tableName - table to insert the item into
  • columnNameArray - the array existing column names for each item being inserted
  • valueArray - the array of values to be inserted into the associated column
  • callback(err) - callback that will be invoked once the insert has been completed.

removeIndex([tableName], indexName, callback)

Remove an index

Arguments

  • tableName - name of the table that has the index (Required for mySql)
  • indexName - the name of the index
  • callback(err) - callback that will be invoked after removing the index

runSql(sql, [params,] callback)

Run arbitrary SQL

Arguments

  • sql - the SQL query string, possibly with ? replacement parameters
  • params - zero or more ? replacement parameters
  • callback(err) - callback that will be invoked after executing the SQL

all(sql, [params,] callback)

Execute a select statement, even in dry run mode. Attention, only use this if you know what you're doing. This can cause you issues if you're utilizing the dry-run mode for testings. To execute sql queries always use runSql!

Arguments

  • sql - the SQL query string, possibly with ? replacement parameters
  • params - zero or more ? replacement parameters
  • callback(err, results) - callback that will be invoked after executing the SQL