Happy slice of mcp pizzaMCP.pizza

MCP_Client

MCP.Pizza Chef: andrewdeng318

MCP_Client is a specialized client implementation of the Model Context Protocol designed to interface with PostgreSQL databases. It allows language models to interact directly with PostgreSQL data sources, enabling real-time querying, data retrieval, and manipulation within an LLM-enhanced workflow. This client supports integration with local LLM servers such as LM Studio, facilitating secure and efficient database operations through structured context feeding. MCP_Client is ideal for developers looking to build AI applications that require dynamic access to relational data, combining the power of LLMs with robust PostgreSQL backend capabilities.

Use This MCP client To

Query PostgreSQL databases via LLMs in real time Integrate PostgreSQL data into AI-enhanced workflows Enable LLMs to read and write relational data securely Build AI copilots with direct database access Automate data retrieval and updates using natural language Support multi-step reasoning with live database context

README

MCP PostgreSQL Client

A PostgreSQL client implementation based on the Model Context Protocol.

alt text

Tutorial Video

For a detailed walkthrough of how to setup LM Studio, check out our tutorial videos:

YouTube Tutorial: MCP PostgreSQL Client Tutorial

Bilibili Tutorial: Watch on Bilibili

Prerequisites

  1. Install LM Studio:

    • Download and install LM Studio
    • Start the local server in LM Studio
    • Set AI_TYPE=lm_studio in your .env file
    • Make sure the local server is running before starting the application

  2. Initialize PostgreSQL Database: First, make sure you have PostgreSQL installed and running. Then create the sample database and table:

-- Create book table
CREATE TABLE book (
    id SERIAL PRIMARY KEY,         -- Auto-incrementing primary key
    title VARCHAR(255) NOT NULL,   -- Book title
    author VARCHAR(255) NOT NULL,  -- Author name
    published_date DATE,           -- Publication date
    genre VARCHAR(100),            -- Book genre
    price NUMERIC(10, 2)           -- Price
);

-- Insert sample data
INSERT INTO book (title, author, published_date, genre, price) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', '1925-04-10', 'Classic', 10.99),
('1984', 'George Orwell', '1949-06-08', 'Dystopian', 8.99),
('To Kill a Mockingbird', 'Harper Lee', '1960-07-11', 'Classic', 12.99),
('Pride and Prejudice', 'Jane Austen', '1813-01-28', 'Romance', 9.99),
('The Catcher in the Rye', 'J.D. Salinger', '1951-07-16', 'Classic', 10.49),
('The Hobbit', 'J.R.R. Tolkien', '1937-09-21', 'Fantasy', 14.99),
('Moby Dick', 'Herman Melville', '1851-10-18', 'Adventure', 11.99),
('War and Peace', 'Leo Tolstoy', '1869-01-01', 'Historical', 15.99),
('Crime and Punishment', 'Fyodor Dostoevsky', '1866-01-01', 'Crime', 13.49),
('The Alchemist', 'Paulo Coelho', '1988-01-01', 'Fiction', 9.49),
('The Lord of the Rings', 'J.R.R. Tolkien', '1954-07-29', 'Fantasy', 20.99),
('Harry Potter and the Sorcerer''s Stone', 'J.K. Rowling', '1997-06-26', 'Fantasy', 19.99),
('The Hunger Games', 'Suzanne Collins', '2008-09-14', 'Dystopian', 15.49),
('Brave New World', 'Aldous Huxley', '1932-01-01', 'Dystopian', 10.99),
('Jane Eyre', 'Charlotte Brontë', '1847-10-16', 'Romance', 8.99),
('Wuthering Heights', 'Emily Brontë', '1847-12-01', 'Romance', 9.49),
('Frankenstein', 'Mary Shelley', '1818-01-01', 'Horror', 7.99),
('Dracula', 'Bram Stoker', '1897-05-26', 'Horror', 6.99),
('The Odyssey', 'Homer', '800-01-01', 'Epic', 12.49),
('Don Quixote', 'Miguel de Cervantes', '1605-01-01', 'Adventure', 11.49);
  1. Download MCP PostgreSQL Server:
git clone https://github.com/modelcontextprotocol/servers.git
cd servers
git checkout main
  1. Build the PostgreSQL server:
cd src/postgres
npm install
npm run build

The build process will create dist/index.js which will be used as the server entry point.

  1. Copy the PostgreSQL server implementation or update your environment variables to point to the built server:
# Option 1: Copy the server
cp -r src/postgres /path/to/your/project/server

# Option 2: Update POSTGRES_SERVER_PATH in .env
POSTGRES_SERVER_PATH=/path/to/servers/src/postgres/dist/index.js

Project Description

This project provides a PostgreSQL client implementation using the Model Context Protocol, supporting database operations and web server functionality.

Requirements

  • Node.js (Latest LTS version recommended)
  • PostgreSQL database
  • npm or yarn package manager

Installation

  1. After cloning the project, navigate to the project directory:
cd clients
  1. Install dependencies:
npm install
  1. Configure environment variables: Create a .env file and set the following variables: 
    DATABASE_URL=your_postgresql_database_url
PORT=server_port_number (default 3000)

Usage

Build the project

npm run build

Run in development mode

npm run dev

Start the web server

npm run web

Project Structure

  • src/
    • index.ts - Main entry file
    • server.ts - Web server implementation
    • PostgresClient.ts - PostgreSQL client implementation

Main Features

  • PostgreSQL database operations
  • Web API service
  • Model Context Protocol integration

Important Notes

  1. Ensure environment variables are properly configured before running
  2. Make sure PostgreSQL database service is running
  3. Verify that required ports are not in use

Tech Stack

  • TypeScript
  • Express.js
  • PostgreSQL
  • Model Context Protocol SDK
  • dotenv
  • OpenAI SDK
  • Zod

MCP_Client FAQ

How do I set up MCP_Client with LM Studio?
Install LM Studio, start its local server, set AI_TYPE=lm_studio in your .env file, and ensure the server is running before launching MCP_Client.
What prerequisites are needed for MCP_Client?
You need a running PostgreSQL instance with the appropriate database and tables created, plus LM Studio or a compatible LLM server.
Can MCP_Client handle complex SQL queries?
Yes, MCP_Client supports executing complex queries by feeding structured context to LLMs for advanced data operations.
Is MCP_Client limited to local LLM servers?
While optimized for local servers like LM Studio, MCP_Client can be adapted to other LLM providers supporting MCP, such as OpenAI, Claude, and Gemini.
How does MCP_Client ensure secure database access?
It uses scoped, protocol-driven interactions that limit LLM access to authorized queries and data, maintaining security and observability.
Where can I find tutorials for MCP_Client?
Detailed setup and usage tutorials are available on YouTube and Bilibili, linked in the MCP_Client documentation.
Does MCP_Client support real-time data updates?
Yes, it enables LLMs to interact with live PostgreSQL data, supporting dynamic querying and updates within workflows.
What programming languages can I use with MCP_Client?
MCP_Client is protocol-based and can be integrated with any language that supports MCP client implementations.