Когда вы проектируете реляционную базу данных, вы кодируете две сущности — файлы миграции и файлы отображения данных. Вы также можете захотеть использовать ER-диаграммы для моделирования базы данных, поскольку вам может показаться, что с их помощью легче или быстрее рассуждать или общаться. Даже если опытные команды могут не интегрировать такие диаграммы в конвейер проектирования, почти каждый фреймворк позволяет вам сделать снимок ERD структуры данных. Мы собрали заметки о нескольких инструментах для взаимодействия с диаграммами как с кодом и показали, где их можно использовать.
Диаграмма как код, или нет
У вас есть три основных формата для построения ER-диаграмм: DOT, UML и DBML. В то время как форматы DOT и PLANTUML являются многоцелевыми утилитами для рисования, DBML — это легко читаемый DSL, ориентированный на таблицы, для проектирования структуры данных в коде и построения ER-диаграммы на ее основе. Пример кода DBML, в котором строится таблица с внешними ключами, составными индексами и отношениями 1-n (<).
Диаграмма-как-код с DBML интересна тем, что вы можете преобразовать этот код в необработанный сценарий миграции SQL. Это означает, что вы можете запустить миграцию с помощью фреймворка или запустить ее непосредственно против базы данных с помощью командной строки.
Однако это только «первый» инструмент проектирования. Вы можете запустить первую полную миграцию только один раз.
Кроме того, остается создать кодовую базу во фреймворке, чтобы она могла взаимодействовать с базой данных.
И наоборот, мы можем преобразовать необработанный SQL-скрипт миграции в DBML и создать из него ERD в коде, но по тем же причинам это имеет смысл только в том случае, если у вас есть первый полный сценарий миграции.
В заключение, DBML — это не инструмент CD, а скорее первый полезный инструмент проектирования «диаграмма-как-код».
Большинство фреймворков имеют библиотеки для генерации ERD-снимка из кодовой базы, будь то из файлов миграции или модели/схемы. Почти все они генерируют файлы DOT. Утилита Graphviz сгенерирует SVG или PNG. Например, NodeJS/Sequelize с sequelize-erd, или Ruby/Rails с rails-erd, или Elixir/Ecto с ecto_erd. Кодовая база остается источником истины. Пример приведен ниже.
Наконец, снимок ERD также может быть сгенерирован непосредственно из базы данных, с помощью инструмента in-build или инструментов для работы с базами данных, таких как dbeaver, или утилит Planter и pg-to-dbml. Подробнее об этом ниже.
Настройка и процесс
Мы будем использовать Elixir/Ecto и PostgreSQL (MySQL тоже подходит). Одна из причин использования Ecto заключается в том, что код, используемый для миграции, не зависит от кода, используемого маппером для общения и управления базой данных.
Мы начнем «стандартным» образом: напишем пример игровой площадки на Elixir/Ecto, запустим миграцию и создадим тесты. С этого момента вы можете генерировать PUML, DOT или DBML код и визуализировать их все!
Затем мы преобразуем то, что может быть отправной точкой — код DBML — обратно в SQL, запустим его с помощью клиента командной строки базы данных psql (или mysql) и протестируем базу данных.
Инструменты
-
DBML CLI конвертирует между DBML и SQL. Это также можно сделать программно с помощью узлового пакета
@dbml/core. -
бесплатный онлайн-сервис dbdiagram.io делает DBML интересным: вы набираете код на DBML, и для вас рисуется синхронизированная диаграмма. Вы можете сгенерировать сценарий миграции (SQL-код) для выбранной базы данных — Postgres или MySQL — и можете распечатать/сохранить ERD.
VSCode имеет расширение vscode-dbml, основанное на @dbml/core, для преобразования туда и обратно между SQL и DBML: в палитре команд, (
>DBML: To SQL) и наоборот (>DBML:From SQL).
- Узловой пакет pg-to-dbml подключается к вашей базе данных Postgres и создает из нее DBML-код. Просто передайте url базы данных (db задается флагом):
pg-to-dbml --c=postgresql://USER:PASSWORD@HOST:PORT -o=pathToOutput --db=DB_NAME
- У вас также есть PlantUML. Это мощный инструмент для рисования. Программа Planter на языке Golang генерирует диаграммы в формате PUML из таблиц PostgreSQL. Просто клонируйте репозиторий, установите ее (конечно, Go needed) и передайте ей url вашей базы данных:
./planter "postgres://postgres@localhost/my_app_repo?sslmode=disable" -o my-db.uml
Код «plantuml» для ERD легко понять:
Для визуализации диаграммы вы можете использовать непосредственно plantuml или использовать расширение IDE (например, PlantUML в VScode, нажмите opt-D) или использовать официальный онлайн-рисовалку.
- встроенный ER-инструмент для PostgreSQL, включенный в pgAdmin. С его помощью можно спроектировать базу данных и, наоборот, сгенерировать снимок ERD из существующей базы данных. Как им пользоваться, вы можете узнать из этого видео.
MySQL также имеет встроенный Workbench. Также стоит упомянуть бесплатный Sequel Ace для MySQL/MariaDB.
- отличный инструмент для работы с базами данных dbeaver:
- проекты, адаптированные для фреймворка, такие как ecto_erd. Это армейский нож Ecto для ERD. Вы можете генерировать DOT-формат по умолчанию, PUML-код или DBML-код. Подробнее об этом ниже.
На диаграмме ниже показано, где можно использовать все эти инструменты.
Пример Playground
Мы берем четыре модели/схемы, говорящие о фильмах, актерах, продюсерах и персонажах. База данных должна отражать следующие отношения:
- у фильма есть один продюсер, а у продюсера может быть много фильмов,
- персонаж имеет один фильм,
- у актера есть уникальный персонаж на фильм через уникальный контракт за определенную сумму,
- персонаж может иметь много актеров на фильм через контракты,
От кодовой базы Ecto к DBML и обратно
Мы настраиваем базу данных PostgreSQL, кодируем миграции с помощью Ecto, запускаем их и сохраняем журналы, чтобы получить SQL-скрипт. Нам нужно очистить журналы, чтобы извлечь только SQL-команды. Это можно сделать программно, например, с помощью небольшой задачи очистки. Затем вы используете DBML cli для преобразования SQL-скрипта в DBML-код:
mix ecto.gen.repo -r MyApp.Repo
[... some config...]
mix ecto.create
mix ecto.gen.migration create_movies
[...code each migration...]
# ---------------------------
mix ecto.migrate --log-migrations-sql > migration-ecto.sql
mix clean.sql migration-ecto.sql
sql2dbml --postgres migration-ecto.sql -o migration-ecto.dbml
Для визуализации диаграммы мы используем удобный dbdiagram.io.
Поскольку наша кодовая база написана на Elixir/Ecto, вы также можете использовать задачу mix ecto_erd. Команда ниже создаст файл DOT, который вы конвертируете в PNG с помощью утилиты Graphviz. Обратите внимание, что вы также можете отобразить DOT-файл в браузере
mix ecto.gen.erd --output-path=my_app-db.dot
dot -Tsvg my_app-db.dot -o my_app-db.svg
От кода DBML к миграции
Предположим, вы спроектировали базу данных на языке DBML. Вы можете преобразовать этот код в SQL с помощью @dbml/cli:
dbml2sql --postgres migration-ecto.dbml -o new-migration.sql
Есть разные способы запустить транзакцию миграции из этого сгенерированного SQL-скрипта; это можно сделать через фреймворк или непосредственно в базе данных.
-
Запуск транзакции миграции с помощью Ecto. Файл миграции будет использовать функцию
upизEcto.Migration. Это немного утомительно: вы оборачиваете каждую необработанную команду SQL командойexecute("CREATE..."). Вы можете сделать это с помощью пользовательской задачи mix. Вы можете поместить сгенерированный файл в подкаталог, например, «priv/repo/migrations/from-diagram» и использовать флаг--migrations-pathдля его запуска. -
Более эффективным способом является запуск скрипта migration-ecto.sql непосредственно с помощью клиента командной строки
psql. Перед этим нам нужно обернуть весь скриптBEGIN; ...;COMMIT;для транзакции. Вы можете использовать, например, простую пользовательскую задачу mixmix sql.prepare migration-ecto.sql.
mix ecto.drop && mix ecto.create
mix sql.prepare migration-ecto.sql
psql -d my_app_repo -f migration-ecto.sql
mix test
- Наконец, вы также можете использовать интерфейс pgAdmin. Это «ручная» операция, поэтому ее нельзя использовать при выпуске образа Docker. После запуска сценария вы можете сгенерировать снимок ERS. И наоборот, с его помощью можно нарисовать ER-диаграмму и сгенерировать сценарий миграции. Чтобы воспользоваться им, перейдите в папку базы данных в интегрированном браузере, затем в общую папку, выберите «Tools» (верхнее меню), затем «Query Tool», затем выберите сценарий SQL (первый значок, как показано ниже).
Затем выполните загруженный скрипт с помощью F5 (или треугольника), 
и, наконец, проверьте таблицы (возможно, Refresh с помощью правого клика). Если вы вернетесь в папку базы данных, щелкните правой кнопкой мыши и выберите «Generate ERD», как показано ниже
и у вас будет ER-снимок базы данных:
pgadmin snap
Заключение
ERD — это ценный инструмент для общения и визуализации базы данных.
«Диаграмма как код» с DBML интересна, особенно с удобным dbdiagram.io, но это не CD инструмент, а скорее ограниченный инструмент для первого проектирования в режиме dev; вы не можете управлять последующими миграциями и код не будет автоматически синхронизирован с проектом.
Вы можете сгенерировать снимок ERD из:
- базы данных (Postgres) с помощью Planter для получения красивого PUML,
- кодовой базы с помощью ecto_erd для получения красивого DOT.
Обратите внимание, что DOT-файл, сгенерированный ecto_erd, богаче, чем DBML-код, поскольку он добавляет ассоциации, созданные уникальными составными индексами, как мы видим ниже при сравнении обеих диаграмм.
Код
Код DBML
Enum "actor_status" {
"celeb"
"blist"
}
Table "producers" {
"id" uuid [pk]
"name" varchar(255) [not null]
"status" "actor_status"
"inserted_at" timestamp(0) [not null]
"updated_at" timestamp(0) [not null]
Indexes {
name [unique, name: "producers_name_index"]
}
}
Table "actors" {
"id" uuid [pk]
"name" varchar(255) [not null]
"status" actor_status
"inserted_at" timestamp(0) [not null]
"updated_at" timestamp(0) [not null]
Indexes {
name [unique, name: "actors_name_index"]
}
}
Table "movies" {
"id" bigserial [pk]
"title" varchar(255) [not null]
"producer_id" uuid [not null]
"inserted_at" timestamp(0) [not null]
"updated_at" timestamp(0) [not null]
Indexes {
title [unique, name: "movies_title_index"]
}
}
Table "characters" {
"id" bigserial [pk]
"name" varchar(255) [not null]
"movie_id" bigint [not null]
"inserted_at" timestamp(0) [not null]
"updated_at" timestamp(0) [not null]
Indexes {
name [unique, name: "characters_name_index"]
}
}
Table "contracts" {
"id" bigserial [pk]
"salary" decimal(7, 2) [not null]
"movie_id" bigint [not null]
"actor_id" uuid [not null]
"character_id" bigint [not null]
"inserted_at" timestamp(0) [not null]
"updated_at" timestamp(0) [not null]
Indexes {
(movie_id, actor_id, character_id) [unique, name: "contract"]
}
}
Ref:"producers"."id" < "movies"."producer_id"
Ref:"movies"."id" < "characters"."movie_id"
Ref:"movies"."id" < "contracts"."movie_id"
Ref:"actors"."id" < "contracts"."actor_id"
Ref:"characters"."id" < "contracts"."character_id"
Помощник задачи смешивания
# lib/mix/tasks/sql_helper.ex
defmodule Mix.Tasks.Sql.Clean do
@shortdoc "clean the migration file to raw SQL"
require Logger
use Mix.Task
@impl Mix.Task
def run(arg) do
case arg do
[] ->
Logger.debug("Please enter a filename")
[filename] ->
with {:ok, txt} <- File.read(filename) do
txt
|> String.split("n")
|> Enum.filter(&(String.contains?(&1, "CREATE") or String.contains?(&1, "ALTER")))
|> Enum.map(&String.replace(&1, "[]", ";"))
|> Enum.filter(&(not String.contains?(&1, "execute")))
|> to_string()
|> then(fn t -> File.write(filename, t) end)
end
System.cmd("echo", ["e[32m u2714 e[0m", "File ready for conversion to dbml"], into: IO.stream())
end
end
defmodule Mix.Tasks.Sql.Prepare do
@shortdoc "prepare SQL script for psql migration"
require Logger
use Mix.Task
@impl Mix.Task
def run(arg) do
case arg do
[] ->
Logger.debug("Please enter a filename")
[filename] ->
filename
|> File.write("COMMIT;", [:append])
|> then(fn _ ->
{:ok, txt} = File.read(filename)
File.write(filename, "BEGIN;" <> txt)
end)
System.cmd("echo", ["e[32m u2714 e[0m", "Ready for transaction"], into: IO.stream())
end
end
end
Миграция Ecto, схема, семена, тесты
⏩ Проверьте github repo для кода Ecto.