AI-Powered SQL Query Generator (Schema-Only) with n8n & OpenAI

Unlock Natural Language Database Interaction with this AI Agent

This n8n AI Agent empowers you to converse with your MySQL database using natural language. It operates in two stages. First, it connects to your database to extract the complete schema (table names, columns, types) and saves this information securely as a local JSON file within your n8n instance. This schema extraction is typically run once or whenever your database structure changes.

Subsequently, when you interact with the Agent via a chat interface, it uses this locally stored schema and an OpenAI model (like GPT-4o) to understand your questions. Based purely on the schema—without accessing your actual database content during the chat—the AI intelligently generates the SQL query needed to answer your request. The workflow then optionally executes this query against your database and returns the results, or you can choose to use the generated SQL elsewhere. This approach ensures your data's privacy while making database querying accessible and efficient.

Key Features & Benefits

• AI-Driven SQL Generation: Leverages OpenAI's GPT-4o (or other compatible models) to translate natural language into accurate SQL queries.
• Schema-First, Data-Private: The AI consults only the pre-extracted database schema, never accessing live data content for query generation, enhancing security.
• Interactive Chat Interface: Ask complex data questions in plain English through n8n's chat functionality.
• Automated Schema Management: Initial step extracts and stores your database schema locally, ensuring the AI has up-to-date structural information.
• Efficient Querying: Drastically reduces the time and expertise needed to write SQL, especially for complex joins or aggregations.
• Flexible Execution: Generated SQL queries can be automatically executed by the workflow, or you can opt to receive the SQL for manual execution or use in other tools.
• Conversation Memory: Remembers the context of your chat session (schema, questions, answers) for more natural follow-up questions. Data values are not part of the AI's memory if queries are run externally or by subsequent workflow steps.

• An n8n instance (Cloud or self-hosted) with write access to its local filesystem (for schema storage).
• OpenAI API Key with access to a suitable model (e.g., GPT-4o).
• MySQL database credentials and network accessibility from your n8n instance.
• If using a self-hosted n8n instance older than v1.16, ensure @n8n/n8n-nodes-langchain package is installed.

1. Download the n8n workflow JSON file.
2. Import the workflow into your n8n instance.
3. One-Time Schema Extraction & Setup: a. Configure the 'List all tables in a database' (MySQL) node with your database credentials. b. Configure the 'Extract database schema' (MySQL) node. It should automatically use settings from the previous MySQL node. The default schema output filename is ./chinook_mysql.json as seen in the 'Save file locally' node. c. Manually trigger the workflow using the 'When clicking "Test workflow"' node. This part of the workflow will run, extract your database schema, and save it as chinook_mysql.json (or your configured name) in your n8n's local data directory. d. Verify the schema file has been created in your n8n instance's file system.
4. Configure AI & Chat: a. In the 'OpenAI Chat Model' node, select or create your OpenAI API credentials and choose your desired model (e.g., GPT-4o). b. Ensure the 'Load the schema from the local file' node is correctly configured to read the schema file generated in step 3c (default is ./chinook_mysql.json). c. (Optional) Customize the system prompt in the 'AI Agent' node if needed. The default prompt guides the AI to generate SQL based on the schema without attempting to execute it directly.
5. Configure Query Execution (Optional but Recommended): a. The 'Run SQL query' (MySQL) node is set up to execute the SQL generated by the AI. Configure this node with your MySQL database credentials if you want the workflow to run the query and return results.
6. Test & Activate: a. Use the Chat Panel (usually accessible via the webhook URL of the 'Chat Trigger' node or by clicking 'Open Chat' on the 'Chat Trigger' node in the n8n editor) to ask questions like 'List all customers from Germany' or 'How many tracks are in the database?'. b. Activate the workflow to make the chat interface persistently available.