andrevdm / postgresql-migration

PostgreSQL Schema Migrations for Haskell

PostgreSQL Migrations for Haskell

Forked from postgresql-simple-migration created by Andreas Meingast

Welcome to postgresql-migrations, a tool for helping you with PostgreSQL schema migrations.

This project is an open-source database migration tool. It favors simplicity over configuration.

It is implemented in Haskell and uses the (excellent) postgresql-simple library to communicate with PostgreSQL.

It comes in two flavors: a library that features an easy to use Haskell API and as a standalone application.

Database migrations can be written in SQL (in this case PostgreSQL-sql) or in Haskell.

Why?

Database migrations should not be hard. They should be under version control and documented both in your production systems and in your project files.

What?

This library executes SQL/Haskell migration scripts and keeps track of their meta information.

Scripts are executed exactly once and any changes to scripts will cause a run-time error notifying you of a corrupted database.

The meta information consists of:

• an MD5 checksum of the executed script to make sure already existing scripts cannot be modified in your production system.
• a time-stamp of the date of execution so you can easily track when a change happened.

This library also supports migration validation so you can ensure (some) correctness before your application logic kicks in.

How?

This utility can be used in two ways: embedded in your Haskell program or as a standalone binary.

Standalone

The standalone program supports file-based migrations. To execute all SQL-files in a directory $BASE_DIR, execute the following command to initialize the database in a first step.

CON="host=$host dbname=$db user=$user password=$pw"
cabal run migrate -- init $CON
cabal run migrate -- migrate $CON $BASE_DIR

To validate already executed scripts, execute the following:

CON="host=$host dbname=$db user=$user password=$pw"
cabal run migrate -- init $CON
cabal run migrate -- validate $CON $BASE_DIR

For more information about the PostgreSQL connection string, see: libpq-connect.

Library

The library supports more actions than the standalone program.

Initializing the database:

main :: IO ()
main = do
    let url = "host=$host dbname=$db user=$user password=$pw"
    con <- connectPostgreSQL (BS8.pack url)
    runMigration con defaultOptions MigrationInitialization

For file-based migrations, the following snippet can be used:

main :: IO ()
main = do
    let url = "host=$host dbname=$db user=$user password=$pw"
    let dir = "."
    con <- connectPostgreSQL (BS8.pack url)
    runMigration con defaltOptions $ MigrationDirectory dir

To run Haskell-based migrations, use this:

main :: IO ()
main = do
    let url = "host=$host dbname=$db user=$user password=$pw"
    let name = "my script"
    let script = "create table users (email varchar not null)";
    con <- connectPostgreSQL (BS8.pack url)
    runMigration con defaultOptions $ MigrationScript name script

Validations wrap MigrationCommands. This means that you can re-use all MigrationCommands to perform a read-only validation of your migrations.

To perform a validation on a directory-based migration, you can use the following code:

main :: IO ()
main = do
    let url = "host=$host dbname=$db user=$user password=$pw"
    con <- connectPostgreSQL (BS8.pack url)
    runMigration con default Options $ MigrationValidation (MigrationDirectory dir)

Transactions

Database migrations should always be performed in a transactional context.

The standalone binary and the API default to using a new transaction for the entire set of migrations.

Using the -t argument to the binary will change this to using a new transaction per migration step (script).

When using the library you have full control over the behaviour of transactions by setting optTransactionControl on the MigrationOptions record.

There are three options

1. No new transaction: you manage the transaction, e.g. if you want to run multiple migrations in a single transaction
2. Transaction per run: This is the default. New transaction for the entire migration
3. Transaction per step

The tests make use TransactionPerRun, after executing all migration-tests, the transaction is rolled back.

Options

The runMigration and runMigrations functions take an options record that let you set the following

• optVerbose: Is verbose logging enabled or not
• optLogWriter: The function used to write log messages. Defaults to stdout for info and stderr for errors
• optTableName: The name for the migrations table. This defaults to schema_migrations.
• optTransactionControl: How transactions should be hanbled

Compilation and Tests

The program can be built with cabal or stack build systems.

The following command builds the library, the standalone binary and the test package with cabal

cabal configure --enable-tests && cabal build -j

To execute the tests, you need a running PostgreSQL server. You need to set the correct environment varaiables for the psql connection.

Tests are executed through cabal as follows:

cabal configure --enable-tests 
PGHOST=localhost PGDATABASE=test make cabal-test

To build with stack use the following command

stack build

To run the tests with stack use the following command

PGHOST=localhost PGDATABASE=test make stack-test

NB note that the psql test argument must be set for the test suite to run. This is so that other CI environments like stackage or nix that won't have a running test psql instance will not fail on these tests

Changes from the original postgresql-simple-migration (version 0.1)

postgresql-migration is fork of postgresql-simple-migration created when the original postgresql-simple-migration project was archived.

postgresql-migration version 0.2.x introduces some new features that will require some minor changes if you were using a 0.1.x version before

The new features are

• Support for custom logging (original PR from ameingast#36. Thanks @unclechu)
• Custom migrations table name (original PR from ameingast#30)
• Transaction control from the API (original request from ameingast#40)

There are two ways to move to postgresql-migration

Compatability layer - the simple way, but no new features

1. Replace postgresql-simple-migration with postgresql-migration in your .cabal file
2. Import Database.PostgreSQL.Simple.Migration.V1Compat rather than Database.PostgreSQL.Simple.Migration

All your existing code should work as is

Porting to version 2

The most obvious code change is that you now use a MigrationOptions rather than a MigrationContext.

Version 0.1.x

This what you would have had

 withTransaction con . runMigration $ MigrationContext Pgm.MigrationInitialization True con

Version 0.2.x

Version 2 with the defaultOptions

 runMigration con defaultOptions Pgm.MigrationInitialization

or if you want to change the default options

 let options = defaultOptions { optTransactionControl = TransactionPerRun, optVerbosity = Verbose }
 runMigration con options Pgm.MigrationInitialization

That is all that needs to change. Your migrations scripts etc all remain as is.

postgresql-migration's Issues

Tests for TransactionControl and Verbosity

Add unit tests for the new features

Pick a name for the forked package

Tests not running with github actions

The current github action setup using https://github.com/haskell-CI/haskell-ci is not correctly setup for running psql tests. The tests should be run as part of the build

"Migrating from v1" section in the README

Add a section with more help & examples showing how to change from V1 to V2 code

Replacing the usage of 'cryptohash' with 'cryptohash-md5'

Hi! I noticed that postgresql-migration is using cryptohash (from what I understand) for MD5. However the cryptohash library has been deprecated, and we can use cryptohash-md5 instead.

Would you be open to a PR replacing the deprecated library for the more lightweight alternative?

`existsTable` returns unexpected result

existsTable conn "myTable" only works properly if you have a single myTable across all schemas. If you try to check for myTable in the default schema while having a myTable in any other schema, it will reverse the result of the function.

Package and submit to stackage

Package and submit to hackage