Learning the Sequelize sync method

The Sequelize instance method sync() is used to synchronize your Sequelize model with your database tables.

The synchronization happens at the table level. When your table doesn’t exist the sync() method will generate and run a CREATE TABLE statement for you.

The sync() method also contains useful options that can modify the generated SQL query under the hood.

Let’s see an example of the sync() method in action.

Suppose you create a Sequelize model instance named Invoice with the following definitions:

const Invoice = sequelize.define(
  "Invoice",
  { amount: Sequelize.INTEGER },
  { timestamps: false }
);

Next, you call the sync() method from the instance as shown below:

Invoice.sync().then(
  () => console.log("Sync complete")
);

The generated SQL query from the sync() method call above would be as follows:

CREATE TABLE IF NOT EXISTS `Invoices` (
  `id` integer NOT NULL AUTO_INCREMENT,
  `amount` integer,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

The generated CREATE TABLE statement contains an IF NOT EXISTS operator to avoid SQL from throwing an error if the table already exists.

If you want to re-create the table on sync, you can add the force: true option to the sync() method call:

Invoice.sync({ force: true }).then(
  () => console.log("Sync complete")
);

The generated SQL statement will include a DROP TABLE statement as follows:

DROP TABLE IF EXISTS `Invoices`;

CREATE TABLE IF NOT EXISTS `Invoices` (
  `id` integer NOT NULL AUTO_INCREMENT,
  `amount` integer,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

The force: true option is dangerous because dropping the table will delete all of your data in that table.

If you need to add, change, or remove some columns from your table, it’s recommended to use the alter: true option instead:

Invoice.sync({ alter: true }).then(
  () => console.log("Sync complete")
);

Here are the generated SQL statements from the above code:

CREATE TABLE IF NOT EXISTS `Invoices` (
  `id` integer NOT NULL AUTO_INCREMENT,
  `amount` integer,
  PRIMARY KEY (`id`)) ENGINE = InnoDB;

ALTER TABLE `Invoices` CHANGE `amount` `amount` INTEGER;

After the usual CREATE TABLE statement, the ALTER TABLE statement is added to perform the changes in your database table.

The alter option also allows you to keep the columns removed from the model in the table by adding the drop: false option to it.

Suppose you remove the amount attribute from the model and replace it with a total attribute as follows:

const Invoice = sequelize.define(
  "Invoice",
  { total: Sequelize.STRING },
  { timestamps: false },
);

With the alter: true option, the following SQL statement will be executed:

CREATE TABLE IF NOT EXISTS `Invoices` (
  `id` integer NOT NULL AUTO_INCREMENT,
  `total` varchar(255),
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

ALTER TABLE `Invoices`
  DROP `amount`;

ALTER TABLE `Invoices` CHANGE `total` `total` VARCHAR(255);

As you can see, the amount column is dropped from the Invoices table with the alter option.

You can make Sequelize keep the amount column by adding the drop: false option to the alter option.

The following JavaScript code:

Invoice.sync({ alter: { drop: false } }).then(
  () => console.log("Sync complete")
);

Will generate the following SQL statements and execute them:

CREATE TABLE IF NOT EXISTS `Invoices` (
  `id` integer NOT NULL AUTO_INCREMENT,
  `total` varchar(255),
  PRIMARY KEY (`id`)) ENGINE = InnoDB;

ALTER TABLE `Invoices`
  ADD `total` VARCHAR(255);

As you can see, the ALTER TABLE ... DROP statement is omitted from the generated SQL statements above.

You can see the full list of options supported by the Sequelize sync() method here.

And that’s how the sync() method works in Sequelize. It allows you to create, re-create, or alter the table referenced by your Sequelize model.

Please note that the sync() method is also dangerous because you won’t be able to undo any changes made by the method.

To keep track and undo (redo) any change made to your database, please use Sequelize migrations instead.