# Haskell Database > Model PostgreSQL databases from Haskell types using Rel8. Covers the type-mapping decision tree (composite types vs jsonb vs enums vs primitives), indexed JSONB queries, composite-type indexing caveats, Rel8 table definitions with HKD, and pure-SQL migrations via hasql-migrations. Use when designing the database layer, choosing PostgreSQL column types for Haskell ADTs, writing Rel8 queries, or setting up migrations. - Skill: `ivelten/haskell-database` (Agent Skill) - Install (CLI): `npx skillmds@latest add ivelten/haskell-database` - Raw SKILL.md: https://api.skillmd.com/api/skills/ivelten/haskell-database/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: ivelten (https://skillmd.com/u/ivelten) - Updated: 2026-09-22 - Page: https://skillmd.com/skills/ivelten/haskell-database --- # PostgreSQL Modeling with Rel8 Default to **Rel8** for the database layer. Fall back to raw **Hasql** only when the user explicitly asks or when a query cannot be expressed through Rel8's relational combinators. ## Type-mapping decision tree Apply this decision in order for every Haskell type that must live in PostgreSQL: ``` Is it a newtype over a primitive? → map to the underlying primitive column type Is it a flat enum (no fields)? → PostgreSQL enum via DBEnum Are all fields fixed at compile-time AND you never need indexed lookups on sub-fields? → PostgreSQL composite type (DBComposite) Is the structure dynamic OR you need GIN-indexed sub-field search? → jsonb column (JSONBEncoded) ``` ### Why the sub-field-indexing rule matters A composite type column `address address_t` cannot have a regular B-tree index placed on `(address).city` without a **functional index**: ```sql CREATE INDEX ON users ((address).city); ``` Functional indexes work but cannot participate in multi-column index strategies and require manual maintenance in migrations. If you query by a sub-field frequently, promote it to a top-level column instead. Reserve composite types for values you read/write atomically and never filter by individual fields in production queries. --- ## 1. Primitives and newtypes Newtypes map straight to their underlying column type. The `coerce` trick keeps boilerplate minimal: ```haskell newtype UserId = UserId UUID deriving stock (Show, Eq, Ord, Generic) deriving newtype (DBType, DBEq, DBOrd, FromJSON, ToJSON) newtype Email = Email Text deriving stock (Show, Eq, Ord, Generic) deriving newtype (DBType, DBEq, FromJSON, ToJSON) ``` `deriving newtype (DBType)` picks up the underlying `DBType` instance automatically — no manual `typeInformation` needed. --- ## 2. Haskell enums → PostgreSQL enums Use `DBEnum` for flat sum types with no constructor fields. The enum name in PostgreSQL must match `enumTypeName`. ```haskell data OrderStatus = Pending | Processing | Shipped | Cancelled deriving stock (Show, Read, Eq, Ord, Enum, Bounded, Generic) instance DBEnum OrderStatus where enumTypeName = "order_status" -- DBType is derived from DBEnum automatically instance DBType OrderStatus where typeInformation = enumTypeInformation @OrderStatus ``` Migration to create the enum: ```sql -- migrations/002_add_order_status_enum.sql CREATE TYPE order_status AS ENUM ('Pending', 'Processing', 'Shipped', 'Cancelled'); ``` The constructor names become enum labels verbatim (case-sensitive). Keep Haskell constructors PascalCase and PostgreSQL labels matching — Rel8 uses `show` to encode and `readsPrec`/`Read` to decode. --- ## 3. Fixed records → PostgreSQL composite types Use when all fields are fixed at compile time and you don't need indexed sub-field queries. ```haskell -- The HKD form of the composite type data AddressF f = Address { addressStreet :: Column f Text , addressCity :: Column f Text , addressCountry :: Column f Text , addressZip :: Column f Text } deriving stock Generic deriving anyclass Rel8able instance DBComposite AddressF where compositeTypeName = "address_t" ``` Reference it from a parent table using `Composite`: ```haskell data UserF f = User { userId :: Column f UserId , userEmail :: Column f Email , userAddress :: Column f (Composite AddressF) } deriving stock Generic deriving anyclass Rel8able userSchema :: TableSchema (UserF Name) userSchema = TableSchema { name = "users" , columns = UserF { userId = "id" , userEmail = "email" , userAddress = "address" } } ``` > **`TableSchema` needs `DisambiguateRecordFields`.** Rel8 1.7 exports three records with a > `name` field — `TableSchema`, `QualifiedName` and `TypeName` — so the block above fails with > `[GHC-87543] Ambiguous occurrence 'name'`. The constructor is explicit, so the extension is > enough to resolve it; enable it per module rather than importing Rel8 qualified: > > ```haskell > {-# LANGUAGE DisambiguateRecordFields #-} > ``` Migration: ```sql -- migrations/003_add_address_type.sql CREATE TYPE address_t AS ( street text, city text, country text, zip text ); ALTER TABLE users ADD COLUMN address address_t NOT NULL; ``` --- ## 4. Dynamic sum types → jsonb Use for types whose shape varies across constructors, for fields that implement `FromJSON`/`ToJSON` and whose schema may evolve, or when you need GIN-indexed key/value lookups. ```haskell -- Any type with FromJSON + ToJSON can live as jsonb data Payload = TextPayload { text :: Text } | ImagePayload { url :: Text, width :: Int, height :: Int } | FilePayload { fileId :: FileId, mimeType :: Text } deriving stock (Show, Eq, Generic) -- Do NOT derive FromJSON/ToJSON here — write custom instances. -- See `haskell-json` for the TaggedObject pattern and manual instance pattern. data EventF f = Event { eventId :: Column f EventId , eventPayload :: Column f (JSONBEncoded Payload) } deriving stock Generic deriving anyclass Rel8able ``` `JSONBEncoded a` requires `FromJSON a` and `ToJSON a`. No extra instances needed. **Always write explicit `ToJSON`/`FromJSON` instances for types stored as `jsonb`.** Aeson's generic defaults for sum types produce a shape that is brittle under constructor renames and hard to query with jsonb path operators. Use the `TaggedObject` pattern with a `"type"` discriminator and flat fields — see `haskell-json` for the full pattern, including the manual-instance approach for constructors without record syntax. ### Indexing jsonb For general key/value lookups, a GIN index covers all paths: ```sql CREATE INDEX ON events USING gin (payload); ``` For a specific frequently-queried path, a partial or path index is more selective: ```sql -- Index only the "url" key of ImagePayload rows CREATE INDEX ON events ((payload ->> 'url')) WHERE payload ? 'url'; ``` --- ## 5. Full table definition example ```haskell -- Types type User = UserF Result type UserExpr = UserF Expr data UserF f = User { userId :: Column f UserId , userEmail :: Column f Email , userStatus :: Column f OrderStatus , userAddress :: Column f (Composite AddressF) , userMeta :: Column f (JSONBEncoded UserMeta) , userCreated :: Column f UTCTime } deriving stock Generic deriving anyclass Rel8able userSchema :: TableSchema (UserF Name) userSchema = TableSchema { name = "users" , columns = UserF { userId = "id" , userEmail = "email" , userStatus = "status" , userAddress = "address" , userMeta = "meta" , userCreated = "created_at" } } ``` --- ## 6. Queries Rel8 queries compose monadically in the `Query` type. Use `select` to run them via Hasql. ```haskell import Rel8 -- Select all users with a given status activeUsers :: Query (UserF Expr) activeUsers = do u <- each userSchema where_ $ u.userStatus ==. lit Active return u -- Run it fetchActiveUsers :: Session [User] fetchActiveUsers = select activeUsers -- Insert insertUser :: UserF Expr -> Statement () insertUser u = insert $ Insert { into = userSchema , rows = values [u] , onConflict = Abort , returning = pure () } ``` Compose queries with `where_`, `orderBy`, `limit`, `offset`, and `aggregate` — all produce `Query`, keeping them composable. --- ## 7. Migrations with hasql-migrations Use numbered SQL files. Apply them with `hasql-migrations` which tracks applied migrations in a `schema_migrations` table. ### File layout ``` migrations/ 001_initial_schema.sql 002_add_order_status_enum.sql 003_add_address_type.sql 004_create_users_table.sql 005_create_events_table.sql ``` ### Applying migrations ```haskell import Hasql.Migration import Hasql.Transaction.Sessions (run) runMigrations :: Pool -> IO () runMigrations pool = do migrations <- loadMigrationsFromDirectory "migrations" result <- use pool $ run (runMigration (MigrationInitialization : migrations)) case result of Left err -> throwIO (MigrationError err) Right () -> pure () ``` ### Migration conventions - One concern per file: type definitions, table creation, index creation, and backfills each live in their own numbered file. - Never edit an applied migration — add a new one. - Enum evolution: PostgreSQL allows `ALTER TYPE ... ADD VALUE` but not removing values or reordering. Design enums conservatively; evolve via new values only. - Composite type evolution: `ALTER TYPE ... ADD ATTRIBUTE` appends a nullable column. Adding a NOT NULL attribute requires a default or a backfill migration. - For complex multi-step changes, use explicit transactions per migration file; `hasql-migrations` wraps each file in a transaction by default. ### Example migration ```sql -- migrations/004_create_users_table.sql CREATE TABLE users ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), email text NOT NULL UNIQUE, status order_status NOT NULL DEFAULT 'Pending', address address_t NOT NULL, meta jsonb NOT NULL DEFAULT '{}', created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX ON users (status); CREATE INDEX ON users USING gin (meta); ``` --- ## 8. cabal dependencies ```cabal build-depends: rel8 ^>= 1.7 , hasql ^>= 1.9 , hasql-pool ^>= 1.3 , hasql-migration ^>= 0.3 , aeson ^>= 2.2 , uuid ^>= 1.3 , time ^>= 1.12 ``` Add `hasql-transaction` if you need explicit multi-statement transactions beyond what `hasql-migration` handles. These bounds are not decorative — the obvious-looking alternatives do not resolve on the pinned GHC 9.10.3 (verified 2026-09): - **`rel8 ^>= 1.5` fails.** rel8 before 1.7 caps `base` below 4.20, so it cannot be used with GHC 9.10.3 at all. - **`hasql ^>= 1.8` together with `hasql-pool ^>= 1.0` is unsatisfiable.** That `hasql-pool` requires `hasql >= 1.6.0.1 && < 1.7`. Solve the two together rather than pinning them independently. ### The constraint that is easy to miss `rel8-1.7.0.0` depends on `semialign` with **no upper bound**, and `semialign-1.4` breaks it: ``` Rel8/Schema/HTable/Vectorize.hs:200: error: [GHC-39999] No instance for 'Unzip (First a)' arising from the superclasses of an instance declaration In the instance declaration for 'Semialign (First a)' ``` The solver picks 1.4 by default, so the build fails before any project code is compiled. Pin it in `cabal.project`: ```cabal constraints: semialign < 1.4 ``` When a dependency fails to compile in its own source, check for an unbounded dependency in its `.cabal` before assuming the GHC pin is at fault. --- ## Related - Domain types feeding into the database layer: see `haskell-type-design` for newtype and record conventions. - JSON serialization for `jsonb` columns: see `haskell-json` for the `TaggedObject` discriminator pattern and manual instances. - Wrapping DB access in an effect: see `haskell-effectful` — the `UserStore` effect pattern applies directly to Rel8 interpreters. - Domain errors from DB calls (constraint violations, connection failures): see `haskell-domain-errors`. - Quality gates before shipping the schema: see `haskell-quality-gates` for the final checklist.