The Mastra Text-to-SQL demo is an interactive web app that generates and executes SQL queries based on natural language prompts.
As it’s written, it specializes in answering questions about various global city populations on a sample database. You could easily repurpose the example for different use cases, however.
Here’s a look at how it works:
Agent
Text-to-SQL is based off a single agent: a SQL expert for a city population database.
The agent is pre-defined to already understand the database schema, query guidelines key SQL formatting tips, and what its basic workflow is (analyze the user's question about city data, generate an appropriate SQL query, execute the query, and return results).
1import { openai } from "@ai-sdk/openai";
2import { Agent } from "@mastra/core/agent";
3import * as tools from "../tools/population-info";
4import { LanguageModelV1 } from "@ai-sdk/provider";
5
6export const sqlAgent = new Agent({
7 name: "SQL Agent",
8 instructions: ``You are a SQL (PostgreSQL) expert for a city population database. Generate and execute queries that answer user questions about city data.
9
10 DATABASE SCHEMA:
11 cities (
12 id SERIAL PRIMARY KEY,
13 popularity INTEGER,
14 geoname_id INTEGER,
15 name_en VARCHAR(255),
16 country_code VARCHAR(10),
17 population BIGINT,
18 latitude DECIMAL(10, 6),
19 longitude DECIMAL(10, 6),
20 country VARCHAR(255),
21 region VARCHAR(255),
22 continent VARCHAR(255), /* Africa, Asia, Europe, North America, Oceania, South America, Antarctica */
23 code2 VARCHAR(10),
24 code VARCHAR(10),
25 province VARCHAR(255)
26 );
27
28 QUERY GUIDELINES:
29 - Only retrieval queries are allowed
30 - For string comparisons, use: LOWER(field) ILIKE LOWER(term)
31 - Use "United Kingdom" for UK and "United States" for USA
32 - This dataset contains only current information, not historical data
33 - Always return at least two columns for visualization purposes
34 - If a user asks for a single column, include a count of that column
35 - Format rates as decimals (e.g., 0.1 for 10%)
36
37 Key SQL formatting tips:
38 - Start main clauses (SELECT, FROM, WHERE, etc.) on new lines
39 - Indent subqueries and complex conditions
40 - Align related items (like column lists) for readability
41 - Put each JOIN on a new line
42 - Use consistent capitalization for SQL keywords
43
44 WORKFLOW:
45 1. Analyze the user\'s question about city data
46 2. Generate an appropriate SQL query
47 3. Execute the query using the Execute SQL Query tool
48 4. Return results in markdown format with these sections:
49
50 ### SQL Query
51 \`\`\`sql
52 [The executed SQL query with proper formatting and line breaks for readability]
53 \`\`\`
54
55 ### Explanation
56 [Clear explanation of what the query does]
57
58 ### Results
59 [Query results in table format]
60 ``,
61 model: openai("gpt-4o") as LanguageModelV1,
62 tools: {
63 populationInfo: tools.populationInfo,
64 },
65);Tools
Once the agent generates an appropriate SQL query, it uses a tool to execute it.
The populationInfo tool is a PostgreSQL database query executor specifically designed for the cities database:
1
2export const populationInfo = createTool({
3 id: "Execute SQL Query",
4 inputSchema: z.object({
5 query: z
6 .string()
7 .describe("SQL query to execute against the cities database"),
8 }),
9 description: `Executes a SQL query against the cities database and returns the results`,
10 execute: async ({ context: { query } }) => {
11 try {
12 const trimmedQuery = query.trim().toLowerCase();
13 if (!trimmedQuery.startsWith("select")) {
14 throw new Error("Only SELECT queries are allowed for security reasons");
15 }
16
17 return await executeQuery(query);
18 } catch (error) {
19 throw new Error(
20 `Failed to execute SQL query: ${error instanceof Error ? error.message : String(error)}`
21 );
22 }
23 },
24});The tool only allows SELECT queries to prevent malicious database modifications. This is enforced through a simple check of the query's starting text.
The tool also implements basic error handling at multiple levels. Find the complete code and follow along here.
And be sure to tell us if you build any new text-to-SQL applications yourself.
Happy building 🚀