Changed description
from
You can learn it in the <https://odr.github.io/pg-schema 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.
to 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.
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.