Skip to main content

Automatic migration planning for Sequelize

TL;DR​

  • Sequelize is an ORM library that's widely used in the Node.js community.
  • Atlas is an open-source tool for inspecting, planning, linting and executing schema changes to your database.
  • Developers using Sequelize can use Atlas to automatically plan schema migrations for them, based on the desired state of their schema instead of crafting them by hand.

Automatic migration planning for Sequelize​

Sequelize is a popular ORM widely used in the Node.js community. Sequelize allows users to manage their database schemas using its sync feature, which is usually sufficient during development and in many simple cases.

However, at some point, teams need more control and decide to employ the migrations methodology, which is a more robust way to manage your database schema. The problem with creating migrations in Sequelize is that they are usually written by hand, which is error-prone and time-consuming.

Atlas can automatically plan database schema migrations for developers using Sequelize. Atlas plans migrations by calculating the diff between the current state of the database, and its desired state.

In the context of versioned migrations, the current state can be thought of as the database schema that would have been created by applying all previous migration scripts.

The desired schema of your application can be provided to Atlas via an External Schema Datasource which is any program that can output a SQL schema definition to stdout.

To use Atlas with Sequelize, users can utilize the Sequelize Atlas Provider, a small program that can be used to load the schema of a Sequelize project into Atlas.

In this guide, we will show how Atlas can be used to automatically plan schema migrations for Sequelize users.

Prerequisites​

If you don't have a Sequelize project handy, you can use sequelize/express-example as a starting point:

git clone git@github.com:sequelize/express-example.git

Using the Atlas Sequelize Provider​

In this guide, we will use the Sequelize Atlas Provider to automatically plan schema migrations for a Sequelize project.

Installation​

Install Atlas from macOS or Linux by running:

curl -sSf https://atlasgo.sh | sh

See atlasgo.io for more installation options.

Install the provider by running:

npm install @ariga/atlas-provider-sequelize

Make sure all your Node dependencies are installed by running:

npm install

Standalone vs Script mode​

The Atlas Sequelize Provider can be used in two modes:

  • Standalone - If all of your Sequelize models exist in a single Node module, you can use the provider directly to load your Sequelize schema into Atlas.
  • Script - In other cases, you can use the provider as an npm package to write a script that loads your Sequelize schema into Atlas.

Standalone mode​

In your project directory, create a new file named atlas.hcl with the following contents:

data "external_schema" "sequelize" {
    program = [
        "npx",
        "@ariga/atlas-provider-sequelize",
        "load",
        "--path", "./path/to/models",
        "--dialect", "mysql", // mariadb | postgres | sqlite | mssql
    ]
}

env "sequelize" {
    src = data.external_schema.sequelize.url
    dev = "docker://mysql/8/dev"
    migration {
        dir = "file://migrations"
    }
    format {
        migrate {
            diff = "{{ sql . \"  \" }}"
        }
    }
}

Script mode​

Create a new file named load.js with the following contents:

#!/usr/bin/env node

// require sequelize models you want to load
const user = require("./models/user");
const task = require("./models/task");
const loadModels = require("@ariga/atlas-provider-sequelize");

console.log(loadModels("mysql", user, task));

Next, in your project directory, create a new file named atlas.hcl with the following contents:

data "external_schema" "sequelize" {
    program = [
        "node",
        "load.js",
        "mysql"
    ]
}

env "sequelize" {
    src = data.external_schema.sequelize.url
    dev = "docker://mysql/8/dev"
    migration {
        dir = "file://migrations"
    }
    format {
        migrate {
            diff = "{{ sql . \"  \" }}"
        }
    }
}

Usage​

Atlas supports a versioned migrations workflow, where each change to the database is versioned and recorded in a migration file. You can use the atlas migrate diff command to automatically generate a migration file that will migrate the database from its latest revision to the current Sequelize schema.

Suppose we have the following Sequelize models directory, with two models user and task:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Task = sequelize.define('Task', {
    complete: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    }
  });

  Task.associate = (models) => {
    Task.belongsTo(models.User, {
      foreignKey: {
        name: 'userID',
        allowNull: false
      },
      as: 'tasks'
    });
  };

  return Task;
};

Using the Standalone mode configuration file for the Atlas Sequelize Provider, we can generate a migration file by running this command:

atlas migrate diff --env sequelize

Running this command will generate files similar to this in the migrations directory:

migrations
|-- 20230918143104.sql
`-- atlas.sum

0 directories, 2 files

Examining the contents of 20230918143104.sql:

-- Create "Users" table
CREATE TABLE `Users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Create "Tasks" table
CREATE TABLE `Tasks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `complete` bool NULL DEFAULT 0,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `userID` int NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `userID` (`userID`),
  CONSTRAINT `Tasks_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `Users` (`id`) ON UPDATE CASCADE ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Amazing! Atlas automatically generated a migration file that will create the Users and Tasks tables in our database!

Next, alter the User model to add a new age field:

    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
+   age: {
+     type: DataTypes.INTEGER,
+     allowNull: false
+   },

Re-run this command:

atlas migrate diff --env sequelize

Observe a new migration file is generated:

-- Modify "Users" table
ALTER TABLE `Users` ADD COLUMN `age` int NOT NULL;

Conclusion​

In this guide we demonstrated how projects using Sequelize can use Atlas to automatically plan schema migrations based only on their data model. To learn more about executing these migrations against your production database, read the documentation for the migrate apply command.

Have questions? Feedback? Find our team on our Discord server