Introduction

If you prefer running node run-migrations.js instead of npx prisma migrate deploy, this article is for you. You might wonder why go through this process instead of installing all dependencies and using npx prisma migrate deploy. Well, there are several reasons to consider this approach. Firstly, using the Prisma dependency in a Docker image can result in a larger image size. Secondly, if you need to run npx prisma migrate within the image during build time, you must provide the databaseURL as a build argument and set it as an environment variable for Prisma. Thirdly, if you want to run it outside of Docker, you can include it in your CI/CD pipeline to install dependencies and run npx prisma migrate. Lastly, this approach is beneficial for creating minimal and independent images that can be distributed without pre-setup concerns.

Prerequisite

In this article, we'll assume that the Prisma database provider is MySQL. Therefore, ensure that the MySQL dependency is installed. If you're using a Docker image, make sure MySQL is available in the production image. Regardless of the environment, run npm i mysql to install and make it accessible for script execution. The same approach and steps can be followed for other providers like PostgreSQL or SQLite.

Code

The code implementation involves five steps:

• Step 1: Locate the migrations folder, which contains multiple directories with .sql files.
• Step 2: To execute migrations in the correct order (oldest first), sort the array of directories based on their creation dates. Create an array of objects with properties like name and createdAt (obtained using fs.statSync(path.join(root, dirent.name)).birthtime).
• Step 3: Iterate through the sorted array of directories.
• Step 4: Read and clean the .sql files within the loop, parsing them into an array of queries. Cleaning the string is necessary to remove newlines, white spaces, and comments.
• Step 5: With the array of queries prepared, execute them safely within a loop.

By following these steps, you can successfully run Prisma migrations in your Node.js application, offering more control and flexibility.

const mysql = require("mysql");
const fs = require("fs");
const path = require("path");

// Step 1: Find migrations folder
const root = path.resolve(__dirname, "../prisma/migrations");

// Step 2: Sort the migrations folder in order of creation
const dirs = fs
  .readdirSync(root, { withFileTypes: true })
  .filter((dirent) => dirent.isDirectory())
  .map((dirent) => ({
    name: dirent.name,
    createdAt: fs.statSync(path.join(root, dirent.name)).birthtime,
  }))
  .sort((a, b) => a.createdAt - b.createdAt);



// Step 3: Loop through the folders
const databaseURL = process.env.DATABASE_URL;
const conn = mysql.createConnection(databaseURL);

conn.connect(function (err) {
  if (err) throw err;

  // Step 4: Get .sql migration string, clean the string into array
  for (const dir of dirs) {
    const queries = fs
      .readFileSync(`${path.join(root, dir.name)}/migration.sql`)
      .toString()
      .replace(/(\r\n|\n|\r)/gm, " ") // remove newlines
      .replace(/\s+/g, " ") // excess white space
      .replace(/--\s(\w+)/gm, "") // remove comments
      .split(";") // split into all statements
      .map(Function.prototype.call, String.prototype.trim)
      .filter(function (el) {
        return el.length != 0;
      });


    // Step 5: Execute the queries
    for (const query of queries) {
      conn.query(query, function (err, result) {
        console.log(query);
        if (err) console.error(err?.sqlMessage);
        console.log("execution done");
      });
    }
  }

  conn.end();
});

Conclusion

In conclusion, embracing custom scripts for executing Prisma migrations empowers developers to handle migrations in a more adaptable and efficient manner, ensuring smooth deployment and maintenance of their Node.js applications.