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 +11/−0
- README.md +109/−9
- pg-schema.cabal +20/−1
- src/PgSchema/Generation.hs +13/−6
- src/PgSchema/Schema.hs +2/−2
- test-pgs/Sch.hs +22/−22
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."