Version control your database Part 1: creating migrations and seeding

Easily plan, validate, and safely apply changes to your database

If you are not working with migrations in your database you’re missing out. Like Git manages changes to source code, you can use migrations to keep track of changes to your database. Execute and revert changes and get your database back into a previous state.

Setting up migrations is easier than you think and the advantages are huge. Migrations are database-independent, offer one source of the truth, track changes and can even seed your database with some data. When you’ve read this article you’ll be able to:

• Create tables with indices and foreign keys
• Easily plan, validate, and safely apply changes in a dev database and then sync all the changes to your production database
• reset your development database (undo all, migrate again)
• Create all specified tables in your database, including indices and associations
• Seed a database (insert data)
• Execute a migration to any database (e.g. both PostgreSQL and SQL Server)

I will try to showcase all of these features with examples that use real code that you can re-use. I’ve cut this process in 4 steps: Setting up, Creating Migrations, Executing and undoing and, lastly, Seeding. Don’t be discouraged by the length of this article, you’ll breeze through using the easy-to-follow steps. Lets go!

Step 1: Setting up

At the end of this step Sequelize is installed and ready for use. If you are an experienced programmer then skip the ‘Explanation’-parts. At the bottom of this part you’ll find a summary of all commands.

1.1 Installing NPM

Install NPM. Verify with npm -v

Explanation:
For this project you need to install NPM the JavaScript package management system. It resembles pip in Python and you can use it to install packages. Download NPM here and follow the installation-instructions. Once the installation is finished you should be able to open a terminal (like command prompt) and run nmp -v. If you se a version (like v14.15.4 then node is correctly installed.

1.2 Setting up your project

Open a terminal, navigate to your project folder and execute npm init

Explanation:
Create a folder you want to create this project in for example c:/migrationtool.
Open a terminal and navigate to this folder: cd c:/migrationtool
Create a new project by calling npm init. NPM will ask you some questions about the project name, version, description and the author name. These are all not required, can be skipped and can also be filled out later. When finished a file appears in the project folder called ‘package.json’. In here all of our project metadata will be registered.

1.3 Installing packages

In the root folder execute# installing packages
npm install --save sequelize sequelize-cli
npm install --save pg pg-hstore   # for PostgreSQL
npm install --save tedious        # for SQL Server

Explanation:
Our project is prepared, let’s install our packages! First we need Sequelize and Sequelize-cli. These packages allow us to start making and executing migrations: npm install --save sequelize sequelize-cli. This is the main package that will allow us to create migrations.
To actually execute these migrations (e.g. create a database, table or new column) Sequelize needs to know a little more about the database. For demonstration purposes we’re going to use two kinds of databases in our project: PostgreSQL and SQL Server. In order for Sequelize to work with these databases we need to install some extra packages: npm install — save pg pgh-store tedious. If you use another database, like mysql sqllite or many others, you can which package to use here.
You’ll notice that a new folder has appeared; node_modules. All of our packages are installed in here. Also our package.json file has expanded, keeping track of all of our installed packages.

1.4 Initialize Sequelize

In your root folder execute npx sequelize-cli init and run through the steps

Explanation:
All of our packages are installed. Initialize Sequelize: npx sequelize-cli init. Notice that we are using NPX here, not NPM. NPX is used to execute the packages we’ve installed with NPM.
When the command has finished you’ll notice that three more folders have appeared:

• config: holds files like databasecredentials
• models: holds files that respresent out database tables as models
• seeders: files that insert into and delete data from tables

1.5 Configuring Sequelize

Sequelize is ready for use. Before we dig in we’re going to make working with Sequelize a bit easier for ourselves
We are going to make it a bit easier to work with our configs. JS files are a bit easier to work with than JSON in my experience. Go to the config folder and change config.json to config.js. Then adjust the content from{
 "development": {

tomodule.exports: {
   "development": {

We’ve made this change to we need to tell Sequelize how to handle the new situation. Go to the root of your project (c:/migrationtool/) and create a new file. Name this file .sequelizerc. Notice that this file does not have a name, only an extension. Open the file and add the content below. This tells Sequelize that we’re now using config.js instead of config.json.const path = require('path');
module.exports = {
  "config": path.resolve('./config', 'config.js'),
   "models-path": path.resolve('./models'),
   "migrations-path": path.resolve('./migrations'),
   "seeders-path": path.resolve('./seeders')
}

Last step before the fun starts: Go to the models folder (c://migrationtool/models) and open the index.js file. On line 8 replace config.json to config.js.

Summary

We’re ready to go! Check out the commands we’ve use before we go to the next step.#Creating folder
cd c:/
mkdir migrationtool
cd migrationtool#Setting up project
npm init# installing packages
npm install --save sequelize sequelize-cli
npm install --save pg pg-hstore   # for PostgreSQL
npm install --save tedious        # for SQL Server# Initialize Sequelize
npx sequelize-cli init

Step 2: Creating migrations

In Sequelize a migration is a JavaScript file. It’s content describe what should happen on executing and undoing, for example “create a schema named ‘persondata’. Lets create one!

2.1 Creating our first migration

In your root folder execute the following command.
npx sequelize-cli migration:create -- name create_schemas. This will tell sequelize to create a new migration. Executing this command will generate a file in our migrations-folder called something like this: ‘20210519183705-create_schemas.js’. Inside you’ll find the code below.

As you can see the migration contains two function ‘up’ and ‘down’. The first function will contain all the code to achieve what we want to do; create schema. The second function will undo the ‘up’ function; it is it’s opposite. Let’s finish our first migration:

This code tells the queryInterface to create a schema called “app”.

2.2 Creating a table migration

Lets speed it up a bit; we’ll create new migration with npx sequelize-cli migration:create -- name create_country_table. Give the newly created migration the following content:

This migration is a bit fancier. First it creates a transaction in which it defines the table with a few columns. It then adds a we indices to the table and then commits it. The transaction is for ensuring that either everything succeeds or nothing. If creating one of the indices fails then it rolls back creating the table. The down-function just drops the newly created table.

Also notice that the Created and Modified columns have default values.

3. Executing and undoing our migrations

Nothing yet happened in our database. We’ve just generated some instructions that we now have to execute. We want to migrate the instructions to the database. First we’re going to define our database connection, then we’ll use that connection to tell the database how to perform the migration

3.1 Set the database connection

Edit the contents of our config.js file (in root/config folder). I’ve created two database connections:module.exports = {
 development: {
   username: "mike",
   password: "my_secret_password",
   database: "country_db_dev",
   host: "localhost",
   port: "5432",
   dialect: "postgres"
 },
 production: {
   username: "mike",
   password: "my_secret_password",
   database: "country_db",
   host: "localhost",
   port: "1433",
   dialect: "mssql"
 }
}

Explanation:
I’ve made two connections: dev and production. These file allow our migration tool to connect to a database to execute the migrations.

When we tell Sequelize to execute a migration it needs to know to which database to connect. We do this by setting NODE_ENV to the name of one of our database connections. We have “development” and “production”. Lets connect with “development”. Open a terminal in your root folder and execute on of the lines below (match your OS):# On windows
SET NODE_ENV=development
# On OSX
export NODE_ENV=development #On powershell $env:NODE_ENV="development"

3.2 Executing migrations

Now that Sequelize knows where to write to we can execute our migrations. Notice that in the previous step we’ve defined our database. We can execute our migrations to any database we’ve downloaded packages for in step 1.3 in this article. This way we can test our migrations in PostgreSQL and then migrate everything to our SQL Server production database!
Lets execute:npx sequelize-cli db:migrate

This code will execute all of the migrations, creating our schema and a table inside, check it out:

3.3 Undoing migrations

Wait go back! Call the code below to drop the table and schema. npx sequelize-cli db:migrate:undo:all. Look inside your database, it’s all clean! You can also undo untill a specific migration by specifying the file name in the command below
npx sequelize-cli db:migrate:undo:all — to XXXXXXXXXXXXXX-create_country_table.js

4. Seeding

Seeding a database is a lot like creating a migration. Lets quickly run through the code.

4. 1 Creating a seed

Step 1: generate a seed file
npx sequelize-cli seed:generate --name seed_country_table
And the content:

4.2 Executing seeds

Execute it with: npx sequelize-cli db:seed:all

4.3 Undoing seeds

Like with a migration undo seeds with
npx sequelize-cli db:seed:undo`
Or up untill a specific seed like
npx sequelize-cli db:seed:undo — seed XXXXXX-seed_country_table.js

Conclusion

We’ve accomplished quite a bit today! We have accomplished the following:

• Installed Node
• Installed Sequelize and all necessary packages
• Configured Sequelize
• Created some migrations
• Executed and undid the migrations
• Created some seeds
• Executed and undid seeds

Click here for the next part which we’ll focus on the more advanced stuff; we’ll get into creating associations between tables and strategies on how records should act once a record it depends on gets deleted. Follow me to stay tuned!

— Mike