An ERD diagram creator that stores the schema as text

An erd diagram creator that belongs in a repo stores the picture as text. You name the entities, mark the keys, and write the crow's foot as a line you can diff, instead of dragging tables around until the arrows look tidy.

The picture is an entity-relationship diagram. The file is Mermaid. I use it when the review question is "what is stored, and how many." I don't use it when the question is "which method calls which," and I don't use it as a drawing of the server room. Commercial pages will promise you a creator. The useful part is still the cardinality. Get that wrong and a pretty layout just publishes the mistake.

The ER diagram syntax page is the head term for the symbols. I'm not going to restate every pair. I'll use two, on a schema small enough to read out loud, and I'll point at the mistakes that still render.

Tables as text you can diff

erDiagram is the keyword. An entity is a name and an optional block of attributes. A relationship is one line: the two names, the foot, a colon, and a verb. That line is what I want in the pull request. USER ||--o{ TICKET : opens is a claim a reviewer can quote. A screenshot of boxes is a mood.

Entity names in this style are single tokens. I use uppercase because the samples do and because a ticket called Support Ticket would be two tokens and a parse error. The id is the name. There is no separate label, so a rename has to touch the relationship lines too. I rename carefully, in one commit, and I don't "clean up" names in a drive-by.

I don't list every column. Keys, plus the attribute that makes the link obvious, are enough. A twenty-column dump is a table definition you already have in a migration. Pasting it into the diagram makes the feet too small to see, which is how a wrong crow survives review. The reviewer was busy reading column types.

Live preview matters here because a reversed foot looks plausible. You will not feel the parse fail. The picture will calmly say an order has many customers. I open the editor and read the foot before I push. No signup, and no draw tool required for this job.

How to read the foot

The symbols sit against the entity they describe. || is exactly one. |o on the left and o| on the right are zero or one. o{ on the right is zero or more. |{ on the right is one or more. The many side is the child. The foreign key lives on the child.

USER ||--o{ TICKET : opens says exactly one user, and zero or more tickets. A user may have opened nothing yet. A ticket does not exist without a user, in this product. If guest tickets are real, the mark against USER has to allow zero, and the line changes. I say that sentence while I type. The parser will accept the other sentence too.

The verb after the colon is required. USER ||--o{ TICKET opens fails. The colon is not a style choice. I have dropped it while typing fast, blamed the preview, and then found the colon in the sample I thought I had copied. Copy the colon.

A direct many-to-many is }o--o{ when the relationship has no columns of its own. The moment it grows a role, a quantity, or a date, I stop using the direct line and I add a join entity. The direct line hides that row. Hidden rows are how people write a migration that doesn't match the picture.

Put the key on the row that owns it

PK marks the primary key. FK marks the foreign key. A column can be both, on a join table, written PK, FK. I don't mark PK on a column that's only a reference, and I don't mark FK on the parent just because the parent is popular.

The owner of a row is the entity whose primary key identifies that row. TICKET.id identifies a ticket. TICKET.userId points at a user. If I put userId on USER, I've said the person row stores one ticket id, and the crow's foot is now lying to match me. I've done a softer version of this, marking both sides FK because the relationship "goes both ways." It doesn't. One row holds the pointer.

Unique keys that aren't the primary key can be marked UK. I use that for an email when the email is the lookup and the id is still the key other tables store. I don't mark every indexed column. An index is a performance choice. The diagram is a model. Those drift apart on purpose, and the diagram should stay the model.

Nullable foreign keys are the zero-or-one mark, not a comment in the margin. If agentId can be null, the agent side of the line is |o, not ||. A bar that says exactly one, next to a column the migration allows to be null, is the bug I most want a review to catch. The picture is easier to read than the migration, which is why the picture has to be right.

A small support schema

A user opens tickets. A ticket has comments. An agent may handle a ticket, and might handle none. That's the whole product slice. I'm leaving out attachments, tags, and the audit log. They can have a diagram when they have a question.

erDiagram
    USER ||--o{ TICKET : opens
    TICKET ||--|{ COMMENT : has
    AGENT |o--o{ TICKET : handles
    USER {
        string id PK
        string email UK
    }
    TICKET {
        string id PK
        string userId FK
        string agentId FK
        string status
    }
    COMMENT {
        string id PK
        string ticketId FK
        string body
    }
    AGENT {
        string id PK
        string name
    }

Read the three lines before the attributes. A user has zero or more tickets, and each ticket has exactly one user. userId sits on TICKET. A ticket has one or more comments. I used |{ because this product doesn't save a ticket with an empty thread. The first comment is the report. If your product creates the ticket first and the comment later, that mark is a lie, and you want o{ instead. Don't copy my bar.

An agent handles zero or more tickets, and a ticket has zero or one agent. |o on the agent side is the unassigned queue. I used to draw || there because every ticket "eventually" has an agent. Eventually is not a constraint. The row allows null today. The diagram should allow null today.

status is a column, not an entity. I don't make STATUS a box unless statuses live in their own table with attributes you will review. A lookup table with a name and a sort order might deserve a box. An enum in the application doesn't. I've drawn the enum as a box, then someone added a foreign key in a migration to match the picture, and we grew a table we didn't need.

body on the comment is the one non-key I kept, so a reader can see that the comment is the text and not another pointer. I didn't include created-at. Timestamps are real and they're not why this diagram exists.

A join when the relationship grows a column

Users and teams are many-to-many, and the relationship has a role. "Member" versus "owner" is a column. A direct USER }o--o{ TEAM line would erase it. MEMBERSHIP is an entity because it has a row.

erDiagram
    USER ||--o{ MEMBERSHIP : has
    TEAM ||--o{ MEMBERSHIP : has
    USER {
        string id PK
        string email UK
    }
    TEAM {
        string id PK
        string name
    }
    MEMBERSHIP {
        string userId PK, FK
        string teamId PK, FK
        string role
    }

Both lines are one-to-many pointing at the join. One user, many memberships. One team, many memberships. Together they are the many-to-many. There is no USER-to-TEAM line, because there is no foreign key that skips the join. If I leave the direct line in "to make it obvious," I have two stories, and they will disagree the first time a membership has a role the direct line can't hold.

userId and teamId are each PK, FK. The pair is the primary key, which also says a user joins a team once. If your product allows two roles for the same pair, the primary key is wrong and you need a surrogate id, with a unique constraint only if you still mean once. I don't hide that choice in a comment. I change the key lines.

role is a string here. If roles are a table with permissions hanging off them, ROLE becomes an entity and MEMBERSHIP points at it. That's a third diagram, or an extension of this one when the permissions review actually happens. I don't add it speculatively. Speculative boxes become tables.

The notes on cardinality go further on the feet than I will here. Use them when you're choosing between o{ and |{ and you want more than one example. The choice is still a product fact, not a symbol fact.

SQL lives next to the diagram

I write the diagram from the migration, or I write the migration from the diagram, and I try not to let them diverge for more than one pull request. The guide to an ER diagram from SQL is how I translate a CREATE TABLE by hand: table name to entity, primary key to PK, references to FK on the child, and nullability to the foot.

Pasting a SQL dump into this editor does not import a schema for you. There is no magic loader I'm going to pretend exists. If a tool you already use claims to import SQL, check that version and read what it drops. I do the translation in the open, because the interesting bugs are the ones an importer would guess: a column named team_id with no foreign key, a join table with a payload, a nullable column drawn as exactly one.

I keep the SQL and the Mermaid in the same change. If the migration adds agent_id and the diagram still shows exactly one agent as if the column were old and required, the review should fail. The diagram is not decoration under the migration. It's the reading of the migration for people who won't open it.

Generated drafts are fine when the nouns are known. The AI ER generator will sketch entities from a description. I name the entities and the "one user, many tickets, agent optional" facts in the prompt, and I still read every foot. Five free AI uses is a lifetime total. Spend one on a blank schema, not on "make it more detailed" after the feet are already right. Detail is how STATUS becomes a box.

Dragging still wins for a whiteboard hour

A drag-and-drop ER tool is the right afternoon when you're standing with a product manager and you don't know the nouns yet. Moving a box is faster than renaming an entity while someone talks. I won't inventory another product's importer or its notation pack. Check the version in front of you if you need that list.

I transcribe into Mermaid when the nouns settle, and I throw away the whiteboard positions. Positions weren't the model. The feet were. If the team refuses to read text, the diagram still belongs in the repo for the people who will change the migration, and the screenshot can go in the meeting notes. Two artifacts is fine if one of them is clearly the scratch copy.

An EER diagram, with subtypes and unions, is a heavier notation than this file. Mermaid's ER diagram will not grow those symbols because you wanted a complete academic figure. The EER diagram generator notes are the place to take that request. Don't fake a subtype with a crow's foot and a prayer. Say the subtype in prose, or draw it as a class diagram if the subtype is actually inheritance in code.

That's the split I keep. Rows and keys stay here. Types and methods go to a class diagram creator. If I feel the urge to put +send() on TICKET, I'm in the wrong file, and the method will drift from the class the first week someone refactors it.

Cardinality mistakes that survive review

The parser catches a missing colon. It does not catch a false model. These are the ones I write down so I stop repeating them.

1. Drawing the crow on the parent. || is the one side. o{ is the many side. The foreign key belongs on the many side. If you reverse them, the preview still looks like a database.
2. Using || for a nullable foreign key because the column is "usually" filled. Usually is o| or |o. Required is ||.
3. Keeping a direct many-to-many and a join entity for the same fact. Pick the join when the relationship has columns. Delete the shortcut.
4. Marking every column PK or FK until the marks mean nothing. One owner, real references only.
5. Listing every column from the migration. The feet shrink, the review skips them, and the one wrong mark was the reason you drew the picture.

A sixth I still commit if I'm tired: naming the entity ORDER and then spending the review on the fact that ORDER is a loud word, instead of on the foot. Pick a name and move on. PURCHASE is fine if ORDER bothers the SQL people. The foot doesn't care what you shouted.

The ecommerce ER template is a larger schema than these two, with products and payments already drawn. Use it when your slice is a shop and you want to delete down to the tables you actually have. Don't extend it with speculative warehouses. And once you want to see the ER beside other diagram types, the diagram examples are a gallery, not a second syntax guide.

Open the editor, write one relationship line, and say the foot out loud before you add attributes. If the sentence and the symbols disagree, the attributes will not save you. Fix the line.