Designing Database Schemas & Queries with Claude Code: A Hands-On Guide (2026)

Can Claude Code actually design a DB and write queries?

Yes - and it does it well, because schema design and query writing are exactly the kind of work an agentic tool like Claude Code was built for. Claude Code is not just autocomplete-style suggestions: it can read your files and your whole repo, run commands in the terminal (psql, mongosh, run tests), read the output, and iterate to fix itself. For databases, that loop - "read the context -> generate DDL/query -> run it -> read the result -> adjust" - is where it is at its strongest.

This guide focuses on the two most common systems: PostgreSQL (relational) and MongoDB (document). The same approach works for both. If you are just getting started and are not sure what Claude Code is yet, read what Claude Code is and what you use it for first, then come back here.

One honest caveat up front: AI is fast at databases but it is not automatically safe. It can invent column names, pick the wrong data type for money, or generate a migration with no way to undo it. That is why this entire guide rests on two principles: only grant read-only access to prod and always give Claude a way to check its own work. We will go through the full loop: connect -> design the schema -> write queries -> index and performance -> migrations.

Setup: connecting Claude Code to your database

Before you ask Claude to do anything, it needs to actually "see" your data. There are three ways, from safest to most flexible:

Option 1 - MCP server (recommended, read-only)

MCP (Model Context Protocol) is the standard way Claude Code connects to outside tools, databases included. The Claude Code MCP docs give a direct example of querying data "based on our PostgreSQL database" (Claude Code MCP docs, Anthropic, 2026). The command to add an HTTP MCP server:

claude mcp add --transport http postgres-db https://your-mcp-endpoint

The critical safety detail: the reference Postgres MCP server is described as "Read-only database access with schema inspection" - meaning it reads and inspects structure only, it does not write (modelcontextprotocol/servers, 2026; this server has since moved to the servers-archived repo). That is exactly what you want when letting AI near your DB: it can read the schema to understand context, but it cannot drop your tables on its own. If you are new to MCP, see what MCP is and how to connect external tools to Claude Code.

Option 2 - psql / mongosh CLI

Simpler still: just let Claude run commands through the terminal. If you already have psql or mongosh configured, Claude can call them directly. This is flexible (it can run write commands too) but that is precisely why it is riskier - point it only at a dev/local DB, never at a production connection string with write access.

Option 3 - paste a schema file with @

When you are not ready to connect a real DB, just hand Claude your schema.sql file or a description of your tables using the @ syntax:

Read @db/schema.sql and summarize the tables, primary keys, and relationships.
Then list 3 design risks you see.

Step 1 - Design the schema with Claude Code

The most common mistake is to open Claude and immediately type "create a users table for me." Do that and you get back a generic schema the AI guessed at. The right way is workload-first: decide the type of load first, then let the AI build the tables.

Classify the workload first

Ask yourself (and tell Claude) what kind of application this is, because each type optimizes for a different data shape:

Use plan mode so Claude reads before it writes

Turn on plan mode (press Shift+Tab to switch modes) and ask Claude to read the requirements, ask about anything unclear, and only then generate the DDL. This stops it from rushing to create tables on a wrong assumption.

Prompt pattern: state invariants, not columns

Instead of listing columns, describe the invariant business rules so the AI sets the right primary key, unique constraints, and foreign keys itself:

Design a PostgreSQL schema for a small shop. Workload: OLTP.
Invariants:
- One email belongs to exactly one account (unique).
- An order must belong to an existing user (an orphan = a bug).
- Each order_items row records the price AT PURCHASE TIME, not the current price.
- Money must be exact, with no rounding error.
Ask me questions if anything is missing before writing the DDL.

Relational checklist (to review the DDL Claude generates)

• Name entities as nouns; name a join table after the relationship it represents.
• Stable identity goes into the primary key; a unique business rule goes into a unique constraint.
• Use a foreign key whenever orphaned data would be a bug.
• Money, quantities, and time use exact types - never float for money (use numeric/decimal).
• Many-to-many: create a dedicated join table and add any useful metadata columns.
• Only add an index for a predicate you have proven you need (do not index everything).

A real example - a minimal e-commerce schema Claude produced (with one line I corrected, see the note):

CREATE TABLE users (
 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 email text NOT NULL UNIQUE,
 created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 user_id bigint NOT NULL REFERENCES users(id),
 status text NOT NULL DEFAULT 'pending',
 created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 order_id bigint NOT NULL REFERENCES orders(id),
 product_id bigint NOT NULL,
 quantity int NOT NULL CHECK (quantity > 0),
 unit_price numeric(12,2) NOT NULL -- NOT float: money must be exact
);

The first time, Claude left unit_price as real (a float). I made it change to numeric(12,2) - this is exactly the class of bug you have to watch for yourself, because the AI does not always remember it.

PostgreSQL or MongoDB? Choose by access pattern

Do not choose by preference, choose by how the data is read and written. A quick table:

The golden rule for MongoDB when you are torn between embed and reference:

• Embed when the child data is read/written together with the parent and has a bounded size (for example, a shipping address inside an order).
• Reference when the data grows without bound, is shared by many parents, or needs its own lifecycle (for example, the comments on a viral post).

Step 2 - Write and optimize queries with Claude Code

This is where Claude Code shines: you describe the result you want in plain English, it writes the query, explains it, then optimizes. For SQL it is fluent in JOINs, subqueries, CTEs, and window functions. For MongoDB it builds aggregation pipelines ($match -> $group -> $lookup).

A real example - monthly revenue reporting. The prompt:

Write a Postgres query for total revenue per month in 2026,
counting only orders with status = 'paid'. Use a CTE for readability,
with a short explanation.

Claude returns:

WITH paid_items AS (
 SELECT o.created_at, oi.quantity * oi.unit_price AS line_total
 FROM orders o
 JOIN order_items oi ON oi.order_id = o.id
 WHERE o.status = 'paid'
 AND o.created_at >= '2026-01-01'
 AND o.created_at < '2027-01-01'
)
SELECT date_trunc('month', created_at) AS month,
 SUM(line_total) AS revenue
FROM paid_items
GROUP BY 1
ORDER BY 1;

Output when run (sample data):

 month | revenue
--------------------+-----------
 2026-01-01 00:00:00 | 154200.00
 2026-02-01 00:00:00 | 187650.50
 2026-03-01 00:00:00 | 203110.00

In MongoDB the same idea is a pipeline: $match filters paid orders, $unwind the items array, $group by month. Ask Claude to write it and then explain each stage - the fastest way to get both a query and an understanding of it.

Important warning: always re-read the query Claude wrote before running it against real data. An UPDATE/DELETE missing its WHERE - which the AI can produce by mistake - can wipe an entire table. Read it, understand it, then hit Enter.

Step 3 - Indexing and performance with EXPLAIN ANALYZE

A query running correctly is not enough, it has to run fast. Have Claude run EXPLAIN ANALYZE (Postgres) or .explain() (Mongo), read the plan, and then suggest an index - in the right place, not blindly.

Run EXPLAIN ANALYZE for the revenue query above.
If you see a Seq Scan on orders, suggest a suitable index and explain why.

On a large orders table, the initial plan often shows a Seq Scan because it filters on status and created_at. Add the right index:

CREATE INDEX idx_orders_status_created
 ON orders (status, created_at);

Run it again and the plan switches to an Index Scan, and the query time drops noticeably. The place you want Claude's help is choosing the column order in a composite index so it matches the predicate - this is where newer devs often get it wrong.

The indexing principle that avoids over-indexing (which Claude itself tends to overdo): only index foreign keys, columns you frequently filter/sort on, and unique constraints. Every index you add slows down writes and costs storage, so do not index "just in case." If you tell Claude "add indexes to make it faster," it tends to go overboard - ask it to only propose indexes with a provable predicate.

Step 4 - Safe migrations with Claude Code

Changing the schema on a live system is the single most incident-prone task there is. A safe process when you let Claude generate a migration:

1. Always include a rollback. Every "up" migration must have a matching "down." Ask Claude to write both and explain how to undo the change.
2. Test on a copy first. Run the migration on a dev DB or a snapshot of prod, never straight against prod.
3. Compare before/after. Count rows and check a few sample records before and after to make sure no data was lost.
4. Review the diff with a subagent. Have a subagent review the migration like an independent PR, looking for destructive operations (DROP, altering a data type) that lack a safety step.

Anthropic's best-practices wrap this principle up in one line: "give Claude a way to verify its work" (Claude Code best practices, Anthropic, 2026). For databases, "verify" means something concrete: run tests, run EXPLAIN, and compare row counts before and after - not taking the AI's word that it is "done."

A guardrail worth setting up: use a permission hook to stop Claude from writing into the migrations/ directory on its own, or to block destructive DDL commands, forcing every change through your review. For more on tightening permissions safely, see running a security audit with Claude Code.

Real pitfalls when you let AI do databases (read this before prod)

This section matters most, and almost no docs say it out loud. AI is fast at DBs, but here is where it genuinely gets things wrong - I have hit all of these:

• Inventing column/table names. Claude sometimes references a column that does not exist because it guessed the schema. Always let it read the real schema (via MCP or @schema.sql) before it writes a query.
• Wrong type for money. It very often reaches for float/real for prices, causing accumulating rounding errors. Require numeric/decimal.
• Over-indexing. Scattering indexes everywhere slows writes without meaningfully speeding reads.
• Migrations without a rollback. Generating the up but forgetting the down, leaving you stuck when you need to undo.
• N+1 or full-scan queries. Writing a loop that queries record by record instead of one JOIN, or dropping a filter condition.

Three non-negotiable rules: (1) grant read-only access to prod only - let the AI read, never write; (2) every schema change goes through a PR + tests, never applied directly; (3) verify with EXPLAIN + row-count comparison, do not trust "done." Do these three things and using AI for your DB is perfectly safe.

Go faster with the ak-databases skill (AgentKit)

If you find yourself rewriting the "workload-first, state the invariants, include the checklist" prompt every single time, there is an honest shortcut: the AgentKit Engineer Kit (which contains the ak-databases skill) packages exactly the backbone in this article. The ak-databases skill covers OLTP/OLAP schema design, Postgres/Mongo query writing, aggregation, indexing, and migrations, along with scripts like db_migrate.py, db_backup.py, and db_performance_check.py. You just type naturally - "design a schema for..." - and the skill activates itself, so you do not have to remember the prompt pattern.

Frequently asked questions (FAQ)

Can Claude Code connect directly to a database?

Yes, in two ways: an MCP server (recommended, usually read-only) or letting Claude run psql/mongosh commands through the terminal. When you do not want to connect a real DB yet, you can paste the schema file using the @ syntax.

Will Claude Code run queries against production on its own?

You should not give it that ability. Grant only a read-only connection to prod, and keep all write/DDL operations on a dev DB or behind a reviewed PR. Use a permission hook to block destructive commands.

Should I choose PostgreSQL or MongoDB?

Choose by access pattern, not by preference. PostgreSQL for transactions that need strict constraints and for aggregated reporting; MongoDB for nested data read/written as a unit. Money transactions should almost always be PostgreSQL.

Can Claude Code write migrations?

Yes, but you have to ask it to write the rollback step (down) too, test on a copy first, and compare row counts before and after running. Do not apply an AI-generated migration straight to prod.

Is it safe to let AI do databases?

It is safe if you follow three rules: read-only on prod, every change through a PR + tests, and verify with EXPLAIN ANALYZE + row-count comparison. The real risk comes from granting write access and trusting the AI without checking.

Do I have to buy the Engineer Kit?

No. The entire workflow in this article works with plain Claude Code. The ak-databases skill only makes it faster and more consistent when you do DB work regularly or as a team.

Conclusion and next steps

To recap the four steps: connect the DB (prefer read-only MCP) -> design the schema by workload -> write and optimize queries with verification -> migrate with a rollback. The key is not letting the AI do everything for you, but giving it enough context and always a way to check its own work. Once the schema is done, the logical next step is wiring the DB into your API layer - see building a backend and API with Claude Code. And if you want to speed up the DB design part, you can try the ak-databases skill in the Engineer Kit.