{"id":8285,"date":"2025-09-30T08:32:52","date_gmt":"2025-09-30T07:32:52","guid":{"rendered":"https:\/\/dasini.net\/blog\/?p=8285"},"modified":"2025-10-07T09:01:06","modified_gmt":"2025-10-07T08:01:06","slug":"ask-your-database-anything-natural-language-to-sql-in-mysql-heatwave","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2025\/09\/30\/ask-your-database-anything-natural-language-to-sql-in-mysql-heatwave\/","title":{"rendered":"Ask Your Database Anything: Natural Language to SQL in MySQL HeatWave"},"content":{"rendered":"\n

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\u2019t fluent in the language of databases. Analysts, product managers, and business users often know the questions they want to ask\u2014just 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.<\/p>\n\n\n\n

MySQL HeatWave<\/strong> now brings Natural Language to SQL (NL2SQL)<\/strong> 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\u2014such as \u201cShow me the top 10 products by revenue this quarter\u201d<\/em>\u2014and HeatWave automatically translates it into an SQL query.<\/p>\n\n\n\n

In this article, we\u2019ll explore how to leverage LLM-powered NL2SQL in MySQL HeatWave<\/strong>, walk through practical examples, and show how this feature empowers both technical and non-technical users to interact with data more intuitively.<\/p>\n\n\n

\n
\"\"\n\t\t\t\n\t\t\t\t\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n<\/div>\n\n\n
<\/div>\n\n\n\n

Context<\/h2>\n\n\n\n

I’m using MySQL HeatWave<\/strong><\/a> 9.4.1<\/strong> on Oracle Cloud Infrastructure<\/a> (OCI):<\/p>\n\n\n\n

SQL> \n-- HeatWave MySQL server version\nSHOW VARIABLES WHERE Variable_name IN ('version_comment', 'version');\n+-----------------+--------------------------+\n| Variable_name   | Value                    |\n+-----------------+--------------------------+\n| version         | 9.4.1-cloud              |\n| version_comment | MySQL Enterprise - Cloud |\n+-----------------+--------------------------+\n<\/code><\/pre>\n\n\n\n
<\/div>\n\n\n\n
Furthermore, I’m using a modified version (available on my GitHub account<\/a>) of the well-known World database<\/strong><\/em><\/a>. Although the data hasn\u2019t been updated in some time, it still serves as a useful tool for visualizing the results of SQL queries.<\/p>\n\n\n\n
Here the schema:<\/p>\n\n\n\n
CREATE TABLE `country` (\n  `code` char(3) NOT NULL DEFAULT '' COMMENT 'Primary key: three-letter country code (ISO standard).',\n  `country_name` char(52) NOT NULL DEFAULT '' COMMENT 'Official name of the country.',\n  `continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia' COMMENT 'Continent where the country is located.',\n  `region` char(26) NOT NULL DEFAULT '' COMMENT 'Geographical region within the continent.',\n  `surface_area` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Total surface area of the country in square kilometers.',\n  `independance_year` smallint DEFAULT NULL COMMENT 'Year the country achieved independence (NULL if unknown).',\n  `country_population` int NOT NULL DEFAULT '0' COMMENT 'Total population of the country.',\n  `life_expectancy` decimal(3,1) DEFAULT NULL COMMENT 'Average life expectancy of the population in years.',\n  `GNP` decimal(10,2) DEFAULT NULL COMMENT 'Gross National Product of the country in millions of USD.',\n  `GNPOld` decimal(10,2) DEFAULT NULL COMMENT 'Gross National Product in an earlier year for comparison.',\n  `local_name` char(45) NOT NULL DEFAULT '' COMMENT 'The country\u2019s name in its local language.',\n  `government_form` char(45) NOT NULL DEFAULT '' COMMENT 'Description of the form of government (e.g., Republic, Monarchy).',\n  `head_of_state` char(60) DEFAULT NULL COMMENT 'Name of the current head of state (e.g., President, Monarch).',\n  `ID_capital` int DEFAULT NULL COMMENT 'ID of the capital city (foreign key reference to city table).',\n  `code2` char(2) NOT NULL DEFAULT '' COMMENT 'Two-letter country code (ISO standard).',\n  PRIMARY KEY (`code`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores detailed information about countries, including identifiers, demographics, economy, and government structure.'\n;\n\n\nCREATE TABLE `country_language` (\n  `country_code` char(3) NOT NULL DEFAULT '' COMMENT 'Three-letter country code referencing the country table. First part of the composite primary key.',\n  `language` char(30) NOT NULL DEFAULT '' COMMENT 'Name of the language spoken in the country. Second part of the composite primary key.',\n  `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).',\n  `percentage` decimal(4,1) NOT NULL DEFAULT '0.0' COMMENT 'Percentage of the country\u2019s population that speaks this language.',\n  PRIMARY KEY (`country_code`,`language`),\n  KEY `country_code` (`country_code`),\n  CONSTRAINT `country_language_ibfk_1` FOREIGN KEY (`country_code`) REFERENCES `country` (`code`)\n) 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.'\n;\n\n\nCREATE TABLE `city` (\n  `ID_city` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key: integer that uniquely identifies each city.',\n  `city_name` char(35) NOT NULL DEFAULT '' COMMENT 'Name of the city.',\n  `country_code` char(3) NOT NULL DEFAULT '' COMMENT 'Three-letter country code referencing the country table.',\n  `district` char(20) NOT NULL DEFAULT '' COMMENT 'District or administrative region where the city is located.',\n  `city_population` int NOT NULL DEFAULT '0' COMMENT 'Population count of the city.',\n  PRIMARY KEY (`ID_city`),\n  KEY `country_code` (`country_code`),\n  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_code`) REFERENCES `country` (`code`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about cities, including their name, country, district, and population.'\n;<\/code><\/pre>\n\n\n\n
As you can see, the main difference from the original version is that I embedded the documentation directly into the tables using COMMENT<\/code><\/em> 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<\/strong><\/em> the LLM.<\/p>\n\n\n\n
Let’s run 3 queries on the data set:<\/p>\n\n\n\n
MySQL>\n-- Find the name, country code and the district of the city with ID 2989\n\nSELECT city_name, country_code, district \nFROM city \nWHERE ID_city = 2989;\n+-----------+--------------+-------------+\n| city_name | country_code | district    |\n+-----------+--------------+-------------+\n| Grenoble  | FRA          | Rh\u00f4ne-Alpes |\n+-----------+--------------+-------------+\n \n \n-- Find the government form of Germany\n\nSELECT government_form\nFROM country\nWHERE code = 'FRA';\n+-----------------+\n| government_form |\n+-----------------+\n| Republic        |\n+-----------------+\n\n\n-- List all official languages spoken in Canada\n\nSELECT language\nFROM country_language\nWHERE country_code = 'CAN' AND is_official = 'T';\n+----------+\n| language |\n+----------+\n| English  |\n| French   |\n+----------+<\/code><\/pre>\n\n\n\n
<\/div>\n\n\n\n
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<\/a><\/strong><\/em><\/code> routine, which generates and executes SQL queries directly from natural language statements.<\/p>\n\n\n\n
<\/div>\n\n\n\n
Generate SQL Queries From Natural-Language Statements<\/h2>\n\n\n\n
Starting with MySQL HeatWave 9.4.1<\/strong>, MySQL HeatWave GenAI<\/a> (on OCI<\/a>, AWS<\/a>, and Azure<\/a>) allows you with the NL_SQL<\/a><\/strong><\/em><\/code> stored procedure, to generate SQL queries directly from natural language<\/strong>, making it easier to interact with your databases. <\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Using nl_sql is quite simple:<\/p>\n\n\n\n
CALL sys.NL_SQL(\"NaturalLanguageStatement<\/em>\",@output<\/em><\/strong>, JSON_OBJECT('schemas',JSON_ARRAY('DBName<\/em>'),'model_id','ModelID<\/em>'));<\/code><\/pre>\n\n\n\n
Replace the following:<\/p>\n\n\n\n