AWS Builder Center

Builder Center

Building a Text-to-SQL System That Converts Natural Language into SQL

SQL is extremely powerful for working with structured data, but users often need to understand database schemas and SQL syntax before they can retrieve useful information.

What if someone could simply ask:
"Who are the top 5 customers by total spending?"
and receive the answer without manually writing a SQL query?
That was the idea behind my Text-to-SQL project.
The system takes a natural-language question, understands the database structure, generates an SQL query, executes it against the database, and returns the result.
This project helped me explore the practical side of combining Python, SQL, database schema introspection, and LLM-based query generation.

What is Text-to-SQL?

Text-to-SQL is the process of converting a natural-language request into a structured SQL query.
For example, a user might ask:
1
Who are the top 5 customers by spending?
The system can transform this into something conceptually similar to:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
Customer.CustomerId,
Customer.FirstName,
Customer.LastName,
SUM(Invoice.Total) AS TotalSpent
FROM Customer
JOIN Invoice
ON Customer.CustomerId = Invoice.CustomerId
GROUP BY
Customer.CustomerId
ORDER BY
TotalSpent DESC
LIMIT 5;
The database executes the query and produces the final result.
The important part is that the user does not need to know the database structure or SQL syntax.

Project Architecture

The basic pipeline of my project is:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
User Question
|
v
 Natural Language
|
v
 Schema Inspection
|
v
 SQL Generation
|
v
 SQL Validation
|
v
 Database Execution
|
v
 Results
The database schema is an important part of this process.
An LLM cannot reliably generate useful SQL if it does not know what tables and columns exist.
Therefore, one of the first steps is understanding the database itself.

The Database

For development and testing, I used the Chinook database.
Chinook is a sample relational database representing a digital music store.
It contains entities such as:
  • Customers
  • Invoices
  • Invoice items
  • Artists
  • Albums
  • Tracks
  • Genres
  • Media types
  • Employees
This makes it useful for testing different types of SQL questions.
For example:
1
2
3
4
5
6
7
8
9
Who spent the most money?

Which artist has the most tracks?

What are the top 5 customers?

Which genres have the most tracks?

Which albums belong to a particular artist?
These questions require different SQL operations such as joins, aggregation, grouping, ordering, and filtering.

Step 1: Understanding the Database Schema

Before generating SQL, the system needs to understand the database.
For a relational database, this means identifying:
1
2
3
4
5
6
7
8
9
10
11
Tables
|
+-- Columns
|
+-- Data types
|
+-- Primary keys
|
+-- Foreign keys
|
+-- Relationships
For example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Customer
---------
CustomerId
FirstName
LastName
Email
Country
...

Invoice
-------
InvoiceId
CustomerId
InvoiceDate
Total
The relationship is:
1
2
3
4
5
Customer.CustomerId
|
| 1:N
|
Invoice.CustomerId
This information is critical when generating SQL involving multiple tables.

Why Schema Information Matters

Consider the question:
1
Which customers spent the most money?
Without schema information, the model has no reliable way of knowing:
  • Where customers are stored
  • Where purchases are stored
  • Which column connects the tables
  • Which column represents the purchase amount
With the schema available, the problem becomes much easier.
The model can reason about the relationship:
1
2
3
4
5
6
7
8
9
Customer
|
| CustomerId
v
Invoice
|
| Total
v
Spending
This is one of the most important concepts in Text-to-SQL systems:
Good SQL generation depends heavily on good schema context.

Step 2: Providing Schema Context

The schema can be represented as structured text and provided to the query-generation component.
A simplified representation could look like:
1
2
3
4
5
6
7
8
9
10
11
12
TABLE Customer
- CustomerId INTEGER PRIMARY KEY
- FirstName TEXT
- LastName TEXT
- Email TEXT
- Country TEXT

TABLE Invoice
- InvoiceId INTEGER PRIMARY KEY
- CustomerId INTEGER
- InvoiceDate DATETIME
- Total NUMERIC
The model can then use this information when generating SQL.
The natural-language question:
1
Who are the top 5 customers by spending?
is combined with the schema information.
Conceptually:
1
2
3
4
5
6
7
8
System Prompt
+
Database Schema
+
User Question
|
v
SQL Generation

Step 3: Generating SQL

The next stage is converting the user's question into SQL.
For example:
1
2
3
Question:

Who are the top 5 customers by spending?
The generated query could be:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
c.FirstName,
c.LastName,
SUM(i.Total) AS TotalSpent
FROM Customer c
JOIN Invoice i
ON c.CustomerId = i.CustomerId
GROUP BY
c.CustomerId,
c.FirstName,
c.LastName
ORDER BY
TotalSpent DESC
LIMIT 5;
Notice that the system needs to understand several things:
  1. Customers are represented by the Customer table.
  2. Purchases are represented by Invoice.
  3. CustomerId connects the two tables.
  4. Spending can be calculated using Invoice.Total.
  5. The results need to be aggregated.
  6. The results need to be sorted.
  7. Only five records should be returned.
This is much more than simply replacing words with SQL keywords.

Step 4: Executing the Query

After SQL generation, the query is sent to the database.
The execution pipeline is:
1
2
3
4
5
6
7
8
9
10
11
12
13
Natural Language
|
v
Generated SQL
|
v
SQLite Database
|
v
Rows
|
v
Formatted Result
For example, a generated query might return:
1
2
3
4
5
6
7
Customer Total Spending
-------------------------------------
Customer A $...
Customer B $...
Customer C $...
Customer D $...
Customer E $...
The important thing is that the SQL is generated dynamically based on the user's question.

Testing Different Queries

One of the useful parts of the project was testing many different questions against the same database.
For example:
1
Who are the top 5 customers by spending?
This requires aggregation and ordering.
Another question:
1
Which artist has the most tracks?
requires counting tracks grouped by artist.
A possible SQL structure is:
1
2
3
4
5
6
7
8
9
10
11
SELECT
a.Name,
COUNT(t.TrackId) AS TrackCount
FROM Artist a
JOIN Album al
ON a.ArtistId = al.ArtistId
JOIN Track t
ON al.AlbumId = t.AlbumId
GROUP BY a.ArtistId
ORDER BY TrackCount DESC
LIMIT 1;
This demonstrates why schema understanding is so important.
The system needs to understand that:
1
2
3
4
5
6
7
Artist
|
v
Album
|
v
Track
is the path connecting artists to tracks.

Handling More Complex Questions

Simple questions are not enough to evaluate a Text-to-SQL system.
I also tested questions that require:
  • Multiple joins
  • Aggregation
  • Filtering
  • Sorting
  • Limits
  • Grouping
  • Nested conditions
For example:
1
Which customers from a specific country spent more than a certain amount?
This requires the system to combine filtering with aggregation.
Another example:
1
Which artists have the largest number of tracks?
requires several joins and a GROUP BY.
These tests helped expose the difference between generating SQL that looks correct and generating SQL that is actually executable and semantically correct.

The SQL Generation Challenge

One of the biggest challenges in Text-to-SQL is ambiguity.
Consider:
1
Show me the best customers.
What does "best" mean?
It could mean:
  • Highest spending
  • Most purchases
  • Most recent activity
  • Longest customer relationship
There is no single correct SQL query without additional context.
A better question would be:
1
Show me the top 5 customers by total spending.
Now the intent is much clearer.
This demonstrates an important limitation of natural-language interfaces:
Natural language can be ambiguous even when SQL is precise.

Another Challenge: Hallucinated Columns

An LLM may sometimes generate a column that does not exist.
For example:
1
2
SELECT customer_name
FROM Customer;
when the actual database contains:
1
2
FirstName
LastName
instead.
Schema grounding significantly reduces this problem, but it does not guarantee that generated SQL will always be correct.
This is why SQL validation and error handling are important.

SQL Validation

A production-quality Text-to-SQL system should not blindly execute generated SQL.
A safer pipeline is:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
User Question
|
v
Generate SQL
|
v
Validate SQL
|
+---- Invalid ---> Regenerate / Explain Error
|
v
Execute
|
v
Return Results
Validation can check things such as:
  • Referenced tables exist
  • Referenced columns exist
  • SQL syntax is valid
  • Only permitted operations are used
  • Query complexity is acceptable
For a read-only analytics system, it is also sensible to restrict operations to queries such as:
1
SELECT
and reject destructive statements such as:
1
2
3
4
5
DROP
DELETE
UPDATE
INSERT
ALTER
This becomes especially important when the system is connected to a real production database.

Read-Only Access

One of the simplest security improvements is to use a database account with read-only permissions.
The architecture becomes:
1
2
3
4
5
6
7
Text-to-SQL Application
|
v
Read-only DB User
|
v
Database
Even if the generated SQL is incorrect, the database credentials should not have permission to modify or destroy data.
This follows a fundamental security principle:
Give a component only the permissions it actually needs.

Error Handling

Generated SQL can fail for many reasons.
For example:
1
2
3
4
5
6
SQL syntax error
Unknown column
Unknown table
Incorrect join
Invalid aggregation
Database timeout
Instead of exposing raw database errors to users, the application can capture the error and either regenerate the query or provide a useful explanation.
A possible flow is:
1
2
3
4
5
6
7
8
9
10
11
Generate SQL
|
v
Execute
|
Error?
/ \
 Yes No
| |
v v
Retry Result
A retry mechanism can provide the database error as feedback to the SQL-generation component.
For example:
1
2
3
4
5
Generated SQL:
SELECT customer_name FROM Customer;

Database:
no such column: customer_name
The system can then use the error to produce a corrected query based on the actual schema.

What I Learned

This project taught me that Text-to-SQL is not simply an LLM prompt engineering problem.
A reliable system requires several components working together:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
+----------------+
| User Question |
+-------+--------+
|
v
+---------------+
| Schema Context|
+-------+-------+
|
v
+---------------+
| SQL Generator |
+-------+-------+
|
v
+---------------+
| SQL Validator |
+-------+-------+
|
v
+---------------+
| Database |
+-------+-------+
|
v
+---------------+
| Results |
+---------------+
The quality of the final answer depends on every stage.
A powerful model cannot compensate for a poorly understood schema.
Similarly, a correct SQL query is not enough if the application executes it with unsafe database permissions.

Improving the System

There are several ways I would improve this project for production use.

1. Better Schema Retrieval

Instead of sending the entire database schema with every request, the system could retrieve only the tables and columns relevant to the user's question.
For example:
1
2
3
4
5
6
7
8
9
10
User Question
|
v
Identify relevant entities
|
v
Retrieve relevant schema
|
v
Generate SQL
This reduces unnecessary context and can improve generation quality.

2. Query Validation

A dedicated SQL validation layer could inspect generated queries before execution.
This would prevent unsafe or unsupported operations.

3. Query Result Verification

The system could check whether the generated query actually answers the user's question.
For example, if the user asks:
1
Who is the highest-spending customer?
the generated query should produce a customer and a spending value, rather than unrelated information.

4. Query Caching

Frequently requested queries could be cached.
This could reduce database load and improve response time.

5. Observability

A production system should log useful metadata such as:
1
2
3
4
5
6
Request ID
Question
Generated SQL
Execution time
Database errors
Result size
Sensitive information should be excluded from logs where necessary.

Taking the Project to AWS

The project can also be evolved into a cloud-based service.
A possible AWS architecture could look like:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
User
|
v
 Amazon CloudFront
|
v
 Application/API
|
v
 Text-to-SQL Service
|
+------------+------------+
| |
v v
 LLM/AI Service Database
|
v
 Query Results
The application could be containerized and deployed using services such as Amazon ECS or Amazon EKS.
Database workloads could be moved to an appropriate managed database service depending on the production requirements.
Secrets and credentials should be managed using AWS-native secret-management solutions rather than being embedded in application code.
The important point is that the AI component should remain separated from the database security boundary.
The model generates a query.
The application validates it.
Only then should the database execute it.

Future Direction

The next evolution of this project would be to make it a complete analytics assistant.
Instead of returning only SQL results, the system could provide:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
User Question
|
v
Understand Intent
|
v
Retrieve Schema
|
v
Generate SQL
|
v
Validate
|
v
Execute
|
v
Analyze Results
|
v
Natural Language Answer
For example:
1
2
3
4
5
User:
Who are the top 5 customers by spending?

System:
The five highest-spending customers are...
The system could also generate charts when appropriate.
That would turn the project from a simple Text-to-SQL system into a natural-language analytics platform.

Conclusion

Building this Text-to-SQL project showed me how different areas of software engineering can come together.
The project combines:
  • Python
  • SQL
  • Relational databases
  • Schema introspection
  • Natural-language processing
  • LLM-based generation
  • Query validation
  • Error handling
  • Security considerations
The most important lesson was that generating SQL is only one part of the problem.
A useful Text-to-SQL system needs to understand the database, generate queries grounded in the schema, validate those queries, execute them safely, and present the results in a way that makes sense to the user.
The project started with a simple goal:
1
Natural language → SQL
but the real engineering challenge is building a reliable pipeline:
1
2
3
4
5
6
7
8
9
10
11
Natural language
↓
Schema understanding
↓
SQL generation
↓
Validation
↓
Safe execution
↓
Useful results
That pipeline is what turns an LLM-powered SQL demo into a practical data application.
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article