😳 How to prevent a child from deleting a parent in Sequelize

!README-sequelize-unexpected-delete.md

😳 How to prevent a child from deleting a parent in Sequelize

About this article

While coding with Sequelize, I found an interesting behavior where when I deleted a record in a child model, the record in the parent model was deleted. This article describes how to reproduce and prevent the behavior. The related resources are shown below.

• Source code
• 日本語版 / Japanese version

Procedure

The procedure for reproducing the behavior is shown below.

1. Database preparation
2. Coding preparation
3. Coding
4. Execution

Database preparation

Execute the following SQL statements to prepare a database.

Click to go to db.sql

To execute a SQL statement in your terminal, run the following command. If you have not set a password, you do not need the -p option.

mysql -u root -p

Coding preparation

Run the following commands in your terminal to prepare for coding.

mkdir sequelize-unexpected-delete
cd sequelize-unexpected-delete
npm init -y
npm install dotenv mysql2 sequelize

Coding

main.js

Open main.js in your editor and enter the following content.

Click to go to main.js

.env

Open .env in your editor and enter the following environment variable.

• DB_URL: database connection string

An example is shown below.

Click to go to .env

Execution

Run the following command in your terminal.

node -r dotenv/config main.js

The execution result is shown below. Only CREATE TABLE, INSERT, SELECT, and DELETE statements are excerpted.

### await sequelize.sync({force: true})
CREATE TABLE IF NOT EXISTS `parents` (`id` INTEGER NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `children` (`id` INTEGER NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `parentId` INTEGER, PRIMARY KEY (`id`), FOREIGN KEY (`parentId`) REFERENCES `parents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `anotherChildren` (`id` INTEGER NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `parentId` INTEGER, PRIMARY KEY (`id`), FOREIGN KEY (`parentId`) REFERENCES `parents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

### const parent = await model.parent.create({})
INSERT INTO `parents` (`id`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?); "2022-06-14 20:13:55", "2022-06-14 20:13:55"

### const child  = await model.child.create({parentId: parent.id})
INSERT INTO `children` (`id`,`createdAt`,`updatedAt`,`parentId`) VALUES (DEFAULT,?,?,?); "2022-06-14 20:13:55", "2022-06-14 20:13:55", 1

### const anotherChild  = await model.anotherChild.create({parentId: parent.id})
INSERT INTO `anotherChildren` (`id`,`createdAt`,`updatedAt`,`parentId`) VALUES (DEFAULT,?,?,?); "2022-06-14 20:13:55", "2022-06-14 20:13:55", 1

### await child.destroy()
SELECT `id`, `createdAt`, `updatedAt` FROM `parents` AS `parent` WHERE `parent`.`id` = 1;
DELETE FROM `parents` WHERE `id` = 1
DELETE FROM `children` WHERE `id` = 1

As you can see from the last three lines of the execution result, the record of the parent model is searched and deleted before the record of the child model is deleted.

Conditions for reproducing

I think that the conditions for reproducing the behavior are the following three points.

1. Multiple child models have belongsTo associations for the same parent model.
2. The same options object is used when setting up the belongsTo association.
3. The belongsTo association options include onDelete:'cascade'.

How to prevent

To prevent the parent model record from being deleted when a child model record is deleted, avoid using the same options object when setting up the belongsTo association. An example of the modified source code is shown below.

// before
const options = {onDelete: 'cascade'}
model.child.belongsTo(model.parent, options)
model.anotherChild.belongsTo(model.parent, options)

// after
const options = () => {onDelete: 'cascade'}
model.child.belongsTo(model.parent, options())
model.anotherChild.belongsTo(model.parent, options())

The execution result after the above change is shown below. Only INSERT and DELETE statements are excerpted.

### const parent = await model.parent.create({})
INSERT INTO `parents` (`id`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?); "2022-06-14 20:13:55", "2022-06-14 20:13:55"

### const child  = await model.child.create({parentId: parent.id})
INSERT INTO `children` (`id`,`createdAt`,`updatedAt`,`parentId`) VALUES (DEFAULT,?,?,?); "2022-06-14 20:13:55", "2022-06-14 20:13:55", 1

### const anotherChild  = await model.anotherChild.create({parentId: parent.id})
INSERT INTO `anotherChildren` (`id`,`createdAt`,`updatedAt`,`parentId`) VALUES (DEFAULT,?,?,?); "2022-06-14 20:37:24", "2022-06-14 20:37:24", 1

### await child.destroy()
DELETE FROM `children` WHERE `id` = 1

Deleting a record in a child model no longer deletes a record in the parent model.

Experiment environment

The experiment environment is show below

• Experiment day: June 15th, 2022
• Sequelize version: 6.20.1
• Node.js version: 16.13.0
• MySQL version: 8.0.27

Conclusion

I spent at least two hours exploring the conditions and countermeasures for this behavior, but I'm happy to have had a rare and valuable experience. I was satisfied when I found out the countermeasures, so I have not investigated the cause. If you know the details, I would appreciate your guidance and comments. I hope this article will help those who have encountered the same behavior. Thank you for reading!

.env

DB_URL=mysql://tech_blog_user:password@localhost:3306/tech_blog_db

.gitignore

/node_modules/
/package-lock.json
# Do no ignore package-lock.json other than gist

db.sql

create database tech_blog_db charset utf8;
create user tech_blog_user@localhost identified by 'password';
grant all privileges on tech_blog_db.* to tech_blog_user@localhost;

main.js

const {Sequelize} = require('sequelize')

main()

async function main () {
  try {
    const sequelize = new Sequelize(process.env.DB_URL, {
      logQueryParameters: true,
    })

    const model = {
      parent: sequelize.define('parent'),
      child: sequelize.define('child'),
      anotherChild: sequelize.define('anotherChild'),
    }

    const options = () => {onDelete: 'cascade'}

    model.child.belongsTo(model.parent, options())
    model.anotherChild.belongsTo(model.parent, options())

    await sequelize.sync({force: true})

    const parent = await model.parent.create({})
    const child  = await model.child.create({parentId: parent.id})
    const anotherChild  = await model.anotherChild.create({parentId: parent.id})

    await child.destroy()
    await sequelize.close()
  } catch (err) {
    console.error(err)
  }
}

package.json

{
  "name": "sequelize-unexpected-delete",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "db": "mysql -u root < db.sql",
    "drop:db": "mysql -u root -e 'drop database tech_blog_db'",
    "drop:user": "mysql -u root -e 'drop user tech_blog_user@localhost'",
    "drop": "npm run drop:db && npm run drop:user",
    "start": "node -r dotenv/config main.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "MIT",
  "dependencies": {
    "dotenv": "^16.0.1",
    "mysql2": "^2.3.3",
    "sequelize": "^6.20.1"
  }
}