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 a database with tables: "orders", "order_positions", "articles".

• There are articles with ids 41 and 42.

• We have generated MySch schema with updateSchemaFile function from PgSchema.Generation module.

Then we can write a function to insert an order with order positions. And select orders with order positions and articles in one request using some conditions for orders and order positions that return in order.

data Order = Order { num :: Text, createdAt :: Day, items :: [OrdPos] } deriving Generic
data OrdPos = OrdPos { id :: Int32, num :: Int32, article :: Article, price :: Double } deriving Generic
data Article = Article { id :: Int32, name :: Text } deriving Generic
type MyAnn tabName = 'Ann 5 CamelToSnake MySch ("dbSchema" ->> tabName)
    ...
do

  void $ insertJSON_ (MyAnn "orders") conn
    [ "num" =: "num1" :. "ord__ord_pos" =:
      [ "num" =: 1 :. "articleId" =: 42 :. "price" =: 10
      , "num" =: 2 :. "articleId" =: 41 :. "price" =: 15 ] ]


  (xs :: [Order]) <- selectSch (MyAnn "orders") conn $ qRoot do
    qWhere $ "created_at" >? someDay
      &&& pchild "ord__ord_pos" defTabParam
        (pparent "ord_pos__article" $ "name" ~~? "%pencil%")
    qOrderBy [descf "created_at", descf "num"]
    qPath "ord__ord_pos" do
      qWhere $ pparent "ord_pos__article" $ "name" ~~? "%pencil%"
      qOrderBy [ascf "num"]
    qLimit 20

Module structure

• 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.

• PgSchema
  • PgSchema.DML
  • PgSchema.Generation
  • PgSchema.Import
  • Schema
    • PgSchema.Schema.Catalog
    • PgSchema.Schema.Info
  • Utils
    • PgSchema.Utils.GenDot