Node.js: MySQL stored procedures migrations

There are a lot of different tools to support the development of database changes in Node.js. It can be:
- Knex migrations;
- Prisma ORM migrations;
- Sequelize migrations;
- TypeORM migrations;
- and a lot of other tools.

All these tools do excellent work with table structure changing, but when you need to change stored procedure…

To change table structure there are many ways: ADD column, DROP column, CHANGE column, RENAME column, ADD INDEX…

What is the process of changing any stored procedure? Just drop it and create a new one with the same name…

So when we create migration for some stored procedure in our Node.js project we create two methods: down and up.

Down:

DROP PROCEDURE IF EXISTS `some_proc`;
-- initial procedure code
CREATE PROCEDURE `some_proc` ()
BEGIN
  ...
END;

Up:

DROP PROCEDURE IF EXISTS `some_proc`;
-- new stored procedure code
CREATE PROCEDURE `some_proc` ()
BEGIN
  ...
END;

Any new changes this is two new methods inside the new migration file.

When you are only one developer in this project this is a possible way. If two or more sometimes it is a problem when the first and the second developers try to change the same stored procedure. The first developer gets the source code of the initial stored procedure and creates his migration with his changes and the second developer makes the same but in the other migration file. Who’s changes will be in DB? The developer who pushes his changes after another. And the changes of the first developer will be lost.

One more question: where should the developer get the initial stored procedure code? Developer DB, production DB, or staging DB?

Here I will try to explain one of the possible ways to solve these problems

Hash migration

Suppose we have an SQL file with the code of the stored procedure:

CREATE  PROCEDURE `update_user`
(
    userID int,
    newFirstName varchar(32)
)
BEGIN
    UPDATE `user`
        SET `first_name` = newFirstName
        WHERE `id` = userID;

    -- two spaces inside a single quote are required for some reason
    SELECT 'All  ok' AS result,`first_name` FROM `user`
        WHERE `id` = userID;
END;

We can calculate the hash:

import crypto from 'crypto';

const SQLHash = (sql: string, marker = '_@#$^*_') => {
  const parts: string[] = [];
  let i = -1;
  let source = sql
    .replace(/\n/g, ' ')
    .replace(/\t/g, ' ')
    .replace(/\\'/g, `${marker}single${marker}`)
    .replace(/\\"/g, `${marker}double${marker}`)
    .replace(/'.*?'/g, (match) => {
      parts.push(match);
      i++;
      return `${marker}${i}${marker}`;
    })
    .replace(/".*?"/g, (match) => {
      parts.push(match);
      i++;
      return `${marker}${i}${marker}`;
    })
    .replace(/\s{2,}/g, ' ');

  parts.forEach((part, i) => {
    source = source.replace(`${marker}${i}${marker}`, part);
  });

  return crypto.createHash('sha256').update(source).digest('hex');
};

So if we change something in our SQL file the hash will be changed!

We need a tool to calculate hashes for all our SQL files, compare them with stored hashes, and if changes are detected run the corresponding migration file.

Hashmig

Based on these thoughts the npm package @rsol/hashmig was developed.

@rsol/hashmig

It includes the terminal tool to work with the SQL files inside the migration folder.

After installing this npm package you can run hashmig command:

Usage: hashmig [options] [command]

CLI migrations tool for MySQL stored procedures and functions

Options:
  -V, --version          output the version number
  -c, --config <string>  Path to config file (default: "./hashmig.config.json")
  -h, --help             display help for command

Commands:
  run [options]          Execute migrations
  clear                  Clear folder and table
  init                   Fill table and folder by existing stored procedures and functions
  help [command]         display help for command


To configure, you can use `./hashmig.config.json` (see `./hashmig.example.config.json`) file or the following environment variables:
  DB_PORT - post number
  DB_SERVER - server address
  DB_SELECT - database name
  DB_USERNAME - username
  DB_PASSWORD - password
  HASHMIG_FOLDER - path to folder with migrations
  HASHMIG_TABLE - name of table with migrations
  HASHMIG_SILENT - disable logger

The command support help command: hashmig help or hashmig help run

Usage: hashmig run [options]

Execute migrations

Options:
-n, --noninteractive  Non-interactive mode (default: false)
-h, --help            display help for command

To use different DB connections you can use the — configoption with file path value. The config file can look like:

{
  "db": {
    "port": 3306,
    "host": "localhost",
    "database": "example",
    "user": "root",
    "password": "root",
    "ssl": { "rejectUnauthorized": false }
  },
  "folder": "./hashmig_example_migrations",
  "table": "hashmig_example_migrations",
  "silent": false
}

Also config support pipes

@rsol/pipe

{
  "db": {
    "port": "DB_PORT|env|toInt",
    "host": "DB_PORT|env",
    "database": "DB_NAME|env",
    "user": "DB_USER|env",
    "password": "DB_PASSWORD|env",
    "ssl": { "rejectUnauthorized": false }
  },
  "folder": "./hashmig_example_migrations",
  "table": "hashmig_example_migrations",
  "silent": true
}

You can have interactive or non-interactive mode for CI/CD usage

hashmig --config=hashmig.user.config.json run -n