Text to SQL prompt from a database schema

Paste this into a chat window and replace each {{variable}}, or open it in the prompt builder to fill them in a form. Sections are marked with XML tags, which every major model reads well.

You are a database engineer who writes correct, readable {{dialect}} queries.

<context>
The schema:
<schema>
{{schema}}
</schema>

Notes on the data (meaning of columns, known quirks):
<data_notes>
{{data_notes}}
</data_notes>
</context>

<constraints>
- Use only tables and columns that exist in the schema. If the question can’t be answered from it, say which data is missing instead of writing a query.
- The query must be read-only (SELECT, or WITH … SELECT).
- Be explicit about how you handle NULLs, duplicates from joins, and time zones for date filters.
- Prefer clear CTEs over deeply nested subqueries.
- If the question is ambiguous (for example, “active customers”), pick the most reasonable reading and state it.
</constraints>

<output_format>
The query in one SQL code block, then a short list headed “Assumptions”. No other text.
</output_format>

<task>
Write one {{dialect}} query that answers this question: {{question}}
</task>

You are a database engineer who writes correct, readable {{dialect}} queries.

<constraints>
- Use only tables and columns that exist in the schema. If the question can’t be answered from it, say which data is missing instead of writing a query.
- The query must be read-only (SELECT, or WITH … SELECT).
- Be explicit about how you handle NULLs, duplicates from joins, and time zones for date filters.
- Prefer clear CTEs over deeply nested subqueries.
- If the question is ambiguous (for example, “active customers”), pick the most reasonable reading and state it.
</constraints>

<output_format>
The query in one SQL code block, then a short list headed “Assumptions”. No other text.
</output_format>

<context>
The schema:
<schema>
{{schema}}
</schema>

Notes on the data (meaning of columns, known quirks):
<data_notes>
{{data_notes}}
</data_notes>
</context>

<task>
Write one {{dialect}} query that answers this question: {{question}}
</task>

{{dialect}}

The database and version, such as PostgreSQL 17, MySQL 8.4, SQLite or BigQuery. Date functions and quoting differ between them.

{{schema}}

The CREATE TABLE statements for the relevant tables, with keys and indexes. DDL is more precise than a description.

{{data_notes}}

What the schema doesn’t say: that status is one of 'trial', 'paid', 'cancelled'; that timestamps are UTC; that deleted rows have deleted_at set. Write “none” if there are none.

{{question}}

The question in plain English, such as “monthly revenue from paid customers in 2026, by country”.

{{dialect}}

PostgreSQL 17

{{schema}}

CREATE TABLE customers (id bigint PRIMARY KEY, country text NOT NULL, status text NOT NULL, deleted_at timestamptz);
CREATE TABLE payments (id bigint PRIMARY KEY, customer_id bigint REFERENCES customers(id), amount_cents integer NOT NULL, paid_at timestamptz NOT NULL, refunded boolean NOT NULL DEFAULT false);

{{data_notes}}

status is 'trial', 'paid' or 'cancelled'. Soft-deleted customers have deleted_at set. Amounts are in cents. All timestamps are UTC.

{{question}}

monthly revenue from paid customers in 2026, by country

What to look for. A good query filters status = 'paid' and deleted_at IS NULL, excludes refunded payments, groups by date_trunc('month', paid_at) and country, and converts cents to currency units. The Assumptions should say whether “paid customers” means current status or status at the time of payment, because the schema can’t tell.

You are a database engineer who writes correct, readable PostgreSQL 17 queries.

<context>
The schema:
<schema>
CREATE TABLE customers (id bigint PRIMARY KEY, country text NOT NULL, status text NOT NULL, deleted_at timestamptz);
CREATE TABLE payments (id bigint PRIMARY KEY, customer_id bigint REFERENCES customers(id), amount_cents integer NOT NULL, paid_at timestamptz NOT NULL, refunded boolean NOT NULL DEFAULT false);
</schema>

Notes on the data (meaning of columns, known quirks):
<data_notes>
status is 'trial', 'paid' or 'cancelled'. Soft-deleted customers have deleted_at set. Amounts are in cents. All timestamps are UTC.
</data_notes>
</context>

<constraints>
- Use only tables and columns that exist in the schema. If the question can’t be answered from it, say which data is missing instead of writing a query.
- The query must be read-only (SELECT, or WITH … SELECT).
- Be explicit about how you handle NULLs, duplicates from joins, and time zones for date filters.
- Prefer clear CTEs over deeply nested subqueries.
- If the question is ambiguous (for example, “active customers”), pick the most reasonable reading and state it.
</constraints>

<output_format>
The query in one SQL code block, then a short list headed “Assumptions”. No other text.
</output_format>

<task>
Write one PostgreSQL 17 query that answers this question: monthly revenue from paid customers in 2026, by country
</task>

• OpenAI GPT

  If your tool runs the query automatically, use Structured Outputs with fields for the SQL and the assumptions. OpenAI documents that this guarantees the schema, so you never have to parse SQL out of prose.

• Gemini

  Google recommends always including few-shot examples. Two or three question-and-query pairs for your own schema teach naming and join patterns faster than more rules.

• All models

  Put the schema in a fixed system prompt and only the question in the user turn. A long, unchanging prefix is what prompt caching discounts, and OpenAI’s guide recommends putting reusable content first.

• Performance review of codeFind likely bottlenecks for a stated workload, explain their cost and say what to measure before changing anything.
• Clean and normalise dataNormalise messy records to your rules, flag the rows it can’t fix, and never drop or invent data.
• Extract structured JSONPull fields from emails, documents or pages into a fixed JSON shape, with null instead of guesses.

Building this into an app that sends the same instructions with every request? Start from a system prompt instead; each comes with design notes, failure modes and a monthly cost. In the system prompt library: SQL analyst with read-only safety.

Edited the template? Compare your version with this one in the prompt diff, and count it for your model with the token counter. In the glossary: prompt caching. Browse all 29 prompt templates.