Ai Agent To Chat With Supabase Postgresql Db

About This Script

Features:

• Automates workflow tasks
• Ready to import into n8n
• Configurable parameters

{
    "nodes": [
        {
            "id": "93981540-f818-4106-aa2a-b7ccced13841",
            "name": "Sticky Note3",
            "type": "n8n-nodes-base.stickyNote",
            "position": [
                1340,
                720
            ],
            "parameters": {
                "color": "#FFF59D",
                "width": 215,
                "height": 80,
                "content": "Configuration note: update with your credentials or endpoint."
            },
            "typeVersion": 1
        },
        {
            "id": "430dacba-c932-494e-a3f1-567550d9fd48",
            "name": "Sticky Note5",
            "type": "n8n-nodes-base.stickyNote",
            "position": [
                200,
                120
            ],
            "parameters": {
                "color": "#FFF59D",
                "width": 280.2462120317618,
                "height": 545.9087885077763,
                "content": "Configuration note: update with your credentials or endpoint."
            },
            "typeVersion": 1
        },
        {
            "id": "15919eb9-1896-4a7d-9979-b5b4e0d53f74",
            "name": "When chat message received",
            "type": "@n8n/n8n-nodes-langchain.chatTrigger",
            "position": [
                1080,
                200
            ],
            "webhookId": "6e95bc27-99a6-417c-8bf7-2831d7f7a4be",
            "parameters": {
                "options": []
            },
            "typeVersion": 1.1
        },
        {
            "id": "3b7902bb-36ab-4518-9335-f6d5f0e0311c",
            "name": "Run SQL query",
            "type": "@n8n/n8n-nodes-langchain.toolCode",
            "position": [
                1400,
                580
            ],
            "parameters": {
                "name": "search_table",
                "jsCode": "const { Pool } = require('pg');\n\n// Create a connection pool to the PostgreSQL database\nconst pool = new Pool({\n  user: 'postgres.<username>', // Replace with your actual Supabase username\n  host: '<host>', // Replace with your actual Supabase host\n  database: 'postgres', // Replace with your actual Supabase database name\n  password: '<pass>', // Replace with your actual Supabase password\n  port: 5432, // Replace with your PostgreSQL port, typically 5432\n});\n\nasync function runQuery(query) {\n  try {\n    // Execute the dynamic query\n    const result = await pool.query(query);\n\n    // Extract the rows from the result\n    const queryResult = result.rows;\n\n    // Return the result as a stringified JSON\n    return JSON.stringify(queryResult);\n\n  } catch (error) {\n    // Handle any errors during the query execution and return error message as a string\n    return JSON.stringify({ error: error.message });\n  }\n}\n\n// Example call to the function with dynamic query\nconst dynamicQuery = query.sql_query;\n\n// Call the function with the dynamic query\nreturn runQuery(dynamicQuery);",
                "description": "Run custom SQL queries using knowledge about Output structure to provide needed response for user request.\nUse ->> operator to extract JSON data.",
                "jsonSchemaExample": "{\n\t\"sql_query\": \"SELECT * FROM FILES\"\n}",
                "specifyInputSchema": true
            },
            "typeVersion": 1.1
        },
        {
            "id": "2e41bc1b-fb0f-4094-a5e4-1fcd30ec4272",
            "name": "OpenAI Chat Model",
            "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
            "position": [
                1200,
                480
            ],
            "parameters": {
                "options": []
            },
            "credentials": {
                "openAiApi": {
                    "id": "zJhr5piyEwVnWtaI",
                    "name": "OpenAi club"
                }
            },
            "typeVersion": 1
        },
        {
            "id": "17a69c88-a7e5-4803-988f-2b2cc70c417e",
            "name": "DB Schema",
            "type": "n8n-nodes-base.postgresTool",
            "position": [
                1540,
                460
            ],
            "parameters": {
                "query": "SELECT table_schema, table_name\nFROM information_schema.tables\nWHERE table_type = 'BASE TABLE' AND table_schema = 'public';",
                "options": [],
                "operation": "executeQuery",
                "descriptionType": "manual",
                "toolDescription": "Get list of all tables in database"
            },
            "credentials": {
                "postgres": {
                    "id": "AO9cER6p8uX7V07T",
                    "name": "Postgres 5minai"
                }
            },
            "typeVersion": 2.5
        },
        {
            "id": "e97fbefa-28a2-4f00-b350-80a1a534415b",
            "name": "Get table definition",
            "type": "n8n-nodes-base.postgresTool",
            "position": [
                1700,
                460
            ],
            "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\") }}' -- Your table name\n    AND c.table_schema = 'public' -- Ensure it's in the right schema\nORDER BY \n    c.ordinal_position;\n",
                "options": [],
                "operation": "executeQuery",
                "descriptionType": "manual",
                "toolDescription": "Get table definition to find all columns and types."
            },
            "credentials": {
                "postgres": {
                    "id": "AO9cER6p8uX7V07T",
                    "name": "Postgres 5minai"
                }
            },
            "typeVersion": 2.5
        },
        {
            "id": "e5d6658f-be37-42ef-998e-4380d872ca0c",
            "name": "Sticky Note",
            "type": "n8n-nodes-base.stickyNote",
            "position": [
                1320,
                80
            ],
            "parameters": {
                "width": 215,
                "height": 80,
                "content": "Configuration note: update with your credentials or endpoint.",
                "color": "#FFF59D"
            },
            "typeVersion": 1
        },
        {
            "id": "3649b816-a733-4b1f-8c01-443a18edfdb9",
            "name": "AI Agent",
            "type": "@n8n/n8n-nodes-langchain.agent",
            "position": [
                1300,
                200
            ],
            "parameters": {
                "text": "={{ $('When chat message received').item.json.chatInput }}",
                "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.\n\nFetch all data to analyse it for response if needed.\n"
                },
                "promptType": "define"
            },
            "typeVersion": 1.6
        }
    ],
    "connections": {
        "DB Schema": {
            "ai_tool": [
                [
                    {
                        "node": "AI Agent",
                        "type": "ai_tool",
                        "index": 0
                    }
                ]
            ]
        },
        "Run 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
                    }
                ]
            ]
        }
    },
    "n8n_version": "1.5.0"
}

Requirements