postgresql-migration
PostgreSQL Schema Migrations
https://github.com/andrevdm/postgresql-migration
Version on this page: | 0.2.1.7 |
LTS Haskell 22.40: | 0.2.1.8 |
Stackage Nightly 2024-11-02: | 0.2.1.8 |
Latest on Hackage: | 0.2.1.8 |
postgresql-migration-0.2.1.7@sha256:38396d0e76e106e8bd474178ebcb4cfb9c8a82f82f398a3db449d283a4e23eb4,4856
Module documentation for 0.2.1.7
- Database
- Database.PostgreSQL
- Database.PostgreSQL.Simple
- Database.PostgreSQL
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
- No new transaction: you manage the transaction, e.g. if you want to run multiple migrations in a single transaction
- Transaction per run: This is the default. New transaction for the entire migration
- 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 notoptLogWriter
: The function used to write log messages. Defaults tostdout
for info andstderr
for errorsoptTableName
: The name for the migrations table. This defaults toschema_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 https://github.com/ameingast/postgresql-simple-migration/pull/36. Thanks @unclechu)
- Custom migrations table name (original PR from https://github.com/ameingast/postgresql-simple-migration/pull/30)
- Transaction control from the API (original request from https://github.com/ameingast/postgresql-simple-migration/issues/40)
There are two ways to move to postgresql-migration
Compatability layer - the simple way, but no new features
- Replace
postgresql-simple-migration
withpostgresql-migration
in your .cabal file - Import
Database.PostgreSQL.Simple.Migration.V1Compat
rather thanDatabase.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.
Changes
Changelog
Unreleased
- Switch from deprecated cryptohash dependency to cryptohash-md5
0.2.1.0
- Forked & renamed to postgresql-migration
- Support for changing the schema_migration table name
- Support custom logging
- API option to control how transactions are used
- Support for older GHC versions dropped
0.1.15.0
- Bumped dependencies
0.1.14.0
- Bumped dependencies
0.1.13.1
- Bumped dependencies
0.1.13.0
- Bumped dependencies
0.1.12.0
- Support for GHC 8.4
0.1.11.0
- Improved documentation
- Fixed exists_table
0.1.10.1
- Fixed hackage warnings
0.1.10.0
- Relaxed time bounds
0.1.9.0
- Bumped dependencies
0.1.8.0
- Added MigrationCommands allowing sequencing of migrations in the Haskell API
- Derived more datatypes for MigrationResult
- Bumped dependencies
0.1.7.0
- Propagate migration and validation result to application exit code
0.1.6.0
- Support for GHC 8
0.1.5.0
- Bumped dependencies
0.1.4.0
- Improved error logging in standalone binary
0.1.3.0
- Better transaction handling
- Improved documentation
0.1.2.0
- Moved Util module
- Improved documentation
0.1.1.0
- Support for schema validations.
- Improved Haskell API
0.1.0.0
- Support for file-based and Haskell migrations.