pg-schema

Type-level definition of database schema and safe DML for PostgreSQL.

https://github.com/odr/pg-schema/tree/master/pg-schema#readme

Stackage Nightly 2026-11-17:0.9.0.1
Latest on Hackage:0.9.0.1

See all snapshots pg-schema appears in

BSD-3-Clause licensed by Dmitry Olshansky
Maintained by [email protected]
This version can be pinned in stack with:pg-schema-0.9.0.1@sha256:dd1f49dd96385c7014bd2ce7863dfe03db741cc3d4400d7ff185d1998055a897,9837

Module documentation for 0.9.0.1

pg-schema

pg-schema is a Haskell library for working with PostgreSQL in which the compiler checks your queries. Two ideas make it different:

  • The schema description is taken from the database itself, so it cannot silently drift from it. A generator reads a live PostgreSQL database and writes a Haskell module that describes its tables, columns, keys, relations and enums at the type level.
  • An SQL query is not written but built in accordance with Haskell types and checked against the generated schema description. You describe which data you need — which fields, and which related rows along foreign keys — and get one SQL query that reads exactly that. Inserts and updates are built the same way, from the type of the input data.

A typo in a column name, a relation to the wrong table, a value of the wrong type, nested data of the wrong shape — all of these become compile errors. When the database changes, you regenerate the module, and the compiler shows every query that no longer matches it.

The tutorial (English and Russian) explains everything step by step. Its complete, runnable code is in tutorial/.

Workflow

1. Create the database

The schema is defined in PostgreSQL as usual, with SQL or migrations; pg-schema never creates or changes tables. A relation between tables is referred to by the name of its foreign key constraint. If you do not name a constraint, PostgreSQL names it <table>_<columns>_fkey:

create schema tut;
create type tut.project_status as enum ('draft', 'active', 'archived');

create table tut.projects (
  id      bigserial primary key,
  title   text not null,
  status  tut.project_status not null default 'draft'
);

create table tut.tasks (
  project_id  bigint not null references tut.projects(id), -- FK name: tasks_project_id_fkey
  seq         integer not null,
  title       text not null,
  priority    integer not null default 100,
  primary key (project_id, seq)
);

The tutorial schema with comments: tutorial/sql/00-create-schema.sql.

2. Generate the schema description

Make a small executable that calls updateSchemaFile from PgSchema.Generation and run it after every change to the database structure (on CI it can check that the generated module is up to date):

import Control.Monad (void)
import PgSchema.Generation

main :: IO ()
main = void $ updateSchemaFile False "src/Sch.hs"
  (Left "PG_CONN") -- connection string from this environment variable
  "Sch"            -- name of the generated module
  "Sch"            -- name of the schema type
  GenNames { schemas = ["tut"], tables = [], addRelations = [] }

You do not write or edit the generated module; it exports the schema type Sch and a Haskell type for every enum (here with the constructors Project_status_draft, Project_status_active, Project_status_archived). You can have any number of such schemas, each with any subset of tables.

Tutorial: the generator and the generated module.

3. Optionally, define data types

Ordinary records with deriving Generic are enough — no FromRow/ToRow/FromJSON instances. Field names match column names (a renamer can map camelCase to snake_case), and a field named after a foreign key holds the related rows. A type may contain any subset of a table’s columns:

data Project = Project
  { title                 :: Text
  , status                :: PGEnum Sch ("tut" ->> "project_status")
  , tasks_project_id_fkey :: [Task] -- child rows along the foreign key
  } deriving Generic

data Task = Task { seq :: Int32, title :: Text } deriving Generic

Instead of records you can also write fields right in the type of an operation: "title" := Text :. "priority" := Int32.

4. Write queries with the EDSL, checked statically

All data functions are in PgSchema.DML. The annotation names the generated schema and the base table:

type MyAnn tab = 'Ann RenamerId Sch 5 ("tut" ->> tab)

activeProjects :: Connection -> IO [Project]
activeProjects conn = fst <$> selectSch (MyAnn "projects") conn (qRoot do
  qWhere $ "status" =? Project_status_active
  qPath "tasks_project_id_fkey" do
    qWhere $ "priority" <? (50 :: Int32)
    qOrderBy [ascf "seq"])

This reads projects with their tasks in one SQL query; insertJSON / upsertJSON write such a tree in one round-trip. If you mistype a name, the program does not compile:

• In schema Sch
  for table "tut" ->> "tasks"
  name "priorty" is not defined.

  Valid values are:
    Fields: project_id, seq, priority, title.
    Foreign key constraints: tasks_project_id_fkey.

  Your source or target type or renamer is probably invalid.

Types and nullability are checked as well, and inserts check that mandatory columns are present at every level of the tree.

What else

  • selectSch reads trees of data along relationships with one SELECT, with conditions, ordering and LIMIT/OFFSET at each level.
  • insertJSON, upsertJSON, updateJSON write a whole tree in one round-trip (JSON is used internally); insertSch, upsertByKey, updateByKey, updateByCond, deleteByCond work with a single table.
  • Enums, json/jsonb, arrays and composite keys are supported; relations missing in the database can be added in the generator; raw SQL is still available for anything else.
  • pg-schema works on top of postgresql-simple: operations take an ordinary Connection.

Modules

  • PgSchema.Generation — schema generation; used by your generator executable.
  • PgSchema.DML — reading and writing data; import it in your application.
  • PgSchema.Import — imported by the generated schema module.

Requirements

GHC ≥ 9.10: the API uses visible type arguments (selectSch (MyAnn "projects") …). Theoretically, one could target older GHC versions at the cost of a slightly worse API without RequiredTypeArguments.

Changes

Changelog for pg-schema

0.9.0.1

  • Generation: the column list of a table (tdFlds) no longer follows the physical order (attnum), which depends on the history of ALTER TABLE: primary key columns go first (in the key order), then the other columns by name. The generated text is the same for a database created from scratch and for one that went through reverted and redeployed migrations.
  • Generation: unique keys of a table (tdUniq) are sorted by their lists of columns instead of the constraint names.
  • Fix: addRelations took the column number from the position of the column in the list, so it got the wrong column after a column was dropped.

0.9.0.0

  • Fix typos in some compile-time error messages
  • Improve Haddock
  • Remove the unused constructor RFEmpty of RecField'.
  • PgOid is no longer exported from PgSchema.DML: it is used only to read the system catalogs, and its Eq instance treats all values as equal. Use Int for oid columns.
  • PgSchema.DML exports the type FldDef' (used by FdType / FdNullable).
  • Fix: tree DML (insertJSON, upsertJSON, updateJSON and their _ variants) stored Nothing for a nullable json / jsonb column as the JSON value null instead of SQL NULL. Now Nothing gives SQL NULL. A JSON null value (e.g. Just Aeson.Null in a Maybe Value field) can’t be told apart from Nothing in the JSON sent to PostgreSQL, so it is stored as SQL NULL too; use insertSch / updateByCond to store the JSON value null itself.
  • named (and type family Named): give field names once for a whole list of plain rows instead of "field" =: value in every row.
  • rowsOf / plains: rows from / to plain values with field names and types taken from the row type.
  • Aggregate fields (Aggr / Aggr') are now type-checked against the SQL result type of the aggregate function. Previously the check was not applied at all.
  • qPath / qPathFromHere / qPathToHere with an unknown relation name now report relation "..." is not defined with the valid relations of the table (previously qPath reported a misleading self-reference error).
  • Fix RETURNING checks of insertJSON / upsertJSON / updateJSON (ReturningMatchesInsert / ReturningMatchesUpsert / ReturningMatchesUpdate), which were silently skipped in most cases. Code that compiled only because of this may now fail to compile:
    • updateJSON: child list elements must be Maybe;
    • insertJSON: child list elements must not be Maybe;
    • upsertJSON: a child list element must be Maybe iff the child in the input row lacks mandatory columns (previously the returning columns were used) or upsert uses ON CONFLICT DO NOTHING with parent FK columns outside the conflict key; the root returning row must be Maybe if the input root row lacks mandatory columns.
  • NeedsFkKeyOnlyMaybe matched the opposite case (a child with plain columns outside the conflict key, i.e. ON CONFLICT … DO UPDATE, which always returns the row); now it matches ON CONFLICT DO NOTHING as documented.
  • upsertJSON: a child upserted with ON CONFLICT DO NOTHING whose key belongs to a row of another parent (parent FK outside the key) was returned as an object of null columns (a JSON decoding error) or, when the returning columns were all in the input, as if it were found under this parent; its own children were still processed. Now such a child is returned as null (Nothing) and its children are skipped, as for a row not found by UPDATE.
  • Fix: upsertByKey with a record whose fields all belong to the key used ON CONFLICT DO NOTHING and returned nothing for rows that already existed. Now it uses a no-op ON CONFLICT (key) DO UPDATE: an existing row is left as is and returned, so there is one returned row per input record. upsertByKey_ uses ON CONFLICT (key) DO NOTHING in this case; previously the conflict target was missing, so a conflict on another unique key was silently ignored, and now it is an error, as for other records.

0.8.1.1

  • aeson upper version is changed

0.8.1.0

  • upsertJSON: require Maybe on child returning when upsert uses ON CONFLICT DO NOTHING with parent FK columns outside the conflict key (NeedsFkKeyOnlyMaybe in ReturningMatchesUpsert)

0.8.0.1

  • Build all with -Wall
  • Small fixes in doc

0.8.0.0

  • Fix SELECT path matching after renamer: promoted PathElem' (peName, peDbName, peKind) replaces (Text, PathKind); runtime pathStepMatches supports point paths and broadcast by shared db fk name; independent filters on aliased sibling fields (e.g. mid1RootFk vs mid1_root_fk2).
  • Internal SELECT paths: [PathElemK] in PathCtx / TabOnDPathRen; removed EffPath on renamer-only pairs; TabOnDPath2 walks peDbName.
  • Flat DML: upsertByKey / upsertByKey_ and updateByKey / updateByKey_ (upsertByKey requires all mandatory columns and a full key — always INSERT … ON CONFLICT …; updateByKey is key-only, never inserts, bare returning type r', result IO ([Maybe r'], Text)).
  • Tree DML: updateJSON / updateJSON_ (update-only); InsertMode for the JSON pipeline.
  • Tree returning: Maybe or Bare for upsertJSON.
  • New constraints: AllHasKeyTree, CheckHasKey, ReturningMatches{Insert,Upsert,Update}, FromJSON / ToJSON for Maybe rows and null in returning arrays.
  • Rename TreeSch to HasSchema

0.7.1.2

  • Fix bug with aggregates (Min/Max/Sum/Avg)

0.7.1.1

  • Add upsertJSONText, upsertJSONText_

0.7.1.0

  • Condition DSL: (=??) on nullable columns generates SQL IS NOT DISTINCT FROM (same null-safe key equality as upsertJSON)
  • Improve transaction behavior for insertJSON/upsertJSON
  • Add tests for insertJSON/upsertJSON with transactions
  • upsertJSON / upsertJSON_: optional ON CONFLICT targets include NOT NULL unique keys (primary key still first), then nullable unique keys when the table has no NOT NULL UK; UPDATE by key uses IS NOT DISTINCT FROM for key columns; IdentityCandidates (compile-time) lists every PK/UK from the schema
  • insertJSON / insertJSON_: insert-only (plain INSERT, no ON CONFLICT or UPDATE; duplicate keys fail at the database)
  • Rename AllMandatoryOrHasPKTree to AllMandatoryOrHasKeyTree

0.7.0.1

  • Bug fixing (upsertJSON_ without change fields: on conflict do nothing)

0.7.0.0

  • Bug fixing (updateText_ signature)
  • Change signature for deleteByCond
  • Add CondAnn & QueryParamsAnn

0.6.1.0

  • Bug fixing (ins/upsertJSON for json db-fields)

0.6.0.0

  • Using Renamer for QueryParams and Cond
  • Improve error messaging
  • Support for ‘UnsafeCol’
  • Check possibility of type conversions for select-list/insert/update (it was broken in 0.5.0)
  • Check optional/mandatory for parent references is SELECT
  • Added qPathFromHere/qPathToHere. Self-reference require explicit direction in qPathXXX

0.5.2.0

  • Bug fixing (Problem with Self-references)

0.5.1.1

  • Improve error messaging

0.5.1.0

  • Bug fixing (TF stucks in some cases)

0.5.0.0

  • First release on Hackage