Building a Database MCP Tool with PostgreSQL

Let AI agents explore your database safely. This tutorial builds a production-ready PostgreSQL MCP tool.

What You'll Build

• list_tables — discover database schema
• describe_table — get column details
• run_query — execute read-only SQL queries
• Safety guardrails against writes and dangerous operations

Step 1: Setup

<span class="hljs-built_in">mkdir</span> pg-mcp &amp;&amp; <span class="hljs-built_in">cd</span> pg-mcp
npm init -y
npm install @modelcontextprotocol/sdk pg

Step 2: Database Connection

<span class="hljs-keyword">import</span> pg <span class="hljs-keyword">from</span> <span class="hljs-string">&quot;pg&quot;</span>;

<span class="hljs-keyword">const</span> pool = <span class="hljs-keyword">new</span> pg.<span class="hljs-title class_">Pool</span>({
  <span class="hljs-attr">connectionString</span>: process.<span class="hljs-property">env</span>.<span class="hljs-property">DATABASE_URL</span>,
  <span class="hljs-attr">max</span>: <span class="hljs-number">5</span>, <span class="hljs-comment">// Limit connections</span>
  <span class="hljs-attr">idleTimeoutMillis</span>: <span class="hljs-number">30000</span>
});

<span class="hljs-comment">// Test connection on startup</span>
pool.<span class="hljs-title function_">query</span>(<span class="hljs-string">&quot;SELECT 1&quot;</span>).<span class="hljs-title function_">then</span>(<span class="hljs-function">() =&gt;</span> {
  <span class="hljs-variable language_">console</span>.<span class="hljs-title function_">error</span>(<span class="hljs-string">&quot;✅ Database connected&quot;</span>);
}).<span class="hljs-title function_">catch</span>(<span class="hljs-function"><span class="hljs-params">err</span> =&gt;</span> {
  <span class="hljs-variable language_">console</span>.<span class="hljs-title function_">error</span>(<span class="hljs-string">&quot;❌ Database connection failed:&quot;</span>, err.<span class="hljs-property">message</span>);
  process.<span class="hljs-title function_">exit</span>(<span class="hljs-number">1</span>);
});

Step 3: Define Tools

server.<span class="hljs-title function_">setRequestHandler</span>(<span class="hljs-string">&quot;tools/list&quot;</span>, <span class="hljs-title function_">async</span> () =&gt; ({
  <span class="hljs-attr">tools</span>: [
    {
      <span class="hljs-attr">name</span>: <span class="hljs-string">&quot;list_tables&quot;</span>,
      <span class="hljs-attr">description</span>: <span class="hljs-string">&quot;List all tables in the database with row counts&quot;</span>,
      <span class="hljs-attr">inputSchema</span>: {
        <span class="hljs-attr">type</span>: <span class="hljs-string">&quot;object&quot;</span>,
        <span class="hljs-attr">properties</span>: {
          <span class="hljs-attr">schema</span>: {
            <span class="hljs-attr">type</span>: <span class="hljs-string">&quot;string&quot;</span>,
            <span class="hljs-attr">description</span>: <span class="hljs-string">&quot;Schema name (default: public)&quot;</span>
          }
        }
      }
    },
    {
      <span class="hljs-attr">name</span>: <span class="hljs-string">&quot;describe_table&quot;</span>,
      <span class="hljs-attr">description</span>: <span class="hljs-string">&quot;Get column names, types, and constraints for a table&quot;</span>,
      <span class="hljs-attr">inputSchema</span>: {
        <span class="hljs-attr">type</span>: <span class="hljs-string">&quot;object&quot;</span>,
        <span class="hljs-attr">properties</span>: {
          <span class="hljs-attr">table</span>: {
            <span class="hljs-attr">type</span>: <span class="hljs-string">&quot;string&quot;</span>,
            <span class="hljs-attr">description</span>: <span class="hljs-string">&quot;Table name&quot;</span>
          }
        },
        <span class="hljs-attr">required</span>: [<span class="hljs-string">&quot;table&quot;</span>]
      }
    },
    {
      <span class="hljs-attr">name</span>: <span class="hljs-string">&quot;run_query&quot;</span>,
      <span class="hljs-attr">description</span>: <span class="hljs-string">&quot;Execute a read-only SQL query (SELECT only)&quot;</span>,
      <span class="hljs-attr">inputSchema</span>: {
        <span class="hljs-attr">type</span>: <span class="hljs-string">&quot;object&quot;</span>,
        <span class="hljs-attr">properties</span>: {
          <span class="hljs-attr">query</span>: {
            <span class="hljs-attr">type</span>: <span class="hljs-string">&quot;string&quot;</span>,
            <span class="hljs-attr">description</span>: <span class="hljs-string">&quot;SQL SELECT query to execute&quot;</span>
          },
          <span class="hljs-attr">limit</span>: {
            <span class="hljs-attr">type</span>: <span class="hljs-string">&quot;number&quot;</span>,
            <span class="hljs-attr">description</span>: <span class="hljs-string">&quot;Maximum rows to return (default: 100, max: 1000)&quot;</span>
          }
        },
        <span class="hljs-attr">required</span>: [<span class="hljs-string">&quot;query&quot;</span>]
      }
    }
  ]
}));

Step 4: Implement Safety Guards

<span class="hljs-keyword">function</span> <span class="hljs-title function_">isReadOnly</span>(<span class="hljs-params">query</span>) {
  <span class="hljs-keyword">const</span> trimmed = query.<span class="hljs-title function_">trim</span>().<span class="hljs-title function_">toUpperCase</span>();
  <span class="hljs-keyword">const</span> dangerous = [<span class="hljs-string">&quot;INSERT&quot;</span>, <span class="hljs-string">&quot;UPDATE&quot;</span>, <span class="hljs-string">&quot;DELETE&quot;</span>, <span class="hljs-string">&quot;DROP&quot;</span>, <span class="hljs-string">&quot;ALTER&quot;</span>,
                     <span class="hljs-string">&quot;CREATE&quot;</span>, <span class="hljs-string">&quot;TRUNCATE&quot;</span>, <span class="hljs-string">&quot;GRANT&quot;</span>, <span class="hljs-string">&quot;REVOKE&quot;</span>];
  <span class="hljs-keyword">return</span> !dangerous.<span class="hljs-title function_">some</span>(<span class="hljs-function"><span class="hljs-params">cmd</span> =&gt;</span> trimmed.<span class="hljs-title function_">startsWith</span>(cmd));
}

<span class="hljs-keyword">function</span> <span class="hljs-title function_">sanitizeIdentifier</span>(<span class="hljs-params">name</span>) {
  <span class="hljs-comment">// Only allow alphanumeric and underscore</span>
  <span class="hljs-keyword">return</span> name.<span class="hljs-title function_">replace</span>(<span class="hljs-regexp">/[^a-zA-Z0-9_]/g</span>, <span class="hljs-string">&quot;&quot;</span>);
}

Step 5: Handle Tool Calls

<span class="hljs-keyword">case</span> <span class="hljs-string">&quot;run_query&quot;</span>: {
  <span class="hljs-keyword">const</span> { query, limit = <span class="hljs-number">100</span> } = args;

  <span class="hljs-comment">// Safety check</span>
  <span class="hljs-keyword">if</span> (!<span class="hljs-title function_">isReadOnly</span>(query)) {
    <span class="hljs-keyword">return</span> {
      <span class="hljs-attr">content</span>: [{
        <span class="hljs-attr">type</span>: <span class="hljs-string">&quot;text&quot;</span>,
        <span class="hljs-attr">text</span>: <span class="hljs-string">&quot;❌ Only SELECT queries are allowed for safety.&quot;</span>
      }]
    };
  }

  <span class="hljs-comment">// Add limit if not present</span>
  <span class="hljs-keyword">const</span> finalQuery = query.<span class="hljs-title function_">toUpperCase</span>().<span class="hljs-title function_">includes</span>(<span class="hljs-string">&quot;LIMIT&quot;</span>)
    ? query
    : <span class="hljs-string">`<span class="hljs-subst">${query}</span> LIMIT <span class="hljs-subst">${<span class="hljs-built_in">Math</span>.min(limit, <span class="hljs-number">1000</span>)}</span>`</span>;

  <span class="hljs-keyword">try</span> {
    <span class="hljs-keyword">const</span> result = <span class="hljs-keyword">await</span> pool.<span class="hljs-title function_">query</span>(finalQuery);
    <span class="hljs-keyword">const</span> columns = result.<span class="hljs-property">fields</span>.<span class="hljs-title function_">map</span>(<span class="hljs-function"><span class="hljs-params">f</span> =&gt;</span> f.<span class="hljs-property">name</span>);
    <span class="hljs-keyword">const</span> rows = result.<span class="hljs-property">rows</span>.<span class="hljs-title function_">slice</span>(<span class="hljs-number">0</span>, limit);

    <span class="hljs-comment">// Format as markdown table</span>
    <span class="hljs-keyword">let</span> output = <span class="hljs-string">`📊 Query returned <span class="hljs-subst">${result.rowCount}</span> rows\n\n`</span>;
    output += <span class="hljs-string">`| <span class="hljs-subst">${columns.join(<span class="hljs-string">&quot; | &quot;</span>)}</span> |\n`</span>;
    output += <span class="hljs-string">`| <span class="hljs-subst">${columns.map(() =&gt; <span class="hljs-string">&quot;---&quot;</span>).join(<span class="hljs-string">&quot; | &quot;</span>)}</span> |\n`</span>;

    <span class="hljs-keyword">for</span> (<span class="hljs-keyword">const</span> row <span class="hljs-keyword">of</span> rows) {
      output += <span class="hljs-string">`| <span class="hljs-subst">${columns.map(c =&gt; <span class="hljs-built_in">String</span>(row[c] ?? <span class="hljs-string">&quot;NULL&quot;</span>).slice(<span class="hljs-number">0</span>, <span class="hljs-number">50</span>)).join(<span class="hljs-string">&quot; | &quot;</span>)}</span> |\n`</span>;
    }

    <span class="hljs-keyword">if</span> (result.<span class="hljs-property">rowCount</span> &gt; limit) {
      output += <span class="hljs-string">`\n*Showing first <span class="hljs-subst">${limit}</span> of <span class="hljs-subst">${result.rowCount}</span> rows*`</span>;
    }

    <span class="hljs-keyword">return</span> { <span class="hljs-attr">content</span>: [{ <span class="hljs-attr">type</span>: <span class="hljs-string">&quot;text&quot;</span>, <span class="hljs-attr">text</span>: output }] };
  } <span class="hljs-keyword">catch</span> (err) {
    <span class="hljs-keyword">return</span> {
      <span class="hljs-attr">content</span>: [{
        <span class="hljs-attr">type</span>: <span class="hljs-string">&quot;text&quot;</span>,
        <span class="hljs-attr">text</span>: <span class="hljs-string">`❌ Query error: <span class="hljs-subst">${err.message}</span>`</span>
      }]
    };
  }
}

Step 6: Deploy and Publish

<span class="hljs-comment"># Set your database URL</span>
<span class="hljs-built_in">export</span> DATABASE_URL=<span class="hljs-string">&quot;postgresql://user:pass@localhost:5432/mydb&quot;</span>

<span class="hljs-comment"># Test</span>
node index.js

<span class="hljs-comment"># Publish</span>
mcpm-dev publish

Configuration for Users

In their Claude/Cursor/Windsurf config:

<span class="hljs-punctuation">{</span>
  <span class="hljs-attr">&quot;mcpServers&quot;</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
    <span class="hljs-attr">&quot;pg-mcp&quot;</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
      <span class="hljs-attr">&quot;command&quot;</span><span class="hljs-punctuation">:</span> <span class="hljs-string">&quot;npx&quot;</span><span class="hljs-punctuation">,</span>
      <span class="hljs-attr">&quot;args&quot;</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span><span class="hljs-string">&quot;-y&quot;</span><span class="hljs-punctuation">,</span> <span class="hljs-string">&quot;pg-mcp&quot;</span><span class="hljs-punctuation">]</span><span class="hljs-punctuation">,</span>
      <span class="hljs-attr">&quot;env&quot;</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
        <span class="hljs-attr">&quot;DATABASE_URL&quot;</span><span class="hljs-punctuation">:</span> <span class="hljs-string">&quot;postgresql://user:pass@host:5432/db&quot;</span>
      <span class="hljs-punctuation">}</span>
    <span class="hljs-punctuation">}</span>
  <span class="hljs-punctuation">}</span>
<span class="hljs-punctuation">}</span>

Security Checklist

• ✅ Read-only queries enforced
• ✅ Connection pooling with limits
• ✅ Row limits on all queries
• ✅ Identifier sanitization
• ✅ No raw error exposure
• ✅ Timeout on long queries

Your AI agents can now safely explore your database. 🎉

#MCP #Tutorial #PostgreSQL #Database #mcpm