Перейти к содержанию

Быстрый старт

В этом разделе мы покажем короткий пример работы с pg-schema. Создадим схему базы данных, подключим её к проекту, добавим и прочитаем данные. Мы будем работать с данными сразу в двух связанных реляционных таблицах: с проектами и их задачами. Проекты вместе с задачами мы запишем одной операцией и одним запросом прочитаем их обратно, а правильность имён таблиц, полей и связей между ними проверит компилятор ещё до запуска программы.

Подготовка

Для работы понадобятся склонированный репозиторий pg-schema, запущенный PostgreSQL с правом создавать базы и GHC с cabal (требования к версиям — во введении). Все команды выполняются из корня репозитория, где лежат cabal.project и Makefile. Генератор схемы и приложение с примерами берут строку соединения с базой из переменной окружения PG_CONN. Учебная база называется tutorial, поэтому для локального PostgreSQL достаточно такой строки:

export PG_CONN='dbname=tutorial'

Если вашему серверу нужны хост, пользователь или пароль, допишите их в ту же строку, например dbname=tutorial host=localhost user=postgres. Эту же переменную используют и команды make, о которых пойдёт речь ниже.

Теперь создадим базу и учебную схему tut:

make tut-db-init

Эта команда создаёт базу tutorial, если её ещё нет, и выполняет скрипт tutorial/sql/00-create-schema.sql. Скрипт начинается с удаления схемы tut, так что его можно запускать сколько угодно раз и каждый раз получать чистую базу. Убедиться, что таблицы users, projects, tasks и task_events появились, можно так:

psql "$PG_CONN" -c "\dt tut.*"

Первая сборка проекта с нуля занимает заметное время, потому что cabal собирает все зависимости. Её удобно запустить заранее, в соседнем терминале, и продолжить чтение: cabal build tutorial:tutorial.

Генерация схемы

Чтобы компилятор мог проверять наши запросы, ему нужно знать, как устроена база. Это описание создаёт генератор, и запускать его нужно после изменений в структуре базы данных. Генератор — небольшая программа tutorial/app/generator/Main.hs: она вызывает updateSchemaFile, читает метаданные PostgreSQL и записывает их в модуль tutorial/app/app/Sch.hs:

cabal run tutorial:generator

Файл tutorial/app/app/Sch.hs уже лежит в репозитории, и генератор перезаписывает его, только если описание базы изменилось. Если же забыть запустить генератор после изменения структуры, компилятор будет проверять запросы по устаревшему описанию: новые колонки и таблицы окажутся для него неизвестными, а запросы к удалённым или переименованным колонкам скомпилируются и упадут уже при выполнении. В Sch.hs на уровне типов описаны таблицы схемы tut, их поля и связи. Подробно мы разберём его содержимое в главе «Рабочая модель pg-schema», а пока достаточно знать, что этот модуль импортируется в код примеров.

Теперь можно запустить сами примеры:

cabal run tutorial:tutorial

Программа выполнит примеры всех глав по порядку, и примеры этой главы идут первыми. Перед ними программа сама пересоздаёт схему tut, чтобы вывод не зависел от предыдущих запусков, поэтому её можно запускать сколько угодно раз. make tut-db-init при этом всё равно нужен: он создаёт саму базу, а генератору схема нужна раньше, чем запустятся примеры. Генерацию схемы и запуск примеров можно выполнить и одной командой make tut-run: она заодно выполняет tut-db-init и сохраняет вывод в tutorial/out/tutorial.txt. Дальше мы по шагам разберём, что делают примеры этой главы и что они выводят.

Описание структур данных приложения

Код примеров этой главы лежит в tutorial/app/app/Tut/Wow.hs. Проект собирается в режиме GHC2024, где уже включены TypeApplications и ScopedTypeVariables, а в начале модуля включены ещё несколько расширений:

{-# LANGUAGE BlockArguments #-}
{-# LANGUAGE DataKinds #-}
{-# LANGUAGE DuplicateRecordFields #-}
{-# LANGUAGE OverloadedStrings #-}

BlockArguments позволяет писать запросы в виде qRoot do …, а DuplicateRecordFields нужно потому, что поле title будет и у проекта, и у задачи. Все функции pg-schema для работы с данными собраны в модуле PgSchema.DML, а сгенерированное описание базы импортируется из Sch:

import Data.Int (Int32, Int64)
import Data.Text (Text)
import Data.Text qualified as T
import Database.PostgreSQL.Simple (Connection)
import GHC.Generics (Generic)
import PgSchema.DML
import Sch

pg-schema работает поверх postgresql-simple, и каждая операция получает обычное соединение Connection. Все примеры ниже выполняются в IO внутри функции wow conn, а само соединение conn создаёт main приложения по строке из PG_CONN.

Любая операция в pg-schema начинается с аннотации Ann. Она сообщает, с какой таблицей мы работаем и как сопоставлять имена в Haskell с именами в базе. Чтобы не повторять её каждый раз, заведём синоним:

type TutAnn tab = 'Ann RenamerId Sch 3 ("tut" ->> tab)

Апостроф в 'Ann нужен потому, что аннотация — это значение на уровне типов (для этого и включено DataKinds). Sch — сгенерированная схема, а ->> соединяет имя схемы базы с именем таблицы. Число 3 ограничивает глубину вложенных связей: оно нужно в основном для того, чтобы типы со взаимными или циклическими связями не разворачивались бесконечно, и в нашем случае его хватает с запасом. RenamerId означает, что имена никак не преобразуются: поля наших типов будут называться точно так же, как колонки в базе. Позже мы увидим, как с помощью ренеймера писать имена в привычном для Haskell стиле camelCase.

Сами данные описываются обычными record-типами, для которых достаточно вывести Generic:

data Task = Task
  { seq      :: Int32
  , title    :: Text
  , priority :: Int32
  } deriving (Show, Generic)

data Project = Project
  { owner_id              :: Int64
  , title                 :: Text
  , status                :: PGEnum Sch ("tut" ->> "project_status")
  , tasks_project_id_fkey :: [Task]
  } deriving (Show, Generic)

Почти все поля здесь соответствуют колонкам таблиц projects и tasks. Исключение — поле tasks_project_id_fkey. Так PostgreSQL по умолчанию назвал внешний ключ tasks.project_id → projects.id (имя складывается из таблицы, колонки и суффикса _fkey). Встретив это имя, pg-schema находит связь в описании схемы и видит, что ключ ведёт из tasks в projects. Значит, для проекта в поле лежат не значения колонки, а список дочерних строк из tasks. Колонки payload и watchers таблицы tasks мы в тип не включили: в запросе участвуют только те колонки, которые есть в типе. Тип поля status тоже взят из схемы: PGEnum Sch ("tut" ->> "project_status") — это тип, который генератор создал по enum project_status из базы, и его значения называются Project_status_draft, Project_status_active и Project_status_archived. Поля id и seq в типах этой главы совпадают по имени с функциями из Prelude, но это не мешает: сами поля мы нигде не вызываем, pg-schema работает с ними через Generic.

Вставка данных

У каждого проекта должен быть владелец: колонка projects.owner_id обязательна и ссылается на таблицу users. Поэтому сначала добавим одного пользователя. Нам хватит типа с единственным полем name, а чтобы узнать, какой id база присвоила новому пользователю, опишем ещё и тип с этим полем:

newtype Owner = Owner { name :: Text } deriving (Show, Generic)

newtype UserKey = UserKey { id :: Int64 } deriving (Show, Generic)

([UserKey ownerId], sqlOwner) <- insertSch (TutAnn "users") conn [Owner "Alice"]
putStrLn $ T.unpack sqlOwner
print ownerId

insert into tut.users(name) values (?) returning id
1
SQL, отформатированный для чтения 
insert into tut.users(name)
values (?)
returning id;

Обратите внимание, что TutAnn "users" — это тип, а передаётся он как обычный первый аргумент. Функции pg-schema объявлены с видимым аргументом типа (для этого и нужен GHC 9.10 или новее), поэтому аннотацию можно писать прямо в вызове, без @.

insertSch вставляет строки в одну таблицу. Как и у других функций pg-schema, состав запроса определяют типы: колонки для insert берутся из типа входных данных (Owner), а колонки для returning — из типа результата (UserKey). Поэтому запрос получился именно таким: insert into tut.users(name) values (?) returning id. Мы вставили одного пользователя и сразу разобрали результат по образцу [UserKey ownerId].

Теперь у нас есть ownerId, и можно вставить два проекта вместе с их задачами. Для этого достаточно одного вызова insertJSON:

let
  projects =
    [ Project ownerId "Website" Project_status_active
      [ Task 1 "Design" 10
      , Task 2 "Backend" 30
      , Task 3 "Nice to have" 100 ]
    , Project ownerId "Mobile app" Project_status_draft
      [ Task 1 "Research" 20 ] ]
(keys :: [ProjectKey], sqlIns) <- insertJSON (TutAnn "projects") conn projects
mapM_ print keys

insertJSON возвращает данные вставленных строк, и какие именно колонки вернуть, определяет тип результата. Нам интересны ключи проектов и их задач, поэтому опишем их отдельными типами:

data TaskKey = TaskKey
  { project_id :: Int64
  , seq        :: Int32
  } deriving (Show, Generic)

data ProjectKey = ProjectKey
  { id                    :: Int64
  , tasks_project_id_fkey :: [TaskKey]
  } deriving (Show, Generic)

Пример печатает ключи вставленных строк:

ProjectKey {id = 1, tasks_project_id_fkey = [TaskKey {project_id = 1, seq = 1},TaskKey {project_id = 1, seq = 2},TaskKey {project_id = 1, seq = 3}]}
ProjectKey {id = 2, tasks_project_id_fkey = [TaskKey {project_id = 2, seq = 1}]}

Строки вставляются в две таблицы, но количество запросов к базе от этого не зависит. По типу данных insertJSON генерирует временную функцию на plpgsql, которая умеет вставить проекты вместе с задачами. Сначала он создаёт эту функцию в базе (create function pg_temp.__ins …), затем вызывает её (select pg_temp.__ins(?)), передав все данные одним параметром типа jsonb, и после этого удаляет. Все три команды выполняются в одной транзакции. Текст функции insertJSON возвращает вторым элементом пары (sqlIns), и заглянуть в него можно здесь:

Текст функции, которую создаёт insertJSON 
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[];
  project_id1 int8; 
  seq1 int4; 
begin
  arr_0:= '{}';
  for row_0 in select * from jsonb_array_elements(data_0)
  loop
    insert into tut.projects(owner_id, title, status)
      values ((row_0.value->>'owner_id')::int8, (row_0.value->>'title')::text, (row_0.value->>'status')::tut.project_status)
      returning id into id0;
      found_0 := found;
    data_1 := row_0.value->'tasks_project_id_fkey';
    arr_1:= '{}';
    for row_1 in select * from jsonb_array_elements(data_1)
    loop
      insert into tut.tasks(project_id, seq, title, priority)
        values (id0, (row_1.value->>'seq')::int4, (row_1.value->>'title')::text, (row_1.value->>'priority')::int4)
        returning project_id, seq into project_id1, seq1;
          found_1 := found;
      arr_1 := array_append(arr_1, jsonb_build_object('project_id', project_id1, 'seq', seq1));
    end loop;
    arr_0 := array_append(arr_0, jsonb_build_object('id', id0, 'tasks_project_id_fkey', to_jsonb(arr_1)));
  end loop;
  return to_jsonb(arr_0);
end; 
$$ language plpgsql;

Заметьте также, что project_id для задач мы нигде не указывали: функция берёт его из только что вставленного проекта.

Чтение данных

Теперь прочитаем данные обратно. Запрос опишем отдельно, чтобы в следующем разделе посмотреть на его SQL, не выполняя его. Тип QueryParamAnn в сигнатуре означает «параметры запроса, который начинается с базовой таблицы аннотации», здесь — с projects. Сам запрос при этом затрагивает несколько таблиц: через qPath он доходит и до задач.

activeProjects :: QueryParamAnn (TutAnn "projects")
activeProjects = qRoot do
  qWhere $ "status" =? Project_status_active
  qPath "tasks_project_id_fkey" do
    qWhere $ "priority" <? (50 :: Int32)
    qOrderBy [ascf "seq"]

qRoot задаёт условия для корневой таблицы projects: нам нужны только активные проекты. Внутри qPath условия относятся уже к ветке задач: мы берём задачи с приоритетом меньше 50 и упорядочиваем их по seq. Операторы =? и <? означают сравнения «равно» и «меньше» с параметром запроса, а ascf — сортировку по полю по возрастанию. Тип литерала 50 указан явно, потому что числовой литерал может быть любого числового типа, а у значения Project_status_active тип только один.

Имя в qPath — то же имя связи, что и у поля tasks_project_id_fkey в типе Project. Опечатку в этом имени поймает компилятор, но сами условия ветки применяются к полю результата:

Условия без ветки в результате

Если поля tasks_project_id_fkey в типе результата нет, задачи не читаются, и условия из qPath молча не попадут в запрос. Ошибки компиляции в этом случае не будет.

Выполним запрос и прочитаем результат в тот же тип Project, которым только что вставляли данные:

(rows :: [Project], (sql, params)) <- selectSch (TutAnn "projects") conn activeProjects
mapM_ print rows
putStrLn $ T.unpack sql
print params

Project {owner_id = 1, title = "Website", status = Project_status_active, tasks_project_id_fkey = [Task {seq = 1, title = "Design", priority = 10},Task {seq = 2, title = "Backend", priority = 30}]}
select t0.owner_id "owner_id",t0.title "title",t0.status "status",array_to_json(array(select jsonb_build_object('seq',t1.seq,'title',t1.title,'priority',t1.priority) from tut.tasks t1  where t1.project_id=t0.id and t1.priority < ? order by t1.seq Asc nulls last)) "tasks_project_id_fkey" from tut.projects t0  where t0.status = ?
[SomeToField 50,SomeToField Project_status_active]
SQL, отформатированный для чтения 
select t0.owner_id "owner_id",
       t0.title "title",
       t0.status "status",
       array_to_json(array(
           select jsonb_build_object('seq',t1.seq,'title',t1.title,'priority',t1.priority)
           from tut.tasks t1
           where t1.project_id=t0.id and t1.priority < ?
           order by t1.seq Asc nulls last)) "tasks_project_id_fkey"
from tut.projects t0
where t0.status = ?;

Проект «Mobile app» в результат не попал, потому что он в статусе draft, а у проекта «Website» нет задачи «Nice to have», потому что её приоритет равен 100. Кроме строк selectSch вернул текст запроса и его параметры. Параметры идут в том порядке, в котором знаки ? встречаются в тексте запроса, поэтому условие на задачи оказалось первым. По тексту видно, что проекты вместе с задачами прочитаны одним SQL-запросом: задачи каждого проекта собраны в JSON-массив прямо внутри него.

SQL без выполнения

Иногда запрос нужно не выполнить, а только посмотреть, какой SQL из него получится: например, в REPL, при отладке или в тесте, которому не нужна база. Для этого есть selectText. Он принимает ту же аннотацию, тип результата и описание запроса, что и selectSch, но к базе не обращается, а сразу возвращает текст запроса и его параметры:

let (sql, params) = selectText (TutAnn "projects") @Project activeProjects
putStrLn $ T.unpack sql
print params

select t0.owner_id "owner_id",t0.title "title",t0.status "status",array_to_json(array(select jsonb_build_object('seq',t1.seq,'title',t1.title,'priority',t1.priority) from tut.tasks t1  where t1.project_id=t0.id and t1.priority < ? order by t1.seq Asc nulls last)) "tasks_project_id_fkey" from tut.projects t0  where t0.status = ?
[SomeToField 50,SomeToField Project_status_active]
SQL, отформатированный для чтения 
select t0.owner_id "owner_id",
       t0.title "title",
       t0.status "status",
       array_to_json(array(
           select jsonb_build_object('seq',t1.seq,'title',t1.title,'priority',t1.priority)
           from tut.tasks t1
           where t1.project_id=t0.id and t1.priority < ?
           order by t1.seq Asc nulls last)) "tasks_project_id_fkey"
from tut.projects t0
where t0.status = ?;

Это тот же самый запрос, который selectSch выполнил в предыдущем разделе. Тип результата здесь указан явно (@Project), потому что строки мы не получаем и вывести его компилятору не из чего.

🤔 Что будет, если…

Посмотрим, что произойдёт, если ошибиться в имени. Допустим, в типе Task мы опечатались и назвали поле titel вместо title. Программа не скомпилируется, а компилятор сообщит, в какой таблице он искал это имя и какие имена в ней есть:

• In schema Sch
  for table "tut" ->> "tasks"
  name "titel" is not defined.

  Valid values are:
    Fields: project_id, seq, title, priority, 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.

Сам тип Task ничего не знает о базе. Проверка происходит там, где тип используется вместе с аннотацией, поэтому компилятор укажет на каждый такой вызов: insertJSON, selectSch и selectText.

Так же проверяются и имена в условиях запроса. Если в activeProjects написать "priorty" вместо "priority", сообщение будет почти таким же:

• In schema Sch
  for table "tut" ->> "tasks"
  name "priorty" is not defined.

  Valid values are:
    Fields: project_id, seq, title, priority, 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.

Обратите внимание, что имя ищется в таблице tasks, хотя запрос строится от projects. Внутри qPath "tasks_project_id_fkey" компилятор уже знает, что мы перешли по связи к задачам, и проверяет условия по их полям. В обычном SQL такая опечатка обнаружилась бы только при выполнении запроса.

Резюме

  • Генератор по живой базе создаёт модуль Sch.hs с описанием таблиц, колонок и связей; его нужно перегенерировать после изменений схемы.
  • Данные описываются обычными рекордами, а поле с именем внешнего ключа связывает проект с его задачами.
  • insertJSON вставляет проекты вместе с задачами одной операцией, а selectSch читает их одним запросом.
  • qRoot задаёт условия для корневой таблицы, qPath — для ветки.
  • selectText показывает SQL запроса, не обращаясь к базе.
  • Опечатки в именах обнаруживает компилятор.

Что дальше

Мы увидели весь путь от пустой базы до чтения связанных данных, но многое приняли на веру. В главе «Рабочая модель pg-schema» мы разберём, что именно генерируется в Sch.hs, как устроены аннотация Ann и ренеймеры и какие ещё ошибки ловит компилятор. Вставке, изменению и удалению данных посвящены главы «Изменение данных в одной таблице» и «Изменение связанных данных», а запросам с условиями — глава «Select».