Migrations

Are pretty stritforward, inspired by the ActiveRecord ones. Single migration is a js/coffeescript module which exports two methods: up and optional down, which perform and rollback some database operation. They get two arguments: prego instance (to perform database operations) and standard callback (first argument should be either null or Error object). Migration should call the callback when the operation is done. Typical migration might look something this:

exports.up = (client, done) ->
  client.query '''
  	create table users(
  		id serial PRIMARY KEY,
  		first_name VARCHAR(128),
  		last_name  VARCHAR(128),
  		created_at TIMESTAMP DEFAULT NOW(),
  		email     VARCHAR,
  		admin	  BOOLEAN default FALSE
  	);

  	CREATE INDEX ix_users_email ON users(email);

    CREATE TABLE orders(
           id serial primary key,
           person_id INTEGER NOT NULL REFERENCES persons ON DELETE CASCADE,
           name VARCHAR,
           qty INTEGER);

    CREATE INDEX ix_orders_persons ON orders(person_id);
  '''

exports.down = (db, done) ->
  db.query '''
    DROP TABLE orders;
    DROP TABLE users;
  '''

The client argument here is a PG client from node-postgres transaction, and you should use nothing but this client to address DB in the migration.

The good idea is to use postgres foreign key/references constraints for your data structure. The migrationm though, might have not just SQL statements but any code you need (importing data, changing it and so on).

Each migration is executed in a separate transaction and will not commit if up/down won't call done(null) - in which case it is assumed that migration is failed.

Migration files

Set of your migrations should be placed in a separated directory, which defaults to ./migrations. Each file should be named in a way that provides newer migrations be lexicographically greater than older ones. For example, you can use date-time prefix:

20120501T180001_creating_tables.coffee
20120517T221010_add_customers_table.js

or use a serail number prefix:

0001_initial_tables.js
0002_add_some_mode_indexes.coffee

To perform all pending migrations, execute in your project home directory:

pmigrate up

If your migrations are in the directory other than ./migrations

pmigrate up ./your_migrations_folder

All migrations will be executed one by one, unless an error occur. Executing stops on the first failed migrations, and database is left in the state before execution of the failed migration. Successfull migrations, if any, will be commited.

To rollback the last successfully executed migrations use

pmigrate down [migrations-directory]

This command tries to execute just one last migration. To perform rolled back migration(s) use again pmigrate up.

Programmatic interface to migrations

prego = require 'prego'

prego.migrate './tests/migrations', (err) ->
    # if !err all migrations are passed (does nothing if there is no new
    # migrations).

prego.rollback './tests/migrations', (err) ->
    # if !err then last migrations is successfully rolled back or there is
    # no passed migrations left (e.g. all are rolled back)

First argument is your migrations folder.

Insights

Migrations are stored in the _migraions table in yout database that is created as need. Its structure might be changed later and it is not advisory to use it direcly in any way. Please issue a request to add more functions if you need instead.