New feature: support sql files

[luukvhoudt]

Because 90% of all use cases involve SQL, or at least that's my suspicion, when using a migration tool like this package provides. That's why I think it might be good addition to include an utility function what adds support for doing something based on a list of (SQL) files their content.

Currently I'm using a function in one of my projects just like that and I thought why not share it?

...

All what is needed is a file structure like this (note as of v1.7.0, you can't have this structure in the same directory as where your migrations are stored, it should be on a higher level):

.
├── up
│   ├── 00-tables.sql
│   └── 123-triggers.sql
└── down
    ├── 00-tables.sql
    └── 123-triggers.sql

Then the next thing to do is to setup a single migration:

const {upFromFiles, downFromFiles} = require("migrate"); // Not available yet?
const database = require("./database");

const upHandler = async (fileContents) => {
  // Do something with the contents of the file.
  // This example is querying the database
  const pool = database.getPool(); // Setup a database connection
  await pool.query(fileContents); // Send a query to the database
};

// This function is needed if you maintain the same order in the down SQL files as you do in the up files.
// Example:
// - up file: create table posts (...); create table comments (..., post_id int references posts);
// - down file: drop table posts; drop table comments;
// This function reverses the order of those statements: drop table comments; drop table posts;
const downHandler = async (fileContents) => {
  const seperator = ";";
  const reversedContents = fileContents
    .split(seperator)
    .reverse()
    .join(seperator);
  await upHandler(reversedContents);
};

module.exports.up = async () => {
  await upFromFiles("./up", upHandler);
};

module.exports.down = async () => {
  await downFromFiles("./down", downHandler);
};

The functions upFromFiles and downFromFiles could be defined as following:

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

const log = (key: string, msg: string, last: boolean = false) => {
  const prefix = "  " + (last ? "└──" : "├──");
  console.log(`${prefix} ${chalk.grey(key)} : ${chalk.cyan(msg)}`);
};

const fromFiles = async (key, fileNames, handler) => {
  for (const index in fileNames) {
    const fileName = fileNames[index];
    const filePath = path.join(dir, path.sep, fileName);
    const contents = fs.readFileSync(filePath, "utf8").toString();
    log(key, fileName, Number(index) === fileNames.length - 1);
    await handler(contents);
  }
};

module.exports.upFromFiles = async (dir, handler) => {
  const fileNames = fs.readdirSync(dir);
  await fromFiles("up", fileNames, handler);
};

module.exports.downFromFiles = async (dir, handler) => {
  const fileNames = fs.readdirSync(dir).reverse();
  await fromFiles("down", fileNames, handler);
}

Maybe if the package author agrees this code can be integrated with this package. If not and people do prefer to have this around for the taking. Then I might consider to publish into a separate package.

[DesignByOnyx]

I think this tool is great as a standalone thing that focuses on its core utilities: executing files up/down exports in the correct order. I am needing this to manage changes for our dev environments as features are added and removed. We will also be using this to manage mongodb, cosmos, and elasticsearch migrations. No need to bloat the core utility with language/database specific stuff.

I suggest creating your own migrate-sql repo which uses this as a core util (don't fork, but reference it in package.json). You can then bootstrap it with a lot of sql-specific stuff. I think a lot of people would find that useful (if the api is right ;)

[wesleytodd]

@DesignByOnyx's comment is exactly what differentiates this tool from the many others out there, you can write technology agnostic migrations, which is often a need in projects. I agree with the design as well of importing this as a library. I actually started a version of this tool which was more oriented to exposing the core components as libs but never ended up having the time to finish it. That said, even though this is not as easy as it could be, the current version of this lib works well enough in that form that it should be pretty simple to wrap (and I have also done that in past projects).

EDIT: this is funny, I think I actually meant to publish that wrapper as OSS, I created the repo but apparently never pushed the code. https://github.com/wesleytodd/migrate-pg