TIL: about Entity Relationship Schemas
Tags: database, elixir, todayilearned, postgres
Published: 2022-08-14 22:01:00


   When you design a relational database, you code two entities,  migration files and data mapping files. You may also want to use [ER diagrams](https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model) for modelling a database as you may find it easier or faster to reason or want to communicate with them. Even if experienced teams might not integrate such diagrams in the design pipeline, almost every framework lets you take an ERD snapshot of your data structure. We gathered notes on a few tools to interact with diagrams as code and show where they can be used.


## Diagram-as-code, or not

You have three main formats to draw ER diagrams: DOT, UML and DBML. While  DOT and PLANTUML formats are multi-purpose drawing utilities,  [DBML](https://www.dbml.org/home/) is an easy-to-read DSL focused on tables, to design the data structure in code and draw an ER diagram from it. An example of DBML code where a table is built, with foreign keys, composite indexes and relations 1-n (`<`). 


![contracts dbml](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xso2wx42avokdofb0cgx.png)

Diagram-as-code with DBML is interesting because you can convert this code into a raw SQL migration script. This means you can run a migration with the framework or run it directly against the database with the command line.

However, this is a "first" design tool only. You can only run the first full migration once.
Furthermore, it remains to produce the code base in the framework for it to communicate with the database.

Conversely, we can convert a raw SQL migration script into DBML and produce an ERD in code from it, but for the same reasons, this is only meaningful if you have the **first** full migration script.

In conclusion, DBML is not a CD tool but rather a first useful "diagram-as-code" design tool.

Most frameworks have libraries to generate an ERD snapshot from the code base, whether from the migration files or the model/schema. Almost all of them will produce DOT files. The [Graphviz](https://graphviz.org/) utility will generate an SVG or PNG. For example, NodeJS/Sequelize with `sequelize-erd`, or Ruby/Rails with `rails-erd`, or Elixir/Ecto with  `ecto_erd`. The code base remains the source of truth. An example is further below.

Finally, an ERD snapshot can also be generated directly from the database,  with in-build tool or database tools like **dbeaver** or utilities like [**Planter**](https://github.com/achiku/planter) and [pg-to-dbml](https://github.com/polarislabs/pg-to-dbml). More on it below.    


## Set up and process

We will use Elixir/Ecto and PostgreSQL (MySQL works as well). One reason to use Ecto is that the code used for the migration is independent of the code used by the mapper to talk and manage the database.

We start in the "standard" way: code the [playground example](#playground-example) with Elixir/Ecto, run the migration and produce tests. From this point, you can generate PUML or DOT or DBML code and visualise all of them!

Then, we will convert what can be the starting point - the DBML code - back to SQL, and run it with the database command line client `psql` (resp. `mysql`) and test the database.

### Tools

- the [DBML CLI](https://www.dbml.org/cli/#syntax-manual) converts between DBML and SQL. This can also be done programmatically with the node package `@dbml/core`.

- the online free service [dbdiagram.io](https://dbdiagram.io/home) makes this DBML interesting: you type the code in DBML and a synced diagram is drawn for you. You can generate a migration script (SQL code) for the selected database - Postgres or MySQL - and you can print/save the ERD.

> VSCode has an extension [vscode-dbml](https://marketplace.visualstudio.com/items?itemName=matt-meyers.vscode-dbml&ssr=false#overview) based on @dbml/core to convert back and forth between SQL and DBML: in the command palette, (`>DBML: To SQL`) and vice-versa (`>DBML:From SQL`). 

- the node package **pg-to-dbml** connects to your Postgres database and produces DBML code from it. Just pass the database url (the db is set by a flag):

```bash
pg-to-dbml --c=postgresql://USER:PASSWORD@HOST:PORT -o=pathToOutput --db=DB_NAME
```

- You also have [PlantUML](https://plantuml.com/en/guide). This is a powerful drawing tool. The Golang program [Planter](https://github.com/achiku/planter) generates diagrams in PUML format from PostgreSQL tables. Just clone the repo, install it (Go needed of course), and pass your database url to it:

```bash
./planter "postgres://postgres@localhost/my_app_repo?sslmode=disable" -o my-db.uml
```

The "plantuml" code for ERD is easily understandable:

![contract uml](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/618n3shcca6lo49hecd5.png)

To visualise the diagram, you can use directly `plantuml` or use an IDE extension (such as PlantUML in VScode, press `opt-D`) or use the official [on-line drawer](http://www.plantuml.com/plantuml/uml/SyfFKj2rKt3CoKnELR1Io4ZDoSa70000).


![plantuml](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/63gc1qwufxdhanxokho8.png)
   
- the build-in [ER tool for PostgreSQL](https://www.pgadmin.org/docs/pgadmin4/latest/erd_tool.html) included in pgAdmin. You can design a database with it, and conversely, generate an [ERD snapshot](#pgadmin-snap) from an existing database. You can learn how to use it with [this video](https://www.youtube.com/watch?v=o23LBJ-9jsU).

> MySQL has also it's build-in [Workbench](https://dev.mysql.com/doc/workbench/en/wb-creating-eer-diagram.html). Also to mention the free [Sequel Ace](https://sequel-ace.com/ref/) for MySQL/MariaDB.

- the excellent database tool [dbeaver](https://github.com/dbeaver/dbeaver):

![dbeaver erd](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h9htiftjfc43zrerddg8.png)

- projects tailored for the framework such as [ecto_erd](https://github.com/fuelen/ecto_erd). It is Ecto's swiss army knife for ERD. You can generate the default DOT format or PUML code or DBML code. More on this below.

The diagram below shows where all these tools can be used.

![all processes](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xjklxvp1ewd3mb1ef0g6.png)

### Playground example

We take four models/schemas talking about movies, actors, producers and characters. The database should capture the following relations:
- a Movie has one Producer, and a Producer can have many movies,
- a Character has one Movie,
- an Actor has a unique Character per Movie through a unique Contract for a certain amount,
- a Character can have many Actors per Movie through contracts,


![dbml erd](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f89k71g5iin9x2ucgkwn.png)


## From Ecto code base to DBML and back

We set up the PostgreSQL database, code the migrations with Ecto, run them and save the logs to get the SQL script. We need to clean the logs to extract only the SQL commands. You can do this programmatically with for example a small [cleaning task](#mix-task-helper). You then use the [DBML cli](https://www.dbml.org/cli/#installation) to convert the SQL script into [DBML code](#dbml-code): 

```bash
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
```

We use the handy [dbdiagram.io](https://dbdiagram.io/home) to visualise the diagram.

Since we have the code base written in Elixir/Ecto, you can also use the mix task `ecto_erd`. The command below will generate a DOT file that you convert into PNG with the Graphviz utility. Note that you can also display a DOT file [into the browser](https://dreampuf.github.io/GraphvizOnline/#digraph%20G%20%7B%0A%0A%20%20subgraph%20cluster_0%20%7B%0A%20%20%20%20style%3Dfilled%3B%0A%20%20%20%20color%3Dlightgrey%3B%0A%20%20%20%20node%20%5Bstyle%3Dfilled%2Ccolor%3Dwhite%5D%3B%0A%20%20%20%20a0%20-%3E%20a1%20-%3E%20a2%20-%3E%20a3%3B%0A%20%20%20%20label%20%3D%20%22process%20%231%22%3B%0A%20%20%7D%0A%0A%20%20subgraph%20cluster_1%20%7B%0A%20%20%20%20node%20%5Bstyle%3Dfilled%5D%3B%0A%20%20%20%20b0%20-%3E%20b1%20-%3E%20b2%20-%3E%20b3%3B%0A%20%20%20%20label%20%3D%20%22process%20%232%22%3B%0A%20%20%20%20color%3Dblue%0A%20%20%7D%0A%20%20start%20-%3E%20a0%3B%0A%20%20start%20-%3E%20b0%3B%0A%20%20a1%20-%3E%20b3%3B%0A%20%20b2%20-%3E%20a3%3B%0A%20%20a3%20-%3E%20a0%3B%0A%20%20a3%20-%3E%20end%3B%0A%20%20b3%20-%3E%20end%3B%0A%0A%20%20start%20%5Bshape%3DMdiamond%5D%3B%0A%20%20end%20%5Bshape%3DMsquare%5D%3B%0A%7D)

```bash
mix ecto.gen.erd --output-path=my_app-db.dot

dot -Tsvg my_app-db.dot -o my_app-db.svg
```



![ecto_erd](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kqu9tcr8147du94a9c3g.png)


## From DBML code to the migration

Suppose you designed the database in DBML. You can convert this code to SQL with the @dbml/cli:

```bash
dbml2sql --postgres migration-ecto.dbml -o new-migration.sql
```

There are different ways to run a migration transaction from this generated SQL script; it can be done via the framework or directly in the database. 

- run a migration transaction with Ecto. The migration file will use the `up` function from `Ecto.Migration`. It is a bit tedious: you wrap each raw SQL command with an `execute("CREATE...")`. You could do this with a custom mix task. You can place the generated file into a subdirectory, say "priv/repo/migrations/from-diagram" for example and use the `--migrations-path` flag to run it.

- a better way is to run the migration-ecto.sql script directly with the command line client `psql`. Prior to this, we need to wrap the whole script with a `BEGIN; ...;COMMIT;` for a transaction. You can use for example a simple custom mix task `mix sql.prepare migration-ecto.sql`

```bash
mix ecto.drop && mix ecto.create
mix sql.prepare migration-ecto.sql
psql -d my_app_repo -f migration-ecto.sql

mix test
```

- lastly, you can also use the **pgAdmin** interface. This is a "manual" operation and thus can't be used when you release your Docker image. Once you run the script, you can generate an ERS snapshot. Conversely, you can draw an ER diagram with it and generate the migration script. To use it, navigate to your database folder in the integrated browser, then the public folder, select "Tools" (top menu), then "Query Tool", then select the SQL script (first icon as shown below).

![menu select SQL file](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7g64uczjjikt6n07g1l8.png)

Then execute the loaded script with F5 (or the triangle), 
![menu execute SQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jo85masr5tml3qo85srg.png)

and finally, check the tables (maybe Refresh with a right click). If you go back to the database folder, click right and select "Generate ERD" as below

![menu generate ER](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1s2stpopkbschm155o5s.png)

and you have an ER snapshot of the database:

### pgadmin snap
![ER shanpshot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/46mfe24vtunjg65vxhnl.png)

## Conclusion

ERD is a valuable tool to communicate and visualizing the database.  
"Diagram-as-code" with DBML is interesting especially with the handy dbdiagram.io but not a CD tool, rather limited to being a first design tool in dev mode; you can't manage subsequent migrations  and the code will not be kept synced automatically with the project.

You can generate an ERD snapshot from:
- the (Postgres) database with **Planter** to get a nice PUML,
- the code base with **ecto_erd** to get a nice DOT.

You note that the DOT file generated by `ecto_erd` is richer than the DBML code since it adds the associations created by unique composite indexes as we see immediately below when comparing both diagrams. 

![Ecto_erd](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g75bxnh2jn4zcqdfcqn7.png)


![DBML](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i18oupvxcmvvmcw293pv.png) 

## Code

### DBML code

```
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"
```


### Mix task helper

```elixir
# 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 Migration, Schema, Seeds, Tests

:fast_forward: check [github repo](https://github.com/ndrean/my_app_ecto_erd) for Ecto code.