Smart Query Generator
A Natural-Language SQL Assistant for a Data Analyst
A mobile services company's data analysts had to write their own SQL against databases they did not fully know the shape of. I built a Streamlit tool that takes a plain-English question, identifies which database it concerns, reads that database's real schema, and asks Claude 3.5 Sonnet to generate a matching SQL query. The query runs immediately and the result shows up as a table in the same screen.
The problem
The client's data analysts needed answers from several Postgres databases without always knowing the exact table and column names ahead of time. Writing SQL by hand meant stopping to look up a schema every time a question touched an unfamiliar table. Asking a language model to write SQL blind does not work either, a model with no view of the real schema invents column names that do not exist. The team needed a tool that would ground a generated query in the actual schema of the actual database being asked about, not a guess.
Constraints
- ·Single analyst-facing tool, not a multi-tenant service, so authentication and access control were out of scope
- ·Had to work against existing Postgres databases without changing their schema
- ·Model access was through AWS Bedrock, so the design had to work within the Bedrock invoke_model API rather than a chat SDK with native tool calling
- ·Built and iterated by one engineer over a short internal build
Architecture
A Streamlit app takes one text question, calls Claude 3.5 Sonnet on Bedrock to identify the target database, reads that database's schema live with SQLAlchemy's inspect(), then calls Claude a second time with the schema attached to produce the SQL. The extracted query runs against Postgres and the result renders as a table.
- ›Streamlit UI (single page, text input plus a chat history sidebar in session state)
- ›Bedrock client calling anthropic.claude-3-5-sonnet-20240620-v1:0 via invoke_model
- ›A tagged-text response protocol (#?#<TAG>value#?#) parsed with regex instead of structured output
- ›Schema introspection with SQLAlchemy inspect() (tables, columns, primary keys, foreign keys, indexes)
- ›Query execution against Postgres through SQLAlchemy, results returned as a Pandas DataFrame
How one request flows
- 01The analyst types a question in plain English and submits it.
- 02The app sends the question to Claude 3.5 Sonnet on Bedrock with a system prompt asking for a tagged reply naming the database, schema, and SQL command.
- 03The app extracts the database name from the tagged reply with a regex match.
- 04If a database was named, the app connects to it and calls SQLAlchemy's inspect() to pull every schema's tables, columns, primary keys, foreign keys, and indexes.
- 05That schema is formatted into plain text and appended to the original question, then sent back to Claude as a second call.
- 06The SQL command is extracted from the second reply and run against the database through SQLAlchemy.
- 07Rows come back as a Pandas DataFrame and render as a table in Streamlit, and the question and query are added to the sidebar chat history.
Engineering decisions
Ground the query in a live schema, not the model's memory
What. Before generating SQL, the app reads the actual schema of the identified database with SQLAlchemy's inspect(), tables, columns, primary keys, foreign keys, and indexes, and hands that to the model as text.
Why. A model asked to write SQL with no schema in front of it invents plausible-looking column names. Giving it the real schema for the specific database in the question turns a guess into a grounded query.
Tradeoff. This costs a second model call and a live database round trip per question, instead of a single one-shot generation. The accuracy gain was worth the extra latency for an analyst tool that is not on a tight response budget.
Two-pass call instead of one big prompt
What. The first Bedrock call only identifies which database and schema the question is about. The full schema is fetched and attached only after that, in a second call.
Why. The schema of every database the analyst might ask about is too large to attach to every single question. Identifying the target first keeps the attached context to just the one database that matters.
Tradeoff. A question that does not clearly name a database returns no target, and the app falls back to running the extracted SQL against a default connection with no schema grounding at all. That fallback path is honest but weaker.
Tagged-text output instead of structured output
What. The system prompt asks Claude to answer inside literal markers like #?#<COMMAND>...#?#, which the app pulls out with re.search rather than asking for JSON or a tool call.
Why. This was built directly against the raw Bedrock invoke_model API in a single text completion, which was the fastest path to a working extractor at the time.
Tradeoff. It is brittle. If the model drops a tag or reformats its answer, the regex returns None and the app shows 'No valid SQL command detected' with no retry. A tool-call or JSON-mode approach would be more resilient, and is the natural next iteration.
No SQL validation before execution
What. Whatever text comes out of the tag extractor runs directly against the database with connection.execute(text(sql_query)), no allowlist, no read-only check, no row limit.
Why. This was a local, single-analyst prototype talking to a personal Postgres instance, not a shared service, so the fastest way to validate the whole approach was to run the query as-is and see the result.
Tradeoff. Acceptable for the prototype stage. Not something to carry into a multi-user or production deployment without adding a statement allowlist and a row cap first.
What changed along the way
- →Started with a version that sent the question to the model with no schema at all (test1.py) and confirmed a real schema was needed after seeing plausible but wrong column names come back.
- →Added schema introspection against a single hardcoded database name (test2.py) before generalizing it to detect the database from the question itself.
- →Split the flow into two calls, identify the database first, then generate SQL with its schema attached, once a single call proved too unreliable at guessing both at once.
- →Removed the debug st.write and st.text calls used while developing (test3.py, test4.py) once the flow was confirmed working, arriving at the final test5-solid.py.
Security and reliability
- ·No authentication or access control, this is a single-analyst local tool, not a shared service.
- ·No SQL validation before execution, extracted queries run as-is against Postgres with no allowlist or row limit.
- ·Errors from both Bedrock calls and the database layer are caught and shown in the UI rather than crashing the app.
- ·AWS credentials are never in the code, boto3 picks them up from the environment or local AWS config.
Metrics
What shipped
- ✓Delivered a working Streamlit tool that turns a plain-English question into a schema-grounded SQL query and runs it.
- ✓Schema introspection with SQLAlchemy inspect() replaced blind query generation, cutting down on invented column names.
- ✓Chat history in the sidebar lets the analyst see prior questions and the SQL that answered them within a session.
- ✓Handed over as a working local prototype with a clear next step already identified: replace the tagged-text protocol with structured output and add SQL validation before any production use.
Lessons learned
- ·A model asked to write SQL needs the real schema in front of it. Without that, it invents column names that look right and are wrong.
- ·Splitting a task into an identify-then-generate pair of calls beats asking one call to do both when the second call depends on data only the first call's answer can unlock.
- ·A tagged-text protocol is a fast way to get structured-enough output from a raw Bedrock invoke_model call, but it is one dropped tag away from silently failing.
- ·A prototype that skips SQL validation is fine for a single trusted user and a personal database. That shortcut has to close before it goes anywhere near more than one user.