How to use ‘sequelize.sync()’ without difficulties
Recently, I’ve participated in one project developed in node.js. As an ORM tool, Sequelize has been introduced and used in the project to implement all kinds of queries such as DDL, DML, etc.
Since I have background knowledge of Django framework, which is well-known for its well-made innate ORM tool named “Django ORM”, I started comparing some features between Django ORM and Sequelize.
Before long, I noticed that there isn’t any tool for generating migration script. (But it seems that similar nodo.js ORM typeORM has a tool for auto-migration though.) That means you have to create a whole migration script like below manually all by yourself.
But, Sequelize has one only weapon called “sequelize.sync()”.
In this post, I will talk about a few points you have to be careful during and after using “sequelize.sync()”.
During using ‘sync()’
“force: true” is very productive but it can cost a lot
On this account, Sequelize’s DB sync command, sequelize.sync(), is widely used in many projects including the project I joined.
sequelize.sync()That command creates new tables according to the schema specified in the model. You can see what Sequelize is doing through the log printed on the terminal.
const sequelize = new Sequelize(
config.database,
config.username,
config.password,
{
dialect: config.dialect as Dialect,
define: config.define,
dialectOptions: config.dialectOption,
pool: config.pool,
logging: true,
}
);If the logging option was false, turn it true and run the server.
$ npx nodemon src/server.ts
[nodemon] starting `ts-node src/server.ts`
🚀 Server ready at http://localhost:4000/graphqlExecuting (default): CREATE TABLE IF NOT EXISTS `channels` (`id` CHAR(36) BINARY NOT NULL , `type` ENUM('PRIVATE', 'PUBLIC') DEFAULT 'PRIVATE', `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `channels`
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` CHAR(36) BINARY NOT NULL , `email` VARCHAR(255), `password` VARCHAR(255), `name` VARCHAR(255), `nickname` VARCHAR(255), `thumbUrl` VARCHAR(255), `photoURL` VARCHAR(255), `birthday` DATE, `gender` ENUM('MALE', 'FEMALE'), `phone` VARCHAR(255), `socialId` VARCHAR(255), `authType` ENUM('EMAIL', 'FACEBOOK', 'GOOGLE', 'APPLE'), `verified` TINYINT(1) DEFAULT false, `statusMessage` TEXT, `isOnline` TINYINT(1), `lastSignedIn` DATETIME, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, PRIMARY KEY (`id`)) ENGINE=InnoDB;
...
As you can check in the SQL queries, It won’t do any field additions/alteration/deletions works if the table of the field exists.
With ‘force’ option, however, it will alter the table, which means you can fully sync between model schema you defined and database.
sequelize.sync({ force: true })Though this is useful during developing the service, you have to be very careful with ‘force’ option. Sequelize adds “DROP TABLE” queries additionally as below. So each time .sync is called, the table is dropped if it already exists.
$ npx nodemon src/server.ts
[nodemon] starting `ts-node src/server.ts`
🚀 Server ready at http://localhost:4000/graphqlExecuting (default): DROP TABLE IF EXISTS `replies`;
Executing (default): DROP TABLE IF EXISTS `photos`;
Executing (default): DROP TABLE IF EXISTS `notifications`;
Executing (default): DROP TABLE IF EXISTS `messages`;
Executing (default): DROP TABLE IF EXISTS `galleries`;
Executing (default): DROP TABLE IF EXISTS `friends`;
Executing (default): DROP TABLE IF EXISTS `memberships`;
Executing (default): DROP TABLE IF EXISTS `users`;
Executing (default): DROP TABLE IF EXISTS `channels`;
Executing (default): DROP TABLE IF EXISTS `channels`;
Executing (default): CREATE TABLE IF NOT EXISTS `channels` (`id` CHAR(36) BINARY NOT NULL , `type` ENUM('PRIVATE', 'PUBLIC') DEFAULT 'PRIVATE', `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, PRIMARY KEY (`id`)) ENGINE=InnoDB;
...
In conclusion, use ‘force’ option to sync between model schema and database only when you don’t need to maintain data in the table.
Because of this problem, you cannot help using migration script when releasing the service on the production server.
Some problems you might face after using ‘sync()’
The problem of pending migration
If you stop using ‘sync()’ and then start using Sequelize’s migration file system, you could have pending migration files.
Let me explain what pending migration files are and how you can deal with them. Suppose you had used ‘sync()’ during developing the product and you started using migration scripts after the service released.
As above, you might have only a few migration files. And you likely don’t have a migration script of DDL(Data Definition Language) to create the tables.
If you need to create a new local environment from scratch for testing or other people to newly set up the project, you might end up using ‘sync()’ command again. As you don’t have a migration script to set up a new database.
However, if you use ‘sync()’, then you will have the error below when you execute “npx sequelize db:migrate” command.
$ npx sequelize db:migrateERROR: Table 'table_users' already exist.
Because the changes in those migration files are already applied to the database when ‘sync’ is run. ‘sync()’ command finds any changes in the model schema even before you run migration scripts. If you want to create and run a new migration file for the production, you have to delete these pending migration scripts.
At this point, the easiest way is faking migration by inserting data with raw SQL. Each time the migration file is run, Sequelize registers the name of the executed migration file to the database table named ‘SequelizeMeta’.
$ mysql -u root -pmysql> INSERT INTO hackatalk_dev.SequelizeMeta VALUES ('20200215081658-Reply.js');
Query OK, 1 row affected (0.04 sec)mysql> INSERT INTO hackatalk_dev.SequelizeMeta VALUES ('20200216091406-Message.js');
Query OK, 1 row affected (0.04 sec)
Literally speaking, this is a makeshift. So some of you might not like this way. And that is correct. You should not use Sequelize’s migration system right after using ‘sync()’ without fixing some code. Let’s find out what those ways are.
One way is generating migration scripts for all models.
If you want to stop using ‘sequelize.sync()’ and use Sequelize’s migration
There is a command to generate a skeleton of DDL script. But relations and details are not automatically created through this command.
$ npx sequelize-cli model:generate --name User --attributes firstName:string, lastName:string, email:string ...$ npx sequelize-cli model:generate --name Channel --attributes type:string, name:string, ...$ npx sequelize-cli model:generate --name Membership --attributes channelId:string, userId:string, ......
When the migration file is created by Sequelize, change the name of it with the date value earlier than exiting files. This is because Sequelize executes migration files in the sequence of the file name. Each script should have “queryInterface.createTable(…)”
If you finish creating these files, run the migration command
$ npx sequelize db:migrateLoaded configuration file "config/config.js".
== 20200215063339-Channel: migrating =======
== 20200215063339-Channel: migrated (0.058s)== 20200215073023-User: migrating =======
== 20200215073023-User: migrated (0.043s)== 20200215075119-Membership: migrating =======
== 20200215075119-Membership: migrated (0.053s)...
As shown above, the tables are created in the database. If the table already exists in the database, migration scripts will overwrite the current database schema, and everything would be the same.
After running migration, you can add migration script without error.
The Second way is using the raw sql queries created by ‘sync()’.
When ‘sync()’ command is run, the SQL queries will be printed on the server terminal.
xecuting (default): CREATE TABLE IF NOT EXISTS `channels` (`id` CHAR(36) BINARY NOT NULL , `type` ENUM('PRIVATE', 'PUBLIC') DEFAULT 'PRIVATE', `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `channels`
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` CHAR(36) BINARY NOT NULL , `email` VARCHAR(255), `password` VARCHAR(255), `name` VARCHAR(255), `nickname` VARCHAR(255), `thumbUrl` VARCHAR(255), `photoURL` VARCHAR(255), `birthday` DATE, `gender` ENUM('MALE', 'FEMALE'), `phone` VARCHAR(255), `socialId` VARCHAR(255), `authType` ENUM('EMAIL', 'FACEBOOK', 'GOOGLE', 'APPLE'), `verified` TINYINT(1) DEFAULT false, `statusMessage` TEXT, `isOnline` TINYINT(1), `lastSignedIn` DATETIME, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `users`
...If we use these raw SQL queries, we can create models in the database effortlessly. In other words, use ‘sync()’ until you are happy with model schema, and then grab the raw SQL output on the terminal to use it as a first migration.
Make a migration script and add SQL queries as below.
module.exports = {
up: function (queryInterface, Sequelize) {
var sql = '''
xecuting (default): CREATE TABLE IF NOT EXISTS `channels` (`id` CHAR(36) BINARY NOT NULL , `type` ENUM('PRIVATE', 'PUBLIC') DEFAULT 'PRIVATE', `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `channels`
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` CHAR(36) BINARY NOT NULL , `email` VARCHAR(255), `password` VARCHAR(255), `name` VARCHAR(255), `nickname` VARCHAR(255), `thumbUrl` VARCHAR(255), `photoURL` VARCHAR(255), `birthday` DATE, `gender` ENUM('MALE', 'FEMALE'), `phone` VARCHAR(255), `socialId` VARCHAR(255), `authType` ENUM('EMAIL', 'FACEBOOK', 'GOOGLE', 'APPLE'), `verified` TINYINT(1) DEFAULT false, `statusMessage` TEXT, `isOnline` TINYINT(1), `lastSignedIn` DATETIME, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `users`
...''';
return queryInterface.sequelize.query(sql, {
type: Sequelize.QueryTypes.RAW
});
},
down: function (queryInterface, Sequelize) {
var sql = '''
DROP TABLE `Channel`
DROP TABLE `User`
DROP TABLE `Membership`
...''';
return queryInterface.sequelize.query(sql, {
type: Sequelize.QueryTypes.RAW
});
}
};
After executing migrate command, you get database that is fully set up.
$ npx sequelize db:migrateDon’t fortget to remove ‘sync()’ option from now on and use migration script instead.
P.S. Feel free to leave a comment if you find some error or have other opinions.
