Sequelize /-CLI With Migrations

The scattered parts from “getting started”

11 min readJun 13, 2019

A more “comprehensive” getting started, covering whats scattered / the pitfalls from Sequelize/-CLI documentation.

GitHub: sequelize_getting_started

Wether you come from a language like python or you’ve just started using JavaScript, sooner rather than later you will have to communicate with a database. If you are a python developer you might have used ORM’s like SQLAlchemy or Django’s ORM.

ORM stands for Object-Relational Management, and it’s a way of treating your database /-tables just like any other object that you’d be programming with.

When you use JavaScript and start looking for an ORM (SQL), Sequelize is a name you will see often. If you’ve ever used Django you know how effortless migrations are and if you haven’t then you “must” have dropped a lot of tables/db’s.

I have yet to find something comparable to the Django framework but sequelize is a great place to start and seems quite capable of most jobs required of an ORM. Even though more manual labor is required the customization possible makes it worth your while as you can adapt to almost any situation.

I am not going to spend any more time on comparing different ORM’s to each other, you have to research that on your own. All I’ll say is after finally understanding some of the weird parts, you wont go wrong starting with Sequelize (unless you are using a document db…).

“Off we go” ( Setup ):

Assumptions:

You are familiar with Node.js and NPM basics
You know JavaScript basics
You understand the concept ORM

For this tutorial I’ll be following the Sequelize CLI / migration documentation, except give you all the tips and tricks as we go along so you dont have to jump back and forth in articles / tutorials / documentation to find the missing links (thank god I had an ultra-wide screen the first time I used Sequelize). It’s not that Sequelize’s documentation is terrible, it’s just written by someone who knows how to use Sequelize. (At least that’s what i think…)

NB: Some of these commands will fail!
I recommend you follow along typing out (or pasting…) the commands and code as I present it. This way you’ll experience the “pitfalls” first hand and hopefully it will stick better.
I will not explain every command and you are expected to look up functions / commands if you dont understand what they to. However, most (99%) are pretty self-explaining. (Options not mentioned are many, but easy to find later)

Lets begin with creating a directory, initializing a npm package and installing Sequelize /-CLI and our database engine of choice. You can find a list of db engines and their packages here, but for simplicity we’ll user sqlite in this tutorial.

$ npm init -y

Unless you want to store all your code and configuration in the root folder (don’t…) we need to create a /src dir and since we’re not going to use a framework for this tutorial let’s create a simple index.js in the /src dir.

In src/index.jsjust add a single line:

console.log('Hello World!')

So our tiny project should now look like this:

$ tree -I node_modules.
├── package.json
├── package-lock.json
└── src 
 └── index.js

So far so good. Printing a line is all our program will do… Later we will get that line from a database.

Now that our “program” is all set up it’s time to get on with database implementation. Lets start by installing Sequalize /-CLI and a database engine:

$ npm i sequelize sequelize-cli sqlite3

Now that we have sequelize and sequelize-cli installed we have access to the cli with the npm executor. All we have to do now is initialize an empty project.

Lets do that:

$ npx sequelize init

Then lets look at our project again:

.
├── config
│   └── config.json
├── migrations
├── models
│   └── index.js
├── package.json
├── package-lock.json
├── seeders
└── src
    └── index.js

Oh dear… This is a mess…

Sequelize provides a way to fix this by adding a .sequelizerc file in the root of our project. So lets delete all the files/folders created by sequelize so we are left with our original project.

Then lets create a .sequelizerc file in the root of our project:

Dont be fooled by the missing .js extension, you can write plain JavaScript in this file.

Now lets run init again:

$ npx sequelize init

And lets look at our project again:

.
├── package.json
├── package-lock.json
└── src
    ├── db
    │   ├── config
    │   │   └── config.js
    │   ├── migrations
    │   ├── models
    │   │   └── index.js
    │   └── seeders
    └── index.js

Now if this doesn’t satisfy your OCD nothing will… Moving on it’s now time to look at our config.js . Lets open it and have a look at it:

{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

As you can see it’s not “JavaScript” it’s JSON (we changed the name in .sequelizerc ). Lets go ahead and fix that by replacing the content with:

The config file can be .json or .js

Now there’s a few things going on here but as you can see it’s basically the same setup where we export an object except we use JavaScript for our path’s and shorthand syntax (ES6) for our storage. The database will be stored in our root dir.

The production environment is not something we will use in this tutorial but I felt it’s important to include because it will save you a lot of headache when it’s “that time”.

If you dont pass a NODE_ENV like production or test Sequelize will assume development

This concludes our setup of sequelize, now lets get on with the models.

“The fun part” (Database Models)

In this tutorial we’ll stick to the “boring” User/Task Models. Not because of my lack of imagination but because of it’s ability to show relationships. I will show you how to create a User Model that .hasMany (one-to-many) tasks and a Task Model that .belongsTO ((one-to-one) did anyone think profile…)) a user.

BelongsTo associations are associations where the foreign key for the one-to-one relation exists on the source model
One-To-Many (hasMany) associations are connecting one source with multiple targets. The targets however are again connected to exactly one specific source.
When you’ve finished this tutorial it is my hope that you will have the foundation “you need” and you can open the Sequelize API reference and find the other relationship options.

Usually you’ll know in advance what models need relationships and what kind . So we are going to assume that we planned this out in advance. If you’re already at the point where you have you’re models and now figured out that you need a relationship, dont worry just follow along and you’ll get it.

These models are not ment for real use and as simple as possible not to introduce unnecessary complication.

Lets start with the Task Model:

$ npx sequelize model:generate --name Task --attributes taskName:string

As you can see this is pretty strait forward. Lets go ahead and create our Users Model as well:

$ npx sequelize model:generate --name User --attributes name:string

Model attributes and default length for string etc can be looked up in the documentation

Sequelize cli has generated our models and the migration files that creates / updates our db, saving us some bootstrapping (and giving us a nice API we can work with in our app, more about that later).

Let’s look at our project again:

.
├── package.json
├── package-lock.json
└── src
    ├── db
    │   ├── config
    │   │   └── config.js
    │   ├── migrations
    │   │   ├── 20190611175237-create-task.js
    │   │   └── 20190611175246-create-user.js
    │   ├── models
    │   │   ├── index.js
    │   │   ├── task.js
    │   │   └── user.js
    │   └── seeders
    └── index.js

Our models are saved in the src/db/models folder (doh) and migrations in the src/db/migrations folder (doubleDoh). The numbers you see in front of the migration files are timestamps so sequelize will know which one to run in what order (not so important now but later when adding columns / tables…). Now src/db/seeders is where it gets interesting. Sequelize provides us a way of seeding (prepopulate) our database, which I will cover shortly. Finally the src/db/models/index.js is what we’ll import in our node app which will give us access to our models.

Now lets go ahead and create/migrate our database:

$ npx sequelize db:migrate

We should now have a db.sqlite file in our root directory containing our tables. At the moment our database is empty, but lets fix that by creating a seed for our database:

$ npx sequelize seed:generate --name task

We now have a new file in src/seeders . Lets start by opening the {dateTime}-task.js :

NB: Notice “Tasks” in plural here. This is because we are referencing the actual table and not the model. The migrations files have the same syntax and this can be a bit confusing in the beginning.

You can see I’ve added an array of tasks.

We’ll stick to just tasks for now as there’s no point in adding users as we can’t actually assign tasks to users as of now. With a seed ready lets run it and populate our database:

$ npx sequelize db:seed:all

db:seed:all runs all seeds in src/db/seeds even the empty ones (does nothing)

ERROR: Validation error

So our seed fails… But we followed the guide… And the message is not very helpful.

Short: This means that our data is not matching up with our columns, not wrong names, the db is telling us our data does validate. Lets have a quick look at our db (You dont need to do this part):

$ sqlite3 db.sqlite 
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.sqlite> .tables
SequelizeMeta  Tasks          Users    sqlite> .schema Users
CREATE TABLE `Users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);sqlite> .schema Tasks
CREATE TABLE `Tasks` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `taskName` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);sqlite> SELECT * FROM SequelizeMeta;
20190611175237-create-task.js
20190611175246-create-user.js
sqlite>

You can see our tables along with SequelizeMeta where sequelize keeps track of migrations. But what you can also see is that Sequelize has added two columns to our Users and Tasks table. createdAt and updatedAt is something sequelize adds automatically and we have to add it to our seed.

If you dont want createdAt and updatedAt you need to remove them from the model migration files before you migrate for the first time. Removing them after creation can be done with another migration which we will cover later.

Lets go ahead and add createdAt and updatedAt to our migration file:

Now lets run our seeds again:

$ npx sequelize db:seed:allSequelize CLI [Node: 11.15.0, CLI: 5.5.0, ORM: 5.8.9]Loaded configuration file "src/db/config/config.js".
Using environment "development".
== 20190611182947-task: migrating =======
== 20190611182947-task: migrated (0.030s)== 20190611182953-user: migrating =======ERROR: Migration 20190611182953-user.js (or wrapper) didn't return a promise

Our database is now populated by our three tasks.

Finally let’s make some changes to our src/index.js file so we can access and print data from our newly seeded database:

getTasksPromise() and get getTaksAsync() does the exact same thing. I prefer async await but not everyone has / want to / likes it so I decided to write both.

On the top we are importing src/db/models/index.js which is a file provided by Sequelize / -CLI. Sequelize then does it’s magic and loops through our models giving us the db.Task (and later db.User ) API to work with.

db.Task.findAll() is a promise and will return an array of our tasks as objects we can work with, and in this case all we do is loop over them and print the column taskName.

Lets run our program:

$ node src/index.js 
Executing (default): SELECT `id`, `taskName`, `createdAt`, `updatedAt` FROM `Tasks` AS `Task`;
Executing (default): SELECT `id`, `taskName`, `createdAt`, `updatedAt` FROM `Tasks` AS `Task`;
This is task number one!
This is task number two!
This is task number three!
This is task number one!
This is task number two!
This is task number three!

Look at that! We have our data printed to the console. The logging you see at the top is Sequelize printing the queries we made to the console. This can be turned off by setting the logging: false option like shown in config.js for production mode.

Right now all we have are two separate Models which is not very useful unless they are totally unrelated. In our case we would like to link tasks to individual users, so lets get on with that in the associations section.

“The manual part” (Associations)

Sadly we can’t add associations via Sequelize’s cli interface, we will need to edit the models created for us by the cli.

Lets open and add our belongsTo association in/db/models/task.js :

As you can see there’s not a whole lot to it. Just “one” line of code and we have our association.

The code here is pretty self explanatory except the foreignKey: "userId" which refers to a column that does not exist in our task model. This is where the User’s ID (Primary key) will be stored. We will take care of that in a migration shortly.

Lets proceed to our Users model /db/models/user.js and add our hasMany:

We now have our updated models with associations, but for this to work we need to update our Tasks table and add column ‘userId’. Lets go ahead and generate a migration file for that:

$ npx sequelize migration:generate --name add-Task-userId-column

Now lets open our new migration file {dateTime}-add-Task-UserId-column.js and add our new column:

Remember we use plural when referencing the database tables.

That’s it, we now have our migration file and a way to keep track of our migrations through Sequelize CLI. Not to mention a way back if things go wrong with db:migrate:undo . Let’s go ahead and apply our migration:

$ npx sequelize db:migrateSequelize CLI [Node: 11.15.0, CLI: 5.5.0, ORM: 5.8.9]Loaded configuration file "src/db/config/config.js".
Using environment "development".
== 20190612212617-add-Task-userId-column: migrating =======
== 20190612212617-add-Task-userId-column: migrated (0.056s)

Great! Then we are done with our models and migrations! The only thing left is to put them to good use. Lets get to that in the conclusion.

“The end…” (Conclusion)

Lets finish up by including some basic Model methods in our src/index.js file:

I’ve included some of the basic calls you will make to the sequelize api. There are of course many more, all available and described in the documentation.

Adding belongsTo gives us .set<Model>
Adding hasMany gives us .get<Model>s
There are more.

Associations, is a good place to find them.

Now you might ask is all this setup and preparation necessary? No certainly not, you could do all this is a single file, but then you wont have migrations and revert options etc. There’s a great article here where you can see how to do this all in JavaScript without any cli.

Do it all in JS

So, do you need all this. No, but now you know how and you are familiar with Sequelize enough to understand other guides and articles with ease. Do take a look at the article linked above, it’s a good starting point for an all JS setup!

I hope you learned something new, and please dont hesitate to leave a comment or a question.

/ Anders