In practice¶
In the examples so far we had one database schema, all relations were declared
as foreign keys, and the schema itself never changed. A real project is more
complicated: tables are spread over several PostgreSQL schemas, not every
relation is a foreign key, and the database structure changes with every
migration. In this chapter we look at how pg-schema copes with all this and
how to make schema generation part of the development process and of CI.
The examples of this chapter are in tutorial/app/app/Tut/Practice.hs, and the
generator is in tutorial/app/generator/Main.hs. Before the examples the
program recreates the schema and adds two users, Alice (id = 1) and Bob
(id = 2).
What the generator describes¶
Which tables and relations go into Sch.hs is defined by the last argument of
updateSchemaFile, a value of type GenNames. Our generator has this one:
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")] } ] }
schemas lists the database schemas all of whose tables should be described.
If only some tables of a schema are needed, they can be listed in tables by
their full names, for example ["billing" ->> "invoices"]. The generator finds
the relations between the selected tables by their foreign keys, and
addRelations can add relations that do not exist in the database. Let us take
all of this in turn.
Several database schemas¶
Besides the schema tut, the tutorial database has a schema audit with a log
of project changes:
create schema audit;
create table audit.project_log (
id bigserial primary key,
project_id bigint not null references tut.projects(id) on delete cascade,
message text not null,
client_ip inet, -- a type with no built-in mapping (appendix "Data types")
created_at timestamptz not null default now()
);
Since both schemas are listed in GenNames, the table audit.project_log is
described in the same Sch.hs as the others. The annotation for it differs
from MyAnn of the chapter Core workflow only in the database schema in the table name, so we
define the same kind of synonym for it and, like MyAnn, pass it to operations
as the first argument:
type AuditAnn t = 'Ann MyRenamer Sch 3 ("audit" ->> t)
The foreign key audit.project_log.project_id → tut.projects.id is an ordinary
relation, and the fact that the tables are in different schemas changes
nothing. We can, for example, insert a project together with log entries in one
call of insertJSON:
type LogIn = "message" := Text
type ProjectWithLog = "ownerId" := Int64 :. "title" := Text
:. "projectLogProjectIdFkey" := [LogIn]
(keys, sqlIns) <- insertJSON (MyAnn "projects") conn $ rowsOf @ProjectWithLog
[ 1 :. "Website" :. [ "created", "owner assigned" ] ]
print (plains @("id" := Int64 :. "projectLogProjectIdFkey" := ["id" := Int64]) keys)
[1 :. [1,2]]
Formatted SQL
create or replace function pg_temp.__ins(data_0 jsonb) returns jsonb as $$
declare
found_0 boolean;
row_0 record;
arr_0 jsonb[];
id0 int8;
data_1 jsonb;
found_1 boolean;
row_1 record;
arr_1 jsonb[];
id1 int8;
begin
arr_0:= '{}';
for row_0 in select * from jsonb_array_elements(data_0)
loop
insert into tut.projects(owner_id, title)
values ((row_0.value->>'ownerId')::int8, (row_0.value->>'title')::text)
returning id into id0;
found_0 := found;
data_1 := row_0.value->'projectLogProjectIdFkey';
arr_1:= '{}';
for row_1 in select * from jsonb_array_elements(data_1)
loop
insert into audit.project_log(project_id, message)
values (id0, (row_1.value->>'message')::text)
returning id into id1;
found_1 := found;
arr_1 := array_append(arr_1, jsonb_build_object('id', id1));
end loop;
arr_0 := array_append(arr_0, jsonb_build_object('id', id0, 'projectLogProjectIdFkey', to_jsonb(arr_1)));
end loop;
return to_jsonb(arr_0);
end;
$$ language plpgsql;
And the log can be read starting from the table audit.project_log, together
with the parent project from the schema tut:
(rows, (sql, _)) <- selectSch (AuditAnn "project_log")
@("message" := Text :. "projectLogProjectIdFkey" := ("title" := Text))
conn $ qRoot do
qOrderBy [ascf "id"]
mapM_ print rows
"message" =: "created" :. "projectLogProjectIdFkey" =: ("title" =: "Website")
"message" =: "owner assigned" :. "projectLogProjectIdFkey" =: ("title" =: "Website")
Formatted SQL
select t0.message "message",
jsonb_build_object('title',t1.title) "projectLogProjectIdFkey"
from audit.project_log t0
join tut.projects t1 on t0.project_id=t1.id
order by t0.id Asc nulls last;
Relations that are not in the database¶
Sometimes tables are related by meaning, but the relation is not declared as a
foreign key. This happens with views (they have no foreign keys at all), with
partitioned tables, with old databases, or when a foreign key is left out on
purpose, for example because of the load. In the tutorial database the column
task_events.author_id is like this: it holds the id of a user, but has no
foreign key to users.
Such a relation can be described in the generator with AddRelation, as in
GenNames above: the name of the relation, the table it leads from, the table
it leads to, and the pairs of columns. After that pg-schema works with it just
as with a foreign key. In result types and in qPath its name goes through the
renamer as usual: "taskEventsAuthor". Where a relation is given by its full
name together with the schema, as in pchild and pparent from the chapter Select,
_add is written instead of the database schema: the generator puts all added
relations into this namespace. There is an example with pparent a bit below.
Let us add tasks with events. The column author_id is an ordinary column, and
we fill it with values; for one event we deliberately give the non-existent user
99:
type EventIn = "eventNo" := Int32 :. "kind" := Text :. "authorId" := Maybe Int64
type TaskIn = "seq" := Int32 :. "title" := Text
:. "taskEventsProjectIdSeqFkey" := [EventIn]
_ <- insertJSON_ (MyAnn "tasks") conn $ rowsOf @("projectId" := Int64 :. TaskIn)
[ 1 :. 1 :. "Design" :. [ 1 :. "created" :. Just 1, 2 :. "reviewed" :. Just 2 ]
, 1 :. 2 :. "Backend" :. [ 1 :. "created" :. Nothing, 2 :. "edited" :. Just 99 ] ]
Now let us read the events together with their authors:
(rows, (sql, _)) <- selectSch (MyAnn "task_events")
@( "seq" := Int32 :. "eventNo" := Int32 :. "kind" := Text
:. "taskEventsAuthor" := Maybe ("name" := Text) )
conn $ qRoot do
qOrderBy [ascf "seq", ascf "eventNo"]
mapM_ print rows
"seq" =: 1 :. ("eventNo" =: 1 :. ("kind" =: "created" :. "taskEventsAuthor" =: Just ("name" =: "Alice")))
"seq" =: 1 :. ("eventNo" =: 2 :. ("kind" =: "reviewed" :. "taskEventsAuthor" =: Just ("name" =: "Bob")))
"seq" =: 2 :. ("eventNo" =: 1 :. ("kind" =: "created" :. "taskEventsAuthor" =: Nothing))
"seq" =: 2 :. ("eventNo" =: 2 :. ("kind" =: "edited" :. "taskEventsAuthor" =: Nothing))
Formatted SQL
select t0.seq "seq",
t0.event_no "eventNo",
t0.kind "kind",
case when t1.name is null then null else jsonb_build_object('name',t1.name) end "taskEventsAuthor"
from tut.task_events t0
left outer join tut.users t1 on t0.author_id=t1.id
order by t0.seq Asc nulls last,t0.event_no Asc nulls last;
The column author_id allows NULL, so the author is Maybe, and the query
uses a left outer join. The event (2, 1) has no author because author_id
is empty, and the event (2, 2) because user 99 does not exist. The same
relation can be followed the other way, from a user to their events:
(rows, (sql, _)) <- selectSch (MyAnn "users")
@("name" := Text :. "taskEventsAuthor" := ["kind" := Text])
conn $ qRoot do
qOrderBy [ascf "name"]
mapM_ print rows
"name" =: "Alice" :. "taskEventsAuthor" =: ["kind" =: "created"]
"name" =: "Bob" :. "taskEventsAuthor" =: ["kind" =: "reviewed"]
Formatted SQL
select t0.name "name",
array_to_json(array(
select jsonb_build_object('kind',t1.kind)
from tut.task_events t1
where t1.author_id=t0.id)) "taskEventsAuthor"
from tut.users t0
order by t0.name Asc nulls last;
And this is how events are selected by author with pparent. As in the chapter Select,
the full name of the relation is convenient to write via a type synonym, only
the database schema here is _add:
type Add name = "_add" ->> name
(rows, (sql, _)) <- selectSch (MyAnn "task_events")
@("seq" := Int32 :. "eventNo" := Int32 :. "kind" := Text) conn $ qRoot do
qWhere $ pparent (Add "taskEventsAuthor") ("name" =? ("Alice" :: Text))
mapM_ print rows
"seq" =: 1 :. ("eventNo" =: 1 :. "kind" =: "created")
Formatted SQL
select t0.seq "seq",
t0.event_no "eventNo",
t0.kind "kind"
from tut.task_events t0
where exists (
select 1
from (
select *
from tut.users t0q1
where t0q1.id = t0.author_id) t0q1
where t0q1.name = ?);
The documentation of GenNames (a fragment of it is in the chapter Core workflow) says "Be
careful!" about added relations. The database knows nothing about them and does
not check them: author_id may hold the id of a non-existent user, as with
the event (2, 2), and the author is simply not found. The compiler checks that
the queries match the description of the relation, but it is up to you that the
description matches the data.
When the schema changes¶
The most useful property of pg-schema shows itself when the database
structure changes. Take, for example, a migration that renames a column and
adds a mandatory one:
-- tutorial/sql/migration-example.sql
-- An example migration for chapter 07. Not part of the tutorial schema:
-- `make tut-db-init` restores the database after the experiment.
alter table tut.tasks rename column priority to weight;
alter table tut.projects add column code text not null;
The column code is added without a default value, so this migration succeeds
only on an empty table, as in our tutorial database right after
make tut-db-init. A real database would need a default value or filling the
column, but for pg-schema this changes nothing: all that matters is that the
column is mandatory.
After the migration is applied, Sch.hs must be regenerated:
psql "$PG_CONN" -f tutorial/sql/migration-example.sql
cabal run tutorial:generator
The generator overwrites the file, and git diff shows what exactly changed in
the description of the database. The previous version is kept in Sch.hs.bak
and is overwritten on every generation, so this file should go into
.gitignore. If the description of the database has not changed, the file is
left untouched, and the output of the generator is deterministic, so there are
no spurious changes in git diff. Now it is enough to build the project, and
the compiler lists all the places the migration broke. In the tutorial project
there are 42 such errors, in five modules. Here are two of them. In the chapter Single-table DML
we inserted projects without the column code:
• We can't insert data because not all mandatory fields are present.
Table: NameNS "tut" "projects"
Missing mandatory fields: '["code"]
And in the chapter Select a condition used the column priority, which no longer
exists:
• In schema Sch
for table "tut" ->> "tasks"
name "priority" is not defined.
Valid values are:
Fields: project_id, seq, title, weight, payload, watchers.
Foreign key constraints: tasks_project_id_fkey, task_events_project_id_seq_fkey.
Your source or target type or renamer is probably invalid.
Without pg-schema each of these errors would show up only when the
corresponding query runs — in tests, if they cover it, or already in
production. Here the compiler immediately gives the full list of places to fix,
and until it is empty the program does not build.
To return the tutorial database to its initial state, run make tut-db-init
and cabal run tutorial:generator again.
This leads to an important practical rule: the generator should be a separate
program (in our project, a separate cabal executable) that does not depend on
Sch.hs. Otherwise, after a migration, until Sch.hs is regenerated, the
generator itself does not build. It must be run from the directory against
which the path to Sch.hs is given; in our case, from the root of the
repository.
Checking in CI¶
It remains to make sure that Sch.hs in the repository does not fall behind
the database schema: someone may have added a migration and forgotten to
regenerate the file. The result of updateSchemaFile helps here: it returns
True if the file had to be created or changed. Our generator accepts the flag
--check and in that case exits with an error. Here it is in full (the first
argument of updateSchemaFile turns on verbose output):
{-# 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
In CI it is enough to bring up a database (for example, a PostgreSQL service in
a container), apply all migrations to it with the same tool you use in the
project, set PG_CONN and run the generator with this flag. If Sch.hs is out
of date, the generator exits with an error, and the build fails. It also
overwrites the file, so when you run it on your own machine all that remains is
to look at git diff and commit the changes.
In the tutorial project all of this is done by a Makefile target:
make tut-schema-check
It recreates the tutorial database with the schema script and runs
cabal run tutorial:generator -- --check. In a real project, of course, the
database need not be recreated: the generator only needs a database with all
migrations applied.
You can also do it differently: not keep Sch.hs in the repository but generate
it in CI right before the build. Then no check is needed, but building without a
database becomes impossible. If a project has several databases, a separate
module is generated for each: updateSchemaFile is called several times with
different paths, module names and schema types.
Summary¶
GenNamesdefines the schemas, single tables and added relations.- Tables from different database schemas are described in one
Sch.hs, and relations between them work as usual. - A relation without a foreign key is added with
AddRelation, but the database does not check it. - After a migration
Sch.hsmust be regenerated, and the compiler shows all the places to fix. - In CI the generator with the check (
--check) does not let you forget to regenerate.
What next¶
This is the last chapter of the tutorial. The compiler messages you meet when
working with pg-schema are collected in the chapter
Compiler errors, and the rules and limitations in the
reference.