Prompting for SQL Query Generation

Why SQL Generation Is Different Generating SQL from natural language, sometimes called text-to-SQL, lets people ask questions such as "How many students completed each course this month?" and get answers from a database. It is powerful, but it has specific risks that other code generation does not:

Risk Example Wrong but valid queries The query runs and returns numbers, but joins tables incorrectly or counts duplicates Invented tables or columns The model guesses students.full_name when the column is name Wrong business definitions "Active student" means something specific in your organization Dialect differences Date functions differ between PostgreSQL, MySQL, and SQLite Destructive statements A generated DELETE or UPDATE changes data Performance problems A query scans millions of rows without using indexes The first risk is the most dangerous: a wrong query does not produce an error. It produces a plausible wrong number. This lesson shows how to prompt for correct SQL and how to check generated queries safely. Lesson 133 builds a complete SQL query assistant as a project.

What the Model Must Know A model can only write correct SQL for your database if the prompt tells it about your database.

Information Why Example Schema Exact table and column names, types, and relationships CREATE TABLE statements Dialect and version Correct functions and syntax SQLite 3, PostgreSQL 16 Business definitions What terms mean in your data "Completed" means progress = 100 Value formats How data is stored Dates as YYYY-MM-DD text Constraints What the query may and may not do Read-only; only SELECT Providing the Schema The most precise way to describe tables is the CREATE TABLE statements themselves:

CREATE TABLE courses ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, fee INTEGER NOT NULL -- in rupees );

CREATE TABLE enrollments ( id INTEGER PRIMARY KEY, student_email TEXT NOT NULL, course_id INTEGER NOT NULL REFERENCES courses(id), enrolled_on TEXT NOT NULL, -- YYYY-MM-DD progress INTEGER NOT NULL -- 0 to 100 ); Comments inside the schema, such as "in rupees" and "0 to 100", give the model information that column names alone do not.

For large databases, include only the tables relevant to the question. Selecting the right tables can itself be a first step, done by code or by a prompt.

The SQL Generation Prompt Write a {dialect} query that answers the question in .

Database schema: {schema}

Definitions:

• A "completed" enrollment has progress = 100.
• An "active" student has at least one enrollment with progress between 1 and 99.
• Dates are stored as text in YYYY-MM-DD format.

Rules:

• Write a single read-only SELECT query (WITH clauses are allowed).
• Use only the tables and columns in the schema.
• If the question cannot be answered with this schema, reply "CANNOT_ANSWER: ".
• Return the query in tags, then a short explanation of what it does in tags.

{question} Why Each Part Matters Part Prevents Dialect Functions from the wrong database system Schema in tags Invented tables and columns Definitions Wrong interpretation of business terms Single read-only SELECT Data changes and multiple statements CANNOT_ANSWER Forced, wrong queries for questions the data cannot answer Explanation Lets a person confirm the query matches the question Example Question: "How many enrollments and completions does each course have, most popular first?"

Possible output:

SELECT c.title, COUNT(e.id) AS enrollments, SUM(CASE WHEN e.progress = 100 THEN 1 ELSE 0 END) AS completed FROM courses c LEFT JOIN enrollments e ON e.course_id = c.id GROUP BY c.id, c.title ORDER BY enrollments DESC; Explanation: It lists every course, counts its enrollments, counts how many have progress 100, and sorts by enrollments. The LEFT JOIN keeps courses with no enrollments, which would show 0.

Checking and Running Generated SQL Safely Generated SQL should pass three gates before anyone trusts its result:

1. Safety check: is it a single read-only query?
2. Test run: does it run on a test or read-only database with sample data?
3. Result check: are the numbers plausible, and does the query match the question? A Safety Check and Test Run With SQLite The example below creates an in-memory SQLite database with sample data, checks a generated query, and runs it.

import re import sqlite3

SCHEMA = """ CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL, fee INTEGER NOT NULL); CREATE TABLE enrollments (id INTEGER PRIMARY KEY, student_email TEXT NOT NULL, course_id INTEGER NOT NULL REFERENCES courses(id), enrolled_on TEXT NOT NULL, progress INTEGER NOT NULL); """

SAMPLE_DATA = """ INSERT INTO courses VALUES (1, 'Python Basics', 1999), (2, 'Django REST APIs', 4999), (3, 'React Fundamentals', 2999); INSERT INTO enrollments VALUES (1, 'asha@example.com', 1, '2026-09-14', 100), (2, 'vikram@example.com', 2, '2026-10-02', 40), (3, 'meera@example.com', 2, '2026-10-05', 100), (4, 'arun@example.com', 3, '2026-08-20', 10), (5, 'asha@example.com', 2, '2026-10-10', 60); """

FORBIDDEN = re.compile( r"\b(insert|update|delete|drop|alter|create|replace|attach|pragma|grant|truncate)\b", re.IGNORECASE, )

def check_select_only(sql: str) -> str | None: """Return a problem description, or None if the query passes the checks.""" statement = sql.strip().rstrip(";") if ";" in statement: return "Only one statement is allowed" if not re.match(r"^\s*(select|with)\b", statement, re.IGNORECASE): return "Query must start with SELECT or WITH" if FORBIDDEN.search(statement): return "Query contains a forbidden keyword" return None

connection = sqlite3.connect(":memory:") connection.executescript(SCHEMA + SAMPLE_DATA)

generated = """ SELECT c.title, COUNT(e.id) AS enrollments, SUM(CASE WHEN e.progress = 100 THEN 1 ELSE 0 END) AS completed FROM courses c LEFT JOIN enrollments e ON e.course_id = c.id GROUP BY c.id, c.title ORDER BY enrollments DESC; """

problem = check_select_only(generated) print("Safety check:", problem or "passed") for row in connection.execute(generated): print(row)

for sql in ["DELETE FROM enrollments", "SELECT * FROM courses; DROP TABLE courses", "SELECT * FROM courses WHERE title = 'x'"]: print(repr(sql[:40]), "->", check_select_only(sql)) Output:

Safety check: passed ('Django REST APIs', 3, 1) ('Python Basics', 1, 1) ('React Fundamentals', 1, 0) 'DELETE FROM enrollments' -> Query must start with SELECT or WITH 'SELECT * FROM courses; DROP TABLE course' -> Only one statement is allowed "SELECT * FROM courses WHERE title = 'x'" -> None Code Explanation check_select_only()

Removes a trailing semicolon, then rejects any remaining semicolon, which would indicate a second statement, a classic way to hide DROP TABLE after a harmless query. Requires the query to start with SELECT or WITH. Rejects keywords that change data or the database structure. The \b word boundaries mean created_at does not match create. sqlite3.connect(":memory:") Creates a temporary database in memory. The sample data is small enough to check results by hand: Django REST APIs has 3 enrollments (IDs 2, 3, 5) and 1 completion (ID 3), which matches the output.

executescript() Runs several SQL statements at once to create the tables and insert sample data. It is used only for our own setup script, never for generated SQL.

Limits of Keyword Checks The keyword check is a first filter, not a security boundary:

It can reject safe queries, for example a query that searches for the word "update" in a text column. It cannot catch every trick in every SQL dialect. The real protection must come from the database itself:

Protection How Read-only database user Grant only SELECT permission on the needed tables Read-only connection For SQLite, open the file with sqlite3.connect("file:app.db?mode=ro", uri=True) Query time limits Configure statement timeouts in the database Row limits Add or enforce LIMIT for exploratory queries Separate replica Run generated queries on a read replica, not the main database Warning: Never run model-generated SQL with a database user that can modify data. Instructions and keyword filters reduce risk; database permissions enforce safety.

Checking That the Query Answers the Question A safe query can still be wrong. Common logical errors in generated SQL:

Error Example How to Catch It Wrong join type INNER JOIN drops courses with no enrollments Check that all expected rows appear Duplicate counting Joining several one-to-many tables multiplies rows Compare totals with a simple direct count Wrong definition Counting progress > 0 as "completed" Compare with the definition in the prompt Wrong date range Off-by-one at month boundaries Test with data on boundary dates Missing filter Including test or deleted records Check filters against business rules A Verification Prompt Check whether the SQL in correctly answers the question in using the schema and definitions in .

1. Restate in plain words what the query actually computes.
2. List any difference between that and the question.
3. Point out any risk of duplicate counting, missing rows, or wrong filters. Restating what the query actually computes, in plain words, is the most useful check: differences between the question and the restatement reveal logical errors.

Test With Known Answers Build a small sample database where you know the correct answers, as in the example above, and compare generated queries against them. This is the SQL equivalent of unit tests (Lesson 94).

Performance Considerations For large tables, a correct query can still be too slow. Ask the database how it will run the query:

SQLite: EXPLAIN QUERY PLAN PostgreSQL: EXPLAIN , or EXPLAIN ANALYZE on a safe copy, since ANALYZE actually runs the query MySQL: EXPLAIN Look for full table scans on large tables. You can give the plan to a model and ask which index would help, then verify the suggestion by measuring.

Common Mistakes Mistake 1: No Schema in the Prompt What it is: Asking for a query with only the question.

Why it happens: The table names seem obvious.

What it causes: Invented tables and columns.

How to fix it: Include the relevant CREATE TABLE statements.

Mistake 2: Undefined Business Terms What it is: Asking about "active students" or "completed courses" without definitions.

Why it happens: The terms feel standard.

What it causes: Queries that use a different definition and return plausible wrong numbers.

How to fix it: Define every business term in the prompt.

Mistake 3: Running Generated SQL With Write Permissions What it is: Executing generated queries with the application's main database user.

Why it happens: It is the connection already available.

What it causes: Risk of data loss or changes.

How to fix it: Use a read-only user or connection, plus a safety filter.

Mistake 4: Trusting Numbers Because the Query Ran What it is: Accepting results because there was no error.

Why it happens: Wrong SQL usually still runs.

What it causes: Wrong decisions based on wrong numbers.

How to fix it: Ask for an explanation, verify with a restatement prompt, and test against known answers.

Mistake 5: Ignoring the Dialect What it is: Not stating which database system the query is for.

Why it happens: SQL seems like one language.

What it causes: Syntax errors or different behavior, especially with dates and string functions.

How to fix it: State the dialect and version.

Best Practices Provide the schema, dialect, definitions, and formats. The model cannot know your database.

Restrict to a single read-only query. And allow CANNOT_ANSWER.

Enforce safety in the database. Read-only users and connections, timeouts, and limits.

Use a keyword filter as an extra layer. Not as the only protection.

Verify meaning, not just execution. Plain-language restatements and known-answer tests.

Check performance on large data. Use the database's query plan.

Try It Yourself Exercise 1: Generate and Verify Using the sample schema, generate queries for: "Which students are enrolled in more than one course?", "What is the total fee revenue per course?", and "Which course has the lowest completion rate?" Run each against the sample database and verify the results by hand.

Exercise 2: Find a Logical Error Write a query that joins courses and enrollments with an INNER JOIN to list enrollments per course, and add a fourth course with no enrollments to the sample data. Compare the results with the LEFT JOIN version, and use the verification prompt to see whether a model notices the missing course.

Exercise 3: Read-Only Protection Save the sample database to a file, open it with sqlite3.connect("file:app.db?mode=ro", uri=True), and try to run an INSERT statement. Observe the error, and explain why this protection is stronger than check_select_only().