A PostgreSQL client implementation based on the Model Context Protocol.
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
-
Install LM Studio:
- Download and install LM Studio
- Start the local server in LM Studio
- Set
AI_TYPE=lm_studioin your .env file - Make sure the local server is running before starting the application
-
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);
- Download MCP PostgreSQL Server:
git clone https://github.com/modelcontextprotocol/servers.git
cd servers
git checkout main- Build the PostgreSQL server:
cd src/postgres
npm install
npm run buildThe build process will create dist/index.js which will be used as the server entry point.
- 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
This project provides a PostgreSQL client implementation using the Model Context Protocol, supporting database operations and web server functionality.
- Node.js (Latest LTS version recommended)
- PostgreSQL database
- npm or yarn package manager
- After cloning the project, navigate to the project directory:
cd clients- Install dependencies:
npm install
- Configure environment variables:
Create a
.envfile and set the following variables:DATABASE_URL=your_postgresql_database_url PORT=server_port_number (default 3000)
npm run build
npm run dev
npm run web
src/index.ts- Main entry fileserver.ts- Web server implementationPostgresClient.ts- PostgreSQL client implementation
- PostgreSQL database operations
- Web API service
- Model Context Protocol integration
- Ensure environment variables are properly configured before running
- Make sure PostgreSQL database service is running
- Verify that required ports are not in use
- TypeScript
- Express.js
- PostgreSQL
- Model Context Protocol SDK
- dotenv
- OpenAI SDK
- Zod