Coding Exercise: Database Migration Tool in Nodejs

I want to have a database migration tool that has the following properties: 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. The currently applied version should be managed by the tool. I want the tool to be self-sufficient. I want the tool to support different databases, such as Postgres, MySQL, SQL Server, etc., so it should be extendable in that sense. I don't want it to be oversized, so only drivers for the necessary database should be installed, ideally on demand. I want it to be part of the javascript ecosystem since most of the projects I work on are a part of it. Every migration should be performed inside of a transaction. Introduction 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! (Or maybe I suck at googling...). 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 steal design the CLI tool! All migrations would have the following naming scheme: _ .sql, where the number would represent the migration version number, for example, 001_initial_setup.sql. All migrations would reside in a single dir. Database driver would be downloaded on demand, either some pre-bundled package or just issuing some sort of npm install . So, the syntax for the tool would be the following: martlet up --database-url --driver --dir or martlet down . Where "up" should apply all migations that are not applied yet, and down should rollback to the specified version. Options have the following meanings and defaults: database-url - connection string for the database, the default would be to lookup 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 with figuring out how I would invoke the tool before writing any actual code. This is a good practice, it helps to realize requirements and reduce development cycles. Part 2. Implementation 2.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 --dir --database-url ", ); console.log( " martlet down --driver --dir --database-url ", ); console.log( " is a number that specifies the version to migrate down to", ); console.log("Options:"); console.log(' --driver Driver to use, default is "pg"'); console.log(' --dir Directory to use, default is "migrations"'); console.log( " --database-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 just simply iterate over the arguments 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.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 ; transact(query: (fn: (text) => Promise )): Promise ; close(): Promise ; } 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 would provide ability to run multiple queries inside of 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})`); }); 2.3 On-demand driver installation const PACKAGES = { pg: "postgres@3.4.4", }; 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), ); 2.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. 3. Testing I decided not to use any specific testing framework but use the built-in nodejs 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 migrations code worked by hand, but I was writing it along with 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 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" }, ]); 4. Conclusion This was an example of how I would approach the development of 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. 5. References martlet repo tern postgres driver I want to have a database migration tool that has the following properties: 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. The currently applied version should be managed by the tool. I want the tool to be self-sufficient. I want the tool to support different databases, such as Postgres, MySQL, SQL Server, etc., so it should be extendable in that sense. I don't want it to be oversized, so only drivers for the necessary database should be installed, ideally on demand. I want it to be part of the javascript ecosystem since most of the projects I work on are a part of it. Every migration should be performed inside of a transaction. 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. The currently applied version should be managed by the tool. I want the tool to be self-sufficient. I want the tool to support different databases, such as Postgres, MySQL, SQL Server, etc., so it should be extendable in that sense. I don't want it to be oversized, so only drivers for the necessary database should be installed, ideally on demand. I want it to be part of the javascript ecosystem since most of the projects I work on are a part of it. Every migration should be performed inside of a transaction. Introduction 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! (Or maybe I suck at googling...). So I decided this could be a nice coding exercise for myself and a story that could be interesting to someone else :) tern Development Part 1. Designing the tool Let's steal design the CLI tool! steal All migrations would have the following naming scheme: _ .sql, where the number would represent the migration version number, for example, 001_initial_setup.sql. All migrations would reside in a single dir. Database driver would be downloaded on demand, either some pre-bundled package or just issuing some sort of npm install . All migrations would have the following naming scheme: _ .sql , where the number would represent the migration version number, for example, 001_initial_setup.sql . _ .sql 001_initial_setup.sql All migrations would reside in a single dir. Database driver would be downloaded on demand, either some pre-bundled package or just issuing some sort of npm install . npm install So, the syntax for the tool would be the following: martlet up --database-url --driver --dir or martlet down . martlet up --database-url --driver --dir martlet down Where "up" should apply all migations that are not applied yet, and down should rollback to the specified version. Options have the following meanings and defaults: database-url - connection string for the database, the default would be to lookup 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 database-url - connection string for the database, the default would be to lookup the env variable DATABASE_URL database-url - connection string for the database, the default would be to lookup the env variable DATABASE_URL database-url DATABASE_URL driver - database driver to use. For the first version, I will only support Postgres with an option named "pg". driver - database driver to use. For the first version, I will only support Postgres with an option named "pg". driver dir - directory where migrations reside, default is migrations dir - directory where migrations reside, default is migrations dir migrations As you can see, I've started with figuring out how I would invoke the tool before writing any actual code. This is a good practice, it helps to realize requirements and reduce development cycles. Part 2. Implementation 2.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 --dir --database-url ", ); console.log( " martlet down --driver --dir --database-url ", ); console.log( " is a number that specifies the version to migrate down to", ); console.log("Options:"); console.log(' --driver Driver to use, default is "pg"'); console.log(' --dir Directory to use, default is "migrations"'); console.log( " --database-url Database URL to use, default is DATABASE_URL environment variable", ); } printHelp(); function printHelp() { console.log( "Usage: martlet up --driver --dir --database-url ", ); console.log( " martlet down --driver --dir --database-url ", ); console.log( " is a number that specifies the version to migrate down to", ); console.log("Options:"); console.log(' --driver Driver to use, default is "pg"'); console.log(' --dir Directory to use, default is "migrations"'); console.log( " --database-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; } 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 just simply iterate over the arguments 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.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 ; transact(query: (fn: (text) => Promise )): Promise ; close(): Promise ; } interface Adapter { connect(url: string): Promise ; transact(query: (fn: (text) => Promise )): Promise ; close(): Promise ; } 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 would provide ability to run multiple queries inside of a transaction. It's easier to grasp by looking at the usage example. connect close transact 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(); } } 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})`); }); 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})`); }); 2.3 On-demand driver installation const PACKAGES = { pg: "postgres@3.4.4", }; 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", }); }; const PACKAGES = { pg: "postgres@3.4.4", }; 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. yarn.lock package.json 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), ); export async function loadAdapter(driver) { await downloadDriver(driver); return import(PACKAGES[driver].split("@")[0]).then( (m) => new PGAdapter(m.default), ); 2.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}`); 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}`); }); 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. 3. Testing I decided not to use any specific testing framework but use the built-in nodejs testing capabilities. They include the test runner and the assertion package. import { it, before, after, describe } from "node:test"; import assert from "node:assert"; 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 . node --test --test-concurrency=1 Actually, I was writing the code in a sort of TDD manner. I didn't validate that my migrations code worked by hand, but I was writing it along with 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 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. testcontainers 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(); }); 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" }, ]); 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" }, ]); 4. Conclusion This was an example of how I would approach the development of 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. 5. References martlet repo tern postgres driver martlet repo martlet repo tern tern postgres driver postgres driver