Squeal.PostgreSQL.Migration

This module defines a Migration type to safely change the schema of your database over time. Let's see an example!

>>> :set -XDataKinds -XOverloadedLabels
>>> :set -XOverloadedStrings -XFlexibleContexts -XTypeOperators
>>> :{
type UsersTable =
  '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>
  '[ "id" ::: 'Def :=> 'NotNull 'PGint4
   , "name" ::: 'NoDef :=> 'NotNull 'PGtext
   ]
:}

>>> :{
type EmailsTable =
  '[  "pk_emails" ::: 'PrimaryKey '["id"]
   , "fk_user_id" ::: 'ForeignKey '["user_id"] "users" '["id"]
   ] :=>
  '[ "id" ::: 'Def :=> 'NotNull 'PGint4
   , "user_id" ::: 'NoDef :=> 'NotNull 'PGint4
   , "email" ::: 'NoDef :=> 'Null 'PGtext
   ]
:}

>>> :{
let
  makeUsers :: Migration IO '[] '["users" ::: 'Table UsersTable]
  makeUsers = Migration
    { name = "make users table"
    , up = void . define $
        createTable #users
        ( serial `as` #id :*
          (text & notNullable) `as` #name )
        ( primaryKey #id `as` #pk_users )
    , down = void . define $ dropTable #users
    }
:}

>>> :{
let
  makeEmails :: Migration IO '["users" ::: 'Table UsersTable]
    '["users" ::: 'Table UsersTable, "emails" ::: 'Table EmailsTable]
  makeEmails = Migration
    { name = "make emails table"
    , up = void . define $
        createTable #emails
          ( serial `as` #id :*
            (int & notNullable) `as` #user_id :*
            (text & nullable) `as` #email )
          ( primaryKey #id `as` #pk_emails :*
            foreignKey #user_id #users #id
              OnDeleteCascade OnUpdateCascade `as` #fk_user_id )
    , down = void . define $ dropTable #emails
    }
:}

Now that we have a couple migrations we can chain them together.

>>> let migrations = makeUsers :>> makeEmails :>> Done

>>> :{
let
  numMigrations
    :: Has "schema_migrations" schema ('Table MigrationsTable)
    => PQ schema schema IO ()
  numMigrations = do
    result <- runQuery (selectStar (from (table (#schema_migrations `as` #m))))
    num <- ntuples result
    liftBase $ print num
:}

>>> :{
withConnection "host=localhost port=5432 dbname=exampledb" $
  manipulate (UnsafeManipulation "SET client_min_messages TO WARNING;")
    -- suppress notices
  & pqThen (migrateUp migrations)
  & pqThen numMigrations
  & pqThen (migrateDown migrations)
  & pqThen numMigrations
:}
Row 2
Row 0

Migration

data Migration io schema0 schema1

A Migration should contain an inverse pair of up and down instructions and a unique name.

Constructors

Migration

Fields

• name :: Text

  The name of a Migration. Each name in a Migration should be unique.

• up :: PQ schema0 schema1 io ()

  The up instruction of a Migration.

• down :: PQ schema1 schema0 io ()

  The down instruction of a Migration.

migrateUp

Arguments

:: MonadBaseControl IO io

=> AlignedList (Migration io) schema0 schema1

migrations to run

-> PQ (("schema_migrations" ::: Table MigrationsTable) ': schema0) (("schema_migrations" ::: Table MigrationsTable) ': schema1) io ()

Run Migrations by creating the MigrationsTable if it does not exist and then in a transaction, for each each Migration query to see if the Migration is executed. If not, then execute the Migration and insert its row in the MigrationsTable.

migrateDown

Arguments

:: MonadBaseControl IO io

=> AlignedList (Migration io) schema0 schema1

migrations to rewind

-> PQ (("schema_migrations" ::: Table MigrationsTable) ': schema1) (("schema_migrations" ::: Table MigrationsTable) ': schema0) io ()

Rewind Migrations by creating the MigrationsTable if it does not exist and then in a transaction, for each each Migration query to see if the Migration is executed. If it is, then rewind the Migration and delete its row in the MigrationsTable.

Migration table

type MigrationsTable = '["migrations_unique_name" ::: Unique '["name"]] :=> '["name" ::: (NoDef :=> NotNull PGtext), "executed_at" ::: (Def :=> NotNull PGtimestamptz)]

The TableType for a Squeal migration.

createMigrations :: Has "schema_migrations" schema (Table MigrationsTable) => Definition schema schema

Creates a MigrationsTable if it does not already exist.

insertMigration :: Has "schema_migrations" schema (Table MigrationsTable) => Manipulation schema '[NotNull PGtext] '[]

Inserts a Migration into the MigrationsTable

deleteMigration :: Has "schema_migrations" schema (Table MigrationsTable) => Manipulation schema '[NotNull PGtext] '[]

Deletes a Migration from the MigrationsTable

selectMigration :: Has "schema_migrations" schema (Table MigrationsTable) => Query schema '[NotNull PGtext] '["executed_at" ::: NotNull PGtimestamptz]

Selects a Migration from the MigrationsTable, returning the time at which it was executed.