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 |
pg-schema-0.9.0.1@sha256:dd1f49dd96385c7014bd2ce7863dfe03db741cc3d4400d7ff185d1998055a897,9837Module documentation for 0.9.0.1
- PgSchema
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
selectSchreads trees of data along relationships with oneSELECT, with conditions, ordering andLIMIT/OFFSETat each level.insertJSON,upsertJSON,updateJSONwrite a whole tree in one round-trip (JSON is used internally);insertSch,upsertByKey,updateByKey,updateByCond,deleteByCondwork 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-schemaworks on top ofpostgresql-simple: operations take an ordinaryConnection.
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 ofALTER 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:
addRelationstook 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
RFEmptyofRecField'. PgOidis no longer exported fromPgSchema.DML: it is used only to read the system catalogs, and itsEqinstance treats all values as equal. UseIntforoidcolumns.PgSchema.DMLexports the typeFldDef'(used byFdType/FdNullable).- Fix: tree DML (
insertJSON,upsertJSON,updateJSONand their_variants) storedNothingfor a nullablejson/jsonbcolumn as the JSON valuenullinstead of SQLNULL. NowNothinggives SQLNULL. A JSONnullvalue (e.g.Just Aeson.Nullin aMaybe Valuefield) can’t be told apart fromNothingin the JSON sent to PostgreSQL, so it is stored as SQLNULLtoo; useinsertSch/updateByCondto store the JSON valuenullitself. named(and type familyNamed): give field names once for a whole list of plain rows instead of"field" =: valuein 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/qPathToHerewith an unknown relation name now reportrelation "..." is not definedwith the valid relations of the table (previouslyqPathreported a misleading self-reference error).- Fix
RETURNINGchecks ofinsertJSON/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 beMaybe;insertJSON: child list elements must not beMaybe;upsertJSON: a child list element must beMaybeiff the child in the input row lacks mandatory columns (previously the returning columns were used) or upsert usesON CONFLICT DO NOTHINGwith parent FK columns outside the conflict key; the root returning row must beMaybeif the input root row lacks mandatory columns.
NeedsFkKeyOnlyMaybematched 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 matchesON CONFLICT DO NOTHINGas documented.upsertJSON: a child upserted withON CONFLICT DO NOTHINGwhose key belongs to a row of another parent (parent FK outside the key) was returned as an object ofnullcolumns (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 asnull(Nothing) and its children are skipped, as for a row not found byUPDATE.- Fix:
upsertByKeywith a record whose fields all belong to the key usedON CONFLICT DO NOTHINGand returned nothing for rows that already existed. Now it uses a no-opON CONFLICT (key) DO UPDATE: an existing row is left as is and returned, so there is one returned row per input record.upsertByKey_usesON CONFLICT (key) DO NOTHINGin 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
Maybeon child returning when upsert usesON CONFLICT DO NOTHINGwith parent FK columns outside the conflict key (NeedsFkKeyOnlyMaybeinReturningMatchesUpsert)
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); runtimepathStepMatchessupports point paths and broadcast by shared db fk name; independent filters on aliased sibling fields (e.g.mid1RootFkvsmid1_root_fk2). - Internal SELECT paths:
[PathElemK]inPathCtx/TabOnDPathRen; removedEffPathon renamer-only pairs;TabOnDPath2walkspeDbName. - Flat DML:
upsertByKey/upsertByKey_andupdateByKey/updateByKey_(upsertByKeyrequires all mandatory columns and a full key — alwaysINSERT … ON CONFLICT …;updateByKeyis key-only, never inserts, bare returning typer', resultIO ([Maybe r'], Text)). - Tree DML:
updateJSON/updateJSON_(update-only);InsertModefor the JSON pipeline. - Tree returning: Maybe or Bare for
upsertJSON. - New constraints:
AllHasKeyTree,CheckHasKey,ReturningMatches{Insert,Upsert,Update},FromJSON/ToJSONforMayberows andnullin returning arrays. - Rename
TreeSchtoHasSchema
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 SQLIS 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_: optionalON CONFLICTtargets include NOT NULL unique keys (primary key still first), then nullable unique keys when the table has no NOT NULL UK;UPDATEby key usesIS NOT DISTINCT FROMfor key columns;IdentityCandidates(compile-time) lists every PK/UK from the schemainsertJSON/insertJSON_: insert-only (plainINSERT, noON CONFLICTorUPDATE; duplicate keys fail at the database)- Rename
AllMandatoryOrHasPKTreetoAllMandatoryOrHasKeyTree
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