A Haskell library to generate DDL queries by analyzing diff between Haskell and Database schema based on beam
- Generate SQL statements for a given schema (trick is to run it against an empty/undefined schema)
- Validate if database schema is in sync
- Generate delta DDL statement for quick sync (dev productivity)
- Automatic schema sync on runtime (pass the generated sql statements to database)
Module Database.Migration exports a function schemaDiff which takes a Connection (postgresql-simple Connection type), schema name and a CheckedDatabaseSetting which returns either DB is in sync or a list of DDL statements.
Refer to examples for implementation
Example output:
❯ stack run
Initiating connect
Connected to postgres
Table names ==>
["Configurations","Issues"]
Schema dump ==>
create schema if not exists migration;
create type migration."enum_Issues_status" as enum ('RAISED', 'ACTIVE', 'RESOLVED');
create sequence if not exists migration."Configurations_id_seq" as bigint increment by 1 minvalue 1 maxvalue 9223372036854775807 start with 1;
create table if not exists migration."Configurations" ("id" bigint not null primary key default nextval('migration."Configurations_id_seq"'::regclass), "key" varchar not null, "value" varchar not null, "createdAt" timestamp with time zone not null, "updatedAt" timestamp with time zone not null);
create table if not exists migration."Issues" ("id" varchar not null primary key, "ticketNo" bigint not null, "message" varchar not null, "status" migration."enum_Issues_status" not null, "image" bytea, "store" json, "createdAt" timestamp with time zone not null, "updatedAt" timestamp with time zone not null);
create index concurrently if not exists "Issues_ticketNo_customIdx" on migration."Issues" ("ticketNo") where "status" = 'RAISED' AND "message" = 'dummy' OR "store" IS NULL;
create unique index concurrently if not exists "Issues_ticketNo_idx" on migration."Issues" ("ticketNo");
Schema diff ==>
Schema in syncThis library allows you to define which type differences are acceptable using option typeLenient.
This options takes a function with 2 parameters column type defined in haskell schema, data type in postgres and result true if the difference is acceptable.
This is useful in case where you already have a database whose column types may not agree with beam and running alters may not be feasible
Sample Usage
-- Define a function which returns true for acceptable type differences
-- Takes table name, column name, haskell type and type defined in database. This function gets called if the column already exists with different type in database
-- Below function says for particular table and column if haskell schema has Text and DB has Varchar (any length) then it's acceptable and vice versa for other cases it's not
columnTypeLenient :: Text -> Text -> ColumnType -> ColumnType -> Bool
columnTypeLenient _ _ PgText (VarChar _) = True
columnTypeLenient _ _ (VarChar _) PgText = True
columnTypeLenient _ _ _ _ = False
-- Supply this function to schemaDiff via options
schemaDiff conn dbSettings $ defaultOptions {typeLenient = Just columnTypeLenient}Refer this table to understand type for lenient type matching
Option partitionOptions can be used to provide partition names for a particular table.
When supplied this with option the mentioned partitions will be included as separate tables and will share the schema of the parent table name supplied in key
Note: Currently both the parent table
Issuesand it's partitions will be included in validation. This option is useful for application managed partitioning.
Sample Usage
-- Define the partitions against a table name
-- In below sample there is an Issues table with 3 partitions by month
partitions :: HM.HashMap Text [Text]
partitions =
HM.fromList
[("Issues", ["Issue_M_202405", "Issue_M_202404", "Issue_M_202403"])]
-- Supply this map to schemaDiff via options
schemaDiff conn dbSettings $ defaultOptions {partitionOptions = DBM.PartitionOption True partitions}Option ignoreEnumOrder if set to True allows to remove differences in enums if the order isn't same as defined in Haskell.
If this is False library will generate queries to rename existing enums and create new and type casting all columns using it.
By default it's set to False.
Sample Usage
schemaDiff conn dbSettings $ defaultOptions {ignoreEnumOrder = True}Indexes can be verified by defining the Table instance's tableIndexes method. This library allows you to define normal, unique and partial indexes via helper function which also helps in type safety.
The below examples defines two indexes for Table Issues both being on ticketNo column
instance B.Table IssueT where
data PrimaryKey IssueT f =
IssuePrimaryKey (B.C f Text)
deriving (Generic, B.Beamable)
primaryKey = IssuePrimaryKey . _id
tableIndexes tblName tblFields@Issue {..} =
[
-- create unique index concurrently if not exists "Issues_ticketNo_idx" on migration."Issues" ("ticketNo");
uniqueIndex tblName [IC _ticketNo]
-- create index concurrently if not exists "Issues_ticketNo_customIdx" on migration."Issues" ("ticketNo")
-- where "status" = 'RAISED' AND "message" = 'dummy' OR "store" IS NULL;
, defaultIndexWithPred
(tblName <> "_ticketNo_customIdx")
tblFields
[IC _ticketNo] $ \Issue {..} ->
_status
B.==. B.val_ RAISED
B.&&. _message
B.==. B.val_ "dummy"
B.||. B.isNothing_ _store
]There are few helper functions to define indexes safely uniqueIndex, defaultIndex, uniqueIndexWithPred, defaultIndexWithPred.
The option ignoreIndexName can be set if you don't want to validate index names.
If you've defined a primary key then there isn't a need to define index on it in tableIndexes function as a primary key cannot exist without an index.
Note: This feature only works with the fork of beam
Option listDifference if set to True allows to return the differences in Haskell and database as DDL queries. Beware this will cause higher memory utilization for higher number of schemas or more no of entities.
If this is False library will discard the list and send only DB_NOT_IN_SYNC or DB_IN_SYNC message for each schema accordingly.
By default it's set to True.
Sample Usage
schemaDiff conn dbSettings $ defaultOptions {listDifference = False}| Haskell | Postgres | db-migration (ColumnType) |
|---|---|---|
| Text | varchar | Varchar |
| Char | char | Char |
| String | varchar | Varchar |
| Integer | bigint | BigInt |
| Int16 | smallint | SmallInt |
| Int32 | integer | Integer |
| Int64 | bigint | BigInt |
| Scientific | numeric | Numeric |
| ByteString | bytea | Bytea |
| LocalTime | timestamp | Timestamp |
| UTCTime | timestamp with time zone | Timestamp |
| Value | json | JSON |
| Value | jsonb | JSONB |
| Bool | bool | Boolean |
| Double | double | Double |
| [Text] | varchar()[] | Arr Varchar |
| T = A | B | enum | Enum |
- Support for postgres partitioned tables
- Report columns defined in database not in Haskell schema
- DROP DDL statements
- If an enum isn't named differently in database then instead of renaming the enum, this library will create a new enum and alter the columns type.
- If a sequence isn't named correctly in database then library will create a new sequence and this new sequence won't have last_val/currval of the existing sequence
- Some features like, index validation and schema specific enums only works with forked beam
- Currently this library only supports PostgreSQL database
- Currently this library only validates if Haskell schema is defined in database. Database may have more tables and columns
- This library uses a fork of beam which has some additional fixes/improvements (You can find the original repo here)
