Text-to-SQL is an AI feature that turns a plain English question, such as which products sold best last month, into a SQL query, runs it against your database and returns the answer as a table, chart or short summary.

Is it safe to let an AI write queries against our production database?

Only with guardrails. The assistant should connect to a read-only replica or warehouse, use a restricted database role, validate every generated query before running it, apply row limits and timeouts, and never be able to run statements that modify data.

How accurate are text-to-SQL assistants?

Accuracy depends heavily on your schema clarity, the business definitions you provide and how you test. A well documented, curated set of tables with example queries typically performs far better than pointing a model at hundreds of raw tables. Always measure accuracy on your own question set before rollout.

Do we still need dashboards if we have text-to-SQL?

Yes. Dashboards remain the best way to track the same core metrics every week. Text-to-SQL is best for one-off follow-up questions that would otherwise sit in an analyst's queue for days.

How long does it take to build a text-to-SQL assistant?

A focused first version covering a handful of well defined tables can often be built in a few weeks. The time goes mostly into documenting business definitions, building an evaluation set and setting up permissions, not into the AI call itself.

Text-to-SQL for Business Teams: Ask Your Database in Plain English

Every growing company has the same bottleneck. The sales head wants to know which region had the most repeat orders last quarter. The operations lead wants to see how many tickets took more than two days to close. The founder wants a quick cut of revenue by plan. All of that data already sits in a database, yet each question goes into a queue and waits for the one person who knows SQL.

Text-to-SQL changes that. Large language models are now good enough at writing SQL that a business user can type a question in plain English and get an answer back in seconds. Done carelessly, it is also an easy way to leak data, overload a database or hand people confidently wrong numbers. This guide walks through how to build a text-to-SQL assistant that is useful, safe and trustworthy for a small or mid sized business.

What Text-to-SQL Actually Does

At its simplest, a text-to-SQL assistant follows a short loop:

The user asks a question such as "How many new customers signed up each week in October?"
The system sends the question to a language model along with a description of the relevant tables, columns and business rules.
The model returns a SQL query.
Your application validates the query, runs it with strict limits, and returns the result.
Optionally, the model summarises the result in a sentence or suggests a chart.

The model call is the easy part. The real engineering work lives in the context you give the model, the guardrails around execution and the way you measure whether the answers are correct.

Why Business Teams Want It Now

Most SMEs and startups do not have a full analytics team. They have one or two people who understand the data model and dozens who need answers from it. Dashboards solve the recurring questions, but the long tail of follow up questions ("ok, now split that by city") never ends. A text-to-SQL layer gives non technical staff a way to explore without filing a request, and frees technical people to work on harder problems.

It also pairs naturally with a reporting stack you may already have. If you have set up a read replica for reporting, as described in our guide to startup dashboards with Metabase and a Postgres read replica, you already have the safe data source a text-to-SQL assistant should point at.

A Real-World Example

Consider a hypothetical regional distributor with a custom order management system built on Postgres. The data includes orders, order items, customers, products, sales reps and territories. The sales team constantly asks the same developer for numbers: top customers by territory, slow moving products, reps who have not logged a visit in two weeks.

A practical first version for a business like this would:

Expose only six curated database views (orders, order lines, customers, products, reps, territories) rather than the full schema of internal tables.
Document each column in plain language, including tricky definitions such as "an active customer has placed at least one order in the last 90 days".
Include around 30 example question and SQL pairs written by the developer who knows the data best.
Run every query through a read-only role on a replica, with a timeout and a row limit.
Show the generated SQL next to the answer, so power users can sanity check it.

In a setup like this, the developer could plausibly move from answering several data requests a day to reviewing a weekly log of questions the assistant struggled with. That log then becomes the roadmap for improving definitions and examples. The numbers here are illustrative, but the pattern is common: the value comes less from the model and more from finally writing down what your business terms actually mean.

Step-by-Step: How to Build a Text-to-SQL Assistant

1. Pick a narrow, valuable scope

Do not start with your entire database. Pick one domain, such as sales or support, where people ask frequent ad hoc questions and where the tables are reasonably clean. A narrow scope makes accuracy far easier to achieve and to measure.

2. Create a semantic layer of curated views

Raw production tables are full of legacy columns, soft deleted rows, internal flags and confusing names. Create database views that present a clean picture: consistent names, only the columns people need, and filters already applied (for example excluding test orders and deleted records). The model then reasons about a simple, honest model of the business instead of your implementation details.

3. Write down business definitions

This is the step most teams skip, and it is the one that matters most. What counts as revenue: gross or net of refunds? Is a customer "new" based on signup date or first order date? Which time zone do reports use? Put these definitions into the context you send to the model, next to the schema. If two departments disagree on a definition, the assistant will expose it quickly, which is a useful side effect.

4. Add example question and query pairs

Few-shot examples improve results noticeably. Write a set of realistic questions with correct SQL, covering joins, date filters, grouping and the definitions above. As your library grows, retrieve only the most relevant examples for each new question rather than sending all of them every time.

5. Lock down execution

Security is not optional here. At minimum:

Connect through a dedicated database role with SELECT only permissions on the curated views.
Point at a read replica or warehouse, never the primary database that serves customers.
Parse the generated SQL and reject anything that is not a single SELECT statement.
Enforce a statement timeout and a maximum row count.
Apply row level security if different users should see different data, for example a rep seeing only their own territory.

Asking the model for a structured response (the SQL, a short explanation and a confidence flag) makes validation much easier. Our guide on getting reliable JSON with structured outputs covers the technique in detail.

6. Build an evaluation set before launch

Collect 50 to 100 real questions from the team, along with the correct answers. Run the assistant against them and compare results, not just SQL text, because two different queries can produce the same correct answer. Track the pass rate every time you change prompts, examples or models. Without this, you have no idea whether a change made things better or worse.

7. Design the interface for trust

Show the generated SQL behind a "view query" toggle. Show which tables and filters were used in plain language ("Orders from 1 to 31 October, excluding cancelled orders"). Let users flag wrong answers with one click. When the model is unsure, it should ask a clarifying question instead of guessing.

8. Log, review and improve

Log every question, generated query, result size and user feedback. Review the failures weekly. Most fixes will be adding a definition, renaming a confusing column in a view or adding an example, not changing the model.

Common Pitfalls to Avoid

Pointing the model at everything. Hundreds of tables confuse the model and widen your security exposure.
Hiding the SQL. If nobody can see how an answer was produced, nobody can catch a wrong join.
Ignoring time zones and date boundaries. "Last month" is ambiguous until you define it.
Treating answers as final reports. Text-to-SQL is for exploration. Numbers that go to the board should still come from reviewed, versioned reports.
Skipping cost controls. Very large schemas in every prompt add up. Retrieve only relevant tables per question.

Key Benefits

Faster answers: Business users get answers to simple questions in seconds instead of waiting in a queue.
Less interruption for engineers: Technical staff spend time on product work instead of one-off queries.
Clearer definitions: Building the semantic layer forces the company to agree on what its metrics mean.
Better questions over time: When asking is cheap, people explore more and spot issues earlier.
Reusable foundation: The curated views and definitions also improve dashboards, exports and future AI features.

When Text-to-SQL Is Not the Right Tool

If your data is scattered across spreadsheets with no consistent structure, start by consolidating it. If the questions people ask are always the same five, a well designed dashboard is cheaper and more reliable. And if your data includes highly sensitive personal information, think carefully about whether natural language access is appropriate at all, and for whom.

The best text-to-SQL projects are really data clarity projects with an AI interface on top. The model is only as good as the definitions you give it.

How Mavani Approaches It

When we scope AI data assistants with clients, we usually spend more time on the semantic layer and the evaluation set than on prompts. That keeps the first release small, measurable and safe, and gives the team a clear way to expand coverage one domain at a time. If you are exploring AI features like this for your own product or internal tools, our AI development services page explains how we typically structure that work.

Conclusion

Text-to-SQL can turn your database from something only engineers can question into something the whole team can explore. The recipe is consistent: start narrow, curate clean views, write down your business definitions, lock execution to read-only and limited queries, and measure accuracy against real questions before and after every change. Treat it as a living system that improves with feedback, and it can become one of the most used internal tools your team has.

Frequently Asked Questions

What is text-to-SQL?: Text-to-SQL is an AI feature that turns a plain English question, such as which products sold best last month, into a SQL query, runs it against your database and returns the answer as a table, chart or short summary.
Is it safe to let an AI write queries against our production database?: Only with guardrails. The assistant should connect to a read-only replica or warehouse, use a restricted database role, validate every generated query before running it, apply row limits and timeouts, and never be able to run statements that modify data.
How accurate are text-to-SQL assistants?: Accuracy depends heavily on your schema clarity, the business definitions you provide and how you test. A well documented, curated set of tables with example queries typically performs far better than pointing a model at hundreds of raw tables. Always measure accuracy on your own question set before rollout.
Do we still need dashboards if we have text-to-SQL?: Yes. Dashboards remain the best way to track the same core metrics every week. Text-to-SQL is best for one-off follow-up questions that would otherwise sit in an analyst's queue for days.
How long does it take to build a text-to-SQL assistant?: A focused first version covering a handful of well defined tables can often be built in a few weeks. The time goes mostly into documenting business definitions, building an evaluation set and setting up permissions, not into the AI call itself.