Sequelizev1.3.0introduced migrations. With those mechanism you can transform your existing database into another state and vice versa. Those state transitions are saved in migration files, which describe the way how to get to the new state and how to revert the changes in order to get back to the old state.
In order to run migrations, sequelize comes with a handy binary file which can setup your project and run migrations. The following snippet shows the possible things:
sequelize -h
sequelize --help
# prints the help
sequelize -V
sequelize --version
# prints the version
sequelize -i
sequelize --init
# creates a migration folder
# creates a config folder
# saves a config.json inside the config folder
sequelize -e [environment]
sequelize --env [environment]
# Use the passed environment. Default: development
sequelize -i -f
sequelize --init --force
# forced creation of migration and config folder
# existing data will be deleted first
sequelize -m
sequelize --migrate
# needs a valid config.json
# runs pending migrations
# saves successfully executed migrations inside the database
sequelize -m -u
sequelize --migrate --undo
# needs a valid config.json
# reverts the last successfull migration
# when there were multiple executed migrations, all of them are reverted
sequelize -c [migration-name]
sequelize --create-migration [migration-name]
# creates the migrations folder
# creates a file with current timestamp + migration-name
# migration-name has the default 'unnamed-migration'
sequelize -p [path]
sequelize --migration-path [path]
# Defines the path to the migration directory.
sequelize -o [path]
sequelize --options-path [path]
# Defines the path to the options file.
# The options file (.js or .json) needs to return an object.
# Keys / values references cli options
# e.g.: { migrationPath: 'db/migrations' }
sequelize --config [path]
# Defines the path to the configuration file.
sequelize --coffee
# Enables coffeescript support.
The following skeleton shows a typical migration file. All migrations are expected to be located in a folder calledmigrationsat the very top of the project. Sequelize1.4.1added the possibility to let the sequelize binary generate a migration skeleton. See the aboves section for more details.
module.exports = {
up: function(migration, DataTypes, done) {
// logic for transforming into the new state
done() // sets the migration as finished
},
down: function(migration, DataTypes, done) {
// logic for reverting the changes
done() // sets the migration as finished
}
}The passedmigrationobject can be used to modify the database. TheDataTypesobject stores the available data types such asSTRINGorINTEGER. The third parameter is a callback function which needs to be called once everything was executed. The first parameter of the callback function can be used to pass a possible error. In that case, the migration will be marked as failed. Here is some code:
module.exports = {
up: function(migration, DataTypes, done) {
migration.dropAllTables().complete(done)
// equals:
migration.dropAllTables().complete(function(err) {
if (err) {
done(err)
} else {
done(null)
}
})
}
}The available methods of the migration object are the following.
Using themigrationobject describe before, you will have access to most of already introduced functions. Furthermore there are some other methods, which are designed to actually change the database schema.
This method allows creation of new tables. It is allowed to pass simple or complex attribute definitions. You can define the encoding of the table and the table's engine via options
migration.createTable(
'nameOfTheNewTable',
{
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
createdAt: {
type: DataTypes.DATE
},
updatedAt: {
type: DataTypes.DATE
},
attr1: DataTypes.STRING,
attr2: DataTypes.INTEGER,
attr3: {
type: DataTypes.BOOLEAN,
defaultValue: false,
allowNull: false
}
},
{
engine: 'MYISAM', // default: 'InnoDB'
charset: 'latin1' // default: null
}
)This method allows deletion of an existing table.
migration.dropTable('nameOfTheExistingTable')This method allows deletion of all existing tables in the database.
migration.dropAllTables()This method allows renaming of an existing table.
migration.renameTable('Person', 'User')This method returns the name of all existing tables in the database.
migration.showAllTables().success(function(tableNames) {})This method returns an array of hashes containing information about all attributes in the table.
migration.describeTable('Person').success(function(attributes) {
/*
attributes will be something like:
{
name: {
type: 'VARCHAR(255)', // this will be 'CHARACTER VARYING' for pg!
allowNull: true,
defaultValue: null
},
isBetaMember: {
type: 'TINYINT(1)', // this will be 'BOOLEAN' for pg!
allowNull: false,
defaultValue: false
}
}
*/
})This method allows adding columns to an existing table. The data type can be simple or complex.
migration.addColumn(
'nameOfAnExistingTable',
'nameOfTheNewAttribute',
DataTypes.STRING
)
// or
migration.addColumn(
'nameOfAnExistingTable',
'nameOfTheNewAttribute',
{
type: DataTypes.STRING,
allowNull: false
}
)This method allows deletion of a specific column of an existing table.
migration.removeColumn('Person', 'signature')This method changes the meta data of an attribute. It is possible to change the default value, allowance of null or the data type. Please make sure, that you are completely describing the new data type. Missing information are expected to be defaults.
migration.changeColumn(
'nameOfAnExistingTable',
'nameOfAnExistingAttribute',
DataTypes.STRING
)
// or
migration.changeColumn(
'nameOfAnExistingTable',
'nameOfAnExistingAttribute',
{
type: DataTypes.FLOAT,
allowNull: false,
default: 0.0
}
)This methods allows renaming attributes.
migration.renameColumn('Person', 'signature', 'sig')This methods creates indexes for specific attributes of a table. The index name will be automatically generated if it is not passed via in the options (see below).
// This example will create the index person_firstname_lastname
migration.addIndex('Person', ['firstname', 'lastname'])// This example will create a unique index with the name SuperDuperIndex using the optional 'options' field.
// Possible options:
// - indicesType: UNIQUE|FULLTEXT|SPATIAL
// - indexName: The name of the index. Default is __
// - parser: For FULLTEXT columns set your parser
// - indexType: Set a type for the index, e.g. BTREE. See the documentation of the used dialect
migration.addIndex(
'Person',
['firstname', 'lastname'],
{
indexName: 'SuperDuperIndex',
indicesType: 'UNIQUE'
}
) This method deletes an existing index of a table.
migration.removeIndex('Person', 'SuperDuperIndex')
// or
migration.removeIndex('Person', ['firstname', 'lastname'])If you need to interact with the migrator within your code,
you can easily achieve that via sequelize.getMigrator.
You can specify the path to your migrations as well as a pattern
which represents the files that contain the migrations.
var migrator = sequelize.getMigrator({
path: process.cwd() + '/database/migrations',
filesFilter: /\.coffee$/
})Once you have a migrator object, you can run its migration
with migrator.migrate. By default, this will
execute all the up methods within your pending migrations.
If you want to rollback a migration, just call it like this:
migrator
.migrate({ method: 'down' })
.success(function() {
// The migrations have been executed!
})