Migrations

Tabel provides support for migrations using knex.js. To use db migrations cli tool in your project, do the following

  • Create a file migrate.js in your project root
  • Paste the following in that file:

migrate.js

1
2
3
4
const migrate = require('tabel/lib/migrate');
const ormConfig = require('orm/config'); // the same config as shown in "Setting up"

migrate(ormConfig);
  • Next, add the following to the scripts tag in your package.json:

    1
    2
    3
    4
    5
    "scripts": {
    ...
    "migrate": "NODE_PATH=. node migrate.js",
    ...
    }
  • Now run npm run migrate


Commands

  1. make
  2. latest
  3. rollback
  4. version
  5. reset
  6. refresh

make

npm run migrate make <MigrationName>

Makes a new migration with a file-name of the format <Timestamp>_<MigrationName>.js in a folder named migrations/. This folder will be created at project root level if it doesn’t already exist. This file is created using a stub file which looks like this:

1
2
3
4
5
6
7
8
9
function up(knex) {

}

function down(knex) {

}

module.exports = {up, down};

Once the migration file is created, you can fill up the up and down functions with the changes that you need to do to the schema, and their respective reversal operations. You will need to use methods from knex.schema to perform operations like create-table, drop-table, alter-table etc.

Suppose you are using babel or something like that so that you have ES6 modules available to you and want to use ES6 style export statements in your modules, just create a file named migration.stub in your project root, and modify your migrate.js to use the newly created migration.stub file as the stub for generating your migrations.

migrate.js:

1
2
3
4
5
import path from 'path';
import migrate from 'tabel/lib/migrate';
import ormConfig from 'orm/config';

migrate(ormConfig, {stub: path.join(__dirname, 'migration.stub')});

migration.stub:

1
2
3
4
5
6
7
export function up(knex) {

}

export function down(knex) {

}


latest

npm run migrate latest

Run all the migrations starting from the last-run migration, right upto the latest created migration.


rollback

npm run migrate rollback

Rollback the last batch of migrations that was run.


version

npm run migrate version

View the number of current batch of migration.


reset

npm run migrate reset

Reset all migrations. Removes all tables that were created using migrate.js.


refresh

npm run migrate refresh

Reset all migrations and migrate back to the latest migration.


Example

Let’s create two tables named users & posts using migrations.

Assuming you have set up migrate.js as shown in the migrations section, run the following commands:

  1. npm run migrate make CreateUsersTable
  2. npm run migrate make CreatePostsTable

Running these two commands will create two files in the folder migrations. The path to these two files looks like this:

  1. migrations/<Timestamp>_CreateUsersTable.js
  2. migrations/<Timestamp>_CreatePostsTable.js

Let’s fill up these two files so that we can run these migrations to create these 2 tables in our database. (Code written for PostgreSQL)

migrations/<Timestamp>_CreateUsersTable.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
function up(knex) {
return knex.schema.createTable('users', (t) => {
t.uuid('id').primary();
t.text('username').unique();
t.text('email').unique();
t.text('hashed_password');
t.timestamps();
});
}

function down(knex) {
return knex.schema.dropTable('users');
}

module.exports = {up, down};

migrations/<Timestamp>_CreatePostsTable.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
function up(knex) {
return knex.schema.createTable('posts', (t) => {
t.uuid('id').primary();
t.uuid('author_id');
t.text('title');
t.text('body');
t.text('slug').unique();
t.timestamps();
});
}

function down(knex) {
return knex.schema.dropTable('posts');
}

module.exports = {up, down};

Now run the following command to create these two tables in the database:

1
npm run migrate latest

(PS: We have used UUID columns for our primary key because there are several advantages that UUIDs have over auto-increment IDs. You can read about these advantages here. tabel ORM can generate UUIDs for your tables on the fly(see table definitions. You can use tabel ORM with auto-increment IDs too.)


Next > Table Definitions