Best SQL migration tool to be used along side `pg`

[neuodev]

There is no direct way to automatically manage schema migrations/updates with pg (e.g., generate migration files, up and down queries, etc.).

I found these two packages to manage migrations but they are not popular.

• node-pg-migrate
• postgres-migrations
  

I wonder: What will be the best tool to do this?

[brianc]

Good question! I haven't used any myself, so I'm not able to answer that...usually I write a file called migrate.ts or something & it has functions to idempotently do all the migrations in it. Something like this (simplifed for the purposes of an example):

async function createUsersTable(client: Client) {
  await client.query('CREATE TABLE IF NOT EXISTS "users" (id UUID default gen_uuid())`)
}

async function addEmailToUsers(client: Client) {
  await client.query('ALTER TABLE "users" ADD COLUMN IF NOT EXISTS email text;')
}

export async function migrate() {
  const client = new pg.Client()
  await client.connect()
  await client.query('BEGIN')
  await createUsersTable(client)
  await addEmailToUsers(client)
  // continue to add more migrations as code changes
  await client.query('COMMIT')
  await client.end()
 }

Then I have an npm script that can call the top level migrate function, and also can run the migrate() function at the beginning of running all the unit/integration tests. The key is the migrate() function and all child functions need to be idempotent or it gets yucky. I also don't make "down" functions because they don't make sense to me...some migrations are naturally destructive and going "backwards" isn't possible...but to each their own!