AI-Powered SQL Query Generator from Database Schema

Overview

This workflow lets you chat with your MySQL database using natural language. It first extracts the complete schema (table structures) from your database and saves it locally. Then, via a chat interface, you can ask questions like "Show me all German customers" or "What is the total sales by country?" and the AI (powered by OpenAI's GPT-4o) generates the corresponding SQL query. The workflow can optionally execute the query and return the results, all within the same chat session.

This is especially useful for non-technical team members who need to query databases without writing SQL, or for developers who want to quickly prototype queries without manually inspecting the schema.

Step-by-Step Node Breakdown

Part 1: One-Time Schema Extraction (Run manually first)

• When clicking "Test workflow" (Manual Trigger) — No auth. Starts the schema extraction process when you click "Test Workflow".
• List all tables in a database (MySQL) — MySQL credentials (username/password). Executes SHOW TABLES; to retrieve all table names from the connected database.
• Extract database schema (MySQL) — Same MySQL credentials. For each table, runs DESCRIBE {{ table_name }}; to get column names, types, and constraints.
• Add table name to output (Set) — No auth. Adds the original table name to each schema record so the AI knows which table each column belongs to.
• Convert data to binary (Convert to File) — No auth. Converts the collected schema data into JSON format.
• Save file locally (Read/Write File) — No auth. Writes the JSON schema to a local file named chinook_mysql.json (you can change the filename).

Part 2: Chat Interface (Runs on every message)

• Chat Trigger (Webhook) — No auth. Provides a chat endpoint (webhook) where users send natural language questions. It outputs chatInput, sessionId, and action.
• Load the schema from the local file (Read/Write File) — No auth. Reads the previously saved chinook_mysql.json file to get the database schema.
• Extract data from file (Extract from File) — No auth. Parses the JSON file into a usable object.
• Combine schema data and chat input (Set) — No auth. Merges the schema (as a string) with the user's chat input, session ID, and action into a single payload for the AI.
• OpenAI Chat Model (LangChain Chat Model) — OpenAI API key (OAuth2 or API Key). Uses gpt-4o with a low temperature (0.2) for consistent SQL generation.
• Window Buffer Memory (LangChain Memory) — No auth. Maintains a conversation window of the last 10 exchanges so the AI remembers context.
• AI Agent (LangChain Agent) — No auth. The core agent that receives the schema and user request. It is configured with a custom system prompt instructing it to generate SQL queries (wrapped in triple quotes) but not to execute them. The agent decides whether a query is needed.
• Extract SQL query (Set) — No auth. Uses a regex (/SELECT[\s\S]*?;/i) to extract any SQL query from the agent's response.
• Check if query exists (IF) — No auth. If an SQL query was extracted, it proceeds to execute it; otherwise, it sends the agent's answer directly to the user.
• Run SQL query (MySQL) — Same MySQL credentials. Executes the extracted SQL query against the database.
• Format query results (Set) — No auth. Converts the query results into a markdown-style table (headers and rows separated by pipes).
• Combine query result and chat answer (Merge) — No auth. Merges the original agent response with the formatted SQL output.
• Prepare final output (Set) — No auth. Combines the agent's text answer and the SQL result into a single output string.
• No Operation, do nothing (NoOp) — No auth. A placeholder for the branch where no SQL query was needed (the agent's answer is passed through without modification).

Setup Instructions

1. MySQL Database: You need a MySQL database with your data. The workflow uses a free MySQL hosting service (like db4free.net) in the example. Import your database schema (the Chinook sample database is used in the demo, available on GitHub).
2. MySQL Credentials: In n8n, create a MySQL credential with your host, port, database name, username, and password.
3. OpenAI API Key: You need an OpenAI account and an API key. Create an OpenAI credential in n8n (choose "API Key" auth).
4. Run Part 1: Click "Test Workflow" on the manual trigger to extract the schema. This will create the chinook_mysql.json file on your n8n server.
5. Chat: After the schema is saved, you can start chatting via the Chat Trigger webhook URL. Send natural language questions and receive SQL queries (and optionally results).

Use Cases and Variations

• Database Exploration: Quickly understand what data is available without writing SQL.
• Reporting: Generate ad-hoc reports by asking questions like "What are the top 5 customers by revenue?"
• Learning SQL: See how natural language translates to SQL queries.
• Adaptation: Replace MySQL with PostgreSQL or SQLite by swapping the database nodes. You can also change the AI model (e.g., GPT-3.5-turbo) or adjust the system prompt to enforce different SQL dialects.
• Security: The AI never sees actual data — only the schema. Queries are executed externally, keeping sensitive data safe.

{
  "id": "P307QnrxpA1ddsM5",
  "meta": {
    "instanceId": "fb924c73af8f703905bc09c9ee8076f48c17b596ed05b18c0ff86915ef8a7c4a",
    "templateCredsSetupCompleted": true
  },
  "name": "Generate SQL queries from schema only - AI-powered",
  "tags": [],
  "nodes": [
    {
      "id": "b7c3ca47-11b3-4378-81fa-68b2f56b295e",
      "name": "OpenAI Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        1460,
        440
      ],
      "parameters": {
        "model": "gpt-4o",
        "options": {
          "temperature": 0.2
        }
      },
      "credentials": {
        "openAiApi": {
          "id": "rveqdSfp7pCRON1T",
          "name": "Ted's Tech Talks OpenAi"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "977c3a82-440b-4d44-9042-47a673bcb52c",
      "name": "Window Buffer Memory",
      "type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
      "position": [
        1640,
        440
      ],
      "parameters": {
        "contextWindowLength": 10
      },
      "typeVersion": 1.2
    },
    {
      "id": "c6e9c0e2-d238-4f0b-a4c8-2271f2c8b31b",
      "name": "No Operation, do nothing",
      "type": "n8n-nodes-base.noOp",
      "position": [
        2340,
// ... truncated (copy to see full JSON)