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

Note: This package has metadata revisions in the cabal description newer than included in the tarball. To unpack the package including the revisions, use 'cabal get'.

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 (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-15T14:17:12Z
Revised Revision 2 made by odr at 2026-11-16T10:08:49Z
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.8.1.0
Downloads 344 total (67 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.0

[back to package description]

pg-schema

pg-schema is a Haskell library that lifts a PostgreSQL schema description to the type level and gives you type-safe, compile-time-checked access to data.

The core idea is a type provider: from a live database (tables, primary and unique keys, foreign keys, column types—including arrays and enums) a schema representation is built that you then use with ordinary GHC types. You can have any number of such schemas; each may include any subset of tables.

To generate schemas you create a separate application that uses PgSchema.Generation.updateSchemaFile to produce .hs files describing each schema (sets of tables, relationships between them, and the types in use).

For reading and writing data, use the PgSchema.DML module. Queries are built from a typed EDSL, without hand-written SQL. You can describe data trees (nested records along relationships), including inserting an entire tree in one database round-trip (JSON is used internally), and fetching a tree with a single SELECT, with predicates, ordering, and limits at each level of the tree (WHERE, ORDER BY, LIMIT/OFFSET, and so on).

Inserts and reads use either ordinary Haskell records with a Generic instance or types of the form "fld1" := Int32 :. "fld2" := Maybe Text. Field names in Haskell records must match those in the database (with renaming supported) but you can work with any subset of the table's fields. Navigation along relationships uses foreign-key constraint names. Types and nullability are checked against the data layout.

SELECT and INSERT/UPDATE/UPSERT are implemented for the nested structures. There are also versions of UPDATE and DELETE applied to a single table.

When reading data, an EDSL sets conditions, ordering, and grouping. All of these operations are type-safe.

For inserts and updates, additional compile-time checks derive from database constraints—for example, inserts verify that mandatory fields are present at every level.

Requirements

GHC ≥ 9.10. Theoretically, one could target older GHC version at the cost of a slightly worse API without RequiredTypeArguments.