packages feed

pg-schema 0.9.0.0 → 0.9.0.1

raw patch · 6 files changed

+177/−40 lines, 6 filesPVP ok

version bump matches the API change (PVP)

API changes (from Hackage documentation)

Files

ChangeLog.md view
@@ -1,5 +1,16 @@ # 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 of `ALTER 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: `addRelations` took 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
README.md view
@@ -1,21 +1,121 @@ # pg-schema -[*pg-schema*](https://hackage-content.haskell.org/package/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.+[*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 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.+- **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. -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).+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. -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).+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). -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.+## Workflow -SELECT and INSERT/UPDATE/UPSERT are implemented for the nested structures. There are also versions of UPDATE and DELETE applied to a single table.+### 1. Create the database -When reading data, an EDSL sets conditions, ordering, and grouping. All of these operations are type-safe.+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`: -For inserts and updates, additional compile-time checks derive from database constraints—for example, inserts verify that mandatory fields are present at every level.+```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**. Theoretically, one could target older GHC version at the cost of a slightly worse API without `RequiredTypeArguments`.+**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`.
pg-schema.cabal view
@@ -1,6 +1,6 @@ cabal-version:  3.12 name:           pg-schema-version:        0.9.0.0+version:        0.9.0.1 category:       Database author:         Dmitry Olshansky maintainer:     olshanskydr@gmail.com@@ -14,6 +14,25 @@   , GHC ==9.14.1 synopsis:       Type-level definition of database schema and safe DML for PostgreSQL. description:+    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 <https://odr.github.io/pg-schema 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.
src/PgSchema/Generation.hs view
@@ -117,11 +117,18 @@         mkNums nns fields = do           pgcl <- L.find (\c -> c.class__namespace == "nspname" =: nns.nnsNamespace             && c.relname == nns.nnsName) classes-          inds <- for fields \fld ->-            L.findIndex ((==fld) . (.attname)) pgcl.attribute__class-          pure $ pgArr' $ fromIntegral . (+1) <$> inds+          nums <- for fields \fld ->+            (.attnum) <$> L.find ((==fld) . (.attname)) pgcl.attribute__class+          pure $ pgArr' nums --- all data are ordered to provide stable `hashSchema`+-- All data are ordered so that the generated text is stable: it must be the+-- same for a database built from scratch (CI) and for one that went through+-- migrations being reverted and redeployed (development).+-- `tdFlds` doesn't follow `attnum`: it depends on the history of `ALTER TABLE`+-- (e.g. a column dropped and added back moves to the end). Instead the primary+-- key columns go first (in the key order), then the other columns by name.+-- `tdUniq` is sorted by the lists of columns, not by the constraint names:+-- names generated by PostgreSQL depend on the names already taken.     qpTyp = qRoot do       qOrderBy [ascf "typname", ordNS "typnamespace"]       qPath "enum__type" do@@ -203,9 +210,9 @@     ptabDef c@PgClass{..} = (tabName, (TabDef{..}, froms, tos))       where         tabName = tabKey c-        tdFlds = attname <$> coerce attribute__class+        tdFlds = tdKey <> L.sort (L.filter (`L.notElem` tdKey) $ attname <$> attribute__class)         tdKey = L.concat $ keysBy (=='p')-        tdUniq = keysBy (=='u')+        tdUniq = L.sort $ keysBy (=='u')         keysBy f           = Mb.mapMaybe -- if something is wrong exclude such constraint           (traverse numToName . unPgArr' . (.conkey))
src/PgSchema/Schema.hs view
@@ -45,9 +45,9 @@  -- | Table shape: ordered column names, primary key, unique constraints. data TabDef' s = TabDef-  { tdFlds :: [s]     -- ^ Physical column names in order.+  { tdFlds :: [s]     -- ^ Column names: primary key columns first, then the rest by name.   , tdKey :: [s]      -- ^ Primary key column names.-  , tdUniq :: [[s]]   -- ^ Unique constraints as lists of column names.+  , tdUniq :: [[s]]   -- ^ Unique constraints as lists of column names, sorted.   } deriving Show  -- | Foreign-key-style link between two qualified tables and column mapping.
test-pgs/Sch.hs view
@@ -64,18 +64,18 @@   type TTypDef Sch name = TTypDefSch name  type family TTabDefSch (name :: NameNSK) :: TabDef' TL.Symbol where-  TTabDefSch ( "test_pgs" ->> "aggr_nums" ) = 'TabDef '[ "id","grp","i2","i4","i8","n","f4","f8" ] '[ "id" ] '[  ]-  TTabDefSch ( "test_pgs" ->> "arrays" ) = 'TabDef '[ "id","root_id","dates_nullable","jsons" ] '[ "id" ] '[  ]-  TTabDefSch ( "test_pgs" ->> "base_arr_converts" ) = 'TabDef '[ "cboolean","cint4","cfloat8","ctimestamptz","ctext" ] '[  ] '[  ]-  TTabDefSch ( "test_pgs" ->> "base_converts" ) = 'TabDef '[ "cboolean","cint4","cfloat8","cdate","ctime","ctimestamp","ctimestamptz","ctext" ] '[  ] '[  ]+  TTabDefSch ( "test_pgs" ->> "aggr_nums" ) = 'TabDef '[ "id","f4","f8","grp","i2","i4","i8","n" ] '[ "id" ] '[  ]+  TTabDefSch ( "test_pgs" ->> "arrays" ) = 'TabDef '[ "id","dates_nullable","jsons","root_id" ] '[ "id" ] '[  ]+  TTabDefSch ( "test_pgs" ->> "base_arr_converts" ) = 'TabDef '[ "cboolean","cfloat8","cint4","ctext","ctimestamptz" ] '[  ] '[  ]+  TTabDefSch ( "test_pgs" ->> "base_converts" ) = 'TabDef '[ "cboolean","cdate","cfloat8","cint4","ctext","ctime","ctimestamp","ctimestamptz" ] '[  ] '[  ]   TTabDefSch ( "test_pgs" ->> "dim" ) = 'TabDef '[ "id","name" ] '[ "id" ] '[  ]-  TTabDefSch ( "test_pgs" ->> "ext_arr_converts" ) = 'TabDef '[ "ccitext","cbytea","cjsonb","cuuid","ccolor" ] '[  ] '[  ]-  TTabDefSch ( "test_pgs" ->> "ext_converts" ) = 'TabDef '[ "ccitext","cbytea","cjsonb","cjson","cuuid","ccolor" ] '[  ] '[  ]-  TTabDefSch ( "test_pgs" ->> "leaf" ) = 'TabDef '[ "root_id","seq","leaf_no","value","category","created_at" ] '[ "root_id","seq","leaf_no" ] '[  ]-  TTabDefSch ( "test_pgs" ->> "mid1" ) = 'TabDef '[ "id","root_id","pos","flag","sort_key","payload" ] '[ "id" ] '[  ]-  TTabDefSch ( "test_pgs" ->> "mid2" ) = 'TabDef '[ "root_id","seq","kind","flag","priority","payload" ] '[ "root_id","seq" ] '[  ]-  TTabDefSch ( "test_pgs" ->> "nullable_uq_row" ) = 'TabDef '[ "id","code","suffix","name","note" ] '[ "id" ] '[ '[ "code","suffix" ] ]-  TTabDefSch ( "test_pgs" ->> "root" ) = 'TabDef '[ "id","code","grp","name","created_at","dim_a_id","dim_b_id" ] '[ "id" ] '[ '[ "code","grp" ] ]+  TTabDefSch ( "test_pgs" ->> "ext_arr_converts" ) = 'TabDef '[ "cbytea","ccitext","ccolor","cjsonb","cuuid" ] '[  ] '[  ]+  TTabDefSch ( "test_pgs" ->> "ext_converts" ) = 'TabDef '[ "cbytea","ccitext","ccolor","cjson","cjsonb","cuuid" ] '[  ] '[  ]+  TTabDefSch ( "test_pgs" ->> "leaf" ) = 'TabDef '[ "root_id","seq","leaf_no","category","created_at","value" ] '[ "root_id","seq","leaf_no" ] '[  ]+  TTabDefSch ( "test_pgs" ->> "mid1" ) = 'TabDef '[ "id","flag","payload","pos","root_id","sort_key" ] '[ "id" ] '[  ]+  TTabDefSch ( "test_pgs" ->> "mid2" ) = 'TabDef '[ "root_id","seq","flag","kind","payload","priority" ] '[ "root_id","seq" ] '[  ]+  TTabDefSch ( "test_pgs" ->> "nullable_uq_row" ) = 'TabDef '[ "id","code","name","note","suffix" ] '[ "id" ] '[ '[ "code","suffix" ] ]+  TTabDefSch ( "test_pgs" ->> "root" ) = 'TabDef '[ "id","code","created_at","dim_a_id","dim_b_id","grp","name" ] '[ "id" ] '[ '[ "code","grp" ] ]   TTabDefSch ( "test_pgs" ->> "tag_link" ) = 'TabDef '[ "owner_id","tag","note" ] '[ "owner_id","tag" ] '[  ]   TTabDefSch name = TE.TypeError (TE.Text "In schema " TE.:<>: TE.ShowType Sch TE.:$$: TE.Text "table " TE.:<>: TE.ShowType name TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""@@ -189,7 +189,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: id, grp, i2, i4, i8, n, f4, f8."+    TE.:$$: TE.Text "  Fields: id, f4, f8, grp, i2, i4, i8, n."     TE.:$$: TE.Text "  Foreign key constraints: ."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."@@ -205,7 +205,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: id, root_id, dates_nullable, jsons."+    TE.:$$: TE.Text "  Fields: id, dates_nullable, jsons, root_id."     TE.:$$: TE.Text "  Foreign key constraints: arrays_root_fk."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."@@ -220,7 +220,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: cboolean, cint4, cfloat8, ctimestamptz, ctext."+    TE.:$$: TE.Text "  Fields: cboolean, cfloat8, cint4, ctext, ctimestamptz."     TE.:$$: TE.Text "  Foreign key constraints: ."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."@@ -238,7 +238,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: cboolean, cint4, cfloat8, cdate, ctime, ctimestamp, ctimestamptz, ctext."+    TE.:$$: TE.Text "  Fields: cboolean, cdate, cfloat8, cint4, ctext, ctime, ctimestamp, ctimestamptz."     TE.:$$: TE.Text "  Foreign key constraints: ."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."@@ -269,7 +269,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: ccitext, cbytea, cjsonb, cuuid, ccolor."+    TE.:$$: TE.Text "  Fields: cbytea, ccitext, ccolor, cjsonb, cuuid."     TE.:$$: TE.Text "  Foreign key constraints: ."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."@@ -285,7 +285,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: ccitext, cbytea, cjsonb, cjson, cuuid, ccolor."+    TE.:$$: TE.Text "  Fields: cbytea, ccitext, ccolor, cjson, cjsonb, cuuid."     TE.:$$: TE.Text "  Foreign key constraints: ."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."@@ -304,7 +304,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: root_id, seq, leaf_no, value, category, created_at."+    TE.:$$: TE.Text "  Fields: root_id, seq, leaf_no, category, created_at, value."     TE.:$$: TE.Text "  Foreign key constraints: leaf_mid2_fk."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."@@ -322,7 +322,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: id, root_id, pos, flag, sort_key, payload."+    TE.:$$: TE.Text "  Fields: id, flag, payload, pos, root_id, sort_key."     TE.:$$: TE.Text "  Foreign key constraints: mid1_root_fk."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."@@ -343,7 +343,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: root_id, seq, kind, flag, priority, payload."+    TE.:$$: TE.Text "  Fields: root_id, seq, flag, kind, payload, priority."     TE.:$$: TE.Text "  Foreign key constraints: mid2_root_fk, leaf_mid2_fk."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."@@ -358,7 +358,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: id, code, suffix, name, note."+    TE.:$$: TE.Text "  Fields: id, code, name, note, suffix."     TE.:$$: TE.Text "  Foreign key constraints: ."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."@@ -385,7 +385,7 @@     TE.:$$: TE.Text "name " TE.:<>: TE.ShowType f TE.:<>: TE.Text " is not defined."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Valid values are:"-    TE.:$$: TE.Text "  Fields: id, code, grp, name, created_at, dim_a_id, dim_b_id."+    TE.:$$: TE.Text "  Fields: id, code, created_at, dim_a_id, dim_b_id, grp, name."     TE.:$$: TE.Text "  Foreign key constraints: root_dim_a_fk, root_dim_b_fk, arrays_root_fk, mid1_root_fk, mid2_root_fk."     TE.:$$: TE.Text ""     TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."