Skip to main content

​
Introduction

For development purposes, you already know the Update DB workflow from the Schema Designer. Once your app is running in production you will at some point need to make changes to your database schema. With migrations, you can evolve your production database schema by writing short SQL statements to make the necessary changes. In IHP a migration file is a plain SQL file consisting of ALTER TABLE and other SQL statements that patch the database. They are stored in Application/Migration and are named by a timestamp and an optional description. Here’s how a directory structure with a few migrations can look like: 
Application/Migration/
    1604852690-create-users-table.sql
    1604852210-add-confirmed_at-to-users.sql
    1604847925-create-projects-table.sql

​
Generating a Migration

1

Open the Code Generator

Open the Code Generator and click Migration. You need to enter an optional description, e.g. Create Posts Table. The description will be sluggified to create-posts-table when generating the migration file.
2

Click Generate

Click Generate. Your editor will open with the new .sql file.
You can also call this from the command line: 
new-migration "Create Posts Table"
To generate our new posts table we copy the DDL statement from Schema.sql into this migration: 
CREATE TABLE posts (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    title TEXT NOT NULL,
    body TEXT NOT NULL
);
After that, we can already run this migration to update our production database.

​
Running Migrations

IHP provides a shell command migrate that runs all migrations that haven’t be executed yet. A table schema_migrations is used to keep track of which migrations already have been run. The table will be automatically created by the migrate tool. To test your migrations locally, first add ihp-migrate to your flake.nix if you haven’t already: 
                     haskellPackages = p: with p; [
                         # Haskell dependencies go here
                         p.ihp
+                        p.ihp-migrate
                         cabal-install
then you can run this tool locally like: 
migrate
In a production context you need to specify the correct database URL via the DATABASE_URL environment variable: 
DATABASE_URL=postgresql://... migrate
For running migrations when deploying from Github Actions, you can use nix run .#migrate.

​
Skipping Old Migrations

You can set the MINIMUM_REVISION env variable when running migrate to ignore migration revisions older than the specified unix timestamp: 
export MINIMUM_REVISION=1000

# 'Application/Migration/999-old-migration.sql' would be ignored now when running 'migrate'
migrate
A good value for MINIMUM_REVISION is typically the unix timestamp of the time when the database was initially created.

​
Custom Migration Directory

In production when running the migrations binary it is sometimes convenient to have all your Migrations in a non-standard place: e.g. if you need to push migrations onto production server without rebuilding the application. There is an Environment variable IHP_MIGRATION_DIR to accomplish this. 
IHP_MIGRATION_DIR=path/to/my/migration/dir
This can be set in the environment attribute set of your IHP app flake.

​
Common Issues

​
ALTER TYPE … ADD cannot run inside a transaction block

When running a migration like this: 
ALTER TYPE my_enum ADD VALUE 'some_value';
you will typical get an error like this: 
Query (89.238182ms): "BEGIN" ()
migrate: SqlError {sqlState = "25001", sqlExecStatus = FatalError, sqlErrorMsg = "ALTER TYPE ... ADD cannot run inside a transaction block", sqlErrorDetail = "", sqlErrorHint = ""}
IHP implicit wraps the migration within a transaction. You can disable this implicit transaction manually like this: 
COMMIT; -- Commit the transaction previously started by IHP
ALTER TYPE my_enum ADD VALUE 'some_value';
BEGIN; -- Restart the connection as IHP will also try to run it's own COMMIT
Make sure to add both COMMIT at the beginning and BEGIN at the end when working around the transaction issue.

Build docs developers (and LLMs) love

Get started for freeTalk to us