Sequelize CLI

Reference

February 04, 2019⭐️ FEATURED

I’ve been working away on Project GFT and finally reached a point where I don’t think it’s wise to proceed without implementing the ability to migrate data which I started learning about a couple months ago. So I’m kind of starting over and re-building the app sort of from scratch, but using everything I already built. For future reference, below I’ll copy the steps of an edited version of my previous step-by-step.

Too Long? Don’t Read

Here are some of the resources I found to be the most helpful:

Table Of Contents

Note steps 7 & 8 can happen concurrently for each model.

Step 1 - Initiate Sequence

  • Create a new directory
  • Run git init to begin version control with an initial commit
  • Add your basic .gitignore as needed
  • Run npm init to set up the app for building with Node packages

Step 2 - Install Basic Node Packages

I’m building a Node app which will use Express to run a server and Sequelize to manage a PostgreSQL database. To future-proof the data tables (i.e. doing your future self a favor if she wants to add columns, associations, etc.) we’ll also use [sequelize-cli](https://www.npmjs.com/package/sequelize-cli). I also want to keep sensitive information out of the repo so will use an environment variable manager. Views will be written courtesy of the ejstemplate engine, and form actions will be helped along withbody-parserandmethod-override`.

npm i express sequelize pg pg-hstore dotenv ejs body-parser method-override

That said, I ignored most of these packages initially. Instead I set up a basic Hello, world application (app.js file in the root folder) with a single get route to make sure Express was working correctly:

const express = require("express");
const app = express();

app.get("/", (req, res) => res.send("Hello, world!"));

const port = 3000;
app.listen(port, () =>
  console.log(`App is running on port ${port} in ${nodeEnv} mode.`)
);

Step 3 - Add Initial Routes

  • Create a routes directory in the root folder

  • Add an index.js file and move the get route to this file. Preface it with a router and export it as a module at the end of the file:

    const express = require("express");
    const router = express.Router();

    router.get("/", (req, res) => res.send("Hello, world!"));

    module.exports = router;

  • Require this router module in the main app.js file

Step 4 - Set Up Views

  • Create a views directory in the root folder
  • Create a public directory in the root folder, as well as public/css and public/js directories. This is where Express will look for any static files to serve
  • Set up the templating engine and public folder in the main app.js file:
    app.set("view engine", "ejs");
    app.use(express.static(__dirname + "/public"));
  • Add some basic content to views/index.ejs and update the get route to serve this file.
  • Now’s also a good time to break up the permanent elements of the template (like head, footer, etc.) into partials, which would go into a new views/partials directory.
  • Add some test code to CSS and JS files in their respective public folders. Link these in the index.ejs file (or relevant partials) to confirm it’s all linked and working.
  • Don’t forget to add a 404.ejs file to serve non-existing routes.

Step 5 - Set Up Database With sequelize-cli

  • Update postgres locally on your machine (or install it if it’s not installed). I use homebrew.
  • Open the postgres command line with psql
  • Create a new user who will be interacting with the app: CREATE ROLE username WITH LOGIN PASSWORD 'password' CREATEDB;. This gives them permission to create databases…this user will be employed by Sequelize shortly.
  • Create a database for the app if it doesn’t already exist: CREATE DATABASE dbname;. Now you can close the postgres command line…won’t need it again for the remaining steps.
  • Note: if you don’t create the user and database from the postgres command line first, it won’t be possible to establish a connection from the Express app. There are also npm packages that can do this.
  • Back in the project, run npm install -D sequelize-cli to save the package as a dev dependency on this project. At this stage I also added a new alias to my bash profile as follows:
alias sqlz="node_modules/.bin/sequelize"

If you skip using an alias, in the instructions below you’ll need to replace sqlz with node_modules/.bin/sequelize. Another option is to install the package globally (npm install -g sequelize-cli)…then you can prefix the commands below with just sequelize

  • OPTIONAL: Create a new .sequelizerc file in the project’s root folder based on the template below. If you skip this step, the folders listed will go into the root folder. Not the end of the world, but this offers a bit more organization:
// /.sequelizerc

const path = require('path');

module.exports = {
  'config': path.resolve('config', 'db-config.js'),
  'models-path': path.resolve('db', 'models'),
  'seeders-path': path.resolve('db', 'seeders'),
  'migrations-path': path.resolve('db', 'migrations')
};
  • Run sqlz init. The files and folders listed above will be auto-generated into the project folder.
  • Open config/db-config.js and wrap the object with module.exports; this is necessary because the default config expects a JSON file, but we’re using js to make use of environment variables and hide private information from git. Then update the dialect to postgres for all 3 environments. If you will not commit this file to source control, you can update the username, password, and database name that were set up above; host and port can be left as is. However since source control is highly recommended and best practice I set these up as environment variables instead. My finished product looked like this:
// config/db-config.js
require('dotenv').config();

module.exports = {
  "development": {
    "username": process.env.PG_USER,
    "password": process.env.PG_PASSWORD,
    "database": process.env.PG_DATABASE,
    "host": process.env.PG_HOST,
    "port": process.env.PG_PORT,
    "dialect": "postgres",
    "operatorsAliases": false
  },
  "test": {
    "username": process.env.PG_USER,
    "password": process.env.PG_PASSWORD,
    "database": process.env.PG_DATABASE,
    "host": process.env.PG_HOST,
    "port": process.env.PG_PORT,
    "dialect": "postgres",
    "operatorsAliases": false
  },
  "production": {
    "username": process.env.PG_USER,
    "password": process.env.PG_PASSWORD,
    "database": process.env.PG_DATABASE,
    "host": process.env.PG_HOST,
    "port": process.env.PG_PORT,
    "dialect": "postgres",
    "operatorsAliases": false
  }
}
  • Though the app will eventually have an .env file to manage these variables, for now all five of these variables need to be exported on the command line. Example: export PG_HOST=localhost and export PG_PORT=5432, etc.

Note added much much later: You need to require dotenv in the db-config.js file in order for the environment variables to work as expected…added above!!

  • Test this is all working as it should by running sqlz model:generate --name User --attributes firstName:string,lastName:string,email:string in the command line. A model file and a migration file will be generated automatically for this test model. NOTE: you can edit the model and migration files however you want. Then run sqlz db:migrate to see the effects of these files in your database. Running sqlz db:migrate:undo will undo the migration, so you can play around with changing the files to customize the model and migration however you want.
  • Once everything’s working, delete the user model and migration files before proceeding with the application.

Some Notes

When I was building the models without using sequelize-cli, I had done a few customizations for personal preference. For example how I named some variables, or using an underscored version of the column names in my database. None of this can be set up by default using the CLI, so if you want a lot of customizations, it will add some extra steps to using this tool.

Another thing that caused some confusion for me at first was separating the files generated by the CLI from the files needed to actually run the full application in my mind. Sequelize CLI is a development tool which does not interact with the app at runtime. As a result, you need to require db/models/index.js in app.js (a step that was left out of most tutorials I came across) as follows.

Step 6 - Connect the Database Instance to the Application

Thankfully most of the work is done for you by Sequelize CLI. You just need to add the following to app.js:

const db = require('./db/models/index');

db.sequelize.sync()
	.then(() => console.log('Database connected & synched.'))
	.catch(err => console.error('Unable to connect to the database:', err));

The sync() method comes from Sequelize, and it creates tables in the database by following the model structure you’ve defined in the model files. Have a look at the ./db/models/index.js file and you’ll see that it loops through all of the files in that folder except index.js—so, all of your model files. These are the tables that will be created when you run sync(). And that’s the magic of Sequelize CLI—for the most part, it creates these files for you!

Step 7 - Add All Models

Use the sqlz model:generate command to create all the known models for your application. Edit the files (don’t forget to edit the migration file any time you edit the model file) as needed for your application and preferred customizations. For example if you want to include any validations or required fields, now’s the time to make those edits.

Based on this tutorial I decided to create all of the basic models first (without associations), and then go back and add the associations, testing and committing along the way. This ensures I can change the tables without altering the data…the whole reason I found it necessary to take 10 steps back and re-create the app using this method.

Example Model

sqlz model:generate --name Language --attributes language_name:string

Edit the generated ./db/models/language.js file according to preference. My result:

'use strict';

module.exports = (sequelize, Sequelize) => {
  const Language = sequelize.define('Language', {
    language_id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false,
    },
    language_name: {
      type: Sequelize.STRING,
      unique: true,
      allowNull: false,
    },
  },
    {
      underscored: true,
    },
  );

  Language.associate = function (models) {
    // associations can be defined here
  };

  return Language;
};

Edit the generated migration file in turn. In my result, note I have edited the column names to reflect my preference of underscored column names:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Languages', {
      language_id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
      },
      language_name: {
        type: Sequelize.STRING,
        unique: true,
        allowNull: false,
      },
      created_at: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updated_at: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    },
      {
        underscored: true,
      },
    );
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Languages');
  }
};

Run sqlz db:migrate to generate these tables.

NOTE: If the table is not being created after running db:migrate, open the data tables (can be done on the Postgres command line, or in a program like Postico or PGAdmin) and check your SequelizeMeta table. This is a table that is generated automatically by Sequelize CLI to keep track of what migrations are available. If you see that one of your models isn’t listed in this file, that would explain why an expected migration isn’t happening. In this case, you might reconfirm that Sequelize CLI is using the correct database (I ran into an issue where I needed to re-export my environment variables, for example).

Step 8 - Add Seed Data

IMPORTANT NOTE ADDED LATER: Seeding data as described below can throw off the primary key sequence in Postgres databases. If this happens, you’ll get a bunch of errors any time you try to add records to the seeded databases (it will try to create a new record using an existing primary key). I wrote about how to fix this problem, for reference.

It helps to add some seed data to test the migrations, especially as you add associations. Doing so will further demonstrate that the data contained in the tables aren’t being lost when migrations are run. To do this with the Languages model above:

sqlz seed:generate --name languages

A new file is automatically created at ./db/seeders/XXXXXXX-languages.js. Open this file and add some seed data:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Languages', [
      {
        language_name: 'English',
        created_at: "2019-01-01 00:01:01.01-01",
        updated_at: "2019-01-01 00:01:01.01-01",
      },
      {
        language_name: 'French',
        created_at: "2019-01-01 00:01:01.01-01",
        updated_at: "2019-01-01 00:01:01.01-01",
      },
      {
        language_name: 'German',
        created_at: "2019-01-01 00:01:01.01-01",
        updated_at: "2019-01-01 00:01:01.01-01",
      },
    ], {});
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('Languages', null, {});
  }
};

Add the data to the data table:

sqlz db:seed:all

Note: there may be a better way to seed the dates. However using new Date.now() doesn’t work, because this generates a BIGINT whereas the postgres column requires a TIMESTAMP WITH TIME ZONE. You also cannot omit these fields, because it throws an error violating the NOT NULL requirement. For the sake of seeding, I opted not to figure out how to insert the current date programatically.

Another note: sqlz db:seed:all is only capable of seeding all of the seeders. Contrary to the docs, just running sqlz db:seed does not seed the single recently added file. Since seeders will error out if there is already data on the tables they’re trying to seed, you might find yourself regularly dropping and then re-migrating all tables in order to seed data. Otherwise, you can seed a single seeder file with the following command:

sqlz db:seed --seed XXXXXXX-languages.js

A single seed can also be reverted:

sqlz db:seed:undo --seed XXXXXXX-languages.js

Step 9 - Add Associations

Depending on the type of association, different steps are required. The Sequelize docs on associations discuss the types in depth.

One-To-Many Relationship (hasMany)

Example: A Country has many Venues, while a Venue has only one Country.

The association method in this case would go on the Country model: Country.hasMany(models.Venue). This will add a country_id column to the Venues table. Here are all of the steps to follow:

  1. Add association method (such as Country.hasMany(models.Venue)) to the parent Country model file (Docs call this the source).
  2. Add new column (such as country_id) to the Venue child model file (Docs call this the target). Include the references option to ensure referential integrity…this means for example, you can’t add 897097 as a country_id for a venue if this id does not already exist in the Countries table.
  3. sqlz migration:generate --name associate-country-to-venue
  4. Open the new migration file and add a QueryInterface.addColumn to the up method, and add a QueryInterface.removeColumn to the down method. Include all of the references and validations as required.
  5. sqlz db:migrate then check the new column has been added to the Venues table.
  6. Test the down method by running sqlz db:migrate:undo. Once ok, run sqlz db:migrate again to confirm the change.
  7. Optional: create a new seeder file (see step 8 above) for a venue which includes the country_id field. Test the references by trying to add a country_id that doesn’t exist in the Countries table. (You will of course need some countries seeded to do this.)
// models/country.js

module.exports = (sequelize, Sequelize) => {
  const Country = sequelize.define('Country', {...},{...});

  Country.associate = models => {
    Country.hasMany(models.Venue);
  };

  return Country;
};


// models/venue.js

module.exports = (sequelize, Sequelize) => {
  const Venue = sequelize.define('Venue', {
    country_id: {
      type: Sequelize.INTEGER,
      validate: {
        notEmpty: true,
      },
      references: {
        model: 'Countries',
        key: 'country_id',
      },
    },
    ...},{...});

  return Venue;
};


// migrations/XXXX-associate-country-to-venue.js

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn(
      'Venues',
      'country_id',
      {
        type: Sequelize.INTEGER,
        validate: {
          notEmpty: true,
        },
        references: {
          model: 'Countries',
          key: 'country_id',
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
      }
    );
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn(
      'Venues',
      'country_id',
    );
  }
};

Many-To-Many Relationship (belongsToMany through)

Example: A Book can have many Languages, and a Language will have many Books.

The association method in this case would go on both models and must include a matching through option. This will necessitate a new table which will be made up of references to each model. Here are all of the steps to follow:

  1. Add association method belongsToMany to both model files. Be sure to specify the name of the joining table with through (examples below).
  2. sqlz migration:generate --name associate-book-to-language
  3. Open the new migration file and add a createTable to the up method, and add a dropTable to the down method. The table should include a column for each parent model (i.e. book_id and language_id), and each is a primary key.
    • Include references too, to ensure referential integrity…this means for example, you can’t add 897097 as a book_id for a language if this id does not already exist in the Books table.
  4. sqlz db:migrate then check the new table has been added to the database.
  5. Test the down method by running sqlz db:migrate:undo. Once ok, run sqlz db:migrate again to confirm the change.
  6. Optional: create a new seeder file (see step 8 above) which includes the book_id and language_id fields. Test the references by trying to add a book_id that doesn’t exist in the Books table. (You will of course need some books seeded to do this.)
// models/book.js

module.exports = (sequelize, Sequelize) => {
  const Book = sequelize.define('Book', {...},{...});

  Book.associate = models => {
    Book.belongsToMany(models.Language, {
      through: 'BookLanguages'
    });
  };

  return Book;
};


// models/language.js

module.exports = (sequelize, Sequelize) => {
  const Language = sequelize.define('Language', {...},{...});

  Language.associate = models => {
    Language.belongsToMany(models.Book, {
      through: 'BookLanguages'
    });
  };

  return Language;
};


// migrations/XXXX-associate-book-to-language.js

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable(
      'BookLanguages',
      {
        book_id: {
          type: Sequelize.UUID,
          primaryKey: true,
          references: {
            model: 'Books',
            key: 'book_id',
          },
        },
        language_id: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          references: {
            model: 'Languages',
            key: 'language_id',
          },
        },
        created_at: {
          allowNull: false,
          type: Sequelize.DATE,
        },
        updated_at: {
          allowNull: false,
          type: Sequelize.DATE,
        },
      }
    );
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('BookLanguages');
  }
};


// seeders/XXXX-book-languages.js

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('BookLanguages', [
      {
        book_id: 'aa0fb96b-0270-487a-a522-d2a44be23bbc',
        language_id: 1,
        created_at: "2019-01-01 00:01:01.01-01",
        updated_at: "2019-01-01 00:01:01.01-01",
      },
      {
        book_id: '373f57c4-7aaf-4b4d-8549-bef7c8b7a422',
        language_id: 1,
        created_at: "2019-01-01 00:01:01.01-01",
        updated_at: "2019-01-01 00:01:01.01-01",
      },
      {
        book_id: '373f57c4-7aaf-4b4d-8549-bef7c8b7a422',
        language_id: 6,
        created_at: "2019-01-01 00:01:01.01-01",
        updated_at: "2019-01-01 00:01:01.01-01",
      },
      // {
      //   // Seed without valid ref correctly errors out
      //   book_id: 'ec0c5cba-20f4-40cd-986c-70c75272d18c',
      //   language_id: 1,
      //   created_at: "2019-01-01 00:01:01.01-01",
      //   updated_at: "2019-01-01 00:01:01.01-01",
      // },
    ], {});
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('BookLanguages', null, {});
  }
};

One-To-One Relationships (hasOne or belongsTo)

I don’t have any of these in my app for examples but the article I learned all this from has a good walk-through.

Tags icon Stacked price tag looking things Tags: