How to configure n8n for AI-generated SQL queries on Microsoft SQL database?

Other Questions N8n
1

Hey everyone! I’m working on a cool project where I want an AI to come up with SQL queries for my Microsoft SQL database using n8n. I’ve got the workflow set up and the database connection is working fine. But I’m stuck on how to make the AI generate the SQL query and feed it into the Microsoft SQL tool.

I’ve looked everywhere but can’t find any info on what to put in the Query parameter to make this happen. I thought it might be something simple like {{ $fromAI('<some_parameter>') }}, but I’m not sure.

Just FYI, I managed to get this working with Supabase without needing to pass a query parameter. But with MS SQL, I’m lost.

I’ve tried leaving it blank, using "", '', {{ $fromAI('') }}, and {{ $fromAI('query') }}, but I’m just guessing at this point.

Has anyone figured this out? Any help would be awesome! Thanks!

2

Hey there! I’ve actually tackled a similar project recently, so I might be able to offer some insights.

From my experience, the key is to use the ‘Function’ node in n8n before your Microsoft SQL node. In this Function node, you can call your AI service (like OpenAI) to generate the SQL query. Then, you pass that generated query to the MS SQL node.

Here’s a rough idea of how I set it up:

  1. AI node (to get the initial request)
  2. Function node (to call AI API and generate SQL)
  3. Microsoft SQL node (to execute the generated query)

In the Function node, I used something like:

const aiResponse = await $node.aiNode.json.response;
const generatedSQL = parseAIResponseToSQL(aiResponse);
items[0].json.sqlQuery = generatedSQL;
return items;

Then in your MS SQL node, you can use {{ $json.sqlQuery }} as the query parameter.

It took some trial and error, but this approach worked well for me. Hope this helps!

3

hey olivias, i’ve been there! try using a function node before ur MS SQL node. in the function, do something like:

const aiQuery = await $node.aiNode.json.query;
items[0].json.sqlQuery = aiQuery;
return items;

then in MS SQL node use {{ $json.sqlQuery }} as query param. hope that helps!

4

I’ve worked on a similar setup and found that the key is to use a combination of nodes to achieve what you’re after. First, you’ll need an AI node to generate the initial query text. Then, use a Function node to process and format the AI output into a valid SQL query. Finally, pass this to your Microsoft SQL node.

In the Function node, you can structure your code like this:

const aiOutput = $node[‘AI Node’].json.output;
const formattedQuery = formatSQLQuery(aiOutput);
items[0].json.sqlQuery = formattedQuery;

Then in your MS SQL node, simply use {{ $json.sqlQuery }} as the query parameter. This approach allows for more control and error handling in the query generation process.

Remember to thoroughly test and validate the generated queries to ensure they’re safe and optimized for your database structure.