How to use MCP to query databases from AI assistants

1// Claude Desktop config:
2{
3  "mcpServers": {
4    "postgres": {
5      "command": "npx",
6      "args": ["-y", "@modelcontextprotocol/server-postgres",
7        "postgresql://readonly_user:password@localhost:5432/mydb"]
8    }
9  }
10}
11
12// Create a read-only PostgreSQL user:
13// CREATE ROLE readonly_user LOGIN PASSWORD 'secure_password';
14// GRANT CONNECT ON DATABASE mydb TO readonly_user;
15// GRANT USAGE ON SCHEMA public TO readonly_user;
16// GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
17// ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

1// Claude Desktop config:
2{
3  "mcpServers": {
4    "sqlite": {
5      "command": "npx",
6      "args": ["-y", "@modelcontextprotocol/server-sqlite",
7        "/path/to/your/database.db"]
8    }
9  }
10}

1// Example natural language queries you can ask:
2
3// Simple: "What tables are in this database?"
4// The AI calls list_tables tool
5
6// Schema: "Describe the users table"
7// The AI calls describe_table with table name
8
9// Query: "How many users signed up this month?"
10// The AI generates: SELECT COUNT(*) FROM users WHERE created_at >= '2026-03-01'
11
12// Join: "Show me the top 10 customers by total order value"
13// The AI generates: SELECT u.name, SUM(o.total) as total_value
14//   FROM users u JOIN orders o ON u.id = o.user_id
15//   GROUP BY u.id ORDER BY total_value DESC LIMIT 10
16
17// Complex: "What's the month-over-month growth rate for new signups?"
18// The AI generates a window function query with LAG()

1// src/db-server.ts
2import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
3import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
4import { z } from "zod";
5import pg from "pg";
6
7const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
8const server = new McpServer({ name: "custom-db-server", version: "1.0.0" });
9
10// Safety: block destructive queries
11const BLOCKED_PATTERNS = /\b(DELETE|DROP|TRUNCATE|UPDATE|INSERT|ALTER|CREATE)\b/i;
12
13server.tool(
14  "query_database",
15  "Execute a read-only SQL query. Available tables: users (id, email, name, created_at), orders (id, user_id, total, status, created_at), products (id, name, price, category).",
16  {
17    sql: z.string().describe("SELECT query to execute"),
18    limit: z.number().default(100).describe("Max rows to return"),
19  },
20  async ({ sql, limit }) => {
21    if (BLOCKED_PATTERNS.test(sql)) {
22      return { content: [{ type: "text", text: "Error: Only SELECT queries are allowed." }], isError: true };
23    }
24
25    try {
26      const limitedSql = sql.includes("LIMIT") ? sql : `${sql} LIMIT ${limit}`;
27      const result = await pool.query({ text: limitedSql, timeout: 5000 });
28      return {
29        content: [{ type: "text", text: JSON.stringify({
30          rowCount: result.rowCount,
31          columns: result.fields.map(f => f.name),
32          rows: result.rows,
33        }, null, 2) }],
34      };
35    } catch (error) {
36      return {
37        content: [{ type: "text", text: `Query error: ${error instanceof Error ? error.message : String(error)}` }],
38        isError: true,
39      };
40    }
41  }
42);
43
44server.tool("get_schema", "Get the database schema for all tables", {}, async () => {
45  const result = await pool.query(
46    `SELECT table_name, column_name, data_type, is_nullable
47     FROM information_schema.columns
48     WHERE table_schema = 'public'
49     ORDER BY table_name, ordinal_position`
50  );
51  return { content: [{ type: "text", text: JSON.stringify(result.rows, null, 2) }] };
52});
53
54async function main() {
55  const transport = new StdioServerTransport();
56  await server.connect(transport);
57  console.error("Custom database MCP server running");
58}
59main().catch(e => { console.error(e); process.exit(1); });