SQL to Mermaid ER Diagram

Example

Three tables with a required foreign key, an optional one and a one-to-one table that shares its primary key:

Input

CREATE TABLE authors (
  id serial PRIMARY KEY,
  email text NOT NULL UNIQUE
);
CREATE TABLE books (
  id serial PRIMARY KEY,
  author_id int NOT NULL REFERENCES authors (id),
  editor_id int REFERENCES authors (id),
  isbn varchar(13) UNIQUE
);
CREATE TABLE book_details (
  book_id int PRIMARY KEY REFERENCES books (id),
  pages int
);

Output

erDiagram
    authors ||--o{ books : "author_id"
    authors |o--o{ books : "editor_id"
    books ||--o| book_details : "book_id"
    authors {
        serial id PK
        text email UK
    }
    books {
        serial id PK
        int author_id FK
        int editor_id FK
        varchar(13) isbn UK
    }
    book_details {
        int book_id PK, FK
        int pages
    }

books.editor_id can be NULL, so a book has zero or one editor (|o), and book_details.book_id is the whole primary key, so each book has at most one details row (o|).

Reading crow’s foot notation

Marker	Meaning	Comes from
`\|\|`	Exactly one	Parent of a NOT NULL foreign key
`\|o`	Zero or one	Parent of a nullable foreign key
`o{`	Zero or more	Child side of an ordinary foreign key
`o\|`	Zero or one	Child side of a unique foreign key or one that is the whole primary key

With Dashed non-identifying on, relationships whose foreign key isn’t part of the child’s primary key are drawn dashed, the classic distinction between identifying and non-identifying relationships. For the full ER syntax, see the Mermaid ER diagram guide.

Get the schema out of your database

PostgreSQL

pg_dump --schema-only --no-owner --no-privileges mydb > schema.sql

MySQL / MariaDB

mysqldump --no-data --skip-comments mydb > schema.sql

SQLite

sqlite3 app.db .schema > schema.sql

Then upload or drop schema.sql into the input. To tidy the SQL itself, use the SQL formatter.

Frequently asked questions

Is my schema uploaded?

No. The SQL is parsed and the diagram is drawn in your browser; nothing is sent to a server. That makes it safe to paste a production schema dump.

Which SQL dialects are supported?

CREATE TABLE statements from PostgreSQL, MySQL and MariaDB, SQLite and SQL Server, including pg_dump and mysqldump output. Quoted names ("x", `x` and [x]), schema-qualified names, inline REFERENCES, table-level FOREIGN KEY and PRIMARY KEY constraints, ALTER TABLE … ADD CONSTRAINT and CREATE UNIQUE INDEX are all read. Other statements, like INSERT, views and functions, are skipped.

How is the cardinality decided?

From the foreign key columns. If any of them can be NULL, the parent side is zero or one (|o), otherwise exactly one (||). If the foreign key is unique, or is the whole primary key of the child table, the child side is zero or one (o|), a one-to-one relationship; otherwise zero or more (o{).

Why are some column types changed?

Mermaid types must be a single word without spaces or quotes, so multi-word types are shortened: character varying(255) becomes varchar(255), timestamp with time zone becomes timestamptz and double precision becomes double. Enum value lists are dropped (enum), and other spaces become underscores, as in int(11)_unsigned.

My schema is too big to read as one diagram. What can I do?

Switch Columns to “Keys only” to keep just primary, unique and foreign keys, or “Tables only” to show the relationships alone. You can also paste only the tables for one area of the schema; foreign keys to tables that aren’t pasted still appear, as empty boxes.