🌏 How to access PlanetScale from Sequelize

!README-sequelize-planetscale.md

🌏 How to access PlanetScale from Sequelize

About this article

This article describes how to access PlanetScale from Sequelize. The related resources are shown below.

• Source code
• 日本語版 / Japanese version

Source code (before)

The source code before supporting access PlanetScale is shown below.

Click to go to before.js

Source code (after)

The source code after supporting access PlanetScale is shown below.

Click to go to after.js

The points are shown below.

1. Specify ssl: {rejectUnauthorized: true} for dialectOptions when creating an instance of Sequelize to enable SSL connections.
2. [Optional] Add a prefix to the table name.
3. [Optional] Specify as for options when creating an association so that it can be accessed without a prefix.
4. Specify constraints: false as an option when creating associations to prevent foreign key constraints from being created.
5. [Optional] Add a prefix to the table name when issuing a query.

Operation check

Execution result (before)

The SQL statement issued by executing the source code (before) is shown below. Only the CREATE statement and the INSERT statement are excerpted.

CREATE TABLE IF NOT EXISTS `users` (
  `id` INTEGER NOT NULL auto_increment ,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `articles` (
  `id` INTEGER NOT NULL auto_increment ,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  `userId` INTEGER NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO `users` VALUES (NULL, NOW(), NOW())

The command example for outputting the execution result is show below.

DB_URL=mysql://ps_user:ps_pass@localhost:3306/ps_db \
  node before.js

Execution result (after)

The SQL statement issued by executing the source code (before) is shown below. Only the CREATE statement and the INSERT statement are excerpted.

CREATE TABLE IF NOT EXISTS `psUsers` (
  `id` INTEGER NOT NULL auto_increment ,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `psArticles` (
  `id` INTEGER NOT NULL auto_increment ,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  `userId` INTEGER NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `psUsers` VALUES (NULL, NOW(), NOW());

The command example for outputting the execution result is show below.

DB_URL=mysql://xxxxxxxxxxxx:pscale_pw_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx@xxxxxxxxxxxx.ap-northeast-2.psdb.cloud/xxxx \
  node after.js

The following two points can be read from the execution result.

• The ps prefix is added to the table names
• FOREIGN KEY constraints are not created

Conclusion

Since SSL is required on PlanetScale, you need to enable SSL by specifying ssl: {rejectUnauthorized: true} for dialectOptions when creating an instance of sequelize. Also, since PlanetScale does not allow foreign key constraints, you neet to specify constraints: false as options when creating associations to prevent foreign key constraints from being created. Tak_iwamoto explains in detail the point that foreign key constraints cannot be set in the following article.

What is PlanetScale and why it doesn't support foreign key constraints

PlanetScale's Free plan allows you to create only one database, so add a prefix to the table name to avoid duplicate table names. If you can create multiple databases for each app, you don't need to do this.

PlanetScale Pricing offers a Hobby plan that can be used free of charge up to a certain resource consumption. When combined with Vercel etc., even apps that require RDB can be operated for free. However, keep in mind that Vercel's Hobby plan is limited to non-commercial & hobby sites according to Vercel Pricing.

.env.after.example

DB_URL=mysql://xxxxxxxxxxxx:pscale_pw_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx@xxxxxxxxxxxx.ap-northeast-2.psdb.cloud/xxxx

.env.before.example

DB_URL=mysql://ps_user:ps_pass@localhost:3306/ps_db

.gitignore

/node_modules/
/.env.after
/.env.before
/package-lock.json
# do not ignore package-lock.json other than gist

after.js

const {Sequelize} = require('sequelize')

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

async function main () {
  try {
    const sequelize = new Sequelize(process.env.DB_URL, {
      dialectOptions: {
        ssl: { // <1>
          rejectUnauthorized: true,
        }
      },
    })

    const user = sequelize.define('psUser', {}) // <2>
    const article = sequelize.define('psArticle', {})

    article.belongsTo(user, {
      as: 'user', // <3>
      foreignKey: {
        allowNull: false,
      },
      constraints: false, // <4>
    })

    await sequelize.sync({force: true})
    await sequelize.query('INSERT INTO `psUsers` VALUES (NULL, NOW(), NOW())') // <5>
    await sequelize.close()
  } catch (err) {
    console.error(err)
  }
}

before.js

const {Sequelize} = require('sequelize')

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

async function main () {
  try {
    const sequelize = new Sequelize(process.env.DB_URL)
    const user = sequelize.define('user', {})
    const article = sequelize.define('article', {})

    article.belongsTo(user, {
      foreignKey: {
        allowNull: false
      },
    })

    await sequelize.sync({force: true})
    await sequelize.query('INSERT INTO `users` VALUES (NULL, NOW(), NOW())')
    await sequelize.close()
  } catch (err) {
    console.error(err)
  }
}

db.sql

create database ps_db charset utf8;
create user ps_user@localhost identified by 'ps_pass';
grant all privileges on ps_db.* to ps_user@localhost;

package.json

{
  "name": "sequelize-planetscale",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "db": "mysql -u root < db.sql",
    "before": "DOTENV_CONFIG_PATH=.env.before node -r dotenv/config before.js",
    "after": "DOTENV_CONFIG_PATH=.env.after node -r dotenv/config after.js"
  },
  "keywords": [],
  "author": "",
  "license": "MIT",
  "dependencies": {
    "dotenv": "^16.0.1",
    "mysql2": "^2.3.3",
    "sequelize": "^6.21.1"
  }
}

Thanks! That was helpful.

Thanks! That was helpful.

I am happy to help you 😄