Search code examples
node.jssqlitesequelize.jsbackup

_backup table out of sync with main table in Sequelize

In my Node/SQLite/Sequelize project, the database is initialised like this:

import db from './db.ts';
import Order from './orders.ts';
import User from './users.ts';

await db.sync({ alter: true, logging: false });

Today in my production instance, this message appeared:

SequelizeDatabaseError: SQLITE_ERROR: table Orders_backup has 15 columns but 16 values were supplied

I understand the message, and I get that a recently added column in Orders has for some reason not been added to Orders_backup.

My question is: why? How did the structure of the backup table get out of sync with the main table? Is there a way to prevent this?

Solution

  • When you have db.sync({ alter: true }) enabled, Sequelize will do the following for each table:

    CREATE TABLE IF NOT EXISTS `YourTable_backup`
INSERT INTO `YourTable_backup` ... FROM `YourTable`;
DROP TABLE `YourTable`
CREATE TABLE IF NOT EXISTS `YourTable`
INSERT INTO `YourTable` ... FROM `YourTable_backup`;

    You can see the queries yourself if you will pass logger function to sync:

    await db.sync({alter: true, logging: (sql: string) => console.log(sql)});

    So, this behaviuor can lead to many many problems if interrupted. For example in development, if you use nodemon and will update your code two times in few seconds, first sync will start, then it will be interrupted somewhere and second sync will be started. That can lead to damaged data in your main table, or some leftover *_backup tables, which should not normally exist in database.

    In production, this problem can arise on deployment, if for some reason your CI/CD will be triggered multiple times or if someone will manuall restart an application twice, while first sync is not finished yet.

    Ore more problem with sync, which is visible from the log above, is that is copies all your data twise per table per sync. So when your database will grow for thousands of rows, the time of the sync will grow up to few minutes, which will also enlarge the possible chance of second restart that will interrupt it and left your main or *_backup tables in inconsistent state.

    Considering this all, it is recommended to avoid using of sync in production, as it stated in sequelize docs:

    As shown above, sync({ force: true }) and sync({ alter: true }) can be destructive operations. Therefore, they are not recommended for production-level software. Instead, synchronization should be done with the advanced concept of Migrations, with the help of the Sequelize CLI.