MySQL Natural Language Query Assistant

intent

turn a user's natural-language analytics request into safe, validated SQL queries executed against a live MySQL database. use this when you need to explore schema, generate read-only queries, debug SQL logic, or handle restricted write workflows that require preview-before-execute. this skill is for ad hoc analysis, schema discovery, and cautious database work where validation against real results matters before presenting answers.

inputs

environment variables (set before running):

• MYSQL_HOST: hostname or ip of the mysql server
• MYSQL_PORT: port number, default 3306
• MYSQL_USER: database user with select privileges (and insert/update/delete for preview workflows only)
• MYSQL_PASSWORD: password for the user
• MYSQL_DB: database name to query

python dependencies:

• mysql-connector-python or PyMySQL or mysqlclient (at least one must be installed; see execution details)

external scripts (included in skill context):

• scripts/introspect_schema.py: discover tables, columns, data types, keys, and comments
• scripts/run_read_query.py: execute read-only sql only; rejects write statements and multi-statement execution

edge cases to handle:

• connection timeout or network unreachability
• invalid credentials or insufficient privileges
• missing database or table
• mysql max connections limit reached
• query execution timeout (long-running aggregations)
• empty result sets (zero matching rows)
• schema changes during a session (stale column cache)

procedure

step 1: verify connection prerequisites

input: environment variables MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB output: confirmation that env vars are set and connection can be established

• check that all five env vars are present and non-empty
• attempt a lightweight test query (e.g., SELECT 1) against the database
• if connection fails, stop and report the error clearly (wrong credentials, host unreachable, db missing, etc.)

step 2: discover schema

input: user's natural-language request, env vars output: list of candidate tables with column metadata

• run scripts/introspect_schema.py to list all tables in the database
• from the table names and user intent, identify the most relevant tables (e.g., users table for a request about user counts)
• for each candidate table, inspect columns, data types, primary/foreign keys, and column comments
• if comments are sparse, use table and column names plus a small probing query (e.g., SELECT * FROM table LIMIT 1) to infer meaning
• document your schema assumptions in the notes section of the output

step 3: draft sql

input: user request, discovered schema output: candidate sql query as a string

• write a SELECT query only (never draft write statements at this stage)
• use explicit column lists instead of SELECT * unless the user explicitly asks for schema exploration
• apply LIMIT by default to keep results bounded, unless the user asks for a full extract or row count
• use joins, filters, grouping, and aggregation as implied by the request and supported by the schema
• follow mysql syntax rules and prefer readable formatting

step 4: execute the query

input: candidate sql query, env vars output: result set (rows), row count, and any error messages

• call scripts/run_read_query.py with the sql string
• if the script rejects the query (non-read-only, multi-statement, syntax error), examine the error and revise the sql
• capture the returned rows, total row count, and column names
• if execution times out or hits a connection limit, note it and suggest a more bounded query (smaller LIMIT, narrower date range, or indexed filter)
• if the result set is empty, do not treat it as failure; proceed to validation

step 5: structural validation

input: final sql, user request, schema metadata output: validation note (pass or fail with reason)

• confirm that all tables exist and have been spelled correctly
• confirm that all columns used in the query exist in those tables
• confirm that WHERE filters, JOIN conditions, and GROUP BY clauses match the user's intent
• confirm that the query syntax is valid mysql
• if any element is wrong, make a note and return to step 3 to revise

step 6: result validation

input: returned rows, user request, schema semantics output: validation note (pass or fail with reason)

• scan the returned rows for semantic plausibility (e.g., if counting users, are the counts non-negative integers?)
• check that the columns returned actually answer the user's question
• if the result is empty, determine whether that is expected (e.g., no orders from a future date) or suggests a missing filter
• if multiple interpretations of the request are plausible, state your assumption and defend the query choice you made
• if confidence is low (e.g., ambiguous column semantics or a suspicious value), flag it in the notes
• if validation fails, return to step 3 and revise the query

step 7: present results

input: validated query, result rows, validation notes, schema assumptions output: formatted response following the output contract

• structure your response with the sections defined in output contract below
• include the final sql, structural and result validation summaries, sample rows (5 to 20), a brief natural-language summary, and any caveats

decision points

if the user asks for a write operation (insert, update, delete, replace, alter, drop, truncate, create):

• do not execute the write statement
• first produce a preview SELECT query that shows exactly which rows would be affected
• then produce the write sql separately as unannotated code
• clearly label the write sql as "not executed"
• call out any missing safety conditions, such as a weak or absent WHERE clause, or absence of a reliable unique key for the update target

if the result set is empty:

• do not treat this as an error
• explain why the result may be empty (e.g., no data matching the filter, wrong date range, table mismatch)
• suggest a corrected query only if a mistake is likely
• if the empty result is expected, say so explicitly

if schema discovery returns no column comments:

• fall back to inferring meaning from table and column names
• run a small probing query (e.g., SELECT * FROM table LIMIT 3) to confirm data types and plausible values
• explicitly state your assumption in the notes section

if the user's request is ambiguous (e.g., "revenue last month" without specifying which revenue column or time field):

• use schema inspection to narrow candidate tables and columns
• state your assumption clearly and ask for clarification if needed
• prefer the interpretation most strongly supported by the schema

if the sql execution times out or hits resource limits:

• suggest a narrower query (smaller LIMIT, indexed filter, or smaller date range)
• do not re-run the same query
• offer to break the query into smaller chunks

if connection fails (invalid credentials, host unreachable, insufficient privileges):

• stop immediately and report the error
• do not attempt workarounds or guesses
• confirm env vars are set correctly before retrying

output contract

all responses must follow this structure unless the user explicitly requests a different format:

Final SQL

[the final validated query, properly formatted]

Validation

• Structural check: [one sentence explaining why the sql shape matches the user's request; reference tables, columns, filters, joins, grouping]
• Result check: [one to two sentences explaining why the returned data is plausible, or why confidence is limited, or why the result is empty and expected]

Sample Results

[5 to 20 rows when available and useful; keep wide tables compact, e.g., truncate long text or use json formatting]

Result Summary

[brief natural-language summary, 2-4 sentences, of what the query shows and what the rows mean in business terms]

Notes

[any assumptions made about schema, caveats, edge cases, or uncertainties; mention if comments were missing or if multiple interpretations were plausible]

outcome signal

the user knows the skill worked when:

• they see a final sql query that executes without error and returns rows (or confirms an expected empty result)
• the structural validation explicitly confirms that the query matches their request
• the result validation confirms that the returned rows are semantically plausible and answer their question
• the sample results and summary are clear and directly address the original request
• any edge cases or schema assumptions are clearly documented in the notes section
• for write requests, they see both a preview SELECT showing which rows would be affected and a separate unannotated write statement clearly marked as not executed

execution details

environment setup:

• set MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB in your shell or deployment environment
• verify that at least one of mysql-connector-python, PyMySQL, or mysqlclient is installed; if not, run pip install mysql-connector-python (or the alternative)

scripts:

• scripts/introspect_schema.py accepts env vars and outputs table and column metadata to stdout or json
• scripts/run_read_query.py accepts a sql string as input, rejects non-read-only statements and multi-statement execution, and outputs rows and metadata

schema exploration workflow:

1. list candidate tables with introspect_schema.py
2. inspect columns, data types, keys, and comments for the most likely tables
3. infer business meaning from column comments and names
4. only then draft sql

read-only execution workflow:

1. run the first candidate sql with scripts/run_read_query.py
2. review row count, sample rows, and whether columns answer the request
3. if result is empty or suspicious, explain why and revise the query when appropriate
4. if multiple interpretations exist, state your assumption and justify the chosen query

examples

example: analytics request

user request: "how many new users did we add each day in the last 7 days?"

expected approach:

1. inspect the schema to find the users table and identify the created-at or signup-date column
2. confirm the time column semantics from comments or a probing query
3. draft a grouped query: SELECT DATE(created_at) as signup_date, COUNT(*) as new_users FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY DATE(created_at) ORDER BY signup_date
4. run it and confirm the row count and date buckets look plausible
5. validate that the counts are non-negative integers and the date range is correct
6. present the results with a summary like "the table shows 7 rows, one per day, with new user counts ranging from X to Y"

example: restricted write request

user request: "update orders with status = 'pending' and created more than 30 days ago to status = 'expired'"

expected approach:

1. draft a preview SELECT: SELECT id, created_at, status FROM orders WHERE status = 'pending' AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
2. run the preview and show the user which rows would be affected (e.g., "123 orders match")
3. draft the write sql separately: UPDATE orders SET status = 'expired' WHERE status = 'pending' AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
4. label it clearly as "not executed"
5. warn if the table lacks a primary key or if the filter might be too broad (e.g., no upper bound on created_at, risk of affecting future data)