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

``bash
> npm install -g wander-cli
`

Initializing Wander

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

`bash
> 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

`bash
> wander env
`

Creating a new Migration

To create a new migration, run wander new .

`bash
> wander new create_post
`

This will create a new file inside your migrations folder

`markdown
+ 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
`javascript
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__

`javascript
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__

`javascript
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__

`javascript
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__

`javascript
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__

`javascript
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__

`javascript
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__

`javascript
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__

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

Sample migration

`javascript
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.

`bash
> 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.

`bash
> 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.

`bash
> 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.