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

sequelize.js

(More or less) formal docs and API reference can be found at http://sequelize.readthedocs.org/en/latest/api/sequelize/#sync

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.

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);
};

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 [email protected] [email protected].

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);

Tested with:

npm install [email protected] [email protected].

force: false alone does not sync the schema, you need alter: true

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

To both 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');

(async () => {
{
  const sequelize = new Sequelize({
    dialect: 'sqlite',
    storage: 'tmp.sqlite',
  });
  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 = new Sequelize({
    dialect: 'sqlite',
    storage: 'tmp.sqlite',
  });
  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

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));

It does not user ALTER statements because SQLite does not currently support them. 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.

Migrations tend to be the recommended way however of updating the schema in real projects: Sequelize: Changing model schema on production

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

Related

Recent Posts