How does sequelize.sync() work, specifically the force option?

Question

What does the force option on sequelize.sync() do?

sequelize.sync({
    force: true
});

Specifically, I am interested in knowing what force: false does? Will it not sync the schema with the database?

Are there any formal docs for sequelize? I could only find examples inside the docs.

score 66 · Accepted Answer · edited Jun 07 '22 at 19:33

66

(More or less) formal docs and API reference can be found at https://sequelize.org/docs/v6/core-concepts/model-basics/#model-synchronization

To your question: force: true adds a DROP TABLE IF EXISTS before trying to create the table - if you force, existing tables will be overwritten.

edited Jun 07 '22 at 19:33

Ni Xt

35
8

answered Jan 12 '14 at 14:07

Jan Aagaard Meier

28,078
8
95
66

Additionally: if you change the table name (that already exist) of an Entity, the table stays with data, and another table with the new name is created. – Ninja Coding Jun 29 '17 at 14:56
8

This is not answering the question. The question asks what `force: false` does, not what `force: true` does. – Trevor Jex Apr 25 '19 at 18:17
I call sequelize.sync({force: false}), but still the tables are re-created. Why is this? – David Okwii Feb 17 '21 at 11:55
1

Link is dead. That's the reason why (stand-alone) links in answers should be avoided. – robsch Jul 21 '21 at 08:03

score 54 · Answer 2 · answered Sep 25 '16 at 16:20

The OP was asking what force: false does, which is what I wanted to know too, so here's the rest.

The major takeaway, for me, was that the individual fields aren't synced (which is what I was hoping for, coming from the Waterline ORM). Meaning, if you have force: false and the table exists, any field additions/modifications/deletions you have won't be executed.

beforeSync hooks are run
table is dropped if force: true
table is created with if not exists
indexes are added if necessary
afterSync hooks are run

Here's the current code from the github repo for reference:

lib.model.js

Model.prototype.sync = function(options) {
  options = options || {};
  options.hooks = options.hooks === undefined ? true : !!options.hooks;
  options = Utils._.extend({}, this.options, options);

  var self = this
    , attributes = this.tableAttributes;

  return Promise.try(function () {
    if (options.hooks) {
      return self.runHooks('beforeSync', options);
    }
  }).then(function () {
    if (options.force) {
      return self.drop(options);
    }
  }).then(function () {
    return self.QueryInterface.createTable(self.getTableName(options), attributes, options, self);
  }).then(function () {
    return self.QueryInterface.showIndex(self.getTableName(options), options);
  }).then(function (indexes) {
    // Assign an auto-generated name to indexes which are not named by the user
    self.options.indexes = self.QueryInterface.nameIndexes(self.options.indexes, self.tableName);

    indexes = _.filter(self.options.indexes, function (item1) {
      return !_.some(indexes, function (item2) {
        return item1.name === item2.name;
      });
    });

    return Promise.map(indexes, function (index) {
      return self.QueryInterface.addIndex(self.getTableName(options), _.assign({logging: options.logging, benchmark: options.benchmark}, index), self.tableName);
    });
  }).then(function () {
    if (options.hooks) {
      return self.runHooks('afterSync', options);
    }
  }).return(this);
};

Ciro Santilli OurBigBook.com · Answer 3 · 2023-05-09T07:49:14.270

Minimal runnable example

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'tmp.sqlite',
});
(async () => {
const IntegerNames = sequelize.define('IntegerNames', {
  value: { type: DataTypes.INTEGER, },
  name: { type: DataTypes.STRING, },
}, {});
//await IntegerNames.sync({force: true})
await IntegerNames.create({value: 2, name: 'two'});
await sequelize.close();
})();

Setup:

npm install sequelize@6.5.1 sqlite3@5.0.2.

On stdout we can see the queries it did:

Executing (default): DROP TABLE IF EXISTS `IntegerNames`;
Executing (default): CREATE TABLE IF NOT EXISTS `IntegerNames` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `value` INTEGER, `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
Executing (default): PRAGMA INDEX_LIST(`IntegerNames`)
Executing (default): INSERT INTO `IntegerNames` (`id`,`value`,`name`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3,$4);

If we use force: false instead we get the same except there's no DROP at the start:

Executing (default): CREATE TABLE IF NOT EXISTS `IntegerNames` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `value` INTEGER, `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
Executing (default): PRAGMA INDEX_LIST(`IntegerNames`)
Executing (default): INSERT INTO `IntegerNames` (`id`,`value`,`name`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3,$4);

And if we remove sync the table does not get created at all:

Executing (default): INSERT INTO `IntegerNames` (`id`,`value`,`name`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3,$4);

Conclusion: force

force: false: if the table exists, nothing happens to it due to IF NOT EXISTS in CREATE TABLE IF NOT EXISTS. Therefore any table schema modifications are ignored.
force: true: the table is dropped first, so any schema changes take effect CREATE TABLE IF NOT EXISTS. But you lose all data

alter: true, force: false actually attempts to keep data and modify the database

With force: true, the database gets dropped and recreated, so of course it matches the latest schema, but you lose all data.

To try to keep existing data and update the schema, we have to use alter: true in addition to force: false.

The following works and stdout shows the convoluted sequence of queries used to make it work by creating a temporary database IntegerNames_backup:

const assert = require('assert')
const { Sequelize, DataTypes } = require('sequelize');

function getSequelize() {
  if (process.argv[2] === 'p') {
    return new Sequelize('tmp', undefined, undefined, {
      dialect: 'postgres',
      host: '/var/run/postgresql',
    })
  } else {
    return new Sequelize({
      dialect: 'sqlite',
      storage: 'tmp.sqlite',
    })
  }
}

(async () => {
{
  const sequelize = getSequelize()
  const IntegerNames = sequelize.define('IntegerNames', {
    value: { type: DataTypes.INTEGER, },
    name: { type: DataTypes.STRING, },
  }, {});
  await IntegerNames.sync({force: true})
  await IntegerNames.create({value: 2, name: 'two'});
  await IntegerNames.create({value: 3, name: 'three'});
  await sequelize.close();
}

// Alter by adding column.
{
  const sequelize = getSequelize()
  const IntegerNames = sequelize.define('IntegerNames', {
    value: { type: DataTypes.INTEGER, },
    name: { type: DataTypes.STRING, },
    nameEs: { type: DataTypes.STRING, },
  }, {});
  await IntegerNames.sync({
    alter: true,
    force: false,
  })
  await IntegerNames.create({value: 5, name: 'five' , nameEs: 'cinco'});
  await IntegerNames.create({value: 7, name: 'seven', nameEs: 'siete'});
  const integerNames = await IntegerNames.findAll({
    order: [['value', 'ASC']],
  });
  assert(integerNames[0].value  === 2);
  assert(integerNames[0].name   === 'two');
  assert(integerNames[0].nameEs === null);
  assert(integerNames[1].name   === 'three');
  assert(integerNames[1].nameEs === null);
  assert(integerNames[2].name   === 'five');
  assert(integerNames[2].nameEs === 'cinco');
  assert(integerNames[3].name   === 'seven');
  assert(integerNames[3].nameEs === 'siete');
  await sequelize.close();
}
})();

If we remove alter: true it blows up because the new column doe not exist:

SequelizeDatabaseError: SQLITE_ERROR: table IntegerNames has no column named nameEs

On PostgreSQL it actually produces ALTER statements, which is awesome:

Executing (default): ALTER TABLE "public"."IntegerNames" ADD COLUMN "nameEs" VARCHAR(255);
Executing (default): ALTER TABLE "IntegerNames" ALTER COLUMN "value" DROP NOT NULL;ALTER TABLE "IntegerNames" ALTER COLUMN "value" DROP DEFAULT;ALTER TABLE "IntegerNames" ALTER COLUMN "value" TYPE INTEGER;
Executing (default): ALTER TABLE "IntegerNames" ALTER COLUMN "name" DROP NOT NULL;ALTER TABLE "IntegerNames" ALTER COLUMN "name" DROP DEFAULT;ALTER TABLE "IntegerNames" ALTER COLUMN "name" TYPE VARCHAR(255);
Executing (default): ALTER TABLE "IntegerNames" ALTER COLUMN "createdAt" SET NOT NULL;ALTER TABLE "IntegerNames" ALTER COLUMN "createdAt" DROP DEFAULT;ALTER TABLE "IntegerNames" ALTER COLUMN "createdAt" TYPE TIMESTAMP WITH TIME ZONE;
Executing (default): ALTER TABLE "IntegerNames" ALTER COLUMN "updatedAt" SET NOT NULL;ALTER TABLE "IntegerNames" ALTER COLUMN "updatedAt" DROP DEFAULT;ALTER TABLE "IntegerNames" ALTER COLUMN "updatedAt" TYPE TIMESTAMP WITH TIME ZONE;

We see that it just runs an ALTER statement for every column separately, even if the column already exists, in which case it does not get modified in any way.

On SQLite, the way alter works is by creating a new table, and moving all data from the old table to the new one through:

Executing (default): CREATE TABLE IF NOT EXISTS `IntegerNames_backup` (`id` INTEGER PRIMARY KEY, `value` INTEGER, `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `nameEs` VARCHAR(255));
Executing (default): INSERT INTO `IntegerNames_backup` SELECT `id`, `value`, `name`, `createdAt`, `updatedAt`, `nameEs` FROM `IntegerNames`;
Executing (default): DROP TABLE `IntegerNames`;
Executing (default): CREATE TABLE IF NOT EXISTS `IntegerNames` (`id` INTEGER PRIMARY KEY, `value` INTEGER, `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `nameEs` VARCHAR(255));

TODO why doesn't it use ALTER statements? SQLite seems to support them: How do you rename a table in SQLite 3.0? This then breaks constraints which sequelize does not automatically drop and recreate as needed: Process of changing a table with sequelize migration if foreign key constraint is active it is quite sad.

Conclusion: alter

alter: true allows us to easily add new columns, or modify properties of existing columns.

Reasons why this cannot fully replace migrations: Sequelize: Changing model schema on production

it simply cannot handle table or column renames, it just doesn't have enough information to know that the old and new column are the same. I think it would also not handle column deletions on PostgreSQL.
it cannot intelligently add/remove/modify data as might be required during a migration (obviously). As a simple example, adding a NOT NULL constraint may require you to fill in some previously NULL buggy rows with some value of choice.

In February 2022, this is the most elaborate and descriptive answer. — Daggie Blanqx - Douglas Mwangi, Feb 01 '22 at 23:30

Shrey Banugaria · Answer 4 · 2022-11-17T09:41:55.443

If you want don't want to drop the table but want all the table updates (add/remove columns, etc) you can use

sequelize.sync({ force: false, alter: true })

force can be used in a development environment but not in production as it will drop the table first and create again, alter will match the existing model and change the table accordingly. Alter is also not advisable in production as will delete data of the columns removed or type changed, but if you want to avoid migrations and update your table this is the option.

How does sequelize.sync() work, specifically the force option?

4 Answers4

Linked