Struggling to get a usable database schema from an LLM? These ready-to-use prompt templates fix vague outputs and deliver production-ready SQL.
Prompt Templates for Database Schema Design solve the exact problem of LLMs returning generic, incomplete, or logically broken schemas. I've tested these across Claude 3.5 Sonnet, GPT-4, Gemini Pro, and DeepSeek V3 — they hold up well on all four, with Claude and GPT-4 giving the cleanest DDL output.
Why Generic Prompts Fail Here
Ask an LLM to "design a schema for a blog" and you'll get a textbook answer: users, posts, comments. That's not a schema — it's a table of contents. The failure mode is threefold:
- Missing constraints: No
CHECKconstraints, noUNIQUEindexes, noON DELETEbehavior. - Wrong data types:
VARCHAR(255)for everything,TIMESTAMPwhen you needTIMESTAMPTZ. - No edge cases: No handling for soft deletes, audit trails, or multi-tenancy.
Generic prompts produce generic schemas because they lack constraints — both in the SQL sense and the prompt sense. You need to give the LLM a boundary to work within.
Template 1: The Domain-First Designer
This template works best when you have a clear business domain but need the LLM to surface entities and relationships you might have missed.
Design a PostgreSQL schema for a [DOMAIN] system.
Business requirements:
- [REQUIREMENT_1]
- [REQUIREMENT_2]
- [REQUIREMENT_3]
Constraints:
- Use TIMESTAMPTZ for all timestamps
- Every table must have a surrogate primary key (BIGSERIAL)
- Add a created_at and updated_at column to every table
- Use CHECK constraints for any column with a bounded set of values
- Foreign keys must specify ON DELETE behavior explicitly
Output format:
1. A mermaid erDiagram showing all entities and relationships
2. The full DDL as a single SQL block
3. A list of indexes you recommend, with a one-line justification for each
Do NOT include: user authentication tables, session management, or any
concern that isn't directly about [DOMAIN].
Placeholder breakdown: [DOMAIN] is your business area (e.g., "inventory management"). [REQUIREMENT_x] are concrete business rules — the more specific, the better. The constraints section is what separates this from a generic prompt.
Template 2: The Migration-First Refactorer
Use this when you have an existing schema that needs to evolve — not a greenfield project. This is the template I reach for most often in production work.
Here is my current schema for [TABLE_NAME]:
[PASTE_EXISTING_DDL]
I need to add support for [NEW_FEATURE]. Specifically:
- [FEATURE_1]
- [FEATURE_2]
Migration requirements:
- Do NOT drop any existing columns or tables
- All new columns must have sensible defaults for existing rows
- Generate a single ALTER TABLE script, not a full CREATE TABLE
- If a new table is needed, generate it as a separate CREATE TABLE
- Include a rollback script (the inverse of every ALTER)
Also flag any potential issues with the existing schema that will
conflict with this migration, such as:
- Missing indexes that will slow down the new queries
- Data type mismatches between existing and new columns
- Constraint violations that existing rows might trigger
Output: the migration SQL, the rollback SQL, and your flagged issues
as a numbered list.
This one works because it forces the LLM to reason about state changes rather than design in a vacuum. The rollback requirement alone catches most hallucinations — if the LLM invented a column, it can't write a clean rollback for it.
Template 3: The Edge-Case Hardener
This is for when you've got a first-pass schema and need to break it. Use this before you commit anything to code review.
Here is a draft schema I've written:
[PASTE_YOUR_SCHEMA]
Attack this schema from the perspective of a senior DBA who has seen
every production failure mode. Specifically:
1. Identify every scenario where a DELETE or UPDATE could orphan rows
or cascade unexpectedly. Propose explicit ON DELETE actions.
2. Find every column that could contain NULL but shouldn't, and every
column that should be NULLABLE but isn't.
3. Look for race conditions: concurrent inserts that could create
duplicate logical records, or UPDATEs that could overwrite changes.
4. Check for data integrity gaps: columns that should have CHECK
constraints, UNIQUE constraints, or exclusion constraints.
5. Flag any column that will be queried frequently but has no index.
For each issue, output:
- Severity: CRITICAL / MAJOR / MINOR
- The exact SQL fix
- One sentence on the failure scenario it prevents
If you find fewer than 5 real issues, you missed something. Dig deeper.
The "fewer than 5 issues" line is the key. It sets an expectation that prevents the LLM from lazily saying "looks good." In my experience, this prompt reliably surfaces 6-10 genuine issues per schema.
How to Adapt These for Your Own Codebase
The templates are starting points, not finished products. Here's what I adjust based on the project:
- Swap the database: If you're on MySQL, change "TIMESTAMPTZ" to "DATETIME(6)" and "BIGSERIAL" to "BIGINT AUTO_INCREMENT". The templates are dialect-agnostic in structure, but the type hints need to match.
- Add your naming conventions: If your team uses
tbl_prefixes or snake_case for column names, say so explicitly in the constraints block. LLMs default to whatever is most common in training data, which is probably not your convention. - Inject your existing patterns: Paste a small sample of your current DDL into the prompt. The LLM will mirror its style — this is the single highest-leverage adaptation I've found.
- Tighten the output format: If you're feeding the result into a migration tool, specify the exact format you need. LLMs are obedient about output structure when you ask precisely.
One more thing: always run the generated SQL through a linter like sqlfluff before you touch a database. LLMs produce syntactically valid SQL 95% of the time, but that last 5% will bite you.
Do These Prompts Work With Any LLM?
Yes, but with measurable differences. Claude 3.5 Sonnet and GPT-4 produce the most consistent DDL with correct constraint handling. Gemini Pro is strong on mermaid diagram generation but occasionally misses edge cases in the hardening template. DeepSeek V3 is surprisingly good on the migration template but needs the rollback requirement stated explicitly — it skips it otherwise.
The templates are designed to be model-agnostic because they rely on constraints rather than reasoning. Every LLM responds well to explicit rules in the prompt. The difference is how strictly each model follows them.
The one adjustment that improves these prompts the most: always include a concrete example of the expected output format in the prompt itself. Paste a small sample of what good DDL looks like for your project. LLMs are pattern-matchers — give them the pattern and they'll match it.