Build a No‑Code AI SQL Assistant with Dify in 12 Simple Steps

1. Prepare a MySQL test database

Create a database (e.g., dify_demo) and run the following statements to define a products table and insert sample rows:

-- Create product table
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) COMMENT '产品名称',
  category VARCHAR(50) COMMENT '产品分类',
  price DECIMAL(10,2) COMMENT '价格',
  stock INT COMMENT '库存数量',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO products (name, category, price, stock) VALUES
('机械键盘', '外设', 299.00, 150),
('游戏鼠标', '外设', 89.00, 300),
('4K显示器', '显示设备', 1999.00, 50),
('降噪耳机', '音频', 499.00, 80),
('无线充电器', '配件', 99.00, 200);

The table and data are now ready for queries.

2. Create a read‑only MySQL account

To prevent accidental data modification, create a user that can only execute SELECT statements:

CREATE USER 'dify_reader'@'%' IDENTIFIED BY 'your_password';
GRANT SELECT ON dify_demo.* TO 'dify_reader'@'%';
FLUSH PRIVILEGES;

3. Install Dify’s Database (MCP) plugin

Open Dify → top menu Plugins → Plugin Marketplace .

Search for "Database" or "MySQL".

Click Install on the database plugin.

4. Configure the database connection in Dify

In the Agent workspace, add the newly installed plugin and fill in the SQLAlchemy‑style connection URL (replace host, password, and database name as needed):

mysql+pymysql://dify_reader:your_password@HOST:3306/dify_demo

5. Build the Agent application

5.1 Create a new Agent

Navigate to Dify → Studio → Create Blank App , choose Agent , and name it SQL Query Assistant .

5.2 Choose a SQL‑capable LLM

Select a model with strong SQL understanding (e.g., DeepSeek Coder, or any local Ollama/vLLM model that supports code generation).

5.3 Define the system prompt

Copy the following prompt into the Prompt box. It supplies the table schema, rules, and examples that guide the model to generate safe SELECT statements only.

你是一个 SQL 查询助手。用户会用自然语言提问，你需要根据数据库表结构生成 SQL 并查询。

## 数据库表结构
products 表：
- id: 产品ID (INT)
- name: 产品名称 (VARCHAR)
- category: 产品分类 (VARCHAR)，可选值：外设、显示设备、音频、配件
- price: 价格 (DECIMAL)
- stock: 库存数量 (INT)
- created_at: 创建时间 (DATETIME)

## 规则
1. 根据用户问题，生成对应的 SQL 查询。
2. 只生成 SELECT 查询，禁止 UPDATE、DELETE、INSERT。
3. 查询结果用自然语言回复用户。
4. 如果用户问题与数据库无关，礼貌告知无法回答。

## 示例
用户问：最贵的产品是什么？
回复：最贵的产品是「4K显示器」，价格 1999 元。

用户问：库存低于 100 的产品有哪些？
回复：以下产品库存低于100：4K显示器（50件）、降噪耳机（80件）。

5.4 Add tools and publish

In the Agent editor, enable the following tools:

Text to SQL

SQL Execute

Click Publish to make the Agent live.

6. Test the assistant

Use the debug panel to ask natural‑language questions. Example interactions:

Simple query: "产品列表有哪些？" – returns the full product list.

Conditional query: "库存低于 100 的产品有哪些？" – returns rows with stock < 100.

Aggregate query: "最贵的产品是什么？" – returns the row with the maximum price.

7. Upgrade to multi‑table scenarios

When the database contains many tables, enable the Get Table Schema tool so the Agent can automatically discover table structures.

Enable Get Table Schema in the tool list.

Replace the original prompt with a workflow‑driven prompt (see below).

你是一个 SQL 查询助手。用户会用自然语言提问，你需要查询数据库并返回结果。

## 工作流程
1. 用 Get Table Schema 获取所有表结构。
2. 根据用户问题找到相关表。
3. 用 Text to SQL 生成 SELECT 语句（禁止 UPDATE/DELETE/INSERT）。
4. 用 SQL Execute 执行查询。
5. 用自然语言回复用户。

## 规则
- 只生成 SELECT 查询。
- 若问题与数据库无关，礼貌告知无法回答。

After updating the prompt, republish the Agent. The assistant will now handle queries across any number of tables without manual schema definitions.

8. Optional: add more tables

Additional tables can be created with standard CREATE TABLE statements and populated with data. Once the schema changes, republish the Agent; the Get Table Schema tool will automatically include the new tables in its discovery process.

Core principle

Providing the LLM with complete table metadata and the appropriate tool (Text‑to‑SQL + SQL‑Execute, optionally Get Table Schema) enables reliable natural‑language database queries without writing SQL manually.