# Directory Structure
```
├── .gitignore
├── assets
│ └── logo.svg
├── docker-compose.yml
├── package-lock.json
├── package.json
├── prisma
│ └── schema.prisma
├── README.md
├── src
│ ├── index.ts
│ └── tools.ts
└── tsconfig.json
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | node_modules/
2 | build/
3 | *.log
4 | .env*
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # MCP PostgreSQL Server
2 |
3 | -<img src="assets/logo.svg" width="256" height="256" alt="MCP Postgres Logo" />
4 |
5 | A Model Context Protocol (MCP) server that provides PostgreSQL database operations through MCP tools.
6 |
7 | ## Features
8 |
9 | - CRUD operations for User and Post entities
10 | - Type-safe database operations using Prisma
11 | - MCP-compatible tool interface
12 | - Built with TypeScript for type safety
13 |
14 | ## Installation
15 |
16 | 1. Clone the repository
17 | 2. Install dependencies:
18 |
19 | ```bash
20 | git clone https://github.com/a21071/mcp-postgres.git
21 | cd mcp-postgres
22 | npm install
23 | ```
24 |
25 | 3. Set up PostgreSQL database:
26 |
27 | ```bash
28 | docker-compose up -d
29 | ```
30 |
31 | 4. Run database migrations:
32 |
33 | ```bash
34 | npx prisma migrate dev
35 | ```
36 |
37 | 5. Build the project:
38 |
39 | ```bash
40 | npm run build
41 | ```
42 |
43 | ## Usage
44 |
45 | Run the server:
46 |
47 | ```bash
48 | npm start
49 | ```
50 |
51 | ### Available MCP Tools
52 |
53 | - **getData**: Retrieve user data from PostgreSQL
54 |
55 | ```json
56 | {
57 | "tableName": "user"
58 | }
59 | ```
60 |
61 | - **addUserData**: Add new user to database
62 |
63 | ```json
64 | {
65 | "email": "[email protected]",
66 | "name": "John Doe",
67 | "age": 30
68 | }
69 | ```
70 |
71 | - **deleteUserData**: Delete user by ID, email or name
72 |
73 | ```json
74 | {
75 | "id": "clxyz...",
76 | "email": "[email protected]",
77 | "name": "John Doe"
78 | }
79 | ```
80 |
81 | - **updateUserData**: Update user information
82 | ```json
83 | {
84 | "id": "clxyz...",
85 | "email": "[email protected]",
86 | "name": "New Name"
87 | }
88 | ```
89 |
90 | ## Database Schema
91 |
92 | The server uses the following Prisma schema:
93 |
94 | ```prisma
95 | model User {
96 | id String @id @default(cuid())
97 | email String @unique
98 | name String?
99 | age Int?
100 | createdAt DateTime @default(now())
101 | posts Post[]
102 | }
103 |
104 | ```
105 |
106 | ## Development
107 |
108 | - Watch mode:
109 |
110 | ```bash
111 | npm run watch
112 | ```
113 |
114 | ## Dependencies
115 |
116 | - [@modelcontextprotocol/sdk](https://github.com/modelcontextprotocol/sdk) - MCP server SDK
117 | - [Prisma](https://www.prisma.io/) - Type-safe database client
118 | - [TypeScript](https://www.typescriptlang.org/) - Type checking
119 |
120 | ## License
121 |
122 | MIT
123 |
```
--------------------------------------------------------------------------------
/docker-compose.yml:
--------------------------------------------------------------------------------
```yaml
1 | services:
2 | postgres:
3 | image: "postgres:latest"
4 | container_name: mcp-postgres-db
5 | ports:
6 | - "5432:5432"
7 | environment:
8 | - POSTGRES_USER=postgres
9 | - POSTGRES_HOST=localhost
10 | - POSTGRES_PASSWORD=postgres
11 | - POSTGRES_DB=test
12 | volumes:
13 | - ./db-data/:/var/lib/postgresql/data/
14 |
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "compilerOptions": {
3 | "target": "ES2022",
4 | "module": "Node16",
5 | "moduleResolution": "Node16",
6 | "outDir": "./build",
7 | "rootDir": "./src",
8 | "strict": true,
9 | "esModuleInterop": true,
10 | "skipLibCheck": true,
11 | "forceConsistentCasingInFileNames": true
12 | },
13 | "include": ["src/**/*"],
14 | "exclude": ["node_modules", "src/**/__tests__/**"]
15 | }
16 |
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "name": "mcp-postgres",
3 | "version": "0.1.0",
4 | "description": "Repeats back whatever it is prompted for testing",
5 | "private": true,
6 | "type": "module",
7 | "bin": {
8 | "echo-server": "./build/index.js"
9 | },
10 | "files": [
11 | "build"
12 | ],
13 | "scripts": {
14 | "build": "tsc && node -e \"require('fs').chmodSync('build/index.js', '755')\"",
15 | "prepare": "npm run build",
16 | "watch": "tsc --watch",
17 | "inspector": "npx @modelcontextprotocol/inspector build/index.js",
18 | "test": "node --experimental-vm-modules node_modules/jest/bin/jest.js",
19 | "test:watch": "node --experimental-vm-modules node_modules/jest/bin/jest.js --watch",
20 | "test:name": "node --experimental-vm-modules node_modules/jest/bin/jest.js -t"
21 | },
22 | "dependencies": {
23 | "@modelcontextprotocol/sdk": "0.6.0",
24 | "@prisma/client": "^6.5.0",
25 | "prisma": "^6.5.0"
26 | },
27 | "devDependencies": {
28 | "@types/jest": "^29.5.14",
29 | "@types/node": "^20.11.24",
30 | "jest": "^29.7.0",
31 | "ts-jest": "^29.2.5",
32 | "typescript": "^5.3.3"
33 | }
34 | }
35 |
```
--------------------------------------------------------------------------------
/src/tools.ts:
--------------------------------------------------------------------------------
```typescript
1 | // Tool Definitions
2 | export const getDataTool = {
3 | name: "getData",
4 | description: "Retrieve data from PostgreSQL database",
5 | inputSchema: {
6 | type: "object",
7 | properties: {
8 | tableName: {
9 | type: "string",
10 | description: "Name of the table to query",
11 | },
12 | },
13 | required: ["tableName"],
14 | },
15 | };
16 |
17 | export const addUserDataTool = {
18 | name: "addUserData",
19 | description: "Add user data to PostgreSQL database",
20 | inputSchema: {
21 | type: "object",
22 | properties: {
23 | email: {
24 | type: "string",
25 | description: "User email address",
26 | },
27 | name: {
28 | type: "string",
29 | description: "User name",
30 | },
31 | age: {
32 | type: "number",
33 | description: "User age",
34 | },
35 | },
36 | required: ["tableName", "email", "name", "age"],
37 | },
38 | };
39 |
40 | export const deleteUserDataTool = {
41 | name: "deleteUserData",
42 | description: "Delete user data from PostgreSQL database",
43 | inputSchema: {
44 | type: "object",
45 | properties: {
46 | id: {
47 | type: "string",
48 | description: "User id",
49 | },
50 | email: {
51 | type: "string",
52 | description: "User email address",
53 | },
54 | name: {
55 | type: "string",
56 | description: "User name",
57 | },
58 | },
59 | required: [],
60 | },
61 | };
62 |
63 | export const updateUserDataTool = {
64 | name: "updateUserData",
65 | description: "Delete user data from PostgreSQL database",
66 | inputSchema: {
67 | type: "object",
68 | properties: {
69 | id: {
70 | type: "string",
71 | description: "User id",
72 | },
73 | email: {
74 | type: "string",
75 | description: "User email address",
76 | },
77 | name: {
78 | type: "string",
79 | description: "User name",
80 | },
81 | },
82 | required: [],
83 | },
84 | };
85 |
```
--------------------------------------------------------------------------------
/src/index.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { Server } from "@modelcontextprotocol/sdk/server/index.js";
2 | import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
3 | import {
4 | CallToolRequestSchema,
5 | ListToolsRequestSchema,
6 | Result,
7 | } from "@modelcontextprotocol/sdk/types.js";
8 | import { z } from "zod";
9 | import { PrismaClient } from "@prisma/client";
10 | import {
11 | addUserDataTool,
12 | deleteUserDataTool,
13 | getDataTool,
14 | updateUserDataTool,
15 | } from "./tools.js";
16 |
17 | // Database Client
18 | export const prismaClient = new PrismaClient();
19 |
20 | const TableNameSchema = z.object({
21 | tableName: z.string().describe("Name of the table to retrieve data from"),
22 | });
23 |
24 | const AddUserSchema = z.object({
25 | email: z.string().email().describe("User email address"),
26 | name: z.string().describe("User name"),
27 | age: z.number().int().positive().describe("User age"),
28 | });
29 |
30 | const DeleteUserSchema = z.object({
31 | id: z.string().describe("User Id").optional(),
32 | email: z.string().email().describe("User email address").optional(),
33 | name: z.string().describe("User name").optional(),
34 | });
35 |
36 | const tools = [
37 | getDataTool,
38 | addUserDataTool,
39 | deleteUserDataTool,
40 | updateUserDataTool,
41 | ];
42 |
43 | // Tool Handlers
44 | class DatabaseToolHandlers {
45 | async getData(tableName: string) {
46 | // Validate input
47 | // TableNameSchema.parse({ tableName });
48 |
49 | const data = await prismaClient.user.findMany({
50 | take: 100, // Limit results
51 | orderBy: { createdAt: "desc" },
52 | });
53 |
54 | return {
55 | content: data.map((user) => ({
56 | type: "text",
57 | text: JSON.stringify(user),
58 | })),
59 | };
60 | }
61 |
62 | async addUserData(params: z.infer<typeof AddUserSchema>) {
63 | // Validate input
64 | AddUserSchema.parse(params);
65 |
66 | const newUser = await prismaClient.user.create({
67 | data: {
68 | name: params.name,
69 | email: params.email,
70 | age: params.age,
71 | },
72 | });
73 |
74 | return {
75 | content: [
76 | {
77 | type: "text",
78 | text: JSON.stringify(newUser),
79 | },
80 | ],
81 | };
82 | }
83 |
84 | async deleteUserData(params: z.infer<typeof DeleteUserSchema>) {
85 | DeleteUserSchema.parse(params);
86 | const { count } = await prismaClient.user.deleteMany({
87 | where: {
88 | id: params.id,
89 | email: params.email,
90 | name: params.name,
91 | },
92 | });
93 | return {
94 | content: [{ type: "text", text: `${count} has been deleted` }],
95 | };
96 | }
97 | }
98 |
99 | // Server Configuration
100 | const server = new Server(
101 | {
102 | name: "mcp-postgres",
103 | version: "0.1.0",
104 | },
105 | {
106 | capabilities: {
107 | tools: {},
108 | },
109 | }
110 | );
111 |
112 | // Request Handlers
113 | server.setRequestHandler(ListToolsRequestSchema, async () => ({
114 | tools,
115 | }));
116 |
117 | const databaseToolHandlers = new DatabaseToolHandlers();
118 |
119 | server.setRequestHandler(
120 | CallToolRequestSchema,
121 | async (request): Promise<Result> => {
122 | const { name, arguments: args } = request.params;
123 |
124 | if (name === "getData") {
125 | return await databaseToolHandlers.getData(
126 | (args?.tableName as string) ?? "user"
127 | );
128 | }
129 | if (name === "addUserData") {
130 | return await databaseToolHandlers.addUserData({
131 | email: args?.email as string,
132 | name: args?.name as string,
133 | age: args?.age as number,
134 | });
135 | }
136 | if (name === "deleteUserData") {
137 | return await databaseToolHandlers.deleteUserData({});
138 | }
139 | throw new Error("Unknown tool");
140 | }
141 | );
142 |
143 | async function main() {
144 | const transport = new StdioServerTransport();
145 | await server.connect(transport);
146 | }
147 |
148 | main().catch((error) => {
149 | console.error("Server error:", error);
150 | process.exit(1);
151 | });
152 |
```
--------------------------------------------------------------------------------
/assets/logo.svg:
--------------------------------------------------------------------------------
```
1 | <svg height="32" viewBox="0 0 32 32" width="32" xmlns="http://www.w3.org/2000/svg"><path d="m22.839 0c-1.245.011-2.479.188-3.677.536l-.083.027c-.751-.131-1.516-.203-2.276-.219-1.573-.027-2.923.353-4.011.989-1.073-.369-3.297-1.016-5.641-.885-1.629.088-3.411.583-4.735 1.979-1.312 1.391-2.009 3.547-1.864 6.485.041.807.271 2.124.656 3.837.38 1.709.917 3.709 1.589 5.537.672 1.823 1.405 3.463 2.552 4.577.572.557 1.364 1.032 2.296.991.652-.027 1.24-.313 1.751-.735.249.328.516.468.755.599.308.167.599.281.907.355.552.14 1.495.323 2.599.135.375-.063.771-.187 1.167-.359.016.437.032.869.047 1.307.057 1.38.095 2.656.505 3.776.068.183.251 1.12.969 1.953.724.833 2.129 1.349 3.739 1.005 1.131-.24 2.573-.677 3.532-2.041.948-1.344 1.375-3.276 1.459-6.412.02-.172.047-.312.072-.448l.224.021h.027c1.208.052 2.521-.12 3.62-.631.968-.448 1.703-.901 2.239-1.708.131-.199.281-.443.319-.86.041-.411-.199-1.063-.595-1.364-.791-.604-1.291-.375-1.828-.26-.525.115-1.063.176-1.599.192 1.541-2.593 2.645-5.353 3.276-7.792.375-1.443.584-2.771.599-3.932.021-1.161-.077-2.187-.771-3.077-2.177-2.776-5.235-3.548-7.599-3.573-.073 0-.145 0-.219 0zm-.063.855c2.235-.021 5.093.604 7.145 3.228.464.589.6 1.448.584 2.511s-.213 2.328-.573 3.719c-.692 2.699-2.011 5.833-3.859 8.652.063.047.135.088.208.115.385.161 1.265.296 3.025-.063.443-.095.767-.156 1.105.099.167.14.255.349.244.568-.02.161-.077.317-.177.448-.339.509-1.009.995-1.869 1.396-.76.353-1.855.536-2.817.547-.489.005-.937-.032-1.319-.152l-.02-.004c-.147 1.411-.484 4.203-.704 5.473-.176 1.025-.484 1.844-1.072 2.453-.589.615-1.417.979-2.537 1.219-1.385.297-2.391-.021-3.041-.568s-.948-1.276-1.125-1.719c-.124-.307-.187-.703-.249-1.235-.063-.531-.104-1.177-.136-1.911-.041-1.12-.057-2.24-.041-3.365-.577.532-1.296.88-2.068 1.016-.921.156-1.739 0-2.228-.12-.24-.063-.475-.151-.693-.271-.229-.12-.443-.255-.588-.527-.084-.156-.109-.337-.073-.509.041-.177.145-.328.287-.443.265-.215.615-.333 1.14-.443.959-.199 1.297-.333 1.5-.496.172-.135.371-.416.713-.828 0-.015 0-.036-.005-.052-.619-.02-1.224-.181-1.771-.479-.197.208-1.224 1.292-2.468 2.792-.521.624-1.099.984-1.713 1.011-.609.025-1.163-.281-1.631-.735-.937-.912-1.688-2.48-2.339-4.251s-1.177-3.744-1.557-5.421c-.375-1.683-.599-3.037-.631-3.688-.14-2.776.511-4.645 1.625-5.828s2.641-1.625 4.131-1.713c2.672-.151 5.213.781 5.724.979.989-.672 2.265-1.088 3.859-1.063.756.011 1.505.109 2.24.292l.027-.016c.323-.109.651-.208.984-.28.907-.215 1.833-.324 2.76-.339zm.203.89h-.197c-.76.009-1.527.099-2.271.26 1.661.735 2.916 1.864 3.801 3 .615.781 1.12 1.64 1.505 2.557.152.355.251.651.303.88.031.115.047.213.057.312 0 .052.005.105-.021.193 0 .005-.005.016-.005.021.043 1.167-.249 1.957-.287 3.072-.025.808.183 1.756.235 2.792.047.973-.072 2.041-.703 3.093.052.063.099.125.151.193 1.672-2.636 2.88-5.547 3.521-8.032.344-1.339.525-2.552.541-3.509.016-.959-.161-1.657-.391-1.948-1.792-2.287-4.213-2.871-6.24-2.885zm-6.391.343c-1.572.005-2.703.48-3.561 1.193-.887.74-1.48 1.745-1.865 2.781-.464 1.224-.625 2.411-.688 3.219l.021-.011c.475-.265 1.099-.536 1.771-.687.667-.157 1.391-.204 2.041.052.657.249 1.193.848 1.391 1.749.939 4.344-.291 5.959-.744 7.177-.172.443-.323.891-.443 1.349.057-.011.115-.027.172-.032.323-.025.572.079.719.141.459.192.771.588.943 1.041.041.12.072.244.093.38.016.052.027.109.027.167-.052 1.661-.048 3.323.015 4.984.032.719.079 1.349.136 1.849.057.495.135.875.188 1.005.171.427.421.984.875 1.364.448.381 1.093.631 2.276.381 1.025-.224 1.656-.527 2.077-.964.423-.443.672-1.052.833-1.984.245-1.401.729-5.464.787-6.224-.025-.579.057-1.021.245-1.36.187-.344.479-.557.735-.672.124-.057.244-.093.343-.125-.104-.145-.213-.291-.323-.432-.364-.443-.667-.937-.891-1.463-.104-.22-.219-.439-.344-.647-.176-.317-.4-.719-.635-1.172-.469-.896-.979-1.989-1.245-3.052-.265-1.063-.301-2.161.376-2.932.599-.688 1.656-.973 3.233-.812-.047-.141-.072-.261-.151-.443-.359-.844-.828-1.636-1.391-2.355-1.339-1.713-3.511-3.412-6.859-3.469zm-8.853.068c-.167 0-.339.005-.505.016-1.349.079-2.62.468-3.532 1.432-.911.969-1.509 2.547-1.38 5.167.027.5.24 1.885.609 3.536.371 1.652.896 3.595 1.527 5.313.629 1.713 1.391 3.208 2.12 3.916.364.349.681.495.968.485.287-.016.636-.183 1.063-.693.776-.937 1.579-1.844 2.412-2.729-1.199-1.047-1.787-2.629-1.552-4.203.135-.984.156-1.907.135-2.636-.015-.708-.063-1.176-.063-1.473 0-.011 0-.016 0-.027v-.005l-.005-.009c0-1.537.272-3.057.792-4.5.375-.996.928-2 1.76-2.819-.817-.271-2.271-.676-3.843-.755-.167-.011-.339-.016-.505-.016zm16.53 7.041c-.905.016-1.411.251-1.681.552-.376.433-.412 1.193-.177 2.131.233.937.719 1.984 1.172 2.855.224.437.443.828.619 1.145.183.323.313.547.391.745.073.177.157.333.24.479.349-.74.412-1.464.375-2.224-.047-.937-.265-1.896-.229-2.864.037-1.136.261-1.876.277-2.751-.324-.041-.657-.068-.985-.068zm-10.978.158c-.276 0-.552.036-.823.099-.537.131-1.052.328-1.537.599-.161.088-.317.188-.463.303l-.032.025c.011.199.047.667.063 1.365.016.76 0 1.728-.145 2.776-.323 2.281 1.333 4.167 3.276 4.172.115-.469.301-.944.489-1.443.541-1.459 1.604-2.521.708-6.677-.145-.677-.437-.953-.839-1.109-.224-.079-.457-.115-.697-.109zm10.557.27h.068c.083.005.167.011.239.031.068.016.131.037.183.073.052.031.088.083.099.145v.011c0 .063-.016.125-.047.183-.041.072-.088.14-.145.197-.136.151-.319.251-.516.281-.193.027-.385-.025-.547-.135-.063-.048-.125-.1-.172-.157-.047-.047-.073-.109-.084-.172-.004-.061.011-.124.052-.171.048-.048.1-.089.157-.12.129-.073.301-.125.5-.152.072-.009.145-.015.213-.02zm-10.428.224c.068 0 .147.005.22.015.208.032.385.084.525.167.068.032.131.084.177.141.052.063.077.14.073.224-.016.077-.048.151-.1.208-.057.068-.119.125-.192.172-.172.125-.385.177-.599.151-.215-.036-.412-.14-.557-.301-.063-.068-.115-.141-.157-.219-.047-.073-.067-.156-.057-.24.021-.14.141-.219.256-.26.131-.043.271-.057.411-.052zm12.079 9.791h-.005c-.192.073-.353.1-.489.163-.14.052-.251.156-.317.285-.089.152-.156.423-.136.885.057.043.125.073.199.095.224.068.609.115 1.036.109.849-.011 1.896-.208 2.453-.469.453-.208.88-.489 1.255-.817-1.859.38-2.905.281-3.552.016-.156-.068-.307-.157-.443-.267zm-10.708.125h-.027c-.072.005-.172.032-.375.251-.464.52-.625.848-1.005 1.151-.385.307-.88.469-1.875.672-.312.063-.495.135-.615.192.036.032.036.043.093.068.147.084.333.152.485.193.427.104 1.124.229 1.859.104.729-.125 1.489-.475 2.141-1.385.115-.156.124-.391.031-.641-.093-.244-.297-.463-.437-.52-.089-.043-.183-.068-.276-.084z"/></svg>
```