🍂 How to make foreign keys non-null in Sequelize in Node.js [video version available]

!README-sequelize-foreign-key.md

🍂 How to make foreign keys non-null in Sequelize of Node.js [video version available]

About this article

This article describes how to make foreign keys non-null when the association is set by using the belongsTo method etc. in Sequelize of Node.js. The related resources are shown below.

• Source code
• 日本語版 / Japanese version
• Video version

Workflow

The workflow is shown below.

1. Creating a database
2. Coding preparation
3. Coding
4. Operation check

Creating a database

Execute the following SQL statements to create a database for operation check.

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

To execute the SQL statements from the terminal, run the following command (if you have not set a password, the -p option is not required).

mysql -u root -p

Coding preparation

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

mkdir sequelize-foreign-key
cd sequelize-foreign-key
npm init -y
npm install --save dotenv mysql2 sequelize
touch .env main.js

Coding

main.js

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

Click to go to main.js

The point is shown below.

1. Set the foreignKey option (second argument) of the belongsTo method to {allowNull: false}.

.env

Open .env in the editor and enter the following point.

• DB_URL: Database connection string (e.g. mysql://tech_blog:password@localhost:3306/tech_blog)

Click to go to .env.example

Operation check

Run the following command in the terminal to create the table.

node -r dotenv/config main.js

Check that the following two points are included in the execution result.

• userId INTEGER NOT NULL
• articleId INTEGER NOT NULL

The execution result is shown below.

Executing (default): DROP TABLE IF EXISTS `userArticle`;
Executing (default): DROP TABLE IF EXISTS `article`;
Executing (default): DROP TABLE IF EXISTS `user`;
Executing (default): DROP TABLE IF EXISTS `user`;
Executing (default): CREATE TABLE IF NOT EXISTS `user` (`id` INTEGER NOT NULL auto_increment, `name` VARCHAR (255) NOT NULL,` createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (` id`)) ENGINE = InnoDB;
Executing (default): SHOW INDEX FROM `user`
Executing (default): DROP TABLE IF EXISTS `article`;
Executing (default): CREATE TABLE IF NOT EXISTS `article` (`id` INTEGER NOT NULL auto_increment, `title` VARCHAR (255) NOT NULL,` createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (` id`)) ENGINE = InnoDB;
Executing (default): SHOW INDEX FROM `article`
Executing (default): DROP TABLE IF EXISTS `userArticle`;
Executing (default): CREATE TABLE IF NOT EXISTS `userArticle` (`id` INTEGER NOT NULL auto_increment, `createdAt` DATETIME NOT NULL,` updatedAt` DATETIME NOT NULL, `userId` INTEGER NOT NULL,` articleId` INTEGER NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`articleId`) REFERENCES` article` (` id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB;
Executing (default): SHOW INDEX FROM `userArticle`

Conclusion

I remember struggling to find a way to make foreign keys non-null from the official Sequelize documentation in the past, but now it's easy to find from the Associations page. In that case, the significance of the existence of this article will be lost, but since I wrote it with great effort, I post it because a bad bush is better than the open field. Thank you for reading!

.env.example

DB_URL=mysql://tech_blog:password@localhost:3306/tech_blog

.gitignore

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

db.sql

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

main.js

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

if (require.main === module) {
  main()
}

async function main () {
  try {
    const sequelize = new Sequelize(process.env.DB_URL)

    try {
      const user = sequelize.define('user', {
        name: {type: DataTypes.STRING, allowNull: false},
      }, {freezeTableName: true})

      const article = sequelize.define('article', {
        title: {type: DataTypes.STRING, allowNull: false},
      }, {freezeTableName: true})

      const userArticle = sequelize.define('userArticle', {
      }, {freezeTableName: true})

      userArticle.belongsTo(user, {
        foreignKey: {allowNull: false}, // <1>
        onUpdate: 'cascade',
        onDelete: 'cascade',
      })

      userArticle.belongsTo(article, {
        foreignKey: {allowNull: false},
        onUpdate: 'cascade',
        onDelete: 'cascade',
      })

      await sequelize.sync({force: true})
    } finally {
      sequelize.close()
    }
  } catch (err) {
    console.error(err)
  }
}

package.json

{
  "name": "sequelize-foreign-key",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "db": "mysql -u root < db.sql",
    "dev": "node -r dotenv/config main.js"
  },
  "keywords": [],
  "author": "",
  "license": "MIT",
  "dependencies": {
    "dotenv": "^16.0.0",
    "mysql2": "^2.3.3",
    "sequelize": "^6.19.0"
  }
}