Wander

Database Migrations for Modern Apps

Introduction

Wander is a command-line tool that enables programmable database changes. Through Migrations, you can version control your database and easily commit or revert changes between versions.

Supported Databases

Currently, only mysql is supported. Adapters for other databases are still under development.

• ✓ MySQL
• ✗ PostgreSQL
• ✗ SQL Server
• ✗ Oracle DB

Getting Started

To get started, globally install wander-cli

> npm install -g wander-cli

Initializing Wander

To initialize a new wander project, run wander init inside your project folder

> mkdir MyDatabase
> cd MyDatabase
> wander init

This will ask you for the name of your migrations folder (default: ./migrations) and your database URI. Once completed, you can now start using wander.

Changing environments

wander supports migrations for different environments. To change your current wander environment, run wander env inside your project folder

> wander env

Creating a new Migration

To create a new migration, run wander new <name>.

> wander new create_post

This will create a new file inside your migrations folder

+ migrations
  - v1_0_0__create_post.js
+ wrconfig.json
+ .gitignore

Note that you can change the type of version that your new migration will use, using the -t or --version-type option.

{major}.{minor}.{patch}

Version Commands:

• major: wander new create_post -t major (e.g. 2.0.0)
• minor: wander new crete_post -t minor (e.g. 1.1.0)
• patch: wander new create_posrt -t patch (e.g. 1.0.1)

Once created, the file would look similar to the following.

v1_0_0__create_post.js

module.exports = {
    version() {
        return '1.0.0';
    },
    description() {
        return `Create post`;
    },
    async up({}) {
    },
    async down({}) {
    }
};

Description

• The version method returns the version name of your migration.
• The description method returns the a brief description of your database changes. You can expand this as you wish.
• The up method runs whenever the migration is committed
• The down method runs whenever the migration is reverted

For the up and down methods, an object is passed to them for your usage, which includes serveral useful functions. Check the API section for more information.

Example

module.exports = {
    version() {
        return '1.0.0';
    },
    description() {
        return `Create post`;
    },
    async up({ create, seed }) {
        create('post', table => {
            table.id();
            table.string('caption');
            table.timestamps();
        });

        seed('post', [
            { caption: 'New post!' },
            { caption: 'Another post!' }
        ]);
    },
    async down({ drop, truncate }) {
        truncate('post');
        drop('post');
    }
};

API

create

Parameters

| Paramter | Data type | | ---------- | ---------------------------------- | | tableName | string | | definition | (table: Table) => void |

Description

• Accepts a tableName that you want to create, and a definition function.
• The definition function has a Table parameter that allows you to define the structure of your table.

Example

create('post', table => {
    table.id();
    table.string('caption');
    table.timestamps();
});

alter

Parameters

| Paramter | Data type | | ---------- | ---------------------------------------- | | tableName | string | | definition | (table: Table) => void |

Description

• Accepts a tableName that you want to alter, and a definition function.
• The definition function has a Table parameter that allows you to define the structure of your table.

Example

alter('comment', table => {
    table.integer('comment_count').add();
});

drop

Parameters

| Paramter | Data type | | ---------- | ---------------------------------- | | tableName | string |

Description

• Accepts a tableName that you want to drop.

Example

drop('comment');

seed

Parameters

| Paramter | Data type | | ---------- | ---------------------------------- | | tableName | string | | seeds | object[] |

Description

• Accepts a tableName that you want to seed with data.
• Accepts a seeds array of objects defining the data you want to insert.

NOTE: Only the columns of the first item in the list will define the columns that will be populated for all of the items.

Example

seed('post', [
    { caption: 'A new post!' },
    { capton: 'Another post' }
]);

truncate

Parameters

| Paramter | Data type | | ---------- | ---------------------------------- | | tableName | string |

Description

• Accepts a tableName that you want to truncate.

Example

truncate('comment');

execute

Parameters

| Paramter | Data type | | ---------- | ---------------------------------- | | query | string |

Description

• Ideal for running scripts that are too complex or are too specific to the database you are running.
• Accepts a query that you want to execute.

WARNING: Running execute is very dangerous especially if the keys and values you are trying to use are not properly escaped.

Example

execute(`
    SET sql_safe_updates = 0;
    UPDATE post SET updated_at = now() WHERE id > 5;
`);

Table class

Description

• Allows you to define the structue of your table via different methods

Methods

• id(columnName?: string)
  • Adds a primary auto_increment key (default: id)
• string(columnName: string, length?: number)
  • Adds a string column (e.g. on MySQL this is equivalent to varchar)
• text(columnName: string)
  • Adds a text column (unlike string, there is no limit to the length)
• integer(columnName: string, length?: number)
  • Adds an integer column
• float(columnName: string, length?: number, precision?: number)
  • Adds a float column
• decimal(columnName: string, length?: number, precision?: number)
  • Adds a decimal column
• double(columnName: string, length?: number, precision?: number)
  • Adds a double column
• date(columnName: string)
  • Adds a date column
• datetime(columnName: string)
  • Adds a datetime column
• boolean(columnName: string)
  • Adds a boolean column (e.g. on MySQL this is equivalent to bit)
• json(columnName: string)
  • Supported in MySQL 5.7+
• char(columnName: string, length?: number)
  • Adds a char data type column
• timestamps()
  • Creates created_at, updated_at and deleted_at datetime columns
• index(columnName: string, alias?: string)
  • Create an index on the table
• unique(columnName: string, alias?: string)
  • Create a unique index on the table

You can also specify specific options for creation.

• nullable(isNullable?: boolean)
  • Determine whether the field should be nullable or not nullable
• increments()
  • State that the field should auto-increment

Key Actions

In addition, when using the alter option, the above methods have specific actions that need to be defined in order to let wander know how the columns are going to be changed.

• add()
  • Add the column/index/unique key
• modify()
  • Modify the column
• drop()
  • Drop the column/index/unique key

You can also specify specific options for the alteration.

• nullable(isNullable?: boolean)
  • Determine whether the field should be nullable or not nullable
• increments()
  • State that the field should auto-increment
• first(column?: string)
  • Determine the position of the column
• after(column?: string)
  • Determine the position of the column

Example

alter('comment', table => {
    table.integer('comment_count').add();
    table.index('IDENT').drop();
});

Sample migration

module.exports = {
    version() {
        return '1.0.0';
    },
    description() {
        return `
            Created the post table.
            Changed the comment table.
            Populated the post table.
        `;
    },
    async up({ create, alter, seed, execute }) {
        create('post', table => {
            table.id();
            table.string('caption', 500);
            table.pointer('user');
            table.json('info');
            table.string('identifier');
            table.index('identifier', 'IDENT');
            table.timestamps();
        });

        // Equivalent to the following
        `
        CREATE TABLE post (
        id int AUTO_INCREMENT,
        caption varchar(500),
        user_id int(11),
        info json,
        identifier varchar(30),
        PRIMARY KEY (id),
        INDEX IDENT (identifier));
        `

        alter('comment', table => {
            table.integer('like_count').add();
            table.string('message', 250).modify();
            table.pointer('post').drop();
            table.index('IDENT').drop();
        });

        // Equivalent to the following
        `
        ALTER TABLE comment 
        ADD COLUMN like_count int,
        MODIFY COLUMN message varchar(250),
        DROP COLUMN post_id;
        `

        seed('post', [
            { caption: 'New caption!' },
            { caption: 'New caption!' },
            { caption: 'New caption!' }
        ]);

        // Equivalent to the following
        `
        INSERT INTO post
        (caption)
        VALUES
        ('New caption!'),
        ('New caption!'),
        ('New caption!');
        `

        execute(`
            SET sql_safe_updates = 0;
            UPDATE post SET privacy = 'public' WHERE role IS NULL;
        `);
    },
    async down({ alter, drop, truncate }) {

        truncate('post');
        drop('post');

        alter('comment', () => {
            this.integer('like_count').drop();
            this.string('message', 500).modify();
            this.pointer('post').add();
            this.index('identifier', 'IDENT').add();
        });

        execute(`UPDATE post SET privacy = 'private' WHERE role IS NULL`);
    }
};

Committing Pending Migrations

After you've set up your migrations, you can now commit them. To commit pending migrations, run the command below.

> wander commit

You can apply the --verbose or -v flag to see the actual scripts being run. Note that wander runs the migrations in the order of their version number.

Reverting Commited Migrations

To revert migrations you've already committed, run the following command.

> wander revert

Like commit, you can apply the --verbose or -v flag to see the actual scripts being run. Also, you can specify the --count or -c parameter in order to tell wander how many migrations are going to be reverted, starting from the latest version.

Resetting All Migrations

To reset all migrations, run the command below.

> wander reset

Like the previous commands, you can apply the --verbose or -v flag to see the actual scripts being run. The commands are reset from latest to oldest version.

Up versus Down

In terms of the Migrations standard for databases, the convention is to always have up and down methods.

The purpose of the up method is to commit the changes you want to apply to the database. Conversely, it is required that you undo everything that you performed, inside the down method.

The reason why this practice is observed is that it allows you, to some extent, to recover from a breaking change that was executed accidentally. Also, it allows you to ensure consistency when changing between database versions.

Do note, however, that unless a seed option is defined inside the methods, any data that was truncated or dropped will be lost forever.

Transactions

For wander, all migrations are executed as a single transaction. That means the MigrationOptions you used will not be fired until the processor has gone through all of them.

That also means that if anything goes wrong, the scripts will be rolled back (except for cases where database auto-commits occur). See the corresponding manuals of your databases for more information.