Squashing Migrations with Sequelize

Edit: a full working repository of the code in this blog post can be found here.

After a good amount of time working on any particular Sequelize project, you are bound to end up with a gigantic list of migrations from the beginning of time until the present. While this is great as it shows the progression of your data store, eventually the list will be too long, and running migrations from scratch every time you need to reset your data store, or on-boarding a new developer, will take way too long.

In this post, I outline very briefly my approach for squashing all previous migrations into a single file.

Assumptions.

For this post, I am going to assume that PostgreSQL is being use for the data store, and that the code base being worked on has a structure like:

code-base/
  migrations/
  scripts/
  src/
  package.json

Dumping the Schema.

Create a single file which represents the schema of your database by:

1. Ensuring your local database is up to date on all migrations
2. Dump your database schema using pg_dump pg_dump -U root -d my_database -s > ~/Desktop/schema.sql
3. Delete all migrations files in code-base/migrations/, you won’t need them anymore
4. Place your dump file into code-base/migrations/schema/schema.sql

If you inspect the file you just created, you will find a list of SQL commands used to generate your database schema in its current form.

You might be interested in going through it line by line and finding what old data you are no longer using in your project.

Migration Scripts.

Next, create a simple way of importing your schema into a database, along with running any future migrations.

1. Add the following to your package.json file

{
  ...
  "scripts": {
    ...
   
    "db:schema": "babel-node ./scripts/db/schema",
    "db:migrate": "babel-node ./scripts/db/migrate",
    ...
  },
  ...
}

* Using babel-node here for those sweet sweet ES6 features.

2. Create the scripts/db/schema.js file

import { exec } from 'child-process-promise';
import { parseURL } from 'whatwg-url';
(async () => {
  const parts = parseURL('postgres://root@localhost:5432/my_database');
  console.log('Schema import running');
  exec(`psql -U root -d ${parts.path[0]} -c "CREATE SCHEMA ${parts.path[0]}"`)
    .then(() =>
      exec(`psql -U root -d ${parts.path[0]} -c "ALTER SCHEMA ${parts.path[0]} OWNER TO ${parts.username};"`)
    )
    .then(() =>
      exec(`psql -U root -d ${parts.path[0]} < ./migrations/schema/schema.sql`)
    )
    .then(() => {
      console.log('*************************');
      console.log('Schema import successful');
      process.exit(0);
    })
    .catch((err) => {
      console.log('*************************');
      console.log('Schema import failed. Error:', err.message);
      process.exit(1);
    });
})();

This script will load your newly created schema.sql file into your local database and can be run simply with npm run db:schema.

3. Create your scripts/db/migrate.js file

import path from 'path';
import { spawn } from 'child-process-promise';
import { parseURL } from 'whatwg-url';
const spawnOptions = { cwd: path.join(__dirname, '../..'), stdio: 'inherit' };
(async () => {
  const parts = parseURL('postgres://root@localhost:5432/my_database');
  const url = `${parts.scheme}://${parts.username}:${parts.password}@${parts.host}:${parts.port || 5432}/${parts.path[0]}`;
  try {
    await spawn('./node_modules/.bin/sequelize', ['db:migrate', `--url=${url}`], spawnOptions);
    console.log('*************************');
    console.log('Migration successful');
  } catch (err) {
    console.log('*************************');
    console.log('Migration failed. Error:', err.message);
  }
  process.exit(0);
})();

This script will simply run any Sequelize migrations you have in your migrations/ directory (not including your schema file).

Executing.

With all the aforementioned changes, you can now run

1. npm run db:schema
2. npm run db:migrate

This will load your schema from all your previous migrations which you deleted, and then run any new migrations.

So easy!

Once your migration list gets long again, simply repeat the same steps here to keep on squashing them.

A full working repository of the code in this blog post can be found here.

Enjoy your quicker migration times, and stay Riipe!