AI coding agents write better code when they know your database schema. Without that context, they guess column names, invent table relationships, and produce queries that fail at runtime. A PostgreSQL MCP server solves this by exposing your schema, constraints, and migration history as structured data that any MCP-compatible agent can consume. Instead of pasting CREATE TABLE statements into a prompt, the agent calls a tool and gets the real schema — current, complete, and typed.
Why Database Context Matters for AI Agents
Consider a typical scenario: you ask an agent to build a user profile endpoint. Without database context, it generates a query against a 'users' table with columns it assumes exist — name, email, created_at. Your actual table is called 'profiles', has a 'display_name' column instead of 'name', stores emails in a separate 'auth.users' table managed by Supabase, and uses 'inserted_at' instead of 'created_at'. The generated code compiles fine but crashes on the first request.
With a PostgreSQL MCP server, the agent calls get_db_schema() before writing a single line. It sees every table, column, type, constraint, and foreign key relationship. It knows that profiles references auth.users via a foreign key on user_id. It knows that display_name is a varchar(255) with a NOT NULL constraint. The code it generates matches your actual database because it has your actual database schema.
How PostgreSQL MCP Servers Work
An MCP server for PostgreSQL connects to your database (usually via a read-only connection string) and exposes tools that agents can call over the Model Context Protocol. The protocol uses JSON-RPC 2.0 over Streamable HTTP transport, so the server can run anywhere — locally, on your VPS, or as a managed service. The agent discovers available tools via the MCP handshake, then calls them as needed during a coding session.
The core tools typically include schema introspection (list tables, describe columns, show constraints and indexes), relationship mapping (foreign keys, junction tables, enum types), and optionally read-only query execution for validation. More advanced servers add migration history, schema diffing between branches, and change tracking so the agent knows not just what the schema is, but how it got there.
// .cursor/mcp.json — PostgreSQL MCP server config
{
"mcpServers": {
"postgres": {
"url": "https://your-mcp-server.com/mcp",
"headers": {
"Authorization": "Bearer <your-api-key>"
}
}
}
}
// Agent tool call:
// get_db_schema({ project_id: "abc-123" })
// Returns: tables, columns, types, constraints, foreign keysWhat Tools Are Available
A well-built PostgreSQL MCP server exposes several categories of tools. Schema tools let agents list all tables in a schema, describe individual tables with column types and constraints, and enumerate foreign key relationships. These are the most frequently called tools — agents use them at the start of nearly every task that touches the database.
Query tools allow agents to run read-only SQL for validation. An agent might execute a SELECT query to verify that the data shape matches its assumptions before generating application code. The read-only constraint is critical — you never want an AI agent running ALTER TABLE or DROP in production. Connection strings should use a role with SELECT-only permissions.
Migration tools expose the history of schema changes: which migrations have been applied, what each migration changed, and whether there are pending migrations. This context helps agents understand why the schema looks the way it does and avoids generating code that conflicts with in-flight migrations.
# Example tool responses from a PostgreSQL MCP server:
## get_db_schema response (truncated):
{
"tables": [
{
"name": "projects",
"schema": "public",
"columns": [
{ "name": "id", "type": "uuid", "nullable": false, "default": "gen_random_uuid()" },
{ "name": "name", "type": "text", "nullable": false },
{ "name": "owner_id", "type": "uuid", "nullable": false },
{ "name": "created_at", "type": "timestamptz", "nullable": false }
],
"foreign_keys": [
{ "column": "owner_id", "references": "auth.users(id)" }
]
}
]
}How AppHandoff Uses PostgreSQL Context
AppHandoff's MCP server includes built-in PostgreSQL schema scanning as part of its project context tools. When you connect a project, AppHandoff reads your Supabase (or any PostgreSQL) schema and makes it available alongside your API spec and frontend contracts. This means agents get the full picture — not just the database schema in isolation, but how the schema relates to API endpoints and frontend expectations.
The schema scanner runs automatically on project connection and re-scans when migrations are applied. It detects schema drift between environments (dev vs. staging vs. production), flags tables without RLS policies, and identifies columns that are referenced by API endpoints but have mismatched types. For example, if your API returns a 'count' field as a number but the database column is a bigint (which PostgreSQL returns as a string in some drivers), AppHandoff flags the type mismatch before it hits production.
PostgreSQL MCP vs. Raw SQL Access
Some teams give agents direct database access via connection strings. This works but has significant drawbacks. First, security: a connection string with write access in an AI agent's context is a disaster waiting to happen. Second, context quality: raw SQL results are untyped text blobs that the agent must parse. Third, scope: an agent with a connection string can only see the database, not how it relates to the rest of your architecture.
An MCP server provides guardrails (read-only access, tool-level permissions), structure (typed JSON responses instead of text tables), and integration (schema data alongside API specs and frontend contracts). The tradeoff is setup complexity — you need to run or connect to an MCP server. For teams using AppHandoff, the setup is already done: connect your project and the schema tools are available immediately via the MCP server at /mcp-server.
Setting Up a PostgreSQL MCP Server
If you want to run a standalone PostgreSQL MCP server, several open-source options exist. The community postgres-mcp-server package connects directly to a PostgreSQL instance and exposes schema and query tools. You provide a read-only connection string, configure which schemas to expose, and point your editor's MCP config at the server URL.
# Standalone PostgreSQL MCP server setup:
# 1. Create a read-only database role
CREATE ROLE mcp_reader WITH LOGIN PASSWORD 'secure-password';
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_reader;
# 2. Run the MCP server
npx @modelcontextprotocol/server-postgres \
--connection-string "postgresql://mcp_reader:secure-password@localhost:5432/mydb" \
--port 3100
# 3. Add to your editor config (.cursor/mcp.json):
# { "mcpServers": { "postgres": { "url": "http://localhost:3100/mcp" } } }Best Practices
Always use a read-only database role. Never expose write access through an MCP server. Limit schema exposure to the schemas your application uses — exclude pg_catalog, information_schema, and internal schemas. Enable migration tracking so agents understand schema evolution. Cache schema responses (schemas change infrequently, so a 5-minute cache is reasonable) to reduce database load during active agent sessions.
For teams already using AppHandoff, the PostgreSQL context is built into the platform. Connect your project, and agents get schema tools alongside API spec tools, mismatch detection, and ticket context — all through a single MCP connection. See the setup guide at /blog/how-to-set-up-mcp-with-lovable for the full walkthrough, or explore the MCP server documentation at /mcp-server for the complete tool reference.