DB Schema Generator

---

name: db-schema description: Generate database schemas, migrations, and ERD diagrams from plain English descriptions — supports PostgreSQL, MySQL, SQLite, and MongoDB with proper indexes and constraints. metadata: {"openclaw":{"emoji":"🗄️","os":["darwin","linux","win32"]}}

---

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"

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)