JSON to ERD — database schema diagram

Three steps to a diagram

1. Open “How to get JSON schema”, pick your database and copy the query.
2. Run that query on your database and copy the erd_json value.
3. Paste it into the editor. The canvas lays it out automatically.

To see it working first, press load-sample. Ready-made queries cover PostgreSQL, MySQL and SQL Server, and any other database works if it produces the same shape.

What the diagram shows

• Each table is a node, titled with its schema, name and table comment.
• Each column takes one row, showing its name, type and column comment.
• An asterisk marks the column as NOT NULL.
• Default values appear on hover rather than inline, because they are often long enough to squeeze out the column name.
• A PK badge marks a primary key, FK a foreign key and UQ a single-column unique constraint.
• Edges attach to the specific columns and are labelled 1:1 or 1:N.

How 1:1 and 1:N are decided

• A foreign key covering the child’s entire primary key is read as 1:1.
• A foreign key that is itself a single-column unique is read as 1:1.
• Everything else is read as 1:N.
• One column of a composite primary key does not count as unique, so it is never read as 1:1.

Which JSON fields are required

• Only tables[].table and columns[].name are required.
• schema can be omitted.
• Common aliases are accepted, for example table_name, column_name,data_type and is_nullable: "NO".

{
  "tables": [
    {
      "schema": "public",
      "table": "users",
      "comment": null,
      "columns": [
        { "name": "id", "type": "bigint", "nullable": false,
          "is_primary_key": true, "is_unique": false, "comment": null }
      ]
    }
  ],
  "relations": [
    { "constraint_name": "orders_user_id_fkey",
      "from_schema": "public", "from_table": "orders", "from_columns": ["user_id"],
      "to_schema": "public",   "to_table": "users",    "to_columns": ["id"],
      "on_delete": "CASCADE" }
  ]
}

Which objects the queries leave out

• All three skip views, sequences, indexes and system schemas, keeping only what means something on an ERD.
• The PostgreSQL query skips individual partitions and keeps the parent, because one partitioned table can expand into dozens of children.
• The PostgreSQL query also skips foreign keys cloned onto partitions, or the same relation would be drawn N times.
• FDW foreign tables are included, but they cannot have keys, so they appear as plain column lists with no relations.

Does your schema ever leave the browser

No. Parsing and layout both happen in your browser, no request carries your schema anywhere, and saves go only to localStorage. See theprivacy policy for the details.