Database schema diagram

A schema diagram goes further than an ER diagram: column types, defaults, indexes and constraints. DBML was designed for exactly this, and it reads like a simplified DDL.

Because the source is text, the diagram can be regenerated whenever the schema changes.

#When to reach for one

• Documenting a database people will write queries against.

• Reviewing a migration before it runs.

• Handing a schema to another team without giving them database access.

#How to draw it in Capable

#A worked example

A schema in DBML, close enough to DDL to review:

The source, which you can paste into a new diagram and edit:

Table users {
  id uuid [pk]
  email varchar [unique, not null]
  display_name varchar
  created_at timestamp [default: `now()`]

  Note: 'One row per person who can sign in'
}

Table sessions {
  id uuid [pk]
  user_id uuid [ref: > users.id]
  expires_at timestamp [not null]
  ip inet

  Indexes {
    (user_id, expires_at)
  }
}

#A few things that catch people out

• Keep the diagram next to the migrations, not in a separate space nobody visits.

• Notes on tables are worth more than notes in a wiki paragraph; they travel with the diagram.

• Generate from your real schema where you can. Hand-written schema diagrams drift.

#Related

Get the cardinality right and the rest follows.