- 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:
- Every table gets a primary key —
id (BIGSERIAL for PG, AUTO_INCREMENT for MySQL, INTEGER AUTOINCREMENT for SQLite) - Timestamps by default —
created_at and updated_at on every table - Foreign keys with proper naming —
table_id references table(id) - ON DELETE behavior — CASCADE for owned relationships, SET NULL for optional
- 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"
