Skip to content

Core workflow

In the chapter Quick start we went all the way from an empty database to reading related data, taking a lot on faith. Now let us see how pg-schema works. Working with it has two stages. First, the generator reads the PostgreSQL metadata and builds a description of the database schema at the Haskell type level — the module Sch.hs. Then, when you work with data, the types of the input and the result define which SQL is run, and the compiler uses the schema description to check that these types match the database. In this chapter we look at both stages in detail and see which errors the compiler catches.

Generating the schema description

The schema description is created by a separate small program, the generator. Here is our generator in full:

{-# LANGUAGE OverloadedStrings #-}
module Main (main) where

import Control.Monad (when)
import PgSchema.Generation
import System.Environment (getArgs)
import System.Exit (exitFailure)
import System.IO (hPutStrLn, stderr)

genNames :: GenNames
genNames = GenNames
  { schemas = ["tut", "audit"] -- all tables of these schemas
  , tables = []                -- and single tables of other schemas (none here)
  , addRelations =             -- relations that are missing in the DB
    [ AddRelation
      { name = "task_events_author"
      , from = "tut" ->> "task_events"
      , to = "tut" ->> "users"
      , cols = [("author_id", "id")] } ] }

main :: IO ()
main = do
  check <- elem "--check" <$> getArgs
  changed <- updateSchemaFile False "tutorial/app/app/Sch.hs"
    (Left "PG_CONN") -- connection string from the PG_CONN environment variable
    "Sch" -- name of the generated module
    "Sch" -- name of the schema type
    genNames
  when (check && changed) $ do
    hPutStrLn stderr "Sch.hs did not match the database and has been regenerated"
    exitFailure

The heart of it is the call of updateSchemaFile. It connects to the database, reads the metadata and writes the description to the file if it has changed. Which tables to describe is defined by the last argument, a value of type GenNames:

data GenNames = GenNames
  { schemas :: [Text]   -- ^ generate data for all tables in these schemas
  , tables  :: [NameNS] -- ^ generate data for these tables
  , addRelations :: [AddRelation] -- ^ additional relations. Be careful!
  }

The description can include all tables of several database schemas, single tables and, if needed, relations that do not exist in the database itself. It is better, of course, to declare relations as foreign keys in the database, but that is not always possible. The generated file gets the selected tables with their columns, all foreign keys between them and all the types they use, including enums. Several schemas, added relations and how to make the generator part of your development process are covered in the chapter In practice.

In practice pg-schema has been used with schemas of 30–50 tables. A large database is better described by several Sch schemas, and one table may belong to several of them.

What is in Sch.hs

🔍 You can skip this section on first reading.

The generated module defines an empty type Sch, which serves as the type-level name of the schema, and instances of several classes for it. The main one is CSchema: it lists all the tables and all the data types they use, including our enum project_status:

module Sch where

import PgSchema.Import

data Sch

instance CSchema Sch where
  type TTabs Sch = '[ ( "audit" ->> "project_log" )
    ,( "tut" ->> "projects" ),( "tut" ->> "task_events" )
    ,( "tut" ->> "tasks" ),( "tut" ->> "users" ) ]

  type TTypes Sch = '[ ( "pg_catalog" ->> "_int8" ),( "pg_catalog" ->> "_text" )
    ,( "pg_catalog" ->> "float8" ),( "pg_catalog" ->> "inet" )
    ,( "pg_catalog" ->> "int4" ),( "pg_catalog" ->> "int8" )
    ,( "pg_catalog" ->> "jsonb" ),( "pg_catalog" ->> "text" )
    ,( "pg_catalog" ->> "timestamptz" ),( "tut" ->> "project_status" ) ]

The operator ->> joins the name of a database schema with the name of a table or a type, both at the type level and at the value level. The context of the class CSchema, as of the other classes that describe the schema, lets the description be brought from the type level to the value level with Data.Singletons.demote: that is how the library gets the names of tables and columns to generate SQL.

The description of each table — its columns, primary key and unique keys — is given by the type family TTabDef:

type family TTabDefSch (name :: NameNSK) :: TabDef' TL.Symbol where
  TTabDefSch ( "audit" ->> "project_log" ) = 'TabDef '[ "id","project_id","message","client_ip","created_at" ] '[ "id" ] '[  ]
  TTabDefSch ( "tut" ->> "projects" ) = 'TabDef '[ "id","owner_id","title","status","tags","created_at" ] '[ "id" ] '[  ]
  TTabDefSch ( "tut" ->> "task_events" ) = 'TabDef '[ "project_id","seq","event_no","kind","meta","author_id","created_at" ] '[ "project_id","seq","event_no" ] '[  ]
  TTabDefSch ( "tut" ->> "tasks" ) = 'TabDef '[ "project_id","seq","title","priority","payload","watchers" ] '[ "project_id","seq" ] '[  ]
  TTabDefSch ( "tut" ->> "users" ) = 'TabDef '[ "id","name","email","manager_id","created_at" ] '[ "id" ] '[ '[ "email" ] ]
  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 ""
    TE.:$$: TE.Text "Valid values are:"
    TE.:$$: TE.Text "  Tables: audit.project_log, tut.projects, tut.task_events, tut.tasks, tut.users."
    TE.:$$: TE.Text "")
instance (ToStar (TTabDef Sch name), ToStar name) => CTabDef Sch name where
  type TTabDef Sch name = TTabDefSch name

The most important instance for the checks is CDBFieldInfo. It describes every name that may be used with a table:

type family TDBFieldInfoSch (t :: NameNSK) (f :: TL.Symbol) :: RecFieldK NameNSK where
  TDBFieldInfoSch ( "tut" ->> "projects" ) "created_at" = 'RFPlain ('FldDef ( "pg_catalog" ->> "timestamptz" ) 'False 'True)
  TDBFieldInfoSch ( "tut" ->> "projects" ) "id" = 'RFPlain ('FldDef ( "pg_catalog" ->> "int8" ) 'False 'True)
  TDBFieldInfoSch ( "tut" ->> "projects" ) "owner_id" = 'RFPlain ('FldDef ( "pg_catalog" ->> "int8" ) 'False 'False)
  TDBFieldInfoSch ( "tut" ->> "projects" ) "status" = 'RFPlain ('FldDef ( "tut" ->> "project_status" ) 'False 'True)
  TDBFieldInfoSch ( "tut" ->> "projects" ) "tags" = 'RFPlain ('FldDef ( "pg_catalog" ->> "_text" ) 'False 'True)
  TDBFieldInfoSch ( "tut" ->> "projects" ) "title" = 'RFPlain ('FldDef ( "pg_catalog" ->> "text" ) 'False 'False)
  TDBFieldInfoSch ( "tut" ->> "projects" ) "project_log_project_id_fkey" = 'RFToHere ( "audit" ->> "project_log" )
      '[ 'Ref "project_id" ('FldDef ( "pg_catalog" ->> "int8" ) 'False 'False) "id" ('FldDef ( "pg_catalog" ->> "int8" ) 'False 'True) ]
  TDBFieldInfoSch ( "tut" ->> "projects" ) "tasks_project_id_fkey" = 'RFToHere ( "tut" ->> "tasks" )
      '[ 'Ref "project_id" ('FldDef ( "pg_catalog" ->> "int8" ) 'False 'False) "id" ('FldDef ( "pg_catalog" ->> "int8" ) 'False 'True) ]
  TDBFieldInfoSch ( "tut" ->> "projects" ) "projects_owner_id_fkey" = 'RFFromHere ( "tut" ->> "users" )
      '[ 'Ref "owner_id" ('FldDef ( "pg_catalog" ->> "int8" ) 'False 'False) "id" ('FldDef ( "pg_catalog" ->> "int8" ) 'False 'True) ]
  TDBFieldInfoSch ( "tut" ->> "projects" ) f = TE.TypeError (TE.Text "In schema " TE.:<>: TE.ShowType Sch
    TE.:$$: TE.Text "for table " TE.:<>: TE.ShowType ( "tut" ->> "projects" )
    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, owner_id, title, status, tags, created_at."
    TE.:$$: TE.Text "  Foreign key constraints: projects_owner_id_fkey, project_log_project_id_fkey, tasks_project_id_fkey."
    TE.:$$: TE.Text ""
    TE.:$$: TE.Text "Your source or target type or renamer is probably invalid."
    TE.:$$: TE.Text "")

...
  TDBFieldInfoSch ( "tut" ->> "users" ) "manager_id" = 'RFPlain ('FldDef ( "pg_catalog" ->> "int8" ) 'True 'False)
  TDBFieldInfoSch ( "tut" ->> "users" ) "users_manager_id_fkey" = 'RFSelfRef ( "tut" ->> "users" )
      '[ 'Ref "manager_id" ('FldDef ( "pg_catalog" ->> "int8" ) 'True 'False) "id" ('FldDef ( "pg_catalog" ->> "int8" ) 'False 'True) ]
...

instance (ToStar (TDBFieldInfo Sch t f), ToStar t, ToStar f) => CDBFieldInfo Sch t f where
  type TDBFieldInfo Sch t f = TDBFieldInfoSch t f

Each name here is of one of several kinds. 'RFPlain is a column of the table, 'RFFromHere is a foreign key from this table to another one (a reference to the parent), 'RFToHere is a foreign key from another table to this one (a reference to child rows), and 'RFSelfRef is a reference of a table to itself, like users.manager_id. The last equation for each table fires when the name is missing and produces a compile error with the list of valid names. Since all these are closed type families, the error messages are detailed, as we will soon see.

For each enum in the database an instance of the data family PGEnum is generated. Its constructors are made of the type name and the enum values:

data instance PGEnum Sch ( "tut" ->> "project_status" )
  = Project_status_draft | Project_status_active | Project_status_archived
  deriving (Show, Read, Ord, Eq, Generic, Bounded, Enum)

The annotation

Every data operation takes an annotation Ann as its first argument. It is a type-level value, and it is passed as a visible type argument:

data Ann = Ann
  { annRen  :: Renamer -- ^ Renamer to convert Haskell names to database names.
  , annSch  :: Type    -- ^ Schema with tables, relations and types.
  , annDepth :: Nat
  -- ^ Depth of the nested relations. It is mostly used to prevent cycles in types.
  , annTab  :: NameNSK -- ^ Name of the root table.
  }

The annotation holds four things. The renamer (annRen) translates the names we use in the program into database names. The schema (annSch) is the generated type Sch. The depth (annDepth) limits the nesting of relations in operations with trees of data and mostly protects against infinite cycles in types: 3–5 is usually enough. Finally, annTab is the base table of the operation, the root for operations with trees.

A renamer is a type-level function from a name in the program to a name in the database. Our renamer turns camelCase names into snake_case and gives a few relations handy synonyms:

data MyRenamer :: Renamer

type family MyRenamerImpl (s :: Symbol) :: Symbol where
  MyRenamerImpl "projectOwner" = "projects_owner_id_fkey"
  MyRenamerImpl "manager" = "users_manager_id_fkey"
  MyRenamerImpl "subordinates" = "users_manager_id_fkey"
  MyRenamerImpl s = CamelToSnake s

type instance ApplyRenamer MyRenamer s = MyRenamerImpl s

The library provides camelCase-to-snake_case conversion out of the box: it is the type family CamelToSnake. It leaves names already written in snake_case unchanged, so with this renamer you can write names either way.

The renamer, the schema and the depth are usually the same for the whole program, so it is convenient to define a synonym that leaves only the table name as a parameter:

type MyAnn t = 'Ann MyRenamer Sch 3 ("tut" ->> t)

First queries

Let us start simple: insert a user and read all users. We describe the data with an ordinary record type:

data User = MkUser
  { name :: Text
  , email :: Maybe Text
  } deriving (Show, Generic)

Here and below conn is an ordinary postgresql-simple connection, and all examples of this chapter are in tutorial/app/app/Tut/Basics.hs:

(cnt, tIns) <- insertSch_ (MyAnn "users") conn [MkUser "Benjy" Nothing]
(res, (tSel, selParams)) <- selectSch (MyAnn "users") @User conn qpEmpty
putStrLn $ "insert text: " <> T.unpack tIns
putStrLn $ "inserted " <> show cnt <> " rows"
putStrLn $ "select text: " <> T.unpack tSel
putStrLn $ "select params: " <> show selParams
putStrLn $ "selected rows: " <> show res

insert text: insert into tut.users(name,email) values (?,?)
inserted 1 rows
select text: select t0.name "name",t0.email "email" from tut.users t0 
select params: []
selected rows: [MkUser {name = "Benjy", email = Nothing}]
Formatted SQL 
insert into tut.users(name,email)
values (?,?);

select t0.name "name",
       t0.email "email"
from tut.users t0;

We got back the same record we inserted, and besides it the query text and its parameters (there are none here). Note that the type User does not contain all the columns of the users table: the query writes and reads only the fields of this type. The kind of operation is defined by the structure of the input and output data, and a Generic instance is all it takes. The field types and their optionality match the column types and whether the columns allow NULL.

🤔 What if we make a mistake?

Let us see how the compiler reacts to mistakes in this example. If we mistype the table name and write MyAnn "Users", the message is short:

• In schema Sch.Sch the table NameNS "tut" "Users" is not defined.

The schema name is written as Sch.Sch here because the module with the example imports Sch without any names (import Sch ()), so GHC prints the type together with its module. If we rename the field email to mail, the compiler lists all the columns and relations of the users table:

• In schema Sch.Sch
  for table "tut" ->> "users"
  name "mail" is not defined.

  Valid values are:
    Fields: id, name, email, manager_id, created_at.
    Foreign key constraints: users_manager_id_fkey, task_events_author, projects_owner_id_fkey.

  Your source or target type or renamer is probably invalid.

Whether a column is mandatory is checked too. The column name is declared NOT NULL, so name :: Maybe Text does not compile:

• You can't use Maybe for mandatory fields

  Table: NameNS "tut" "users"
  DB Field name: "name"
  DB Field type: NameNS "pg_catalog" "text"
  Haskell Field type: Maybe Text

And the column email allows NULL, so without Maybe it does not work either:

• You have to use Maybe for nullable fields

  Table: NameNS "tut" "users"
  DB Field name: "email"
  DB Field type: NameNS "pg_catalog" "text"
  Haskell Field type: Text

Finally, if the field type simply does not fit the column type, for example email :: Maybe Int, the message is less friendly:

• Could not solve: ‘CanConvert1
                      Sch.Sch
                      (NameNS "tut" "users")
                      "email"
                      (NameNS "pg_catalog" "text")
                      (TypDef "S" Nothing '[])
                      Int’
    arising from a use of ‘insertSch_’

But it can be read too: in the schema Sch, the column email of the table tut.users has the type pg_catalog.text, and there is no conversion to Int for it. Which Haskell types fit which PostgreSQL types is listed in the appendix Data types.

PgTag

Record types are convenient, but not always: defining a separate type for a couple of fields is tedious, and the same field names in different types need the DuplicateRecordFields extension. So pg-schema has a second notation, PgTag, a field with a type-level name:

newtype PgTag s t = PgTag { unPgTag :: t }
  deriving stock (Eq, Ord, Functor, Foldable, Traversable)
  deriving newtype (Semigroup, Monoid)

-- Show / Read: for s :: Symbol use "field" =: value; otherwise
-- PgTag {unPgTag = …} (see PgSchema.Types).

type s := t = PgTag s t
infixr 5 :=

(=:) :: forall b. forall a -> b -> a := b
(=:) _ = coerce
infixr 5 =:

PgTag is similar to the well-known type PgTagged, but has different instances. For convenience there is the operator (:=) at the type level and (=:) at the value level. Fields are joined with the operator (:.) from postgresql-simple, and Generic records can be mixed in. Let us insert two users and ask for their id, manager_id and the fields of the type User:

(res2 :: ["id" := Int64 :. "managerId" := Maybe Int64 :. User], tIns2) <-
  insertSch (MyAnn "users") conn
    [ MkUser "Quentin" (Just "quentin@example.com")
    , MkUser "Jason" (Just "jason@example.com") ]
(res3 :: ["name" := Text], (tSel3, selParams3)) <- selectSch (MyAnn "users") conn qpEmpty
putStrLn $ "insert text: " <> T.unpack tIns2
putStrLn $ "inserted: " <> show res2
putStrLn $ "select text: " <> T.unpack tSel3
putStrLn $ "select params: " <> show selParams3
putStrLn $ "selected rows: " <> show res3

insert text: insert into tut.users(name,email) values (?,?) returning id,manager_id,name,email
inserted: ["id" =: 2 :. ("managerId" =: Nothing :. MkUser {name = "Quentin", email = Just "quentin@example.com"}),"id" =: 3 :. ("managerId" =: Nothing :. MkUser {name = "Jason", email = Just "jason@example.com"})]
select text: select t0.name "name" from tut.users t0 
select params: []
selected rows: ["name" =: "Benjy","name" =: "Quentin","name" =: "Jason"]
Formatted SQL 
insert into tut.users(name,email)
values (?,?)
returning id,manager_id,name,email;

select t0.name "name"
from tut.users t0;

Here insertSch returns the result of the insert (insert … returning), and the set of columns in returning is defined by the result type. Note that we write managerId, not manager_id: field names go through the renamer. If we write managerId1 instead, the compiler reports that there is no such field and names it as it is after the renamer, manager_id1:

• In schema Sch.Sch
  for table "tut" ->> "users"
  name "manager_id1" is not defined.

  Valid values are:
    Fields: id, name, email, manager_id, created_at.
    Foreign key constraints: users_manager_id_fkey, task_events_author, projects_owner_id_fkey.

  Your source or target type or renamer is probably invalid.

Summary

  • The generator builds the module Sch.hs from the database: tables, columns, keys, relations and enums at the type level.
  • Every operation takes an annotation Ann: the renamer, the schema, the depth and the base table.
  • The renamer translates names in the program into database names; error messages show names that are already translated.
  • The SQL of an operation is defined by the types of the input and the result, and the compiler checks them against the schema description: names, mandatory columns and column types.
  • Data can be described by Generic records, by PgTag fields, or by a mix of both.

What next

In the chapter Single-table DML we look at all the ways to insert, update and delete rows, and in the chapter Select at all the ways to read them.