Chat with PostgreSQL Database using AI Agent

Overview

This workflow creates an interactive chat interface that lets you ask natural language questions about your PostgreSQL database. Instead of writing SQL queries manually, you can simply ask questions like "Which tables are available?" or "Show me the top 10 customers by revenue" and the AI agent will generate and execute the appropriate SQL query, returning the results to you in real-time.

It's a powerful way to democratize database access for non-technical team members, reduce the time spent crafting SQL, and get instant insights from your data. The workflow uses a memory buffer to maintain conversation context, so follow-up questions can build on previous answers.

Node Breakdown

1. When chat message received (No auth) — This is the chat trigger node that listens for incoming messages through the n8n chat interface. It processes user input and passes it to the AI agent. No authentication is required as it's a built-in n8n feature.

2. AI Agent (No auth) — The core orchestrator that receives the chat message, processes it using the connected language model, and decides which tools to use. It uses the OpenAI Chat Model for language understanding and the Postgres tool for database queries. It also leverages the Simple Memory node to retain conversation history. No additional authentication is needed.

3. OpenAI Chat Model (API Key auth) — Connects to OpenAI's GPT-4o-mini model to interpret user questions and generate appropriate SQL statements. Requires an OpenAI API key configured in n8n credentials.

4. Postgres (Database credentials auth) — The PostgreSQL tool that executes SQL queries. The actual query is dynamically generated by the AI agent (using {{ $fromAI('sql_statement') }}). It requires a PostgreSQL database connection with host, port, database name, user, and password credentials.

5. Simple Memory (No auth) — A buffer window memory that stores the last few exchanges between the user and the AI agent. This allows the agent to maintain context across multiple questions (e.g., remembering that the user asked about "customers" in the previous question).

6. Sticky Note — A visual note that prompts the user to try the workflow by clicking the chat button and typing "Which tables are available?"

7. Sticky Note1 — A note indicating that the PostgreSQL database can be swapped for MySQL or SQLite by replacing the Postgres node with the appropriate database node.

Setup Instructions

• PostgreSQL Database: You need access to a PostgreSQL database. Obtain the host, port, database name, username, and password. In n8n, create a new credential of type "Postgres" and fill in the details.
• OpenAI API Key: Sign up at OpenAI and generate an API key. Create an n8n credential of type "OpenAI" and paste your API key.
• n8n Chat: The workflow uses the built-in n8n chat interface. Once the workflow is active, click the chat button at the bottom of the canvas to start interacting.

Use Cases and Variations

• Database Exploration: Quickly discover schemas, tables, and sample data without writing SQL.
• Business Intelligence: Ask ad-hoc questions like "What was the total sales last quarter?" or "Show me orders with late shipments."
• Customer Support: Allow support agents to query customer data using natural language.
• Variations: Replace the Postgres node with a MySQL or SQLite node to work with other databases. You can also swap the OpenAI model for another provider (e.g., Anthropic, Ollama) by changing the language model node.

{
  "meta": {
    "instanceId": "408f9fb9940c3cb18ffdef0e0150fe342d6e655c3a9fac21f0f644e8bedabcd9",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "d08a2559-17fd-4bdb-a976-795c3823a88a",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -520,
        240
      ],
      "parameters": {
        "content": "## Try me out\nClick the 'chat' button at the bottom of the canvas and paste in:\n\n_Which tables are available?_"
      },
      "typeVersion": 1
    },
    {
      "id": "3019b559-6100-4ead-8e1a-a7dece2a6982",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -380,
        -60
      ],
      "parameters": {
        "color": 7,
        "width": 677,
        "height": 505,
        "content": "This workflow uses a Postgres DB, but you could swap it for a MySQL or SQLite one"
      },
      "typeVersion": 1
    },
    {
      "id": "73786411-5383-4921-82ee-06b3b582bab7",
      "name": "When chat message received",
      "type": "@n8n/n8n-nodes-langchain.chatTrigger",
      "position": [
        -320,
        40
      ],
      "webhookId": "1c0d08f0-abd0-4bdc-beef-370c27aae1a0",
      "parameters": {
        "options": {}
      },
      "typeVersion": 1.1
    },
    {
// ... truncated (copy to see full JSON)