Migrations

Migrating the schema of a database as features are built is an imporant part of Evolutionary Database Design. Platter's branching model was built with this incremental design process in mind, and makes database migrations a less error-prone process.

While the Postgres Client does not have any built-in schema migration methods, the schema can be altered directly with query. In many cases, this will be sufficient. For those that would like a more systematic migration experience, we recommend migrate, an abstract migration framework for Node.

This guide will show you how to use migrate and @platter/migrate-store to incrementally change the schema of your Postgres databases. For this example, you will learn how to build a schema for a basic blog API that returns collections of posts and authors.

Prerequisites#

A Postgres database. See the "Setting Up Postgres" section of the Quick Start for more information. Like the Quick Start, this guide will use a database named my-new-database.

Creating Migrations#

Install migrate as a devDependency with:
npm install --save-dev migrate
migrate uses timestamped .js files in a migrations directory to schedule migrations. To generate your first migration, run:
npx migrate create add-posts
...which will create a ./migrations/1613143187252-add-posts.js file on your behalf (example timestamp). Your project structure will look like this afterwards:
├── migrations
│   └── $TIMESTAMP-add-posts.js
├── platter
│   └── postgres
│   └── node
│   ├── MyNewDatabase.d.ts
│   └── MyNewDatabase.js
└── package.json
Migration files export up and down methods that correspond to applying and reverting migrations, respectively. Replace the contents of your *-add-posts.js file with the following:
migrations/$TIMESTAMP-add-posts.js
const Postgres = require('../platter/postgres/node/MyNewDatabase')
const postgres = new Postgres({ key: process.env.PLATTER_API_KEY })

module.exports.up = async () => postgres.query(
`CREATE TABLE IF NOT EXISTS posts (
id SERIAL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL,
content TEXT NOT NULL
)`
)

module.exports.down = async () => postgres.query('DROP TABLE posts')
From here, migrate your schema with:
PLATTER_API_KEY=<your-api-key> npx migrate up
...and reverted those changes with:
PLATTER_API_KEY=<your-api-key> npx migrate down

Tracking Migrations#

By default, migrate only stores migrations locally. This approach does not work well with branching databases, as the state of a database will often be different from the state of those migration-tracking files. Instead, migrations can be tracked in the database by implementing a custom store.

Platter has created a custom store implementation that uses Postgres Clients to track migrations separately in each branch of a database.

Install @platter/migrate-store as a devDependency with:
npm install --save-dev @platter/migrate-store
@platter/migrate-store expects PLATTER_API_KEY and PLATTER_POSTGRES_CLIENT environment variables on invocation, and migrate expects the store implementation to be provided through a --store option. Putting that together, migrations can now be performed like so:
PLATTER_API_KEY=<your-api-key> \
PLATTER_POSTGRES_CLIENT=platter/node/MyNewDatabase \
npx migrate up --store='@platter/migrate-store'

Transactional Migrations#

Now that each branch of a database tracks its own set of migrations, it's time to add another migration that includes more complex logic that needs to be performed in multiple steps. This is a perfect use-case for a Transaction.

posts should have authors, of course. Create a migration for authors with:
npx migrate create add-authors
Replace the contents of your new *-add-authors.js migration file with the following:
migrations/$TIMESTAMP-add-authors.js
const Postgres = require('../platter/postgres/node/MyNewDatabase')
const postgres = new Postgres({ key: process.env.PLATTER_API_KEY })

module.exports.up = async () => {
const transaction = await postgres.transaction()

await transaction.query(
`CREATE TABLE IF NOT EXISTS authors (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
)`
)

await transaction.query(
`ALTER TABLE posts
ADD COLUMN author_id INTEGER REFERENCES authors NOT NULL`
)

await transaction.commit()
}

module.exports.down = async () => {
const transaction = await postgres.transaction()
await transaction.query('ALTER TABLE posts DROP COLUMN author_id')
await transaction.query('DROP TABLE authors')
await transaction.commit()
}
Run the up migration command once more, and you'll notice that only the second migration was run. This will be true for anyone that clones your project or branches this database in the future!

Deployment#

To see how migrations interact with popular build systems and branch deploys, take a look at the guide for Netlify Functions.