packages feed

pg-schema-0.9.0.1: README.md

# pg-schema

[*pg-schema*](https://hackage-content.haskell.org/package/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](https://odr.github.io/pg-schema) (English and Russian) explains everything step by step. Its complete, runnable code is in [`tutorial/`](https://github.com/odr/pg-schema/tree/master/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`:

```sql
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`](https://github.com/odr/pg-schema/blob/master/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):

```haskell
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](https://github.com/odr/pg-schema/blob/master/tutorial/app/generator/Main.hs) and [the generated module](https://github.com/odr/pg-schema/blob/master/tutorial/app/app/Sch.hs).

### 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:

```haskell
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:

```haskell
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`.