How to create a database table using Sequelize code

When you run a query method like findAll() from a Sequelize model, Sequelize will look into your database for an existing table to search the data.

If the table doesn’t exist, then Sequelize will throw a Table doesn't exist (MySQL and SQLite) or Relation doesn't exist (PostgreSQL) error.

There are two ways you can create a table using Sequelize:

• Using the Model.sync() method
• Using sequelize-cli database migration system

This tutorial will help you learn both ways. Let’s start with learning about the Model.sync() method.

Sequelize create table with sync() method

The Sequelize model you create using the sequelize.define() method comes with a sync() method that you can use to create a table.

The table created using the sync() method will follow the model definition for its column(s).

For example, suppose you have the following User model defined in your code:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  isActive: {
    type: Sequelize.BOOLEAN,
    defaultValue: false,
  },
});

await User.sync();

The call to User.sync() above will cause Sequelize to synchronize the model with the database.

The method will create the database table if it doesn’t already exist.

By default, Sequelize will include the createdAt and updatedAt columns to keep track of when you insert or update a row with Sequelize.

If you don’t want the createdAt and updatedAt columns, you can add the timestamps: false option to your model definition as shown below:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  isActive: {
    type: Sequelize.BOOLEAN,
    defaultValue: false,
  },
},
{
  timestamps: false,
});

sync() method options

The sync() method also accepts two options as described below:

• sync({ force: true }) - This option force Sequelize to create a table, dropping it first if it already existed
• sync({ alter: true }) - This option alters the columns and data types of an existing table to match the model

The alter option will also create the table first if it doesn’t exist.

Now you know how to use the sync() method to create a table with Sequelize.

Sequelize create table with migrations

Sequelize also comes with a command line tool called sequelize-cli that provides support for migration and new project bootstrapping.

In ORM libraries, migrations are code files that keep track of changes to a database structure and definitions. These migration files help you to make and revert changes to your database with ease.

Let’s see how you can use Sequelize migrations to create a table.

First, you need to install sequelize-cli globally using npm:

npm install -g sequelize-cli

Or if you don’t want to install sequelize-cli, you can also use the NPX package runner:

npx sequelize-cli --version

First, you need to run the init command to generate several folders required by Sequelize:

npx sequelize-cli init

Your project should now have the following folders:

• config/ - contains config file that takes your environment into account (production or development)
• models/ - contains all models for your project
• migrations/ - contains all migration files for you to run
• seeders/ - contains all seed files

First, open the config/config.json file and make sure you have the right credential to connect to your database.

For local development, you only need to adjust the development credential.

Next, you need to create a new migration file with model:generate command. This command requires you to pass the model name and attributes.

Consider the following example:

npx sequelize-cli model:generate --name User \
--attributes firstName:string,isActive:boolean

The above command would generate a new migration and model files for the User model.

In the generated migration file, you should see two asynchronous functions exported from the file:

• The up() function for making changes
• The down() function for undoing changes

The last step is to run the up() function from the migration file with the db:migrate command as follows:

npx sequelize-cli db:migrate

You can see the Users table generated from the User model.

There’s also another table called SequelizeMeta to keep track of migrations you’ve executed in your database.

To revert the changes made by the db:migrate command, you can run the db:migrate:undo command:

npx sequelize-cli db:migrate:undo

The down() function will be executed, reverting any changes made by the up() function.

You can visit the Sequelize migration documentation to learn more about it.

Conclusion

To summarize, Sequelize provides you with two convenient ways to create a table in your SQL database.

You can call the Model.sync() method to perform a quick synchronization between the model and the database, or you can use the migration system.

You can choose which one of the two ways you want to use in your project.