Coding Exercise: Database Migration Tool in NodeJS

Database management is a vast and dynamic industry. There are a lot of nice schema migration tools: some are standalone, like Atlas, some are a part of a broader ecosystem, like Drizzle or Prisma. 

I prefer simplicity and narrow specialization over tools that try to solve everything, so my choice would be a migration tool that operates on top of bare SQL statements. I couldn't find such a tool in the JavaScript ecosystem, so I figured this would make a great exercise.

Requirements

I want to have a database migration tool that has the following properties:

1. Every migration is written in a single SQL file, meaning both "up" and "down" parts. This will allow Copilot to fill in the rollback migration. And the fact that it's a bare SQL also makes it the most flexible and supported solution.
2. The currently applied version should be managed by the tool. I want the tool to be self-sufficient.
3. I want the tool to support different databases, such as Postgres, MySQL, SQL Server, etc., so it should be extendable in that sense.
4. I don't want it to be oversized, so only drivers for the necessary database should be installed, ideally on demand.
5. I want it to be part of the JavaScript ecosystem since most of the projects I work on are a part of it.
6. Every migration should be performed within a transaction.

A lot of these points were born from my experience with this awesome tool called tern. I was sad that JavaScript doesn't have the same. So I decided this could be a nice coding exercise for myself, and a story that could be interesting to someone else.

Development

Part 1. Designing the Tool

Let's design the CLI tool!

1. All migrations would have the following naming scheme: <number>_<name>.sql, where the number would represent the migration version number, for example, 001_initial_setup.sql.
2. All migrations would reside in a single directory.
3. Database driver would be downloaded on demand — either a pre-bundled package or just issuing some sort of npm install <driver>.

So the syntax for the tool would be the following: martlet up --database-url <url> --driver <driver> --dir <dir> or martlet down <version> <same options>.

Where "up" should apply all migrations that have not been applied yet, and down should roll back to the specified version. Options have the following meaning and defaults:

• database-url – connection string for the database, the default would be to look up the env variable DATABASE_URL
• driver – database driver to use. For the first version, I will only support Postgres with an option named "pg."
• dir – directory where migrations reside, default is migrations

As you can see, I've started by figuring out how to invoke the tool before writing any actual code. This is good practice; it helps identify requirements and reduce development cycles.

Part 2. Implementation

1. Parsing Options

Ok, first things first! Let's create an index.js file and output the help message. It would look something like this:

function printHelp() {
  console.log(
    "Usage: martlet up --driver <driver> --dir <dir> --database-url <url>",
  );
  console.log(
    "       martlet down <version> --driver <driver> --dir <dir> --database-url <url>",
  );
  console.log(
    "       <version> is a number that specifies the version to migrate down to",
  );
  console.log("Options:");
  console.log('  --driver <driver>  Driver to use, default is "pg"');
  console.log('  --dir <dir>        Directory to use, default is "migrations"');
  console.log(
    "  --database-url <url> Database URL to use, default is DATABASE_URL environment variable",
  );
}

printHelp();

Now, we will parse options:

export function parseOptions(args) {
  const options = {
    dir: "migrations",
    driver: "pg",
    databaseUrl: process.env.DATABASE_URL,
  };
  for (let idx = 0; idx < args.length; ) {
    switch (args[idx]) {
      case "--help":
      case "-h": {
        printHelp();
        process.exit(0);
      }
      case "--dir": {
        options.dir = args[idx + 1];
        idx += 2;
        break;
      }
      case "--driver": {
        options.driver = args[idx + 1];
        idx += 2;
        break;
      }
      case "--database-url": {
        options.databaseUrl = args[idx + 1];
        idx += 2;
        break;
      }

      default: {
        console.error(`Unknown option: ${args[idx]}`);
        printHelp();
        process.exit(1);
      }
    }
  }
  return options;
}

As you can see, I don't use any library for parsing; I simply iterate over the argument list and process every option. So, if I have a boolean option, I would shift the iteration index by 1, and if I have an option with a value, I would shift it by 2.

2. Implementing the Driver Adapter

To support multiple drivers, we need to have some universal interface to access a database; here is how it may look:

interface Adapter {
    connect(url: string): Promise<void>;
    transact(query: (fn: (text) => Promise<ResultSet>)): Promise<ResultSet>;
    close(): Promise<void>;
}

I think connect and close are pretty obvious functions, let me explain the transact method. It should accept a function that would be called with a function that accepts a query text and returns a promise with an intermediate result. This complexity is required to have a general interface that allows running multiple queries within a transaction. It's easier to grasp by looking at the usage example.

So this is how the adapter looks for the Postgres driver:

class PGAdapter {
  constructor(driver) {
    this.driver = driver;
  }

  async connect(url) {
    this.sql = this.driver(url);
  }

  async transact(query) {
    return this.sql.begin((sql) => (
      query((text) => sql.unsafe(text))
    ));
  }

  async close() {
    await this.sql.end();
  }
}

And the usage example could be:

import postgres from "postgres";

const adapter = new PGAdapter(postgres);
await adapter.connect(url);
await adapter.transact(async (sql) => {
    const rows = await sql("SELECT * FROM table1");
    await sql(`INSERT INTO table2 (id) VALUES (${rows[0].id})`);
});

3. On-Demand Driver Installation

const PACKAGES = {
  pg: "[email protected]",
};

const downloadDriver = async (driver) => {
  const pkg = PACKAGES[driver];
  if (!pkg) {
    throw new Error(`Unknown driver: ${driver}`);
  }
  try {
    await stat(join(process.cwd(), "yarn.lock"));
    const lockfile = await readFile(join(process.cwd(), "yarn.lock"));
    const packagejson = await readFile(join(process.cwd(), "package.json"));
    spawnSync("yarn", ["add", pkg], {
      stdio: "inherit",
    });
    await writeFile(join(process.cwd(), "yarn.lock"), lockfile);
    await writeFile(join(process.cwd(), "package.json"), packagejson);
    return;
  } catch {}
  spawnSync("npm", ["install", "--no-save", "--legacy-peer-deps", pkg], {
    stdio: "inherit",
  });
};

We try to install the driver with yarn at first, but we don't want to generate any diffs in the directory, so we preserve yarn.lock and package.json files. If yarn is not available, we will fall back to npm.

When we ensure that the driver is installed, we can create an adapter and use it:

export async function loadAdapter(driver) {
  await downloadDriver(driver);
  return import(PACKAGES[driver].split("@")[0]).then(
    (m) => new PGAdapter(m.default),
  );

4. Implementing the Migration Logic

We start by connecting to the database and getting the current version:

await adapter.connect(options.databaseUrl);
console.log("Connected to database");

const currentVersion = await adapter.transact(async (sql) => {
    await sql(`create table if not exists schema_migrations (
      version integer primary key
    )`);
    const result = await sql(`select version from schema_migrations limit 1`);
    return result[0]?.version || 0;
});

console.log(`Current version: ${currentVersion}`);

Then, we read the migrations directory and sort them by version. After that, we apply every migration that has a version greater than the current one. I will just present the actual migration in the following snippet:

await adapter.transact(async (sql) => {
    await sql(upMigration);
    await sql(
      `insert into schema_migrations (version) values (${version})`
    );
    await sql(`delete from schema_migrations where version != ${version}`);
});

The rollback migration is similar, but we sort the migrations in reverse order and apply them until we reach the desired version.

Testing

I decided not to use any specific testing framework in favor of Node.js' built-in testing capabilities. They include the test runner and the assertion package.

import { it, before, after, describe } from "node:test";
import assert from "node:assert";

And to execute tests, I would run node --test --test-concurrency=1.

Actually, I was writing the code in a sort of TDD manner. I didn't validate that my code worked by hand, but I wrote it alongside tests. That's why I decided that end-to-end tests would be the best fit for this tool. For such an approach, tests would need to bootstrap an empty database, apply some migrations, check that the database contents are correct, and then roll back to the initial state and validate that the database is empty. To run a database, I used the "testcontainers" library, which provides a nice wrapper around Docker.

before(async () => {
    console.log("Starting container");
    container = await new GenericContainer("postgres:16-alpine")
    .withExposedPorts(5432)
    .withEnvironment({ POSTGRES_PASSWORD: "password" })
    .start();
});

after(async () => {
    await container.stop();
});

I wrote some simple migrations and tested that they worked as expected. Here is an example of a database state validation:

const sql = pg(`postgres://postgres:password@localhost:${port}/postgres`);
const result = await sql`select * from schema_migrations`;
assert.deepEqual(result, [{ version: 2 }]);
const tables =
    await sql`select table_name from information_schema.tables where table_schema = 'public'`;
assert.deepEqual(tables, [
    { table_name: "schema_migrations" },
    { table_name: "test" },
]);

Conclusion

This was an example of how I would approach developing a simple CLI tool in the JavaScript ecosystem. I want to note that the modern JavaScript ecosystem is pretty charged and powerful, and I managed to implement the tool with a minimum of external dependencies. I used a Postgres driver that would be downloaded on demand and Testcontainers for tests. I think that approach gives developers the most flexibility and control over the application.