dasini.net – Diary of a MySQL expert

The ability to query data efficiently has always been central to unlocking insights, but writing SQL can be a barrier for many users who aren’t fluent in the language of databases. Analysts, product managers, and business users often know the questions they want to ask—just not how to express them in SQL. With the rise of large language models (LLMs) and advancements in database technology, that gap is closing quickly.

MySQL HeatWave now brings Natural Language to SQL (NL2SQL) capabilities directly into the database engine, allowing users to generate SQL queries from plain English statements. Instead of wrestling with complex joins, filters, or aggregate functions, users can simply type a natural-language request—such as “Show me the top 10 products by revenue this quarter”—and HeatWave automatically translates it into an optimized SQL query.

In this article, we’ll explore how to leverage LLM-powered NL2SQL in MySQL HeatWave, walk through practical examples, and show how this feature empowers both technical and non-technical users to interact with data more intuitively.

Context

I’m using MySQL HeatWave 9.4.1 on Oracle Cloud Infrastructure (OCI):

SQL> 
-- HeatWave MySQL server version
SHOW VARIABLES WHERE Variable_name IN ('version_comment', 'version');
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| version         | 9.4.1-cloud              |
| version_comment | MySQL Enterprise - Cloud |
+-----------------+--------------------------+

Furthermore, I’m using a modified version of the well-known World database. Although the data hasn’t been updated in some time, it still serves as a useful tool for visualizing the results of SQL queries.

Here the schema:

CREATE TABLE `country` (
  `code` char(3) NOT NULL DEFAULT '' COMMENT 'Primary key: three-letter country code (ISO standard).',
  `country_name` char(52) NOT NULL DEFAULT '' COMMENT 'Official name of the country.',
  `continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia' COMMENT 'Continent where the country is located.',
  `region` char(26) NOT NULL DEFAULT '' COMMENT 'Geographical region within the continent.',
  `surface_area` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Total surface area of the country in square kilometers.',
  `independance_year` smallint DEFAULT NULL COMMENT 'Year the country achieved independence (NULL if unknown).',
  `country_population` int NOT NULL DEFAULT '0' COMMENT 'Total population of the country.',
  `life_expectancy` decimal(3,1) DEFAULT NULL COMMENT 'Average life expectancy of the population in years.',
  `GNP` decimal(10,2) DEFAULT NULL COMMENT 'Gross National Product of the country in millions of USD.',
  `GNPOld` decimal(10,2) DEFAULT NULL COMMENT 'Gross National Product in an earlier year for comparison.',
  `local_name` char(45) NOT NULL DEFAULT '' COMMENT 'The country’s name in its local language.',
  `government_form` char(45) NOT NULL DEFAULT '' COMMENT 'Description of the form of government (e.g., Republic, Monarchy).',
  `head_of_state` char(60) DEFAULT NULL COMMENT 'Name of the current head of state (e.g., President, Monarch).',
  `ID_capital` int DEFAULT NULL COMMENT 'ID of the capital city (foreign key reference to city table).',
  `code2` char(2) NOT NULL DEFAULT '' COMMENT 'Two-letter country code (ISO standard).',
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores detailed information about countries, including identifiers, demographics, economy, and government structure.'
;


CREATE TABLE `country_language` (
  `country_code` char(3) NOT NULL DEFAULT '' COMMENT 'Three-letter country code referencing the country table. First part of the composite primary key.',
  `language` char(30) NOT NULL DEFAULT '' COMMENT 'Name of the language spoken in the country. Second part of the composite primary key.',
  `is_official` enum('T','F') NOT NULL DEFAULT 'F' COMMENT 'Indicates whether the language is an official language of the country (T = true, F = false).',
  `percentage` decimal(4,1) NOT NULL DEFAULT '0.0' COMMENT 'Percentage of the country’s population that speaks this language.',
  PRIMARY KEY (`country_code`,`language`),
  KEY `country_code` (`country_code`),
  CONSTRAINT `country_language_ibfk_1` FOREIGN KEY (`country_code`) REFERENCES `country` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about the languages spoken in each country, including whether they are official and the share of the population using them.'
;


CREATE TABLE `city` (
  `ID_city` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key: integer that uniquely identifies each city.',
  `city_name` char(35) NOT NULL DEFAULT '' COMMENT 'Name of the city.',
  `country_code` char(3) NOT NULL DEFAULT '' COMMENT 'Three-letter country code referencing the country table.',
  `district` char(20) NOT NULL DEFAULT '' COMMENT 'District or administrative region where the city is located.',
  `city_population` int NOT NULL DEFAULT '0' COMMENT 'Population count of the city.',
  PRIMARY KEY (`ID_city`),
  KEY `country_code` (`country_code`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_code`) REFERENCES `country` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about cities, including their name, country, district, and population.'
;

As you can see, the main difference from the original version is that I embedded the documentation directly into the tables using COMMENT clauses. In addition, some column names have been renamed. The goal is to capture as much relevant information as possible to provide business context. This contextual information will then be used to augment the LLM.

Let’s run 3 queries on the data set:

MySQL>
-- Find the name, country code and the district of the city with ID 2989

SELECT city_name, country_code, district 
FROM city 
WHERE ID_city = 2989;
+-----------+--------------+-------------+
| city_name | country_code | district    |
+-----------+--------------+-------------+
| Grenoble  | FRA          | Rhône-Alpes |
+-----------+--------------+-------------+
 
 
-- Find the government form of Germany

SELECT government_form
FROM country
WHERE code = 'FRA';
+-----------------+
| government_form |
+-----------------+
| Republic        |
+-----------------+


-- List all official languages spoken in Canada

SELECT language
FROM country_language
WHERE country_code = 'CAN' AND is_official = 'T';
+----------+
| language |
+----------+
| English  |
| French   |
+----------+

These queries are fairly simple, but they still require some SQL skills. What if you could write in plain English and have MySQL HeatWave understand you?
This is now possible with the NL_SQL routine, which generates and executes SQL queries directly from natural language statements.

Generate SQL Queries From Natural-Language Statements

Starting with MySQL HeatWave 9.4.1, MySQL HeatWave GenAI (on OCI, AWS, and Azure) allows you with the NL_SQL stored procedure, to generate SQL queries directly from natural language, making it easier to interact with your databases.

The feature gathers information about the schemas, tables, and columns you have access to, then leverages a Large Language Model to generate an appropriate SQL query based on your request. You can also execute the generated query and view the results instantly.

Using nl_sql is quite simple:

CALL sys.NL_SQL("NaturalLanguageStatement",@output, JSON_OBJECT('schemas',JSON_ARRAY('DBName'),'model_id','ModelID'));

Replace the following:

• NaturalLanguageStatement: natural-language statement. It can be a question, statement, or query pertaining to your data available in MySQL HeatWave.
• DBName: database to consider for generating and running the SQL query.
• ModelID: LLM to use.

You can find additional information here.

Also as of MySQL HeatWave 9.3.2, you can view the list of available Large Language Models and embedding models directly from the database using the sys.ML_SUPPORTED_LLMS view. This enhancement, part of the MySQL HeatWave GenAI feature, allows you to stay current with the available models, including in-database models and those from external services like Oracle Cloud Infrastructure (OCI) Generative AI.

To display the list of supported content generation LLMs and their provider, you can use the following simple query:

MySQL> 
SELECT model_id, provider 
FROM sys.ML_SUPPORTED_LLMS 
WHERE capabilities LIKE '["GENERATION"]';
+-------------------------------+---------------------------+
| model_id                      | provider                  |
+-------------------------------+---------------------------+
| llama2-7b-v1                  | HeatWave                  |
| llama3-8b-instruct-v1         | HeatWave                  |
| llama3.1-8b-instruct-v1       | HeatWave                  |
| llama3.2-1b-instruct-v1       | HeatWave                  |
| llama3.2-3b-instruct-v1       | HeatWave                  |
| mistral-7b-instruct-v1        | HeatWave                  |
| mistral-7b-instruct-v3        | HeatWave                  |
| cohere.command-latest         | OCI Generative AI Service |
| cohere.command-plus-latest    | OCI Generative AI Service |
| cohere.command-a-03-2025      | OCI Generative AI Service |
| meta.llama-3.3-70b-instruct   | OCI Generative AI Service |
| cohere.command-r-08-2024      | OCI Generative AI Service |
| cohere.command-r-plus-08-2024 | OCI Generative AI Service |
+-------------------------------+---------------------------+

We can see here, the in-MySQL HeatWave models (provider HeatWave) as well as Oracle Cloud Infrastructure Generative AI service models (provider OCI Generative AI Service).

To view or use the OCI Generative AI Service models in this list, you need to enable the database system to access OCI services. For more information, see Authenticate OCI Generative AI Service.

When using the sys.NL_SQL routine in MySQL HeatWave, you have several options for the underlying (LLM) that translates your natural language queries into SQL.
The available models are part of the MySQL HeatWave GenAI feature. You can select from in-database models or, like we have seen above, if your environment is configured for it, models from the Oracle Cloud Infrastructure (OCI) Generative AI Service.

In MySQL HeatWave 9.4.1, some of the available models for natural language to SQL tasks include:

• meta.llama-3.3-70b-instruct (OCI Generative AI Service)
• llama3.1-8b-instruct-v1 (In-database HeatWave)
• llama3.2-3b-instruct-v1 (In-database HeatWave)

The specific models available may vary by MySQL HeatWave version and the cloud service region you are using. To get the most current list, don’t forget that you can always query the sys.ML_SUPPORTED_LLMS view.

What Can I Ask My Database? Example Queries

Now, let’s see how it works in action. We’ll start with our earlier queries and then try out a few new ones.

Find the name, country code and the district of the city with ID 2989:

MySQL> SET @nlq = "Find the name, country code and the district of the city with ID 2989";
Query OK, 0 rows affected (0.0004 sec)

MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "llama3.2-3b-instruct-v1", "schemas":["nl2sql_world"]}');
+--------------------------------------------------------------------------------------------------+
| Executing generated SQL statement...                                                             |
+--------------------------------------------------------------------------------------------------+
| SELECT `city_name`, `country_code`, `district` FROM `nl2sql_world`.`city` WHERE `ID_city` = 2989 |
+--------------------------------------------------------------------------------------------------+
1 row in set (3.7089 sec)

+-----------+--------------+-------------+
| city_name | country_code | district    |
+-----------+--------------+-------------+
| Grenoble  | FRA          | Rhône-Alpes |
+-----------+--------------+-------------+
1 row in set (3.7089 sec)

Find the government form of Germany:

MySQL> SET @nlq = "Find the government form of Germany";
Query OK, 0 rows affected (0.0003 sec)

MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "llama3.1-8b-instruct-v1", "schemas":["nl2sql_world"]}');
+-----------------------------------------------------------------------------+
| Executing generated SQL statement...                                        |
+-----------------------------------------------------------------------------+
| SELECT `government_form` FROM `nl2sql_world`.`country` WHERE `code` = 'DEU' |
+-----------------------------------------------------------------------------+
1 row in set (4.0998 sec)

+------------------+
| government_form  |
+------------------+
| Federal Republic |
+------------------+
1 row in set (4.0998 sec)

List all official languages spoken in Canada:

MySQL> SET @nlq = "Find the top 5 most populated cities in the world";
Query OK, 0 rows affected (0.0002 sec)

MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world"]}');
+----------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement...                                                                     |
+----------------------------------------------------------------------------------------------------------+
| SELECT `city_name`, `city_population` FROM `nl2sql_world`.`city` ORDER BY `city_population` DESC LIMIT 5 |
+----------------------------------------------------------------------------------------------------------+
1 row in set (2.6276 sec)

+-----------------+-----------------+
| city_name       | city_population |
+-----------------+-----------------+
| Mumbai (Bombay) |        10500000 |
| Seoul           |         9981619 |
| São Paulo       |         9968485 |
| Shanghai        |         9696300 |
| Jakarta         |         9604900 |
+-----------------+-----------------+
5 rows in set (2.6276 sec)

Find the top 10 most multilingual countries (by number of languages spoken):

MySQL> SET @nlq = "Find the top 10 most multilingual countries (by number of languages spoken)";
Query OK, 0 rows affected (0.0004 sec)

MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world"]}');
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement...                                                                                                                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT `T1`.`country_name`, COUNT(`T2`.`language`) AS `num_languages` FROM `nl2sql_world`.`country` AS `T1` JOIN `nl2sql_world`.`country_language` AS `T2` ON `T1`.`code` = `T2`.`country_code` GROUP BY `T1`.`code` ORDER BY `num_languages` DESC LIMIT 10 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (5.8831 sec)

+--------------------+---------------+
| country_name       | num_languages |
+--------------------+---------------+
| United States      |            12 |
| China              |            12 |
| India              |            12 |
| Canada             |            12 |
| Russian Federation |            12 |
| Tanzania           |            11 |
| South Africa       |            11 |
| Philippines        |            10 |
| Iran               |            10 |
| Kenya              |            10 |
+--------------------+---------------+
10 rows in set (5.8831 sec)

List all official languages spoken in Canada:

MySQL> SET @nlq = "List all official languages spoken in Canada";
Query OK, 0 rows affected (0.0003 sec)

MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "llama3.2-3b-instruct-v1", "schemas":["nl2sql_world"]}');
+------------------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement...                                                                             |
+------------------------------------------------------------------------------------------------------------------+
| SELECT `language` FROM `nl2sql_world`.`country_language` WHERE `is_official` = 'T' AND `country_code` IN ('CAN') |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (3.0730 sec)

+----------+
| language |
+----------+
| English  |
| French   |
+----------+
2 rows in set (3.0730 sec)

Pretty impressive, isn’t it? 🙂

Click the picture to enlarge

A word of caution: Natural Language to SQL is not foolproof. The LLM can make mistakes and may generate an incorrect query. Additionally, while there are no guarantees, a larger number of model parameters (the b in 70b) typically leads to better results.

The next section will discuss key considerations for ensuring optimal performance and accuracy when using the sys.NL_SQL routine.

Getting the Best Results from Natural Language to SQL

Best Practices for Optimal Results

To get the most out of the NL_SQL feature, follow these best practices for generating accurate and efficient queries:

• Provide Specific Context: Help the LLM by restricting its focus. Use the schemas and tables parameters to specify only the relevant schemas or tables for your query.
• Use Descriptive Names: Ensure your tables, columns, and views have clear, semantically meaningful names. Using descriptive views is especially helpful for improving the accuracy of complex JOIN operations.
• Be Specific with Values: To avoid errors, provide exact values in your natural language input, rather than vague descriptions.

Important Limitations and Considerations

While powerful, the NL_SQL feature does have a few limitations to keep in mind:

• Query Accuracy: The generated SQL may not always be perfectly valid or optimal. Always review the output before executing it.
• Performance: Generated queries can sometimes be complex, leading to unpredictable execution times and potentially large result sets that consume excessive resources.
• Stateless Operations: The feature does not maintain the state of previous calls. Each invocation is independent and does not learn from prior interactions.
• Metadata Volume: Accuracy can decrease if the database contains a large amount of metadata, as this can confuse the LLM.
• Unsupported Features: The NL_SQL routine does not support temporary tables.

Peroraison

With the Natural Language to SQL feature in MySQL HeatWave, we’re witnessing a major shift in how we interact with databases. This technology empowers a broader range of users—from business analysts to product managers—to access and analyze data without needing deep SQL expertise. By simply using plain English, anyone can now translate a business question into an executable SQL query. This not only democratizes data access but also significantly accelerates the time from question to insight.

The sys.NL_SQL routine, backed by powerful LLMs, is a testament to the seamless integration of generative AI into core database services. While it’s not a silver bullet—and requires careful consideration of best practices like providing context and using descriptive names—it marks a fundamental step toward making data more accessible and intuitive.

By providing a bridge between human language and database queries, MySQL HeatWave is not just helping us write code; it’s enabling us to ask our databases anything, paving the way for a more natural and productive relationship with our data.

With the sys.NL_SQL routine, asking questions of your database has never been more natural.

Resources

• Introducing Natural Language to SQL for MySQL HeatWave
• Generate SQL Queries From Natural-Language Statements
• NL_SQL
• MySQL HeatWave GenAI Supported Models and Languages

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
AI, Artificial Intelligence, HeatWave, MySQL by Olivier DASINI