How to build an MCP server that queries a database

1// TypeScript
2import { Pool } from "pg";
3
4const pool = new Pool({
5  connectionString: process.env.DATABASE_URL,
6  max: 10,                    // max connections in pool
7  idleTimeoutMillis: 30000,   // close idle connections after 30s
8  connectionTimeoutMillis: 5000, // fail if can't connect in 5s
9});
10
11// Test connection on startup
12pool.query("SELECT 1").then(() => {
13  console.error("Database connected");
14}).catch((err) => {
15  console.error("Database connection failed:", err.message);
16  process.exit(1);
17});

1// TypeScript
2import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
3import { z } from "zod";
4
5const server = new McpServer({ name: "postgres-server", version: "1.0.0" });
6
7server.registerTool("query", {
8  description: "Run a read-only SQL query against the database. Only SELECT statements are allowed.",
9  inputSchema: {
10    sql: z.string().describe("SQL SELECT query to execute"),
11  },
12}, async ({ sql }) => {
13  const trimmed = sql.trim();
14
15  // Enforce read-only
16  if (!/^SELECT\b/i.test(trimmed)) {
17    return {
18      content: [{ type: "text", text: "Error: Only SELECT queries are allowed. Use INSERT, UPDATE, or DELETE tools for write operations." }],
19      isError: true,
20    };
21  }
22
23  // Block dangerous patterns
24  if (/;\s*(DROP|DELETE|UPDATE|INSERT|ALTER|CREATE|TRUNCATE)/i.test(trimmed)) {
25    return {
26      content: [{ type: "text", text: "Error: Multi-statement queries with write operations are not allowed." }],
27      isError: true,
28    };
29  }
30
31  try {
32    const result = await pool.query(trimmed);
33    return {
34      content: [{
35        type: "text",
36        text: JSON.stringify({
37          rowCount: result.rowCount,
38          rows: result.rows.slice(0, 100), // Limit response size
39        }, null, 2),
40      }],
41    };
42  } catch (error) {
43    console.error("Query error:", error);
44    return {
45      content: [{ type: "text", text: `Error: ${(error as Error).message}` }],
46      isError: true,
47    };
48  }
49});

1// TypeScript
2server.registerResource("database-schema", "db://schema", {
3  description: "Complete database schema with tables, columns, and types",
4  mimeType: "text/plain",
5}, async (uri) => {
6  const result = await pool.query(`
7    SELECT
8      t.table_name,
9      c.column_name,
10      c.data_type,
11      c.is_nullable,
12      c.column_default
13    FROM information_schema.tables t
14    JOIN information_schema.columns c
15      ON t.table_name = c.table_name AND t.table_schema = c.table_schema
16    WHERE t.table_schema = 'public'
17    ORDER BY t.table_name, c.ordinal_position
18  `);
19
20  // Format as readable text
21  const tables = new Map<string, string[]>();
22  for (const row of result.rows) {
23    const cols = tables.get(row.table_name) || [];
24    cols.push(`  ${row.column_name} ${row.data_type}${row.is_nullable === 'NO' ? ' NOT NULL' : ''}${row.column_default ? ` DEFAULT ${row.column_default}` : ''}`);
25    tables.set(row.table_name, cols);
26  }
27
28  const schema = Array.from(tables.entries())
29    .map(([table, cols]) => `TABLE ${table}:\n${cols.join('\n')}`)
30    .join('\n\n');
31
32  return {
33    contents: [{ uri: uri.href, text: schema }],
34  };
35});

1// TypeScript
2server.registerTool("list-tables", {
3  description: "List all tables in the database with row counts",
4  inputSchema: {},
5}, async () => {
6  try {
7    const result = await pool.query(`
8      SELECT
9        schemaname,
10        relname AS table_name,
11        n_live_tup AS approximate_row_count
12      FROM pg_stat_user_tables
13      ORDER BY n_live_tup DESC
14    `);
15    return {
16      content: [{ type: "text", text: JSON.stringify(result.rows, null, 2) }],
17    };
18  } catch (error) {
19    return {
20      content: [{ type: "text", text: `Error: ${(error as Error).message}` }],
21      isError: true,
22    };
23  }
24});

1// TypeScript
2server.registerTool("describe-table", {
3  description: "Get detailed column information for a specific table",
4  inputSchema: {
5    tableName: z.string().regex(/^[a-zA-Z_][a-zA-Z0-9_]*$/).describe("Table name (alphanumeric and underscores only)"),
6  },
7}, async ({ tableName }) => {
8  try {
9    const columns = await pool.query(
10      `SELECT column_name, data_type, is_nullable, column_default
11       FROM information_schema.columns
12       WHERE table_schema = 'public' AND table_name = $1
13       ORDER BY ordinal_position`,
14      [tableName]
15    );
16
17    if (columns.rowCount === 0) {
18      return {
19        content: [{ type: "text", text: `Error: Table '${tableName}' not found` }],
20        isError: true,
21      };
22    }
23
24    const indexes = await pool.query(
25      `SELECT indexname, indexdef FROM pg_indexes
26       WHERE schemaname = 'public' AND tablename = $1`,
27      [tableName]
28    );
29
30    return {
31      content: [{
32        type: "text",
33        text: JSON.stringify({ columns: columns.rows, indexes: indexes.rows }, null, 2),
34      }],
35    };
36  } catch (error) {
37    return {
38      content: [{ type: "text", text: `Error: ${(error as Error).message}` }],
39      isError: true,
40    };
41  }
42});