DB Schema

Describe your data model in English. Get production-ready schema, migrations, and diagrams.

What It Does

Takes a plain English description of your data and generates:

• SQL schema (CREATE TABLE statements with constraints)
• Migration files (for Prisma, Drizzle, Knex, Alembic, etc.)
• Entity-Relationship diagram (Mermaid or ASCII)
• Indexes (auto-detected from common query patterns)
• Seed data (realistic sample data for development)

Usage

From description:

db-schema "Users have many posts. Posts have many comments. Users can like posts."

With options:

db-schema "E-commerce with products, orders, customers" --dialect postgres --orm prisma

Options:

• --dialect — postgres (default), mysql, sqlite, mongodb
• --orm — raw (default), prisma, drizzle, knex, sqlalchemy, typeorm
• --format — sql (default), json, markdown
• --diagram — include ERD diagram: mermaid (default), ascii, none
• --seed — generate seed data (default: false)
• --seed-count — rows per table for seed data (default: 10)

Generation Rules

Schema Design:

1. Every table gets a primary key — id (BIGSERIAL for PG, AUTO_INCREMENT for MySQL, INTEGER AUTOINCREMENT for SQLite)
2. Timestamps by default — created_at and updated_at on every table
3. Foreign keys with proper naming — table_id references table(id)
4. ON DELETE behavior — CASCADE for owned relationships, SET NULL for optional
5. Proper types — use appropriate types (TEXT not VARCHAR(255) for PG, TIMESTAMPTZ not TIMESTAMP)

Relationship Detection:

English	Relationship	Implementation
"has many"	One-to-Many	FK on the "many" side
"belongs to"	Many-to-One	FK on current table
"has one"	One-to-One	FK with UNIQUE constraint
"many to many"	Many-to-Many	Junction table
"can like/follow/tag"	Many-to-Many	Junction table with metadata

Auto-Indexing:

Pattern	Index Type
Foreign keys	B-tree index
Email, username	UNIQUE index
Created/updated dates	B-tree index
Status/type/role columns	B-tree index
Full-text search fields	GIN index (PG) / FULLTEXT (MySQL)
Slug/path columns	UNIQUE index
Composite lookups	Composite index

Type Mapping:

Concept	PostgreSQL	MySQL	SQLite
ID	BIGSERIAL	BIGINT AUTO_INCREMENT	INTEGER
Short text	VARCHAR(N)	VARCHAR(N)	TEXT
Long text	TEXT	TEXT	TEXT
Money	NUMERIC(12,2)	DECIMAL(12,2)	REAL
Boolean	BOOLEAN	TINYINT(1)	INTEGER
Timestamp	TIMESTAMPTZ	DATETIME	TEXT
JSON	JSONB	JSON	TEXT
UUID	UUID	CHAR(36)	TEXT
Enum	Custom TYPE	ENUM(...)	TEXT CHECK

Output (SQL):

-- Generated by db-schema
-- Description: E-commerce with products, orders, customers

CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(12,2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    total NUMERIC(12,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12,2) NOT NULL
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

ERD Output (Mermaid):

erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : "included in"