Generate SQL queries from schema only using AI in N8N

Querying databases often requires technical expertise in SQL, making it challenging for non-technical users to retrieve information efficiently. However, by leveraging AI-driven workflows, we can simplify this process, allowing users to interact with databases using natural language on N8N. This blog explores how an automated workflow enhances database accessibility by storing schema information locally and using an AI agent to generate SQL queries dynamically.

Pre-workflow Setup

This workflow consists of several steps to ensure a seamless setup:

• List All Tables – The workflow begins by querying the database hosted on Elestio to retrieve a list of all available tables.

• Extract Schema Information – Once the tables are listed, the workflow extracts the schema for each table, which includes column names, data types, and other metadata.

• Add Table Names to the Output – A manual step ensures that each table's schema is appropriately labeled and structured.

• Convert Schema to Binary JSON – The extracted schema is converted into a binary JSON format for efficient storage and retrieval.

• Save Locally – The final step writes the structured schema into a local file (mysql.json) so it can be used for future queries.

Building AI Workflow

Once the database schema is extracted and saved locally, every chat interaction follows an optimized workflow for efficiency and security. Instead of repeatedly fetching table structures from a remote database, the system retrieves the schema from a local file using the Load the schema from the local file node. This significantly reduces processing time and ensures that query generation is fast and responsive while keeping the database structure accessible without exposing actual data. When a chat message is received via the Chat Trigger node, the workflow processes the schema by extracting it as JSON through the Extract data from file node. Inside the node make sure you have selected the correct file.

JSON fields from the chat input are then combined with the schema using the Combine schema data and chat input node before being sent to the AI Agent node. This AI agent operates purely based on schema knowledge and does not execute any queries itself. Instead, it generates SQL queries and passes them to subsequent nodes, ensuring security by preventing direct data access. The AI agent intelligently determines when an SQL query is required. This selective execution minimizes unnecessary database interactions and improves response times.

Configuring Nodes: Part 1

• Load the schema from the local file – Reads schema files from disk for faster query generation.

• Extract data from file – Parses the schema file and extracts the required JSON content.

• Combine schema data and chat input – Merges the extracted schema with user input for better query formulation.

• AI Agent – Uses OpenAI's Chat Model with a Window Buffer Memory to generate SQL queries based on the provided schema and user request.

• OpenAI Chat Model: This node is used to configure OpenAI credentials and model.

• Window Buffer Memory: Buffer node is used to store a limited history of previous interactions, allowing the AI Agent to maintain context while avoiding excessive memory usage.

Once a query is generated, the Extract SQL query node scans the AI agent’s response using a regular expression to detect SQL code. If a query exists, the Run SQL query node executes it against the database. The results are formatted through the Format query results node and combined with the chat response using the Combine query result and chat answer node. The Prepare final output node ensures the response is structured before sending it back to the user.

If no SQL query is required, the No Operation, do nothing node ensures an immedia te response is delivered without additional processing. This structured pipeline ensures that users can interact with their database seamlessly while maintaining security, efficiency, and scalability.

Configuring Nodes: Part 2

• Extract SQL query – Identifies and extracts the SQL query from the AI-generated response.

• Check if query exists – Verifies whether a valid SQL query has been generated before execution.

• Run SQL query – Executes the generated SQL query on the database.

• Format query results – Structures and formats the database response into a readable format.

• Combine query result and chat answer – Merges the AI-generated response with the SQL query results.

• Prepare final output – Processes the final combined result for user display.

• No Operation, do nothing – Acts as a placeholder for scenarios where no action is required.

Testing Workflow

When a user requests a blog with a specific ID, the AI-powered chat system generates an SQL query to retrieve the relevant data from the Blogs table. In this case, the query SELECT * FROM Blogs WHERE id = 1; fetches all columns where the ID is 1. The system then executes the query and returns the structured result, displaying details such as the blog ID, name, associated software, and content. This efficient process enables users to interact with the database seamlessly, retrieving precise information without manually writing SQL queries.

Thanks for reading ❤️

To learn more, explore Elestio’s resources and official documentation on integrating AI-powered workflows for automation. Whether you're building intelligent query systems or optimizing data processing, these tools can help streamline your workflow. Click the button below to set up your own automated system on Elestio and start experimenting today. See you in the next one! 👋