npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@mft/postgres-migrations

v2.4.0

Published

Stack Overflow style database migrations for PostgreSQL

Downloads

946

Readme

Postgres migrations

A PostgreSQL migration library inspired by the Stack Overflow system described in Nick Craver's blog.

Requires Node 12+

Supports PostgreSQL 9.6+

API

const {createDb, migrate} = require("postgres-migrations")

createDb("database-name", {
  defaultDatabase: "postgres", // optional, default: "postgres"
  user: "postgres",
  password: "password",
  host: "localhost",
  port: 5432,
})
.then(() => {
  return migrate({
    database: "database-name",
    user: "postgres",
    password: "password",
    host: "localhost",
    port: 5432,
  }, "path/to/migration/files")
})
.then(() => {/* ... */})
.catch((err) => {
  console.log(err)
})

Design decisions

No down migrations

There is deliberately no concept of a 'down' migration. In the words of Nick Craver:

If we needed to reverse something, we could just push another migration negating whatever we did that went boom ... Why roll back when you can roll forward?

Simple ordering

Migrations are guaranteed to run in the same order every time, on every system.

Some migration systems use timestamps for ordering migrations, where the timestamp represents when the migration file was created. This doesn't guarantee that the migrations will be run in the same order on every system.

For example, imagine Developer A creates a migration file in a branch. The next day, Developer B creates a migration in master, and deploys it to production. On day three Developer A merges in their branch and deploys to production.

The production database sees the migrations applied out of order with respect to their creation time. Any new development database will run the migrations in a different order.

The migrations table

A migrations table is created as the first migration, before any user-supplied migrations. This keeps track of all the migrations which have already been run.

Hash checks for previous migrations

Previously run migration scripts shouldn't be modified, since we want the process to be repeated in the same way for every new environment.

This is enforced by hashing the file contents of a migration script and storing this in migrations table. Before running a migration, the previously run scripts are hashed and checked against the database to ensure they haven't changed.

An exception is made when -- postgres-migrations disable-hash-check is included at the top of the migration file. This allows to make amendments to the migration but it should only be used as a last resource if the migration has already been deployed.

Each migration run in a transaction

Ensures each migration is atomic. Either it completes successfully, or it is rolled back and the process is aborted.

An exception is made when -- postgres-migrations disable-transaction is included at the top of the migration file. This allows migrations such as CREATE INDEX CONCURRENTLY which cannot be run inside a transaction.

Abort on errors

If anything fails, the process is aborted by throwing an exception.

Migration rules

Prefix all file names with a consecutive integer ID

Migrations will be performed in this order.

They must be consecutive, e.g. if you have migrations 1-4, the next one must be 5.

Migration IDs must start from 1.

Example:

migrations
├ 1_create-initial-tables.sql
└ 2_alter-initial-tables.sql

Or, if you want better ordering in your filesystem:

migrations
├ 00001_create-initial-tables.sql
└ 00002_alter-initial-tables.sql

Note that file names cannot be changed later.

Make migrations idempotent

Migrations should only be run once, but this is a good principle to follow regardless.

Migrations are immutable

Once applied (to production), a migration cannot be changed.

This is enforced by storing a hash of the file contents for each migration in the migrations table.

These hashes are checked when running migrations.

Migrations should be backwards compatible

Backwards incompatible changes can usually be made in a few stages.

For an example, see this blog post.

Tips

If you want sane date handling, it is recommended you use the following code snippet to fix a node-postgres bug:

const pg = require("pg")

const parseDate = (val) => val === null ? null : moment(val).format("YYYY-MM-DD")
const DATATYPE_DATE = 1082
pg.types.setTypeParser(DATATYPE_DATE, val => {
  return val === null ? null : parseDate(val)
})

Further work

  • Ability to force migrations to run (i.e. no hash checks)
  • Ability to run migrations up to a set point (e.g. run to migration 5)
  • Ability to configure timeouts (and add timeout to migrations)
  • Ability to configure migration table name
  • CLI if people want it

Useful resources

Stack Overflow: How We Do Deployment - 2016 Edition (Database Migrations)

Database Migrations Done Right

Database versioning best practices

Developing postgres-migrations

The tests require Docker to be installed.