node-migrate

Migrate - A database agnostic migration system for Node.js

By Ryan Sandor Richards
SQLite 3 Contribution by Curtis Schlak

Introduction

Migrate is a tool that allows you to define database schema migrations with javascript. It borrows very heavily from the ruby migration system and contains many of the same features. If you are unfamiliar with how migrations work don't fret, just read on and everything will be explained!

Requirements

1. Node.js - http://github.com/ry/node
2. node-mysql - https://github.com/felixge/node-mysql
3. node-sqlite3 - https://github.com/developmentseed/node-sqlite3

Please note that at the current time we only support MySQL and SQLite 3 but other DBMS' are on their way (next up: Postgres).

Installation

1. Download the Migrate source - http://github.com/rsandor/node-migrate
2. Fill out the supplied "config.js"

Where, exactly, you include the migrate source in your project matters very little as long as both migrate.js and config.js are in the same directory and the node-mysql and migration paths in the configuration file are relative to the directory where the migrate.js file resides.
The configuration file has the following keys:

• dbms - The database management system to use (currently we only support a a value of either 'mysql' or 'sqlite3').
• migration_path - Relative path to the directory in which migrations are stored.
• mysql - Just a simple client configuration for mysql. Fill out your username, password, and any other information needed to connect to the database via node-mysql's Client class.
• sqlite3 - Just a simple client configuration for SQLite 3. Fill out the filename for the database to which you would like to connect.

How do I use migrate?

Once you have the configuration file filled you you can create a new migration from the command line:
node migrate.js create create_users_table
This command will create a blank migration and stick it in the migrations folder that you supplied in the configuration file. Once you fill out the migration's up and down functions you can then apply the migration to your schema like so:
node migrate.js migrate
That command will determine if there are any migrations that have not been applied and apply them sequentially until they are all done or one of them fails.
If you wish to roll back any migrations that's super simple too, just use:
node migrate.js rollback
By default this will roll back only a single migration, but you can provide a numeric parameter to tell it how many migrations you'd like it to roll back. For instance, here's how you would roll back five migrations:
node migrate.js rollback 5

What is a migration?

A migration is a programmatic way of defining incremental database schema changes. It has an "up" method for describing how to apply the changes, and a "down" method for removing them. Here is an example migration:

var create_users_table = new Migration({
  up: function() {
    this.create_table('users', function(t) {
      t.integer('id');
      t.string('email');
      t.string('password');
      t.primary_key('id');
    });
  },
  down: function() {
    this.drop_table('users');
  }
});

In the above migration the "up" function creates a table named "users" with three fields (id, email, and password) and a primary key on id. The "down" function reverses these changes and simply drops the entire "users" table.
When you run the migration it gets converted into a collection of database agnostic objects which are then translated into SQL for the appropriate DBMS.

What can I do in a migration?

The "up" and "down" methods of a migration support the exact same set of methods . This means you can create and destroy schema information in both methods. The Migration object supports the following methods:

create_table(name, body)

This method creates a table with the given name and passes the newly created table representation to the supplied body closure. From within the body closure one can execute methods on the table to add columns and indices. Here is a complete list of all the "table" methods available:

• t.column(name, type, options) - Creates a column with the given name, type and additional options. Additional options include: limit, not_null, precision, scale, and default_value. limit controls the number of bytes to use for the integer type, not_null is used to determine if the column is allowed to be null, precision and scale are used for the decimal data type, and default_value allows you to set the default value for the column.
• t.primary_key(name) - Sets the primary key for the table to the column with the given name.
• t.index(name) - Sets an index on the table for the column with the given name

Finally the body also contains shortcut functions for each abstract data-type tracked by Migrate. Each function has the form t.type(name, options) where name and options are as explained in the t.column method. Here's a complete list:

• string, text, integer, float, decimal, datetime, timestamp, time, date, binary, boolean

Example:

this.create_table('high_scores', function(t) {
    t.integer('id');
    t.string('name', {limit: 32});
    t.create('score', 'integer', {limit: 8})
    t.datetime('date');
    t.primary_key('id');
    t.index('name');
}); 

Producing SQL:

CREATE TABLE high_scores (
    id INT,
    name VARCHAR(32),
    score BIGINT,
    date DATETIME,
    PRIMARY KEY (id),
    INDEX (name)
);

drop_table(name)

Simply drops a table from the schema. Example:
this.drop_table('high_scores');
Producing SQL:
DROP_TABLE high_scores;

rename_table(old_name, new_name)

Renames a table. Example:
this.rename_table('high_scores', 'all_time_high_scores');
Producing SQL:
RENAME TABLE high_scores TO all_time_high_scores;

change_table(name, body)

Has all of the same functionality as create_table except it is used to modify existing tables and adds the following functionality to body method:

• t.rename(old_name, new_column) - Renames and alters a column.
• t.change(name, type, options) - Alters a column without changing its name.
• t.remove(name) - Removes a column from the table.
• t.remove_index(name) - Removes an index from the table.
• t.remove_primary_key() - Removes a primary key from the table.

Example:

this.change_table('all_time_high_scores', function(t) {
  t.remove_index('name');
  t.remove_primary_key();
  t.remove('date');
  t.date('date');
  t.rename('score' {
    name: 'high_score',
    type: 'integer',
    limit: 4
  });
  t.change('name', 'string' {limit: 128});
});

Producing SQL:

ALTER TABLE all_time_high_scores
  DROP INDEX (name),
  DROP PRIMARY KEY,
  DROP COLUMN 'date',
  ADD COLUMN date DATE,
  CHANGE COLUMN score high_score INT,
  MODIFY COLUMN name VARCHAR(128);

add_column(table_name, column_name, type, options)

Adds a column to a table. Example:
this.add_column('all_time_high_scores', 'comment', 'string', {limit: 512});
Producing SQL:
ALTER TABLE all_time_high_scores ADD COLUMN comment VARCHAR(512);

rename_column(table_name, column_name, new_column)

Renames and modifies a column in a table. Example:

this.rename('all_time_high_scores', 'high_score', {
  name: 'score',
  type: 'integer',
  limit: 8
});

Producing SQL:
ALTER TABLE all_time_high_scores CHANGE COLUMN high_score score BIGINT;

change_column(table_name, column_name, type, options)

Changes a column's definition. Example:
this.change_column('all_time_high_scores', 'comment', 'text');
Producing SQL:
ALTER TABLE all_time_high_scores MODIFY COLUMN comment TEXT;

remove_column(table_name, column_name)

Removes a column from a table. Example:
this.remove_column('all_time_high_scores', 'date');
Producing SQL:
ALTER TABLE all_time_high_scores DROP COLUMN date;

add_index(table_name, column_name, options)

Adds an index to a table. Example:
this.add_index('all_time_high_scores', 'id');
Producing SQL:
ALTER TABLE all_time_high_scores ADD INDEX (id);

remove_index(table_name, index_name)

Removes an index from a table. Example:
this.remove_index('id');
Producing SQL:
ALTER TABLE all_time_high_scores DROP INDEX (id);

execute(sql)

Executes arbitrary SQL. Example:
this.execute('insert into all_time_high_scores (name, score) values ('Ryan', 100000000);');
Producing SQL:
insert into all_time_high_scores (name, score) values ('Ryan', 100000000);

Outtro

So that about sums it up. Simple and easy ;). It's a very early alpha version so please don't hate on only having MySQL and SQLite 3 support! If you have a feature request feel free to send me a message and I'll try to get it in ASAP.
Thanks!

from https://github.com/rsandor/node-migrate