Connect MySQL database and read content of table using N8N

Requirement:

Pre-requisites:

n8n tools is installed
mysql database is installed and accessible

Brief description of flow :

here , the flow is triggered with a chat.
In chat, the table name (only one) is given in "" (double quotes)
Extract table name from the chat input
Identify the table is present in database or not, using mysql node
If table is present, extract the table content

Extract the content of that table and send content in chat.

If table is not present, send message back to chat "This table xxxx is not present in database""

Flow diagram is as following;

Login to n8n portal

Create credentials:

on left side of the page, click on + (plus) symbol, click on credentials.in search box, type mysql
Give a name to your credentials (on top left), as I mentioned "MySQLConnector_N8nDemo"
Add your database connection details , hostname, port, Database name, username, password
rest of the details can be left as default.

Create the workflow

Add chat trigger

Add the node "code", to extract the table name from the chat output.
Put this code in the code section
const inputText = $input.first().json.chatInput

const match = inputText.match(/"([^"]*)"/);

if (match && match[1]) {
return [{ json: { tablename: match[1] } }];
} else {
return [{ json: { tablename: null } }]; // Or handle cases where no tablename is found
}

Add the node "MySQL", add below details

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'neerajN8N_Demo'
AND TABLE_NAME = '{{ $json.tablename }}'

Add new node "If"

Case 1: If statement validation is TRUE:

Add Node MySQL , to fetch the content of table from database

Add the Node "Aggregator":

Add the Node "Code", that takes the input from previous node and convert the content in HTML

// Take the first input item
const data = $input.first().json;

// Get users array
const users = data.users || [];

// Build headers dynamically
const headers = Object.keys(users[0] || {});

// Start table
let html = '<html><body><table border="1" cellspacing="0" cellpadding="5" style="border-collapse: collapse;">';

// Header row
html += '<tr>';
for (const header of headers) {
html += `<th>${header}</th>`;
}
html += '</tr>';

// Data rows
for (const user of users) {
html += '<tr>';
for (const header of headers) {
html += `<td>${user[header]}</td>`;
}
html += '</tr>';
}

html += '</table> </body> </html>';

// Return as single item with HTML string
return [
{
json: {
htmlTable: html
}
}
];

Add Node "Respond back to chat":

Case 2: If statement validation is FALSE:

Send the response back to CHAT

------------- End of the flow -------------

Connect MySQL database and read content of table using N8N

Search