Run a single SELECT for root table tab (see annotation ann with schema sch and Renamer ren) and decode rows into [r], also returning the SQL text and bind parameters (for tracing or debugging).

The desired result type r fixes the shape of each row: typically a record with columns of the root table and nested Haskell values for relations along the schema graph. There may be several child-side fields (often lists of nested records) and several parent-side fields (nested records; wrapped in Maybe when the foreign-key side you join from allows NULL).

What actually appears in the generated SQL beyond that shape is controlled by the QueryParam: filters, which paths are traversed, ordering on a branch (e.g. child rows sorted by their position number), limits, and similar options. You only configure in QueryParam what you need for this query—not every possible relation field in r.

Build QueryParam with the MonadQP API.

Return the generated SELECT SQL text (and bind parameters), e.g. for debugging.

Empty QueryParam.

It means that SELECT is defined only by structure of output type

Execute MonadQP and get QueryParam.

The table t defines a context and becomes the "current" table

Change context (current table) to parent or child table.

The Symbol must name the foreign-key constraint (edge to the parent or from the child) for the step away from the current table.

Add WHERE condition for the current table.

If several qWhere exist they are composed according to the Monoid instance for Cond, i.e. with (&&&)

Add ORDER BY condition for the current table

Several qOrderBy are possible and they are composed together.

Ordering can span the current table and joined parents. For example:

qOrderBy [ascf "f1"]
qPath "ref-to-parent" do
  qOrderBy [descf "f2"]
qOrderBy [ascf "f3"]

we get ORDER BY t1.f1, t2.f2 DESC, t1.f3

Add DISTINCT condition for the current table. It is applied only to "root" or "children" tables.

Add DISTINCT ON condition for the current table.

It can also be applied on a parent table because that parent is joined to the current table.

qDistinctOn automatically adds fields to the ORDER BY clause (as PostgreSQL requires). (That's why OrdDirection is needed) So having

qOrderBy [descf "f0"]
qDistinctOn [ascf "f1"]
qPath "ref-to-parent" do
  qDistinctOn [descf "f2"]
qDistinctOn [ascf "f3"]

we get DISTINCT ON (t1.f1, t2.f2, t1.f3) ... ORDER BY t1.f1, t2.f2 DESC, t1.f3, t1.f0 DESC

Add LIMIT condition for the current table. It is applied only to "root" or "children" tables.

If qLimit is applied several times on the same path, only the last one is used

Add OFFSET condition for the current table. It is applied only to "root" or "children" tables.

If qOffset is applied several times on the same path, only the last one is used

Parameters that are used to describe SELECT.

You don't need to make it directly. Use MonadQP to define QueryParam instead.

GADT to safely set `where` condition

GADT to safely set `order by` clauses

GADT to safely set `limit/offset` clauses

GADT to safely set `distinct/distinct on` clauses

Disjunction

Conjunction

Boolean NOT

Check that field value is NULL

Check that field value belongs to non-empty list of values

Check that field value belongs to the list of values. If the list is empty, the condition evaluates to false.

Check that condition is satisfied in parent table

True when related rows exist in the child table and satisfy the nested condition there

TabParam is used to limit child dataset (usually with ORDER BY and LIMIT) before applying condition on child table

Parameters for child table.

It is used to limit child dataset (usually with ORDER BY and LIMIT) before applying condition on child table

Default empty TabParam.

GADT to safely set `where` condition for table tab based on definition of schema sch

Cond is Monoid with conjunction ((&&&)) as mappend

Comparison constructors; each is paired with its corresponding operator (e.g. (:=) with (=?)).

Just boolean operations

RWS-Monad to generate condition. * Read: Stack of numbers of parent tables. The top is "current table" * Write: List of placeholder-values.

Note: SQL is generated top-down so placeholder values appear in the correct order.

• State: Last number of table "in use"

Relation definition for relation name ref.

Insert records into a table. You can request any subset of columns from the inserted row via the result type.

All mandatory fields having no defaults should be present.

Insert records into a table without RETURNING.

Construct SQL text for inserting records into a table and returning some fields.

Construct SQL text for inserting records into a table without RETURNING.

All fields are plain (no RFToHere/RFFromHere)

Plain insert without RETURNING. Check that all fields belong to the root table and all mandatory fields are present.

Plain insert with RETURNING. Check that all inserted and returned fields belong to the root table and all mandatory fields are present.

Update rows matching a condition; the result type selects which columns are returned.

Update records by condition without RETURNING.

Construct SQL text for updating records by condition and returning some fields.

Construct SQL text for updating records by condition without RETURNING.

Plain update with RETURNING. Check that all updated and returned fields belong to the root table.

Plain update without RETURNING. Check that all updated fields belong to the root table.

Insert records into a table and its children using JSON data internally.

Like upsertJSON, but requires all mandatory columns at every node (insert-only constraint).

Like insertJSON, but does not return rows.

Upsert a forest of rows into the root table and its child tables in one round-trip, using JSON inside PostgreSQL (same pipeline as insertJSON).

Input shape (r): a record tree that may contain the root table’s columns and nested child branches (one-to-many from the root downward). There are no nested parent branches: parent keys are implied by the tree you send, not by embedding parent rows inside children.

Output shape (r'): a record tree whose graph of nested tables is a subgraph of the input: the same tables can appear, but you choose which columns (and which levels) appear in the result—whatever is available through the generated RETURNING/result projection. Field sets may differ from r; relation structure cannot grow beyond what you sent in.

What to supply at each node: at every level, each row must either include all mandatory columns (for columns that are mandatory in the schema /sense of this API/) or, alternatively, enough primary-key columns to identify an existing row. Foreign-key columns that are filled in by the parent level (for example after an auto-generated id on insert) do not need to be present on the child payload.

Insert vs update vs upsert per row: the engine picks one of INSERT, UPDATE, or UPSERT from the keys and mandatory fields you provide:

• all mandatory fields present and no primary key → INSERT
• primary key present, not all mandatory fields → UPDATE
• primary key present and all mandatory fields → UPSERT

insertJSON is the same execution path but adds a stricter type-level constraint: every mandatory field must be present (pure inserts). upsertJSON relaxes that so updates and upserts are expressible as in the rules above.

Like upsertJSON, but does not return rows.

Insert tree without RETURNING.

Check that all mandatory fields are present in all tables in tree. Reference fields in the child tables are not checked - they are inserted automatically.

Insert tree with RETURNING.

Check that all mandatory fields are present in all tables in tree. Reference fields in the child tables are not checked - they are inserted automatically.

It also checks that we get returnings only from the tables we inserted into.

Upsert tree without RETURNING.

Check that all mandatory fields or primary keys are present in all tables in tree. Reference fields in the child tables are not checked - they are inserted automatically.

Upsert tree with RETURNING.

Check that all mandatory fields or primary keys are present in all tables in tree. Reference fields in the child tables are not checked - they are inserted automatically.

It also checks that we get returnings only from the tables we upserted into.

Delete records in table by condition.

Construct SQL text for deleting records by condition.

Type-level annotation: enforce constraints at compile time and drive demoted types used to generate correct SQL. annRen and annSch are fixed for the whole DML-operation. annDepth and annTab are changed while traversing the structure of the ADT.

Renamer that does not change the symbol.

Closed type family to convert CamelCase to snake_case that can be used in Renamer.

>>> import Data.Proxy
>>> symbolVal (Proxy @(CamelToSnake TESTCamelTo_snake_Case))
"t_e_s_t_camel_to_snake__case"

Renamer is a type-level function from Symbol to Symbol.

Apply renamer to symbol.

Like Apply but specialized for Symbol.

To make your own Renamer, typically you make

• data MyRenamer :: Renamer
• closed type family: `type family MyRenamerImpl (s :: Symbol) :: Symbol where ... `
• type instance ApplyRenamer MyRenamer s = MyRenamerImpl s

Annotates a value with schema/type information for DML codecs.

You can describe rows in two ways:

1. Ordinary Haskell records with a Generic instance.
2. Symbol-labelled rows built from (=:) and chained with (:.) from postgresql-simple (re-exported from PgSchema.DML).

PgTag is analogous to Tagged from the tagged package, but carries JSON (and related) instances suited to pg-schema.

Both representations can be mixed in one row.

Two pieces of syntax cooperate with PgTag:

• (=:) builds a PgTag value (field name + payload). RequiredTypeArguments lets you avoid noisy explicit type application on the field name: you write "name" =: John instead of @"name" =: John.
• (:=) is the type-level spelling of the same idea (see the (:=) type synonym below).

A composite type to parse your custom data structures without having to define dummy newtype wrappers every time.

instance FromRow MyData where ...

instance FromRow MyData2 where ...

then I can do the following for free:

res <- query' c "..."
forM res $ \(MyData{..} :. MyData2{..}) -> do
  ....

Introduce enum database types. Data instances are produced by schema generation. You can use these data instances in you records to SELECTINSERTUPSERT data

All aggregate functions except count can return NULL. But if field under aggregate is mandatory they return NULL only on empty set if there is no group by clause. E.g. `select min(a) from t where false` So we require Nullable for Aggr.

Aggr' is like Aggr but cannot be used in SELECT without `group by`. So it is mandatory if field is mandatory.

Introduce aggregate functions.

I.e. "fld" := Aggr AMin (Maybe Int32) means "minimum value of the field fld"

Aggr requires a Maybe argument for all functions except count.

Only a small set of aggregations are supported currently: count, min, max, sum, avg.

Supported SQL aggregate functions

PGArray has no JSON instances. '[]' has JSON, but no PG instances. This one has both.

Use this type to work with arrays in database.

All elements are Maybe because PostgreSQL does not guarantee that all elements are present. PgTag typeName PgArr can be safely converted to ToField with type information (e.g. val::int[]). This instance is used internally in the generation of SQL.

Make PgArr from list. All elements are lifted to Maybe

Make list from PgArr. All empty (Nothing) elements are omitted.

Closed type family to check that field can be converted to or from Haskell type. To make your types convertible to some database type use open type family CanConvert1.

Open mapping from a PostgreSQL type (non-nullable) to a Haskell type. Add your own equations to this family to support extra pairings.