PolarDB:Convert natural language to SQL (LLM-based NL2SQL)

Step 1: Set up the extension

1. Log on to the cluster using a privileged account.

2. Get the node token. Go to the PolarDB console. On the cluster's product page, find the AI node in the Database Nodes section. Click View and record the Node Token.

   

3. Create the polar_ai extension and set the node token. Run the following commands to create the extension and configure the key for accessing the LLM service.

   Important

   Make sure you have set a database connection account for the AI node before proceeding.

   -- Create the extension
   CREATE EXTENSION polar_ai;
   
   -- Set your key
   SELECT polar_ai._ai_nl2sql_alter_token('sk-xxx');

Step 2: Prepare data and add comments

Before building the schema index, add comments to your tables and columns. Good comments are the single most effective way to improve NL2SQL accuracy.

Table comments — Summarize the table's main content in 10 words or fewer, for example: "Orders table" or "Inventory table". Avoid lengthy explanations.

Column comments — Use common nouns or short phrases that accurately describe the column. For columns with a fixed set of values, include the value mapping. For example, for a column named isValid: Is valid. 0: No. 1: Yes.

1. Create sample tables. Run the following SQL to create three tables for the "Alixiang" restaurant system: an outlet table, a menu table, and a bill table. <details><summary>Click to expand the SQL</summary>

   -- Outlet table
   CREATE TABLE restaurant_info (
     id INT PRIMARY KEY,
     position VARCHAR(128)
   );
   COMMENT ON TABLE restaurant_info IS 'Outlet table';
   COMMENT ON COLUMN restaurant_info.id IS 'Outlet ID';
   COMMENT ON COLUMN restaurant_info.position IS 'Outlet location';
   
   -- Menu table
   CREATE TABLE menu_info (
     id INT PRIMARY KEY,
     name VARCHAR(64),
     type INT,
     unit_price float8
   );
   COMMENT ON TABLE menu_info IS 'Menu table';
   COMMENT ON COLUMN menu_info.id IS 'Dish ID';
   COMMENT ON COLUMN menu_info.name IS 'Dish name';
   COMMENT ON COLUMN menu_info.type IS 'Dish type';
   COMMENT ON COLUMN menu_info.unit_price IS 'Dish unit price';
   
   -- Bill table
   CREATE TABLE bill_info (
     id INT PRIMARY KEY,
     items VARCHAR(512),
     actual_amount INT,
     restaurant_id INT,
     waiter VARCHAR(16),
     diner_count INT,
     pay_time DATE
   );
   COMMENT ON TABLE bill_info IS 'Bill table';
   COMMENT ON COLUMN bill_info.id IS 'Bill ID';
   COMMENT ON COLUMN bill_info.items IS 'Ordered dishes';
   COMMENT ON COLUMN bill_info.actual_amount IS 'Actual amount paid';
   COMMENT ON COLUMN bill_info.restaurant_id IS 'Dining outlet';
   COMMENT ON COLUMN bill_info.waiter IS 'Waiter';
   COMMENT ON COLUMN bill_info.diner_count IS 'Number of diners';
   COMMENT ON COLUMN bill_info.pay_time IS 'Order time';
   
   INSERT INTO restaurant_info (id, position) VALUES
   (1, 'South Area, Taikoo Li Sanlitun, Chaoyang District, Beijing'),
   (2, 'Nanjing Road Pedestrian Street, Huangpu District, Shanghai'),
   (3, 'Block B, TeeMall, Tianhe District, Guangzhou'),
   (4, 'Kexing Science Park, Science and Technology Park, Nanshan District, Shenzhen'),
   (5, 'Huanglong Times Square, Wensan Road, Xihu District, Hangzhou'),
   (6, 'IFS International Finance Centre, Chunxi Road, Jinjiang District, Chengdu'),
   (7, 'Jiefangbei Pedestrian Street, Yuzhong District, Chongqing'),
   (8, 'South Square of Giant Wild Goose Pagoda, Yanta District, Xi''an'),
   (9, 'Deji Plaza, Xinjiekou, Xuanwu District, Nanjing'),
   (10, 'Wushang Plaza, Hankou, Jianghan District, Wuhan'),
   (11, 'Binjiang Avenue Commercial Street, Heping District, Tianjin'),
   (12, 'BBG Plaza, Meixi Lake, Yuelu District, Changsha'),
   (13, 'The Mixc, May Fourth Square, Shinan District, Qingdao'),
   (14, 'Parkson, Friendship Square, Zhongshan District, Dalian'),
   (15, 'Zhongshan Road Pedestrian Street, Siming District, Xiamen'),
   (16, 'Times Square, Jinji Lake, Suzhou Industrial Park, Suzhou'),
   (17, 'Tianyi Square, Yinzhou District, Ningbo'),
   (18, 'Nanchang Street, Nanshan Temple, Liangxi District, Wuxi'),
   (19, 'The Mixc, Shushan District, Hefei'),
   (20, 'Wanda Plaza, Honggu Middle Avenue, Honggutan District, Nanchang');
   
   INSERT INTO menu_info (id, name, type, unit_price) VALUES
   (1, 'Kung Pao Chicken', 1, 38.0),
   (2, 'Braised Pork', 1, 48.0),
   (3, 'Mapo Tofu', 1, 22.0),
   (4, 'Steamed Sea Bass', 1, 68.0),
   (5, 'Yuxiang Shredded Pork', 1, 32.0),
   (6, 'Hot and Sour Shredded Potatoes', 2, 16.0),
   (7, 'Hand-torn Cabbage', 2, 18.0),
   (8, 'Stir-fried Water Spinach with Garlic', 2, 15.0),
   (9, 'Scrambled Eggs with Tomatoes', 2, 18.0),
   (10, 'Griddle Cooked Cauliflower', 2, 26.0),
   (11, 'Sichuan Boiled Beef', 1, 58.0),
   (12, 'Twice-cooked Pork', 1, 36.0),
   (13, 'Stir-fried Yellow Beef', 1, 42.0),
   (14, 'Spicy Diced Chicken', 1, 46.0),
   (15, 'Dongpo Pork', 1, 52.0),
   (16, 'Iced Plum Soup', 3, 12.0),
   (17, 'Sago in Coconut Milk', 3, 14.0),
   (18, 'Red Bean Paste Soup', 3, 10.0),
   (19, 'Lemon Honey Water', 3, 16.0),
   (20, 'White Fungus and Lotus Seed Soup', 3, 18.0);
   
   INSERT INTO bill_info (id, items, actual_amount, restaurant_id, waiter, diner_count, pay_time) VALUES
   (1, 'Kung Pao Chicken*1,Mapo Tofu*1,Hot and Sour Shredded Potatoes*1', 92, 1, 'Zhang Li', 4, '2025-03-01'),
   (2, 'Steamed Sea Bass*1,Braised Pork*1,Hand-torn Cabbage*1', 132, 2, 'Wang Qiang', 5, '2025-03-02'),
   (3, 'Yuxiang Shredded Pork*2,Scrambled Eggs with Tomatoes*1', 82, 3, 'Li Na', 3, '2025-03-03'),
   (4, 'Sichuan Boiled Beef*1,Griddle Cooked Cauliflower*1,Iced Plum Soup*2', 92, 4, 'Liu Yang', 6, '2025-03-04'),
   (5, 'Twice-cooked Pork*1,Stir-fried Water Spinach with Garlic*1,Sago in Coconut Milk*1', 76, 5, 'Chen Fang', 2, '2025-03-05'),
   (6, 'Stir-fried Yellow Beef*1,Spicy Diced Chicken*1,Hand-torn Cabbage*1', 104, 6, 'Zhao Min', 4, '2025-03-06'),
   (7, 'Dongpo Pork*1,Scrambled Eggs with Tomatoes*1,White Fungus and Lotus Seed Soup*1', 88, 7, 'Zhou Jie', 3, '2025-03-07'),
   (8, 'Kung Pao Chicken*1,Steamed Sea Bass*1', 106, 8, 'Wu Ting', 2, '2025-03-08'),
   (9, 'Mapo Tofu*2,Hot and Sour Shredded Potatoes*1,Lemon Honey Water*1', 70, 9, 'Xu Hao', 4, '2025-03-09'),
   (10, 'Braised Pork*1,Sichuan Boiled Beef*1,Hand-torn Cabbage*1', 122, 10, 'Sun Li', 5, '2025-03-10'),
   (11, 'Yuxiang Shredded Pork*1,Griddle Cooked Cauliflower*1,Red Bean Paste Soup*1', 68, 11, 'Gao Xiang', 3, '2025-03-11'),
   (12, 'Spicy Diced Chicken*1,Stir-fried Water Spinach with Garlic*1,Iced Plum Soup*1', 74, 12, 'Lin Xue', 4, '2025-03-12'),
   (13, 'Twice-cooked Pork*1,Scrambled Eggs with Tomatoes*1,Sago in Coconut Milk*1', 68, 13, 'Zheng Kai', 2, '2025-03-13'),
   (14, 'Stir-fried Yellow Beef*1,Hot and Sour Shredded Potatoes*1', 58, 14, 'He Jing', 2, '2025-03-14'),
   (15, 'Dongpo Pork*1,Mapo Tofu*1,White Fungus and Lotus Seed Soup*1', 90, 15, 'Feng Lei', 3, '2025-03-15'),
   (16, 'Kung Pao Chicken*1,Steamed Sea Bass*1,Hand-torn Cabbage*1', 124, 16, 'Deng Chao', 6, '2025-03-16'),
   (17, 'Sichuan Boiled Beef*1,Scrambled Eggs with Tomatoes*1,Lemon Honey Water*1', 94, 17, 'Han Mei', 4, '2025-03-17'),
   (18, 'Braised Pork*2,Stir-fried Water Spinach with Garlic*1', 112, 18, 'Tang Jun', 5, '2025-03-18'),
   (19, 'Yuxiang Shredded Pork*1,Griddle Cooked Cauliflower*1,Plum Soup*1', 66, 19, 'Xu Wei', 3, '2025-03-19'),
   (20, 'Kung Pao Chicken*1,Twice-cooked Pork*1,Sago in Coconut Milk*1', 92, 20, 'Qiao Feng', 4, '2025-03-20');

   </details>

2. Grant the AI node's database account permissions on the business tables. For example, if the database connection account for the AI node is polarai_user:

   Note In a production environment, make sure the AI node's database account has permissions on all relevant business tables.

   -- Grant permissions
   GRANT ALL PRIVILEGES ON TABLE public."restaurant_info" TO polarai_user;
   GRANT ALL PRIVILEGES ON TABLE public."menu_info" TO polarai_user;
   GRANT ALL PRIVILEGES ON TABLE public."bill_info" TO polarai_user;

Step 3: Build the schema index

Run the following command to create a schema index named schema_index for all tables in the current database. This collects and vectorizes table structures, comments, and other information to give the LLM context about your data.

SELECT polar_ai.ai_BuildSchemaIndex('schema_index');

The command returns a task ID, for example: bce632ea-97e9-11ee-bdd2-492f4dfe0918. Check the task status with:

SELECT polar_ai.ai_ShowTask('<your_task_id>');

The index is ready when the status shows finish.

Step 4: Run NL2SQL queries

Use ai_nl2sql to convert a natural language question into SQL.

Simple query

SELECT polar_ai.ai_nl2sql('What is the total revenue for this week?', 'schema_index');

Expected SQL generated:

SELECT SUM(actual_amount) AS total_income FROM bill_info WHERE pay_time BETWEEN '2025-08-25' AND '2025-08-31';

Complex queries

The model handles multi-table queries with joins, grouping, sorting, and calculations.

When results don't match expectations

The model captures the core query logic but may miss implicit expectations about output format. For example, for "Which outlet in Shanghai has the highest revenue?", the model returns the outlet ID (restaurant_id) rather than the address. You can either adjust your question explicitly — for instance, "Which outlet in Shanghai has the highest revenue? Return the outlet address" — or use the fine-tuning methods below to solve this systematically.

_ai_nl2sql_alter_token

Configures the node token required to access the LLM service. Run this once during initialization before using NL2SQL.

Syntax

text _ai_nl2sql_alter_token(text token);

Parameters

Example

SELECT polar_ai._ai_nl2sql_alter_token('sk-xxxxxx');

ai_BuildSchemaIndex

Creates a vectorized index for a database schema or question templates. This is an asynchronous operation.

Syntax

text ai_BuildSchemaIndex(text name, jsonb text2vecOption default '{"mode": "async", "resource": "schema"}');

Parameters

`text2vecOption` fields

Return value

A unique asynchronous task ID (job_id) to track task status.

Examples

-- Create an index for all tables
SELECT polar_ai.ai_BuildSchemaIndex('my_schema_index');

-- Create an index for specific tables
SELECT polar_ai.ai_BuildSchemaIndex('my_schema_index_2', '{"mode": "async", "resource": "schema", "tables_included": "my_table1, my_table2"}');

ai_ShowTask

Queries the status of an asynchronous task created by ai_BuildSchemaIndex.

Syntax

text ai_ShowTask(text job_id);

Parameters

Return value

Example

SELECT polar_ai.ai_ShowTask('bce632ea-97e9-11ee-bdd2-492f4dfe0918');

ai_nl2sql

Converts a natural language question into an executable SQL statement.

Syntax

text ai_nl2sql(text nl, text basic_index_name, jsonb options default '{}');

Parameters

`options` fields

Return value

The generated executable SQL string.

Example

SELECT polar_ai.ai_nl2sql('What is the total revenue for this week?', 'schema_index');

-- Result
SELECT SUM(actual_amount) AS total_income FROM bill_info WHERE pay_time BETWEEN '2025-08-25' AND '2025-08-31';

ai_nl2chart

Runs an SQL query and returns a publicly accessible URL to a chart generated from the results.

Syntax

text ai_nl2chart(text sql, text usr_query);

Parameters

Return value

A publicly accessible image URL.

Example

SELECT polar_ai.ai_nl2chart(
    $$SELECT r.id, SUM(b.actual_amount) AS total_income FROM restaurant_info r JOIN bill_info b ON r.id = b.restaurant_id GROUP BY r.id$$,
    'Show outlet revenue'
);

-- Result
-- http://db4ai-aiengine-cn-hangzhou-dataset-pre.oss-cn-hangzhou.aliyuncs.com/pc-xxx/xxx.png?security-token=...

ai_nl2summary

Runs an SQL query and returns both the raw results and a natural language summary.

Syntax

text ai_nl2summary(sql text, usr_query text);

Parameters

Return value

Two fields:

• sql_result text — The raw SQL query result.

• summary_result text — A natural language summary of the result.

Example

SELECT polar_ai.ai_nl2summary(
    $$SELECT r.id, SUM(b.actual_amount) AS total_income FROM restaurant_info r JOIN bill_info b ON r.id = b.restaurant_id GROUP BY r.id$$,
    'Summarize the revenue of all outlets'
);

-- Result
-- ("[{""total_income"": 68, ""id"": 11}, {""total_income"": 70, ""id"": 9}, ...]",)