n8n Chat with PostgreSQL Database Workflow
Overview
💬 n8n Chat with Postgresql Database
This n8n Chat with PostgreSQL Database workflow enables an AI assistant to chat with a PostgreSQL database, allowing users to query and retrieve data using natural language. It supports custom SQL queries and schema introspection.
🔧 Workflow Steps:
- When chat message received: This node acts as the trigger, initiating the workflow when a new chat message is received from the user, serving as the interface for natural language interaction with your database.
- AI Agent: This is the core intelligence of the workflow, utilizing an OpenAI Functions Agent to interpret user queries, determine the necessary database operations, and orchestrate the use of various tools to fulfill the request.
- OpenAI Chat Model: Provides the large language model capabilities (e.g., GPT-4o-mini) that the AI Agent uses for understanding natural language, generating SQL queries, and formulating human-like responses.
- Chat History: Manages the conversational context, allowing the AI agent to remember previous interactions and provide more coherent and relevant responses throughout the chat session.
- Execute SQL Query: A PostgreSQL tool that executes the SQL queries generated by the AI agent, fetching data directly from your database. It ensures that schema prefixes are correctly appended to table names.
- Get DB Schema and Tables List: A PostgreSQL tool that retrieves a list of all tables and their associated schemas in the connected database, providing the AI agent with essential information for constructing accurate queries.
- Get Table Definition: Another PostgreSQL tool that provides detailed information about a specific table, including column names, data types, and constraint types (like foreign keys), which helps the AI agent understand table structures.
📌 Use Cases:
- Building an internal tool for data analysis where non-technical users can query the database using natural language.
- Creating a customer support chatbot that can retrieve order information or customer details directly from your PostgreSQL database based on natural language queries.
- Automating data reporting by allowing stakeholders to ask questions about business metrics and receive instant, aggregated data.
- Developing a smart assistant for developers to quickly look up table schemas or test queries without writing complex SQL.
🧰 Required Credentials:
- PostgreSQL Database Credentials
- OpenAI Account Credentials
⚙️ Notes & Enhancements:
- You can easily swap the
OpenAI Chat Modelwith any other compatible chat model of your choice by changing the node settings. - Adjust the
Context Window Lengthoption in theChat Historynode to control how many previous messages the AI agent remembers; it’s set to 5 by default. - Ensure your PostgreSQL and OpenAI credentials are properly added and configured.
- After activating the workflow, you can make the chat publicly available for broader access.
Workflow Editor Screenshot
Workflow JSON Code
{
"id": "eOUewYsEzJmQixI6",
"meta": {
"instanceId": "77c4feba8f41570ef06dc76ece9a6ded0f0d44f7f1477a64c2d71a8508c11faa",
"templateCredsSetupCompleted": true
},
"name": "Chat with Postgresql Database",
"tags": [],
"nodes": [
{
"id": "6501a54f-a68c-452d-b353-d7e871ca3780",
"name": "When chat message received",
"type": "@n8n/n8n-nodes-langchain.chatTrigger",
"position": [
-300,
-80
],
"webhookId": "cf1de04f-3e38-426c-89f0-3bdb110a5dcf",
"parameters": {
"options": {}
},
"typeVersion": 1.1
},
{
"id": "cd32221b-2a36-408d-b57e-8115fcd810c9",
"name": "AI Agent",
"type": "@n8n/n8n-nodes-langchain.agent",
"position": [
0,
-80
],
"parameters": {
"agent": "openAiFunctionsAgent",
"options": {
"systemMessage": "You are DB assistant. You need to run queries in DB aligned with user requests.\n\nRun custom SQL query to aggregate data and response to user. Make sure every table has schema prefix to it in sql query which you can get from `Get DB Schema and Tables List` tool.\n\nFetch all data to analyse it for response if needed.\n\n## Tools\n\n- Execute SQL query - Executes any sql query generated by AI\n- Get DB Schema and Tables List - Lists all the tables in database with its schema name\n- Get Table Definition - Gets the table definition from db using table name and schema name"
}
},
"typeVersion": 1.7
},
{
"id": "8accbeeb-7eaf-4e9e-aabc-de8ab3a0459b",
"name": "OpenAI Chat Model",
"type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
"position": [
-60,
160
],
"parameters": {
"model": {
"__rl": true,
"mode": "list",
"value": "gpt-4o-mini"
},
"options": {}
},
"credentials": {
"openAiApi": {
"id": "48uG61Ilo8jndw3r",
"name": "Your OpenAI Account Credentials"
}
},
"typeVersion": 1.2
},
{
"id": "11f2013f-a080-4c9e-8773-c90492e2c628",
"name": "Get Table Definition",
"type": "n8n-nodes-base.postgresTool",
"position": [
780,
140
],
"parameters": {
"query": "select\n c.column_name,\n c.data_type,\n c.is_nullable,\n c.column_default,\n tc.constraint_type,\n ccu.table_name AS referenced_table,\n ccu.column_name AS referenced_column\nfrom\n information_schema.columns c\nLEFT join\n information_schema.key_column_usage kcu\n ON c.table_name = kcu.table_name\n AND c.column_name = kcu.column_name\nLEFT join\n information_schema.table_constraints tc\n ON kcu.constraint_name = tc.constraint_name\n AND tc.constraint_type = 'FOREIGN KEY'\nLEFT join\n information_schema.constraint_column_usage ccu\n ON tc.constraint_name = ccu.constraint_name\nwhere\n c.table_name = '{{ $fromAI(\"table_name\") }}'\n AND c.table_schema = '{{ $fromAI(\"schema_name\") }}'\norder by\n c.ordinal_position",
"options": {},
"operation": "executeQuery",
"descriptionType": "manual",
"toolDescription": "Get table definition to find all columns and types"
},
"credentials": {
"postgres": {
"id": "nGI61D0TEEZz18rr",
"name": "Your Postgresql Database Credentials"
}
},
"typeVersion": 2.5
},
{
"id": "760bc9bc-0057-4088-b3f0-3ee37b3519df",
"name": "Sticky Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-300,
-240
],
"parameters": {
"color": 5,
"width": 560,
"height": 120,
"content": "### 👨🎤 Setup\n1. Add your **postgresql** and **OpenAI** credentials.\n2. Click **Chat** button and start asking questions to your database.\n3. Activate the workflow and you can make the chat publicly available."
},
"typeVersion": 1
},
{
"id": "0df33341-c859-4a54-b6d9-a99670e8d76d",
"name": "Chat History",
"type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
"position": [
120,
160
],
"parameters": {},
"typeVersion": 1.3
},
{
"id": "4938b22e-f187-4ca0-b9f1-60835e823799",
"name": "Sticky Note3",
"type": "n8n-nodes-base.stickyNote",
"position": [
360,
300
],
"parameters": {
"color": 7,
"width": 562,
"height": 156,
"content": "🛠️ Tools Used:\n1. Execute SQL Query: Used to execute any query generated by the agent.\n2. Get DB Schema and Tables List: It returns the list of all the tables with its schema name.\n3. Get Table Definition: It returns table details like column names, foreign keys and more of a particular table in a schema."
},
"typeVersion": 1
},
{
"id": "39780c78-4fbc-403e-a220-aa6a4b06df8c",
"name": "Sticky Note1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-100,
300
],
"parameters": {
"color": 7,
"width": 162,
"height": 99,
"content": "👆 You can exchange this with any other chat model of your choice."
},
"typeVersion": 1
},
{
"id": "28a5692c-5003-46cb-9a09-b7867734f446",
"name": "Sticky Note2",
"type": "n8n-nodes-base.stickyNote",
"position": [
100,
300
],
"parameters": {
"color": 7,
"width": 162,
"height": 159,
"content": "👆 You can change how many number of messages to keep using `Context Window Length` option. It's 5 by default."
},
"typeVersion": 1
},
{
"id": "c18ced71-6330-4ba0-9c52-1bb5852b3039",
"name": "Execute SQL Query",
"type": "n8n-nodes-base.postgresTool",
"position": [
380,
140
],
"parameters": {
"query": "{{ $fromAI(\"sql_query\", \"SQL Query\") }}",
"options": {},
"operation": "executeQuery",
"descriptionType": "manual",
"toolDescription": "Get all the data from Postgres, make sure you append the tables with correct schema. Every table is associated with some schema in the database."
},
"credentials": {
"postgres": {
"id": "nGI61D0TEEZz18rr",
"name": "Your Postgresql Database Credentials"
}
},
"typeVersion": 2.5
},
{
"id": "557623c6-e499-48a6-a066-744f64f8b6f3",
"name": "Get DB Schema and Tables List",
"type": "n8n-nodes-base.postgresTool",
"position": [
580,
140
],
"parameters": {
"query": "SELECT \n table_schema,\n table_name\nFROM information_schema.tables\nWHERE table_type = 'BASE TABLE'\n AND table_schema NOT IN ('pg_catalog', 'information_schema')\nORDER BY table_schema, table_name;",
"options": {},
"operation": "executeQuery",
"descriptionType": "manual",
"toolDescription": "Get list of all tables with their schema in the database"
},
"credentials": {
"postgres": {
"id": "nGI61D0TEEZz18rr",
"name": "Your Postgresql Database Credentials"
}
},
"typeVersion": 2.5
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "10c7c74e-b383-4ac7-8cb2-c9a15a2818fe",
"connections": {
"Chat History": {
"ai_memory": [
[
{
"node": "AI Agent",
"type": "ai_memory",
"index": 0
}
]
]
},
"Execute SQL Query": {
"ai_tool": [
[
{
"node": "AI Agent",
"type": "ai_tool",
"index": 0
}
]
]
},
"OpenAI Chat Model": {
"ai_languageModel": [
[
{
"node": "AI Agent",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"Get Table Definition": {
"ai_tool": [
[
{
"node": "AI Agent",
"type": "ai_tool",
"index": 0
}
]
]
},
"When chat message received": {
"main": [
[
{
"node": "AI Agent",
"type": "main",
"index": 0
}
]
]
},
"Get DB Schema and Tables List": {
"ai_tool": [
[
{
"node": "AI Agent",
"type": "ai_tool",
"index": 0
}
]
]
}
}
}