NetanelBollag simple psql mcp

NetanelBollag simple psql mcp avatar

by NetanelBollag

A beginner-friendly MCP server template featuring a PostgreSQL connector with clean, easy-to-understand code. Perfect for developers new to Model Context Protocol who want to experiment and create their own AI tool connectors with minimal setup.

What is NetanelBollag simple psql mcp

Simple PostgreSQL MCP Server

This is a template project for those looking to build their own MCP servers. I designed it to be dead simple to understand and adapt - the code is straightforward with MCP docs attached so you can quickly get up to speed.

What is MCP?

TL;DR - It's a way to write plugins for AI

Model Context Protocol (MCP) is a standard way for LLMs to interact with external tools and data. In a nutshell:

  • Tools allow the LLM to execute commands (like running a database query)
  • Resources are data you can attach to conversations (like attaching a file to a prompt)
  • Prompts are templates that generate consistent LLM instructions

Features

This PostgreSQL MCP server implements:

  1. Tools

    • execute_query - Run SQL queries against your database
    • test_connection - Verify the database connection is working
  2. Resources

    • db://tables - List of all tables in the schema
    • db://tables/{table_name} - Schema information for a specific table
    • db://schema - Complete schema information for all tables in the database
  3. Prompts

    • Query generation templates
    • Analytical query builders
    • Based on the templates in this repo

Prerequisites

  • Python 3.8+
  • uv - Modern Python package manager and installer
  • npx (included with Node.js)
  • PostgreSQL database you can connect to

Quick Setup

  1. Create a virtual environment and install dependencies:

    # Create a virtual environment with uv
    uv venv
    
    # Activate the virtual environment
    source .venv/bin/activate  # On Windows: .venv\Scripts\activate
    
    # Install dependencies
    uv pip install -r requirements.txt
    
  2. Run the server with the MCP Inspector:

    # Replace with YOUR actual database credentials
    npx @modelcontextprotocol/inspector uv --directory . run postgres -e DSN=postgresql://username:password@hostname:port/database -e SCHEMA=public
    

    Note: If this is your first time running npx, you'll be prompted to approve the installation. Type 'y' to proceed.

    After running this command, you'll see the MCP Inspector interface launched in your browser. You should see a message like:

    MCP Inspector is up and running at http://localhost:5173
    

    If the browser doesn't open automatically, copy and paste the URL into your browser. You should see something like this: MCP Inspector Interface

  3. Using the Inspector:

    • Click the "Connect" button in the interface (unless there's an error message in the console on the bottom left)
    • Explore the "Tools", "Resources", and "Prompts" tabs to see available functionality
    • Try clicking on listed commands or typing resource names to retrieve resources and prompts
    • The interface allows you to test queries and see how the MCP server responds
  4. Take a look at the official docs

    Official server developers guide: https://modelcontextprotocol.io/quickstart/server

    More on the inspector: https://modelcontextprotocol.io/docs/tools/inspector

Connect Your AI Tool to the Server

You can configure the MCP server for your AI assistant by creating an MCP configuration file:

{
   "mcpServers": {
      "postgres": {
         "command": "/path/to/uv",
         "args": [
            "--directory",
            "/path/to/simple-psql-mcp",
            "run",
            "postgres"
         ],
         "env": {
            "DSN": "postgresql://username:password@localhost:5432/my-db",
            "SCHEMA": "public"
         }
      }
   }
}

Alternatively, you can generate this config file using the included script:

# Make the script executable
chmod +x generate_mcp_config.sh

# Run the configuration generator
./generate_mcp_config.sh

When prompted, enter your PostgreSQL DSN and schema name.

How to use it

You can now ask the LLM questions about your data in natural language:

  • "What are all the tables in my database?"
  • "Show me the top 5 users by creation date"
  • "Count addresses by state"

For testing, Claude Desktop supports MCP natively and works with all features (tools, resources, and prompts) right out of the box.

Example Database (Optional)

If you don't have a database ready or encounter connection issues, you can use the included example database:

# Make the script executable
chmod +x example-db/create-db.sh

# Run the database setup script
./example-db/create-db.sh

This script creates a Docker container with a PostgreSQL database pre-populated with sample users and addresses tables. After running, you can connect using:

npx @modelcontextprotocol/inspector uv --directory . run postgres -e DSN=postgresql://postgres:postgres@localhost:5432/user_database -e SCHEMA=public

Next Steps

To extend this project with your own MCP servers:

  1. Create a new directory under /src (e.g., /src/my-new-mcp)
  2. Implement your MCP server following the PostgreSQL example
  3. Add your new MCP to pyproject.toml:
[project.scripts]
postgres = "src.postgres:main"
my-new-mcp = "src.my-new-mcp:main"

You can then run your new MCP with:

npx @modelcontextprotocol/inspector uv --directory . run my-new-mcp

Documentation

Security

This is an experimental project meant to empower developers to create their own MCP server. I did minimum to make sure it won't die immediately when you try it, but be careful - it's very easy to run SQL injections with this tool. The server will check if the query starts with SELECT, but beyond that nothing is guaranteed. TL;DR - don't run in production unless you're the founder and there are no paying clients.

License

MIT

Leave a Comment

Frequently Asked Questions

What is MCP?

MCP (Model Context Protocol) is an open protocol that standardizes how applications provide context to LLMs. Think of MCP like a USB-C port for AI applications, providing a standardized way to connect AI models to different data sources and tools.

What are MCP Servers?

MCP Servers are lightweight programs that expose specific capabilities through the standardized Model Context Protocol. They act as bridges between LLMs like Claude and various data sources or services, allowing secure access to files, databases, APIs, and other resources.

How do MCP Servers work?

MCP Servers follow a client-server architecture where a host application (like Claude Desktop) connects to multiple servers. Each server provides specific functionality through standardized endpoints and protocols, enabling Claude to access data and perform actions through the standardized protocol.

Are MCP Servers secure?

Yes, MCP Servers are designed with security in mind. They run locally with explicit configuration and permissions, require user approval for actions, and include built-in security features to prevent unauthorized access and ensure data privacy.

Related MCP Servers

chrisdoc hevy mcp avatar

chrisdoc hevy mcp

mcp
sylphlab pdf reader mcp avatar

sylphlab pdf reader mcp

An MCP server built with Node.js/TypeScript that allows AI agents to securely read PDF files (local or URL) and extract text, metadata, or page counts. Uses pdf-parse.

pdf-parsetypescriptnodejs
aashari mcp server atlassian bitbucket avatar

aashari mcp server atlassian bitbucket

Node.js/TypeScript MCP server for Atlassian Bitbucket. Enables AI systems (LLMs) to interact with workspaces, repositories, and pull requests via tools (list, get, comment, search). Connects AI directly to version control workflows through the standard MCP interface.

atlassianrepositorymcp
aashari mcp server atlassian confluence avatar

aashari mcp server atlassian confluence

Node.js/TypeScript MCP server for Atlassian Confluence. Provides tools enabling AI systems (LLMs) to list/get spaces & pages (content formatted as Markdown) and search via CQL. Connects AI seamlessly to Confluence knowledge bases using the standard MCP interface.

atlassianmcpconfluence
prisma prisma avatar

prisma prisma

Next-generation ORM for Node.js & TypeScript | PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, MongoDB and CockroachDB

cockroachdbgomcp
Zzzccs123 mcp sentry avatar

Zzzccs123 mcp sentry

mcp sentry for typescript sdk

mcptypescript
zhuzhoulin dify mcp server avatar

zhuzhoulin dify mcp server

mcp
zhongmingyuan mcp my mac avatar

zhongmingyuan mcp my mac

mcp
zhixiaoqiang desktop image manager mcp avatar

zhixiaoqiang desktop image manager mcp

MCP 服务器,用于管理桌面图片、查看详情、压缩、移动等(完全让Trae实现)

mcp
zhixiaoqiang antd components mcp avatar

zhixiaoqiang antd components mcp

An MCP service for Ant Design components query | 一个减少 Ant Design 组件代码生成幻觉的 MCP 服务,包含系统提示词、组件文档、API 文档、代码示例和更新日志查询

designantdapi

Submit Your MCP Server

Share your MCP server with the community

Submit Now