\r \r

DB Schema\r

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

What It Does\r

\r Takes a plain English description of your data and generates:\r

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

Usage\r

\r

From description:\r

db-schema "Users have many posts. Posts have many comments. Users can like posts."\r
```\r
\r
### With options:\r
```\r
db-schema "E-commerce with products, orders, customers" --dialect postgres --orm prisma\r
```\r
\r
### Options:\r
- `--dialect` — `postgres` (default), `mysql`, `sqlite`, `mongodb`\r
- `--orm` — `raw` (default), `prisma`, `drizzle`, `knex`, `sqlalchemy`, `typeorm`\r
- `--format` — `sql` (default), `json`, `markdown`\r
- `--diagram` — include ERD diagram: `mermaid` (default), `ascii`, `none`\r
- `--seed` — generate seed data (default: false)\r
- `--seed-count` — rows per table for seed data (default: 10)\r
\r
## Generation Rules\r
\r
### Schema Design:\r
1. **Every table gets a primary key** — `id` (BIGSERIAL for PG, AUTO_INCREMENT for MySQL, INTEGER AUTOINCREMENT for SQLite)\r
2. **Timestamps by default** — `created_at` and `updated_at` on every table\r
3. **Foreign keys with proper naming** — `table_id` references `table(id)`\r
4. **ON DELETE behavior** — CASCADE for owned relationships, SET NULL for optional\r
5. **Proper types** — use appropriate types (TEXT not VARCHAR(255) for PG, TIMESTAMPTZ not TIMESTAMP)\r
\r
### Relationship Detection:\r
\r
| English | Relationship | Implementation |\r
|---------|-------------|---------------|\r
| "has many" | One-to-Many | FK on the "many" side |\r
| "belongs to" | Many-to-One | FK on current table |\r
| "has one" | One-to-One | FK with UNIQUE constraint |\r
| "many to many" | Many-to-Many | Junction table |\r
| "can like/follow/tag" | Many-to-Many | Junction table with metadata |\r
\r
### Auto-Indexing:\r
\r
| Pattern | Index Type |\r
|---------|-----------|\r
| Foreign keys | B-tree index |\r
| Email, username | UNIQUE index |\r
| Created/updated dates | B-tree index |\r
| Status/type/role columns | B-tree index |\r
| Full-text search fields | GIN index (PG) / FULLTEXT (MySQL) |\r
| Slug/path columns | UNIQUE index |\r
| Composite lookups | Composite index |\r
\r
### Type Mapping:\r
\r
| Concept | PostgreSQL | MySQL | SQLite |\r
|---------|-----------|-------|--------|\r
| ID | BIGSERIAL | BIGINT AUTO_INCREMENT | INTEGER |\r
| Short text | VARCHAR(N) | VARCHAR(N) | TEXT |\r
| Long text | TEXT | TEXT | TEXT |\r
| Money | NUMERIC(12,2) | DECIMAL(12,2) | REAL |\r
| Boolean | BOOLEAN | TINYINT(1) | INTEGER |\r
| Timestamp | TIMESTAMPTZ | DATETIME | TEXT |\r
| JSON | JSONB | JSON | TEXT |\r
| UUID | UUID | CHAR(36) | TEXT |\r
| Enum | Custom TYPE | ENUM(...) | TEXT CHECK |\r
\r
### Output (SQL):\r
```sql\r
-- Generated by db-schema\r
-- Description: E-commerce with products, orders, customers\r
\r
CREATE TABLE customers (\r
    id BIGSERIAL PRIMARY KEY,\r
    email VARCHAR(255) NOT NULL UNIQUE,\r
    name VARCHAR(255) NOT NULL,\r
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\r
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\r
);\r
\r
CREATE TABLE products (\r
    id BIGSERIAL PRIMARY KEY,\r
    name VARCHAR(255) NOT NULL,\r
    description TEXT,\r
    price NUMERIC(12,2) NOT NULL CHECK (price >= 0),\r
    stock INTEGER NOT NULL DEFAULT 0,\r
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\r
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\r
);\r
\r
CREATE TABLE orders (\r
    id BIGSERIAL PRIMARY KEY,\r
    customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,\r
    status VARCHAR(50) NOT NULL DEFAULT 'pending',\r
    total NUMERIC(12,2) NOT NULL DEFAULT 0,\r
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\r
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\r
);\r
\r
CREATE TABLE order_items (\r
    id BIGSERIAL PRIMARY KEY,\r
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,\r
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,\r
    quantity INTEGER NOT NULL CHECK (quantity > 0),\r
    unit_price NUMERIC(12,2) NOT NULL\r
);\r
\r
CREATE INDEX idx_orders_customer_id ON orders(customer_id);\r
CREATE INDEX idx_orders_status ON orders(status);\r
CREATE INDEX idx_order_items_order_id ON order_items(order_id);\r
CREATE INDEX idx_order_items_product_id ON order_items(product_id);\r
```\r
\r
### ERD Output (Mermaid):\r
```\r
erDiagram\r
    CUSTOMERS ||--o{ ORDERS : places\r
    ORDERS ||--|{ ORDER_ITEMS : contains\r
    PRODUCTS ||--o{ ORDER_ITEMS : "included in"\r
```\r