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

[ bsd3, database, library ] [ Propose Tags ] [ Report a vulnerability ]

A PostgreSQL library 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 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 (fields, and related rows along foreign keys) and get one SQL query that reads exactly that.

The workflow:

  1. Create the database as usual (SQL or migrations). Relations are referred to by the names of foreign key constraints (by default PostgreSQL names them <table>_<columns>_fkey).

  2. Generate the schema description with updateSchemaFile from PgSchema.Generation.

  3. Optionally, define data types: ordinary records with Generic, no instances needed.

  4. Write queries with the EDSL from PgSchema.DML; every name, type and relation is checked at compile time.

Learn it in the tutorial; its complete code is in https://github.com/odr/pg-schema/tree/master/tutorial.

Schema definition

Use updateSchemaFile function from PgSchema.Generation module to generate type-level definition of database schema. You can make several schemas with any parts of your database.

Usually you make executable which imports this module and run it to generate schema definition. You can run it on CI to check if schema definition is up to date.

Safe DML for PostgreSQL

Use PgSchema.DML module to describe and generate (in runtime) safe DML for PostgreSQL. All operations are statically checked and you can't write invalid SQL.

With pg-schema you can select or insert/upsert data into tree-like ADT in one request.

Example

Let's say we have tables shop.ord, shop.ord_pos and shop.article, where ord_pos references ord and article. The foreign keys have the default names given by PostgreSQL: ord_pos_ord_id_fkey and ord_pos_article_id_fkey. There are articles with ids 41 and 42, and we have generated the schema MySch with updateSchemaFile from PgSchema.Generation.

Then we can insert an order with its positions, and select orders with their positions and articles, using conditions for orders and positions. Each operation is one request.

type ShopAnn tabName = 'Ann RenamerId MySch 5 ("shop" ->> tabName)

-- An order with its positions to insert
type OrdIn = "num" := Text :. "ord_pos_ord_id_fkey" := [OrdPosIn]
type OrdPosIn = "num" := Int32 :. "article_id" := Int32 :. "price" := Double

-- Orders to select: with positions and their articles
data Order = Order { num :: Text, created_at :: Day, ord_pos_ord_id_fkey :: [OrdPos] } deriving Generic
data OrdPos = OrdPos { num :: Int32, ord_pos_article_id_fkey :: Article, price :: Double } deriving Generic
data Article = Article { name :: Text } deriving Generic

example :: Connection -> Day -> IO [Order]
example conn someDay = do
  void $ insertJSON_ (ShopAnn "ord") conn $ rowsOf @OrdIn
    [ "num1" :. [ 1 :. 42 :. 10, 2 :. 41 :. 15 ] ]
  fst <$> selectSch (ShopAnn "ord") conn (qRoot do
    qWhere $ "created_at" >=? someDay
    qOrderBy [descf "created_at", descf "num"]
    qPath "ord_pos_ord_id_fkey" do
      qWhere $ "price" >? (12 :: Double)
      qOrderBy [ascf "num"])

Note that all table names, field names and foreign key constraint names are checked at compile time!

Structure of modules

  • PgSchema.Generation - module with generation functions. Usually you make executable which generates schema definition using this module.

  • PgSchema.DML - module with DML functions. Import this module into your application and use it to generate safe DML for PostgreSQL.

  • PgSchema.Import - generated schema module imports this module.


[Skip to Readme]

Flags

Manual Flags

NameDescriptionDefault
time

Make CanConvert1 instances for Time types

Enabled
uuid

Make CanConvert1 instances for UUID type

Enabled
case-insensitive

Make CanConvert1 instances for CaseInsensitive type

Enabled
arbitrary

Make Arbitrary instances for all types (Enums, PgTag)

Disabled
debug

Trace queries

Disabled
flat

Make Flat instances for all types (Enums, PgTag)

Disabled
hashable

Make Hashable instances for all types (Enums, PgTag)

Disabled

Use -f <flag> to enable a flag, or -f -<flag> to disable that flag. More info

Downloads

Maintainer's Corner

Package maintainers

For package maintainers and hackage trustees

Candidates

  • No Candidates
Versions [RSS] 0.5.0.0, 0.5.1.0, 0.5.1.1, 0.5.2.0, 0.6.0.0, 0.6.1.0, 0.6.1.1, 0.7.0.0, 0.7.0.1, 0.7.1.0, 0.7.1.1, 0.7.1.2, 0.8.0.0, 0.8.0.1, 0.8.1.0, 0.9.0.0, 0.9.0.1 (info)
Change log ChangeLog.md
Dependencies aeson (>=2.0 && <2.4), base (>=4.20 && <5), bytestring (>=0.10 && <0.13), case-insensitive (>=1.0 && <1.3), containers (>=0.7 && <0.9), directory (>=1.3 && <1.5), exceptions (>=0.9 && <0.11), mtl (>=2.0 && <2.4), postgresql-libpq (>=0.11 && <0.12), postgresql-simple (>=0.6 && <0.8), scientific (>=0.2 && <0.4), singletons (>=3.0.3 && <3.1), singletons-th (>=3.4 && <3.6), text (>=2.0 && <2.2), time (>=1.12 && <2), uuid-types (>=1.0 && <1.1) [details]
Tested with ghc ==9.10.3, ghc ==9.12.4, ghc ==9.14.1
License BSD-3-Clause
Copyright Dmitry Olshansky
Author Dmitry Olshansky
Maintainer olshanskydr@gmail.com
Uploaded by odr at 2026-11-16T11:00:20Z
Category Database
Home page https://github.com/odr/pg-schema/tree/master/pg-schema#readme
Bug tracker https://github.com/odr/pg-schema/issues
Source repo head: git clone https://github.com/odr/pg-schema
Distributions Stackage:0.9.0.1
Downloads 347 total (69 in the last 30 days)
Rating (no votes yet) [estimated by Bayesian average]
Your Rating
  • λ
  • λ
  • λ
Status Docs uploaded by user
Build status unknown [no reports yet]

Readme for pg-schema-0.9.0.1

[back to package description]

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.