You can now import the desired database schema from your Sequelize project into Atlas, and use it to automatically plan migrations for you.
Introduction
Sequelize is one of the most popular ORMs for Node.js. It supports a variety of databases, including MySQL, PostgreSQL, SQLite, and Microsoft SQL Server.
Atlas is a modern tool for managing your database schema. It allows you to inspect, plan, lint and execute schema changes to your database. It is designed to be used by developers, DBAs and DevOps engineers alike.
Atlas supports many ways to describe database schemas: Using Schema Loaders, plain SQL, a connection to another database or using Atlas HCL.
Today, I'm happy to announce that Atlas supports loading the desired schema from Sequelize projects. This means that Sequelize users can now use Atlas instead of the existing Sequelize CLI to manage their database schema.
By using Atlas, Sequelize users can now enjoy these benefits:
- A declarative migration flow - Atlas can operate like a "Terraform for databases", where
by running
atlas schema apply
the application schema is applied on a target database. - Automatic schema migration planning - Alternatively, Atlas can operate using a more traditional versioned migration flow. However, contrary to most tools, Atlas will automatically analyze the diff between the migration directory and the current application data model, and will produce correct and safe SQL migration files.
- CI for schema changes - Atlas can be used during CI to make sure you never merge a pull request that will break your database schema.
- Modern CD integrations - Atlas integrates seamlessly with modern deployment tools such as Kubernetes, Terraform, Helm, Flux, and ArgoCD. This allows you to deploy changes to your database schema as part of your existing deployment pipelines.
- Visualization - Atlas users can create beautiful, shareable ERDs of their application data model with a single command.
- .. and much more (read more about Atlas features)
Evolving beyond Sequelize's native migration support
Sequelize allows users to manage their database schemas using its sync feature, which is usually sufficient during development and in many simple cases:
await sequelize.sync({ force: true });
console.log("All models were synchronized successfully.");
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 in a very specific DSL, which is error-prone and time consuming:
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Person', {
name: Sequelize.DataTypes.STRING,
isBetaMember: {
type: Sequelize.DataTypes.BOOLEAN,
defaultValue: false,
allowNull: false
}
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('Person');
}
};
Atlas can automatically plan database schema migrations for developers using Sequelize by calculating the diff between the current state of the migration directory and its desired state defined by the Sequelize schema.
Demo time
Let's demonstrate how to set up Atlas to manage your Sequelize schema.
Installation
If you haven't already, install Atlas from macOS or Linux by running:
curl -sSf https://atlasgo.sh | sh
See the documentation for more installation options.
Install the Atlas Sequelize Provider by running:
- JavaScript
- TypeScript
npm install @ariga/atlas-provider-sequelize
npm install @ariga/ts-atlas-provider-sequelize
Make sure all your Node dependencies are installed by running:
npm install
Standalone vs Script mode
The provider can be used in two modes:
- Standalone - If all of your Sequelize models exist in a single Node.js 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:
- JavaScript
- TypeScript
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 . \" \" }}"
}
}
}
data "external_schema" "sequelize" {
program = [
"npx",
"@ariga/ts-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 . \" \" }}"
}
}
}
For the sake of brevity, we will not review the Script mode in this post, but you can find more information about it in the Sequelize Guide.
Load Sequelize Schema in action
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 task
and user
:
- JavaScript
- TypeScript
- task.js
- user.js
'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;
};
'use strict';
module.exports = function(sequelize, DataTypes) {
const User = sequelize.define('User', {
name: {
type: DataTypes.STRING,
allowNull: false
},
email: {
type: DataTypes.STRING,
allowNull: false,
validate: {
isEmail: true
},
}
});
User.associate = (models) => {
User.hasMany(models.Task, {
foreignKey: {
name: 'userID',
allowNull: false
},
as: 'tasks'
});
};
return User;
};
- task.ts
- user.ts
import { Table, Column, Model, PrimaryKey, ForeignKey, BelongsTo, AutoIncrement, DataType, AllowNull, Default } from "sequelize-typescript";
import User from "./user";
@Table({ tableName: "Tasks" })
class Task extends Model {
@PrimaryKey
@AutoIncrement
@Column(DataType.INTEGER)
id!: number;
@Default(false)
@Column(DataType.BOOLEAN)
complete!: boolean;
@AllowNull(false)
@ForeignKey(() => User)
@Column(DataType.INTEGER)
userID!: number;
@BelongsTo(() => User)
user!: User;
}
export default Task;
import { Table, Column, Model, PrimaryKey, AutoIncrement, DataType, AllowNull, HasMany} from "sequelize-typescript";
import Task from "./task";
@Table({ tableName: "Users" })
class User extends Model {
@PrimaryKey
@AutoIncrement
@Column(DataType.INTEGER)
id!: number;
@AllowNull(false)
@Column
name!: string;
@AllowNull(false)
@Column
email!: string;
@HasMany(() => Task)
task!: Task[];
}
export default User;
We can now 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:
- JavaScript
- TypeScript
name: {
type: DataTypes.STRING,
allowNull: false
},
+ age: {
+ type: DataTypes.INTEGER,
+ allowNull: false
+ },
@AllowNull(false)
@Column
name!: string;
+ @AllowNull(false)
+ @Column
+ age!: number;
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 post, we have presented how Sequelize projects can use Atlas to automatically plan schema migrations based only on their data model.
How can we make Atlas better?
We would love to hear from you on our Discord server ❤️.