DBML to ER Diagram: A Practical Guide

DBML, the Database Markup Language, is the text format used by dbdiagram.io. A .dbml file describes tables, columns, keys, indexes, and relationships in a syntax closer to a schema than to SQL. Teams keep it in git next to their migrations and generate diagrams from it. Knowing what each construct means in relational terms is the difference between a diagram that explains the schema and one that merely redraws it.

What DBML is, and what it is not

DBML is not a database dialect and not an ORM schema. It has no type system of its own: the type after a column name is a free-form string such as integer, varchar(255), or timestamptz, and nothing validates it. It is a design and documentation format, which is why it can describe a schema that has not been implemented yet, and why a DBML file can be exported to different SQL dialects with different results.

Three top-level blocks carry structural meaning: Table, Ref, and enum. Indexes and notes live inside a Table block; TableGroup, Project, and Note blocks only organize and document the file. Comments start with //.

Tables and columns

A table is a block with one column per line, written as name type [settings]. Settings go in one pair of brackets, separated by commas: [pk], [unique], [not null], [increment], [default: value], and [note: 'text']. A default can be a literal or a SQL expression wrapped in backticks.

Table names can be schema-qualified with a dot (Table sales.orders); names with spaces or reserved words are double-quoted. A single-column primary key uses [pk]; a composite key belongs in an indexes block as (order_id, line_no) [pk]. Declaring [pk] on two columns creates two single-column keys, which the database will reject.

A complete five-table example

An order schema, with one enum, four relationships, and a natural key on products:

enum order_status {
  pending
  paid
  shipped
  cancelled
}

Table users {
  id         integer      [pk, increment]
  email      varchar(320) [not null, unique]
  full_name  varchar(120) [not null]
  created_at timestamp    [not null]
}

Table categories {
  id   integer     [pk, increment]
  name varchar(80) [not null, unique]
}

Table products {
  id          integer       [pk, increment]
  category_id integer       [not null]
  sku         varchar(40)   [not null, unique]
  name        varchar(200)  [not null]
  price       decimal(12,2) [not null, default: 0]
}

Table orders {
  id        integer      [pk, increment]
  user_id   integer      [not null]
  status    order_status [not null, default: 'pending']
  placed_at timestamp    [not null]
}

Table order_items {
  id         integer       [pk, increment]
  order_id   integer       [not null]
  product_id integer       [not null]
  quantity   integer       [not null, default: 1]
  unit_price decimal(12,2) [not null]
}

Ref: orders.user_id > users.id
Ref: products.category_id > categories.id
Ref: order_items.order_id > orders.id [delete: cascade]
Ref: order_items.product_id > products.id

Read as a diagram, users is an entity with four attributes: id is a generated key, email is unique and required. The Ref lines become edges, and the delete: cascade setting belongs to the edge between orders and order_items.

Enums and indexes

An enum is a named set of values declared outside any table; a column typed order_status can only hold one of them. In a diagram it is not an entity: it has no rows and no key, so it is drawn as the column's type name or a note. In a relational schema it becomes a database enum, a check constraint, or a lookup table when users edit the values.

An indexes block lists composite indexes and options, such as (user_id, placed_at) [name: 'idx_orders_user_placed'] or email [unique], with [type: hash] where supported. Only unique indexes change the logical model: a unique index is a candidate key and, over foreign-key columns, makes the relationship one-to-one. Ordinary indexes are access paths and create no entities or edges.

Refs and cardinality

A Ref connects a column to a column, and the operator states cardinality in the direction written. > means many-to-one: orders.user_id > users.id reads as many orders for one user. < is the same relationship from the other end, - is one-to-one, and <> is many-to-many.

A Ref can be attached inline as [ref: > users.id], declared on one line, or named in a long form: Ref fk_orders_users: orders.user_id > users.id. The long form accepts referential actions in brackets: delete: cascade, restrict, set null, set default, or no action, and the same for update.

Direction is the usual source of confusion. users.id > orders.user_id says many users belong to one order, the opposite of the intended schema. The operator describes the left side.

Many-to-many: implicit and explicit

DBML lets you write a many-to-many relationship directly with <>. That is a drawing convenience. A relational database cannot store a many-to-many relationship in two tables, so the schema needs a join table with one foreign key to each side. The explicit form is longer but it is the one that maps to a real database, and it is the only form that has a place to put data about the relationship itself.

// Shorthand: a line in the diagram, no table in the database
Ref: books.id <> authors.id

// Explicit join table: what a relational schema stores
Table book_authors {
  id        integer [pk, increment]
  book_id   integer [not null]
  author_id integer [not null]
  added_at  timestamp
}

Ref: book_authors.book_id > books.id
Ref: book_authors.author_id > authors.id

// For a pure link table, drop the id column and use a composite key:
// indexes {
//   (book_id, author_id) [pk]
// }

Use the explicit form whenever the link carries anything: a role, a timestamp, a sort order, or a count. Use it even when it does not, if the DBML file is meant to describe the database rather than sketch it. The shorthand is fine for a first whiteboard pass, but it leaves the join table unnamed and its constraints undecided.

How DBML maps to a relational schema

The mapping is direct in most places. A Table is a table. A column is a column, with [not null] becoming NOT NULL and [default] becoming a DEFAULT clause. [pk] becomes the primary key, [unique] a unique constraint, and [increment] an identity or serial column depending on the engine. A Ref becomes a foreign key. An enum becomes an enum type, a check constraint, or a lookup table.

Two gaps matter. Free-form type strings must be interpreted for a target dialect; varchar(255) is portable, integer unsigned is not. And <> has no DDL equivalent, which is why explicit join tables are safer. Otherwise the diagram and the database can be generated from the same file and stay in step.

Common mistakes

• Reversing a Ref. The operator describes the left side, so users.id > orders.user_id means many users to one order.
• Using <> and expecting a join table to appear in the database. It is a diagram shorthand; the table has to be written out.
• Putting [pk] on two columns of a join table. That declares two separate keys; a composite key belongs in an indexes block.
• Referencing a column or table that does not exist. DBML does not resolve names for you, and a typo produces a missing edge rather than an error in the diagram.
• Adding [not null] to a column in an existing table without a default. The migration fails on the first existing row.
• Treating an enum as a table. It is a value set, not an entity, and it should not get a box.

How ERDGenerator imports DBML

Paste a .dbml file and the importer parses Table and Ref blocks rather than guessing from names. Primary keys, uniqueness, nullability, defaults, increments, and notes carry onto the columns, and Ref operators become edges with their cardinality. Enum names stay as column types. The result is editable: boxes can be moved, columns added, and relationship ends changed. Explicit join tables arrive as entities, matching what the database will contain.

Frequently asked questions

Is DBML a SQL dialect? No. It has no fixed types and no engine to execute it. It is a schema description that tools translate to diagrams or to DDL for a chosen database.

How do I declare a composite primary key? Use an indexes block inside the table with the column list in parentheses, for example (order_id, line_no) [pk]. Putting [pk] on each column separately declares two single-column keys.

Do ordinary indexes belong on the diagram? No. They are physical access paths. Only unique indexes matter logically, because they can turn a many-to-one edge into a one-to-one edge.

Can a DBML file describe a database that already exists? Yes, and that is a common use: reverse the schema into DBML once, keep it in the repository, and review the diff when the schema changes.