Database Resources

Databases are the most common enterprise data source, and MCP provides a clean pattern for exposing them as resources. A database MCP server maps tables, views, and named queries to URIs. The critical constraint: resources are read-only. An AI model reading customer records through MCP should never be able to execute UPDATE or DELETE statements. This is enforced at the server level — the database connection uses a read-only user, the query executor rejects write operations, and the server only exposes SELECT-based resources.

The three major relational databases each have their own MCP server patterns. PostgreSQL servers leverage pg_catalog for schema discovery, LISTEN/NOTIFY for change subscriptions, and read-only transactions for query safety. MySQL servers use information_schema and can tail the binlog for change detection. SQLite servers are the simplest — a single file, opened in read-only mode, with filesystem watching for change detection. All three follow the same resource pattern: schema resources (list tables and columns), table resources (read rows), and query resources (execute named read-only queries).

import { Pool } from "pg";

// Read-only connection — enforced at the database level
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // Use a role with SELECT-only grants
});

// Schema discovery resource
server.resource("schema", "postgres://schema", async (uri) => {
  const tables = await pool.query(`
    SELECT table_name, column_name, data_type
    FROM information_schema.columns
    WHERE table_schema = 'public'
    ORDER BY table_name, ordinal_position
  `);
  return {
    contents: [{
      uri: uri.href,
      mimeType: "application/json",
      text: JSON.stringify(tables.rows),
    }],
  };
});

// Table resource — parameterized, with row limit
server.resource(
  "table",
  new ResourceTemplate("postgres://tables/{tableName}", { list: undefined }),
  async (uri, { tableName }) => {
    // Allowlist check — only expose approved tables
    if (!ALLOWED_TABLES.includes(tableName)) {
      throw new Error(`Table ${tableName} is not exposed`);
    }
    const rows = await pool.query(
      `SELECT * FROM "${tableName}" LIMIT 500`
    );
    return {
      contents: [{
        uri: uri.href,
        mimeType: "application/json",
        text: JSON.stringify(rows.rows),
      }],
    };
  }
);

1. Create a read-only database role Grant SELECT on only the tables and views the AI should access. Never use your application's read-write credentials for MCP.
2. Build an allowlist Maintain a hardcoded list of table names the server can query. Reject any table name not on the list, even if the database role has access.
3. Set row limits Always LIMIT query results (500-1000 rows). Large result sets consume context tokens and can overwhelm the model. Provide pagination templates for larger datasets.