MCP PostgreSQL Server

MCP server that gives an agent a PostgreSQL connection and a fixed set of tools. The server holds the connection, runs the SQL, and returns rows. The agent does not open pg itself.

The primary purpose is normal database work: connect, inspect the schema, run reads, check SQL, and run writes. Search and embedding tools are a second layer. They run when the database has pgvector.

What it does

PostgreSQL. connect_db opens the connection. list_schemas, list_tables, and describe_tables show the agent what it can query. describe_tables includes primary keys, defaults, PostGIS geometry(type, srid) and geography(type, srid), and vector / halfvec types when those catalogs exist. query runs SELECT, WITH, and EXPLAIN. execute runs INSERT, UPDATE, and DELETE. validate_sql runs EXPLAIN and returns true or the error. resolve_mentions ranks name matches with pg_trgm (similarity or word_similarity). Parameters use $1 or ?.

pgvector. list_vector_tables finds vector and halfvec columns. vector_search sends the query text to an OpenAI-compatible /v1/embeddings endpoint (vLLM, Ollama, or a hosted API), then ranks rows in PostgreSQL with cosine, L2, or inner product. Pass text_column to fuse that ranking with English full text or trigrams. The fused score keeps a closer embedding ahead of a weak keyword hit. vector_execute embeds text and inserts or updates the vector column. The agent passes text. The server fetches the embedding, checks its length against the column, and binds it as a query parameter.

PostgreSQL tools need only a database connection. list_vector_tables needs the vector extension. vector_search and vector_execute also need EMBEDDING_URL and EMBEDDING_MODEL.

Related MCP server: PostgreSQL MCP Server

Why this shape

• query accepts only SELECT, WITH, and EXPLAIN. execute accepts only INSERT, UPDATE, and DELETE. A read-only database role still blocks writes if execute is called.

• Schema output includes GIS and vector types, so the agent can write SQL that matches the column.

• Vector tools take text. The agent does not build or paste embedding arrays.

• One search call accepts up to 20 strings and can return JSON or one markdown table per string.

• stdio serves a local MCP client. Streamable HTTP serves a remote client, and each session can name its own database in X-PG-* headers. Embedding settings stay process-wide.

Requirements

• Node.js 20 or newer

• PostgreSQL, for every tool

• pg_trgm, for resolve_mentions and for hybrid search with lexical: "trgm"

• vector extension for list_vector_tables

• vector extension, plus EMBEDDING_URL and EMBEDDING_MODEL, for vector_search and vector_execute

pgvector HNSW indexes on vector support at most 2000 dimensions. For a 2560-dimension model such as Qwen3-Embedding-4B, store the column as halfvec(2560).

Install

From a clone:

npm install
npm run build

Or, once published:

npx -y mcp-postgres-server

Configuration

Database credentials and embedding settings are not interchangeable. Put each group in the file below and leave it out of the other.

On startup the server loads .env and fills only variables the process does not already have. The stdio client starts the process with PG_* already set, so those values stay. .env then supplies the embedding endpoint. Several stdio entries can point at different databases and still share one .env.

connect_db can replace the database connection after startup. It does not read .env, and it does not change the embedding endpoint.

EMBEDDING_TIMEOUT_MS (default 30000), EMBEDDING_MAX_RESPONSE_BYTES (default 52428800), PORT (default 3000), and TRANSPORT (http or stdio) have defaults. They are not in the example files. Set them in the process environment only when you need a different value.

Local files and git

Committed files use placeholders. Copy them and put real hosts, passwords, and URLs only in the gitignored copies.

grants.sql is site-specific and gitignored. It is not part of the server.

cp .env.example .env
cp config-stdio.example.json config-stdio.json
cp config.example.json config.json

On Windows PowerShell:

Copy-Item .env.example .env
Copy-Item config-stdio.example.json config-stdio.json
Copy-Item config.example.json config.json

Edit the copies. Do not put hosts, passwords, API keys, or internal URLs in the example files.

Environment

stdio

Default transport. Put only PG_* in the client env. Leave EMBEDDING_* in .env.

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["build/index.js"],
      "env": {
        "PG_HOST": "localhost",
        "PG_PORT": "5432",
        "PG_USER": "postgres",
        "PG_PASSWORD": "postgres",
        "PG_DATABASE": "postgres"
      }
    }
  }
}

Use the absolute path to build/index.js when the client does not start in this directory.

Streamable HTTP

npm start

That builds and runs node build/index.js --http. TRANSPORT=http does the same.

Send database credentials on the initialize request. Later calls in that session reuse them. If the headers are absent, the server uses PG_* from the process environment. Embedding settings still come from .env. config.example.json is an Inspector client config for this transport: it has the server URL and the X-PG-* headers, not the embedding variables.

Inspector

npm run inspector

That reads config-stdio.json. Copy it from config-stdio.example.json first.

Tools

PostgreSQL

These run against the connected database. pgvector is not required.

pgvector

These need the vector extension and EMBEDDING_URL plus EMBEDDING_MODEL in .env. list_vector_tables only needs the extension.

SQL

{
  "sql": "SELECT id, name FROM places WHERE id = $1",
  "params": [1]
}

execute uses the same sql and params shape. Use query for SELECT.

Mentions

{
  "table": "places",
  "text_column": "name",
  "mentions": ["harbour", "old town"],
  "operator": "trgm",
  "limit": 5,
  "response_format": "markdown"
}

operator is trgm (default) or word. where is a SQL fragment without the WHERE keyword. include_scores defaults to true.

Vector search

One string returns rows. Several strings run the same search once per string and return a group per string.

{
  "table": "documents",
  "embedding_column": "embedding",
  "query": ["permits near the river"],
  "where": "status = 'active'",
  "columns": ["id", "content"],
  "limit": 10
}

Hybrid search: set text_column. lexical is fts (default) or trgm.

• fts uses English full text. It stems, drops stop words, keeps Arabic, drops query terms that appear in more than 40% of rows, and ranks the rest by inverse document frequency. Use it on a passage column.

• trgm uses word_similarity() above pg_trgm.word_similarity_threshold. Use it on a short name column that has a trigram index.

{
  "schema": "catalog",
  "table": "documents",
  "embedding_column": "embedding",
  "text_column": "body",
  "lexical": "fts",
  "query": ["archaeological sites", "district boundaries"],
  "response_format": "markdown",
  "limit": 10
}

metric is cosine (default), l2, or ip. It must match the HNSW operator class for the index to be used. A where clause sets hnsw.iterative_scan=strict_order for that query. The default payload omits the embedding column and columns named hash or ending in _hash. include_scores defaults to true and adds distance, plus rrf, dense_rank, and lexical_rank on hybrid search. rrf is the sort score: for cosine, similarity plus at most 0.05 for the strongest lexical hit.

Vector write

vector_execute writes the vector column only. Delete rows, or change other columns, with execute.

Insert embeds text. text_column also stores that text. fields sets other columns.

{
  "operation": "insert",
  "table": "documents",
  "embedding_column": "embedding",
  "text": "River permit application",
  "text_column": "content",
  "fields": { "status": "active" }
}

Update with text must match one row (id, or a where that returns a single row). Update with text_column embeds that varchar on each matched row. id_column defaults to id. limit defaults to 10 and caps at 100.

{
  "operation": "update",
  "schema": "catalog",
  "table": "documents",
  "embedding_column": "embedding",
  "text_column": "body",
  "where": "body IS NOT NULL AND embedding IS NULL",
  "limit": 10
}

Development

npm install
npm run watch
npm run inspector
npm run start:http

npm start builds, then starts the HTTP transport.

Security

Queries use parameters. Identifiers passed to the vector and mention tools are restricted to simple names. where is a SQL fragment, so give the database role only the rights the agent should have. Prefer a read-only role when the agent does not need to write.

Do not commit .env, config.json, or config-stdio.json.

License

MIT