Add database migrations

[sscovil]

In #19, we manually created the contacts database table in PostgreSQL using the psql command line tool. Another way to do that would have been to use a database migration tool like Knex.

Now, we are going to use Knex to perform database migrations so that anyone developing or deploying our app can easily create all of the necessary tables using a single command.

To do this, we must:

1. install Knex.js globally via NPM
2. create knexfile.js in the root directory
3. create a migrations directory
4. add migration files
5. run migrations

Step 1: Install Knex.js globally

From the command line, run:

$ npm install knex -g

This will enable you to run the knex command line interface (CLI) from any directory.

Step 2: Create knexfile.js

In the project root directory, add a file called knexfile.js that looks like this:

'use strict';

module.exports = {
  client: 'pg',
  connection: {
    database : 'blog'
  }
};

Step 3: Create /migrations directory

In the project root directory, add a directory called migrations:

$ mkdir migrations

Step 4: Add migration files

In the migrations directory, add a file called 001-create-table-contacts.js that looks like this:

'use strict';

exports.up = (knex) => {
  return knex.schema.createTableIfNotExists('contacts', function (table) {
    table.increments('id');
    table.text('name');
    table.text('email');
    table.text('subject');
    table.text('message');
  });
};

exports.down = (knex) => {
  return knex.schema.dropTable('contacts');
};

Using the Knex Schema Builder documentation, try creating two more migration files similar to the above file: one for creating a posts table, and one for creating a pages table. Use the object properties we defined for posts and pages as column names.

Step 5: Run migrations

Files in this directory are run in order based on their file name, which is why this file has a 001 prefix. The next file we create will start with 002, then 003 and so on.

Each migration file exports two functions: up and down. The up function is run when we use the command:

$ knex migrate:latest

...and the down function is run when we use the command:

$ knex migrate:rollback

All migrations are run that have not already been run, so if you run migrations once and then add a migration file, running knex migrate:latest will only run the newly added migration. Then, running knex migrate:rollback would roll back the changes in the newly added migration only. Running it again would roll back all previous migrations.

[sscovil]

Resolved by #28