I Built a Local AI SQL Agent That Lets You Talk to Your PostgreSQL Database

GitHub: NileshRaut-code/Database-Ai-Agent

---

The Problem: Simple Questions, Painful SQL

Every backend developer knows this moment. Someone from sales or ops walks up and asks something that sounds simple:

“Which are our top 5 products by sales?” “Which customers haven’t come back in 90 days?”

The answer is sitting right there in the database. But to get it, you have to open the SQL editor, remember which table holds what, figure out how orders connects to order_items and products, write the JOINs, add the WHERE clause, and hope you didn’t mess up the GROUP BY.

For a developer it takes five minutes. For a non-technical person, it’s impossible without asking a developer.

So I asked myself one question: what if you could just ask the database?

The Idea: Database → Just Ask

I built an AI agent that sits between you and your PostgreSQL database. You type a question in normal language. The agent:

1. Understands your database schema (tables, columns, keys, relationships)
2. Converts your question into a safe, read-only SQL query
3. Runs it against the database
4. Explains the result back to you in plain language

And the part that surprised most people in the Reel: it runs completely locally. No OpenAI key, no cloud API. The language model runs on your own machine through Ollama, so your database content never leaves your system. Turn off the Wi-Fi and it still works.

The Tech Stack

The whole project is intentionally lightweight:

• Node.js — the agent runtime
• PostgreSQL — the database you’re querying
• pgvector — PostgreSQL extension used as the vector store for RAG
• Ollama — runs the models locally
• Qwen 4B (qwen:4b) — the LLM that writes SQL and answers
• Qwen3 Embedding 4B (qwen3-embedding:4b) — creates embeddings for schema retrieval
• pg, axios, dotenv — the only real dependencies

No LangChain, no heavy framework. Every step is plain, readable JavaScript so you can see exactly what’s happening.

How It Works: The Full Pipeline

Here’s the flow from question to answer:

Your Question
     │
     ▼
┌─────────────────────┐
│ 1. Read Schema      │  information_schema → tables, columns, PKs, FKs, row counts
└─────────────────────┘
     │
     ▼
┌─────────────────────┐
│ 2. RAG Search       │  question → embedding → pgvector similarity search
└─────────────────────┘
     │
     ▼
┌─────────────────────┐
│ 3. Generate SQL     │  Qwen 4B + schema + context + strict rules
└─────────────────────┘
     │
     ▼
┌─────────────────────┐
│ 4. Validate SQL     │  only SELECT/WITH, single statement, no DML/DDL
└─────────────────────┘
     │
     ▼
┌─────────────────────┐
│ 5. Execute          │  run on PostgreSQL, measure rows + time
└─────────────────────┘
     │
     ▼
┌─────────────────────┐
│ 6. Explain Answer   │  Qwen 4B turns rows into a human answer
└─────────────────────┘

Let’s walk through each piece.

Step 1: Teaching the Agent Your Database

An LLM can’t write correct SQL if it doesn’t know your tables. So the first thing the agent does is read PostgreSQL’s own metadata from information_schema: every column with its data type and nullability, every primary key, every foreign key relationship, and the row count of each table.

The foreign keys are the most important part. A line like this tells the model exactly how to JOIN:

orders.customer_id -> customers.id
order_items.order_id -> orders.id
order_items.product_id -> products.id

All of this is formatted into a clean text document. I also add a small business definitions section, plain-English notes like “customer spending = SUM(orders.total_amount).” This bridges the gap between how a human asks (“top spenders”) and how the data is actually stored.

Step 2: RAG with pgvector

On startup, the agent embeds the schema document using the local Qwen3 embedding model and stores it in a rag_documents table using pgvector:

sql

CREATE TABLE rag_documents (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    embedding VECTOR(<dimension>) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

A small but useful detail: the embedding dimension isn’t hardcoded. The agent embeds a test string, reads the vector length, and creates the table to match. If you switch embedding models later and the dimension changes, it throws a clear error instead of silently breaking.

When you ask a question, the question is embedded too, and the agent runs a cosine similarity search with an HNSW index:

sql

SELECT id, content, metadata,
       1 - (embedding <=> $1::vector) AS similarity
FROM rag_documents
ORDER BY embedding <=> $1::vector
LIMIT $2;

The best-matching context is passed into the SQL prompt.

Step 3: Natural Language → SQL

This is where the magic from the Reel happens. The prompt gives Qwen the full schema, the RAG context, your question, and a strict set of rules. A few of them:

• Return only SQL, no markdown, no explanation
• Only SELECT or WITH queries
• Never INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, and so on
• Use only tables and columns that exist in the schema (never invent one)
• Use the foreign-key relationships correctly
• For “top N” questions, use LIMIT, and never exceed 100

The model runs with temperature: 0, because for SQL you want the same question to produce the same query every time, not creativity.

Small models sometimes wrap output in ```sql fences or add a sentence before the query, so a cleanSQL() function strips fences and cuts everything before the first SELECT or WITH.

So when you type “Show me the top 5 products by sales”, the model produces something like:

sql

SELECT p.name, SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.name
ORDER BY total_sales DESC
LIMIT 5;

You never had to know order_items existed.

Step 4: The Safety Layer

Letting an AI write SQL against your database without guardrails would be reckless. Prompt rules alone aren’t enough, because models don’t always obey. So every generated query passes through a separate validator in code before it touches the database:

js

if (!normalized.startsWith("select") && !normalized.startsWith("with")) {
    throw new Error("Only SELECT or WITH queries are allowed.");
}

// Block stacked statements like "SELECT 1; DROP TABLE users"
if (withoutTrailingSemicolon.includes(";")) {
    throw new Error("Multiple SQL statements are not allowed.");
}

Then it checks the query against a blocklist of keywords: insert, update, delete, drop, alter, truncate, create, grant, revoke, copy, pg_sleep, call, and do. If anything matches, the query is rejected.

That’s two layers: the prompt asks nicely, the validator enforces.

Step 5: Execute

The validated query runs through a pg connection pool. The agent logs how many rows came back and how long the query took, which is handy for spotting slow, heavy queries the model produced.

Step 6: Rows → Human Answer

Raw rows aren’t friendly for a non-technical user, so the question, SQL, and result are sent back to Qwen with a second prompt: answer using ONLY the database result. No invented numbers, no outside knowledge, and if there are no rows, say no matching data was found.

This grounding rule is what keeps the final answer honest. The model is summarizing real data, not guessing.

Why Local Matters

Most “chat with your database” tools send your schema, and often your actual query results, to a cloud LLM provider. For a hobby project that’s fine. For a company database with customer names, emails, and revenue numbers, that’s a real conversation with your security team.

With Ollama, both the SQL model and the embedding model run on your machine. The only network connection is between the agent and your own PostgreSQL server. That makes this approach practical for internal tools, sensitive data, and offline environments.

“Local” doesn’t automatically mean “secure,” though. It removes the third-party API from the picture, but you still need normal database hygiene (more on that below).

Run It Yourself

Prerequisites: Node.js, PostgreSQL with the pgvector extension available, and Ollama installed.

1. Pull the models

bash

ollama pull qwen:4b
ollama pull qwen3-embedding:4b

2. Clone and install

bash

git clone https://github.com/NileshRaut-code/Database-Ai-Agent.git
cd Database-Ai-Agent
npm install

3. Create a .env file in the project root

env

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=your_database
POSTGRES_USER=your_user
POSTGRES_PASSWORD=your_password

OLLAMA_URL=http://localhost:11434
OLLAMA_MODEL=qwen:4b
OLLAMA_EMBED_MODEL=qwen3-embedding:4b

4. Start the agent

bash

node src/server.js

On startup it tests the PostgreSQL connection, checks Ollama and lists your available models, reads and embeds your schema, and then drops you into an interactive prompt:

You > How many completed orders do we have?
You > Show the top 3 customers by spending
You > Show all products in Electronics

Type exit to quit.

You can swap in any Ollama model through .env. A bigger coder model will generally write better SQL on complex schemas, at the cost of speed.

Honest Limitations (and What’s Next)

This is a working project, not a finished product, and I’d rather tell you where it stands:

• Small model, real mistakes. A 4B model handles single-table and simple JOIN questions well, but multi-step analytical questions can produce wrong SQL. Always sanity-check important numbers.
• The keyword validator is a guardrail, not a wall. It’s a strong first line of defense, but the right production setup is to connect the agent with a read-only PostgreSQL user so the database itself refuses writes, no matter what SQL gets generated. Adding a statement_timeout is a good idea too.
• The business definitions are currently written for an e-commerce schema (customers, products, orders, order_items). If your database is different, update that section in src/database/schema.js with your own business rules; it makes a big difference in accuracy.
• RAG is simple right now. The whole schema is embedded as one document. For large databases with hundreds of tables, the next step is chunking per table so only the relevant tables go into the prompt.
• It’s CLI-based. The next step is exposing ask() as an Express API endpoint with a chat UI on top, which is already easy since the agent returns the question, SQL, rows, timing, and answer as one object.

Planned improvements: a per-table RAG chunking strategy, a web chat interface, query history, and a self-correction loop where the agent retries with the error message when PostgreSQL rejects a query.

Wrapping Up

The story in the Reel was simple: the database is difficult → what if we just ask it → it works → and it runs locally. Under the hood, that’s six clear steps: read the schema, retrieve context, generate SQL, validate it, execute it, and explain the result.

No magic framework. Just Node.js, PostgreSQL, and a local LLM wired together carefully.

If you try it on your own database, I’d love to hear what questions you asked and where it broke. Star the repo, open an issue, or drop a comment on the Reel.

⭐ Code: github.com/NileshRaut-code/Database-Ai-Agent

Follow along on Instagram for more builds like this. Next up: putting a proper chat UI on top of this agent.