MCP + Ollama — natural language queries to a Postgres database

Read-only MCP (Model Context Protocol) server + local agent backend that translates Portuguese questions into safe SQL queries, using a local LLM (Ollama) to decide which "tool" to call. The sample data are fictitious: service orders and customers.

How it works

Usuário (interface web)
  ↓ pergunta em português
Backend agente (agent/server.js)
  ↓ roteamento determinístico (regex) OU decisão do LLM
Ollama (function calling, modelo qwen2.5:3b)
  ↓ escolhe uma tool + argumentos
Cliente MCP (agent/mcp-client.js)
  ↓ JSON-RPC via STDIO
Servidor MCP (src/server.js)
  ↓ SQL parametrizado, usuário somente-leitura
PostgreSQL

Security points worth highlighting:

• The MCP server only runs parameterized SELECTs; Postgres connects with a dedicated user (mcp_reader) that only has GRANT SELECT, runs in a read-only transaction (default_transaction_read_only), and has a short statement_timeout.

• Before calling the LLM, the backend already blocks questions that ask for writes (agent/write-policy.js) — it doesn't rely on the model behaving well.

• Whenever the question matches a known pattern (agent/os-routing.js, agent/client-routing.js), the tool and arguments are chosen by rule, not by the LLM — more predictable and cheaper. The LLM only decides freely when the question is ambiguous.

Related MCP server: kond-royalties-agent

Requirements

• Node.js 20+

• npm

• Docker and Docker Compose

• Ollama installed locally, with the qwen2.5:3b model

Step-by-step to run from scratch

1. Clone and install dependencies

git clone https://github.com/leomc06/mcp-teste-llm2.git
cd mcp-teste-llm2
npm install

2. Configure environment variables

cp .env.example .env

Edit .env and replace the values marked as troque (user, password, and name of the Postgres database, and the password of the read-only user mcp_reader). The .env is never versioned — make sure it doesn't show up in git status.

3. Start PostgreSQL

docker compose up -d

This creates the mcp-teste-llm container and automatically runs, on the first volume start, the db/init.sh script — which creates the tables, the sample data, and the mcp_reader user. The migrations live in db/migrations/ and are applied in order (001, 002, 003, ...) via \ir inside init.sh.

If the container already exists from a previous run and you add a new migration, init.sh will not run again on its own (the volume already has data). Apply the migration manually:

docker compose exec -T postgres psql -U <POSTGRES_USER> -d <POSTGRES_DB> -f - < db/migrations/00X_nome.sql

4. Prepare Ollama

sudo systemctl start ollama
ollama pull qwen2.5:3b

5. Run the tests (optional, but recommended)

npm test

Runs node --check on all files and the node --test suite (only routing and formatting logic, no need for Postgres or Ollama to be running).

6. Start the agent backend

npm run start:agent

The backend starts the MCP server automatically (via STDIO), connects to Postgres, and serves the web interface. Access:

http://127.0.0.1:3100

Ask questions like:

• "Which OS orders are overdue?"

• "List the OS orders of the person in charge, Carlos."

• "How many OS orders has customer Bruno Santos already resolved?"

• "Which customers are inactive?"

7. Shut down

In the backend terminal, Ctrl+C (this also shuts down the child MCP server).

sudo systemctl stop ollama
docker compose stop   # para o Postgres sem apagar dados/volumes

Project structure

src/server.js            servidor MCP: define as tools e faz as queries SQL
agent/server.js          backend HTTP: recebe a pergunta, orquestra tudo
agent/os-routing.js      roteamento por regex das perguntas sobre OS
agent/client-routing.js  roteamento por regex das perguntas sobre clientes
agent/tool-selector.js   junta as duas rotas e decide quais tools expor ao LLM
agent/agent-loop.js      loop de function calling com o Ollama
agent/mcp-client.js      cliente MCP + allowlist de tools permitidas
agent/write-policy.js    bloqueio de perguntas que pedem escrita
agent/response-formatter.js  formata o resultado das tools em texto
db/init.sh               script de inicialização do Postgres (roles, grants)
db/migrations/           migrations SQL, aplicadas em ordem
web/                      interface web estática
test/                     testes (node --test)
integration-agent.mjs     teste de integração ponta a ponta (precisa da stack de pé)

Running integration tests

With the backend (npm run start:agent) and Ollama already running in another terminal:

npm run test:integration

Available tools

The MCP server exposes query tools for service orders (search by number, list open/overdue/recent, filter by status, priority, person in charge, requester, or customer, history, summaries, and average resolution time) and for customers (list, list inactive/recent, search by id/email/name, email domains, summaries). The complete and up-to-date list of tools released to the agent is in allowedToolNames, at the top of agent/mcp-client.js.