Posted inDatabase

ERD Design with dbdiagram.io: From Concept to Auto-Generated SQL

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The Problem with “Schema as You Go”

You’ve started a new project. You open your SQL client, create a users table, add some columns, then realize you need an orders table, then a products table, then a junction table — and somewhere along the way, foreign keys are pointing in the wrong direction and column names are inconsistent across tables.

This is what I call “schema as you go” — and most developers have been there. The fix isn’t more discipline writing SQL. The fix is spending 20 minutes drawing your schema visually before writing a single line of code.

That’s exactly what dbdiagram.io is built for. It’s a browser-based ERD tool that lets you sketch your database schema using a simple markup language called DBML, then auto-generates SQL for PostgreSQL, MySQL, or SQL Server with one click.

Core Concepts: ERDs, Entities, and Relationships

An Entity-Relationship Diagram (ERD) is a visual map of your database. It shows the tables (entities), their columns (attributes), and how they connect to each other (relationships). Think of it like an architect’s blueprint — you wouldn’t build a house without one.

Entities and Attributes

An entity is essentially a table. Each entity has attributes — columns with names and data types. A User entity might have attributes like id, email, and created_at. Sketching these out before writing SQL forces you to think about what data actually needs to exist versus what you’re guessing you’ll need.

Relationships and Cardinality

Relationships describe how entities connect. The main types are:

  • One-to-One (1:1) — One user has one profile.
  • One-to-Many (1:N) — One user can place many orders.
  • Many-to-Many (M:N) — Many orders can contain many products, handled with a junction table.

Getting cardinality right at the design stage saves hours of refactoring later. Drawing it visually makes it far easier to spot mistakes before they get baked into production migrations.

The DBML Syntax

DBML (Database Markup Language) is the DSL used by dbdiagram.io. It’s clean and readable — closer to writing documentation than writing raw SQL. Here’s a minimal example:

Table users {
  id integer [primary key, increment]
  email varchar [not null, unique]
  password_hash varchar [not null]
  created_at timestamp [default: `now()`]
}

Each Table block defines a table. Column definitions follow the pattern: column_name data_type [constraints]. Relationships use a Ref: statement outside the table block:

Ref: orders.user_id > users.id  // Many-to-one

The arrow direction matters: > means many-to-one, < means one-to-many, and - means one-to-one.

Hands-on Practice: Designing an E-Commerce Schema

Go to dbdiagram.io and click Create your diagram. No account needed to start. The interface splits into two panels: a code editor on the left, a live visual diagram on the right. As you type, the diagram updates instantly.

We’ll design the schema for a simple e-commerce platform. We need users, products, orders, and a way to handle multiple products per order.

Step 1: Define Your Entities First

Before writing any DBML, list what you need to store — not how you’ll store it. For this e-commerce system:

  • Users — people who buy things
  • Products — items for sale
  • Orders — a purchase event tied to a user
  • Order Items — the junction table linking orders and products

Four entities. Now jot down the key attributes each one needs. This five-minute step alone prevents most schema mistakes.

Step 2: Write the DBML

Paste the following into the dbdiagram.io editor:

Table users {
  id integer [primary key, increment]
  email varchar [not null, unique]
  full_name varchar
  created_at timestamp [default: `now()`]
}

Table products {
  id integer [primary key, increment]
  name varchar [not null]
  description text
  price decimal(10,2) [not null]
  stock_quantity integer [default: 0]
  created_at timestamp [default: `now()`]
}

Table orders {
  id integer [primary key, increment]
  user_id integer [not null]
  status varchar [default: 'pending']  // pending, paid, shipped, delivered
  total_amount decimal(10,2)
  created_at timestamp [default: `now()`]
}

Table order_items {
  id integer [primary key, increment]
  order_id integer [not null]
  product_id integer [not null]
  quantity integer [not null]
  unit_price decimal(10,2) [not null]  // price at time of purchase
}

As you type, the diagram draws each table as a box with its columns listed. You’ll immediately spot naming inconsistencies or missing columns that are easy to miss in raw SQL files.

Step 3: Add Relationships

Now connect the tables. Add these Ref: statements below your table definitions:

Ref: orders.user_id > users.id           // Many orders belong to one user
Ref: order_items.order_id > orders.id    // Many items belong to one order
Ref: order_items.product_id > products.id // Many items reference one product

The diagram immediately draws connecting lines. You’ll see that order_items sits in the middle, bridging orders and products — exactly the many-to-many pattern it’s supposed to handle.

Notice I stored unit_price in order_items rather than just referencing products.price. That’s intentional — product prices change over time, but historical orders must reflect what the customer actually paid. These design decisions are far easier to catch on an ERD than buried in migration files six months later.

Step 4: Export to SQL

Once the diagram looks right, click the Export button in the top-right corner. Choose your target database (PostgreSQL, MySQL, or MSSQL). dbdiagram.io generates full CREATE TABLE statements including foreign key constraints:

CREATE TABLE "users" (
  "id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "email" varchar UNIQUE NOT NULL,
  "full_name" varchar,
  "created_at" timestamp DEFAULT (now())
);

CREATE TABLE "order_items" (
  "id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "order_id" integer NOT NULL,
  "product_id" integer NOT NULL,
  "quantity" integer NOT NULL,
  "unit_price" decimal(10,2) NOT NULL
);

ALTER TABLE "orders" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "order_items" ADD FOREIGN KEY ("order_id") REFERENCES "orders" ("id");
ALTER TABLE "order_items" ADD FOREIGN KEY ("product_id") REFERENCES "products" ("id");

Copy that into your database client or migration tool and you’re done. No boilerplate writing, no typos in constraint syntax.

Bonus: Import an Existing Schema

If you’ve already got a production database and want to document it visually, dump the schema first:

# PostgreSQL — schema only, no data
pg_dump --schema-only -U your_user -d your_database > schema.sql

# MySQL
mysqldump --no-data -u your_user -p your_database > schema.sql

Then in dbdiagram.io: click Import → Import from PostgreSQL (or MySQL) → paste the SQL. It reverse-engineers the DBML and draws the diagram for you. Handy for onboarding onto a legacy codebase.

Tips for Cleaner Schema Design

A few things I’ve picked up after running this workflow on several projects:

  • Name columns consistently. If you use created_at in one table, use it everywhere — not created, date_created, or creation_date in different tables.
  • Always snapshot prices and quantities. Never rely on live lookups from products for historical order data. Store unit_price and quantity at the time of transaction.
  • Add indexes on foreign keys. DBML supports indexes blocks inside table definitions. Foreign keys without indexes cause full table scans on joins, which will bite you once data grows.
  • Use TableGroup for large schemas. If you have 20+ tables, group related ones with TableGroup blocks to keep the diagram readable.
Table order_items {
  id integer [primary key, increment]
  order_id integer [not null]
  product_id integer [not null]
  quantity integer [not null]
  unit_price decimal(10,2) [not null]

  indexes {
    order_id
    product_id
  }
}

TableGroup ecommerce {
  users
  products
  orders
  order_items
}

One more workflow tip: when seeding the database with initial data from spreadsheets, I often need to convert CSV exports before importing. I use toolcraft.app/en/tools/data/csv-to-json for quick CSV-to-JSON conversion — it runs entirely in the browser so no data leaves your machine, which matters when seed data contains anything sensitive like customer records or pricing information.

Wrapping Up

Twenty minutes with dbdiagram.io before your first migration saves hours of refactoring later. The DBML syntax is quick to learn, the live diagram catches relationship errors immediately, and the SQL export means you skip all the CREATE TABLE boilerplate entirely.

The habit to build is this: whenever you’re starting a feature that touches the database, sketch the tables first. Even a rough ERD with three or four tables forces you to work through relationships, naming, and data types before you’re committed to a migration that’s painful to roll back.

Take the e-commerce example from this guide and swap in your own domain — a blog CMS, an inventory system, a SaaS subscription model. The ERD-first pattern stays the same, and your future self will thank you every time you don’t have to reverse-engineer your own schema.

Share:
Tags: