# 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:
--------------------------------------------------------------------------------
```
node_modules/
build/
*.log
.env*
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# MCP PostgreSQL Server
-<img src="assets/logo.svg" width="256" height="256" alt="MCP Postgres Logo" />
A Model Context Protocol (MCP) server that provides PostgreSQL database operations through MCP tools.
## Features
- CRUD operations for User and Post entities
- Type-safe database operations using Prisma
- MCP-compatible tool interface
- Built with TypeScript for type safety
## Installation
1. Clone the repository
2. Install dependencies:
```bash
git clone https://github.com/a21071/mcp-postgres.git
cd mcp-postgres
npm install
```
3. Set up PostgreSQL database:
```bash
docker-compose up -d
```
4. Run database migrations:
```bash
npx prisma migrate dev
```
5. Build the project:
```bash
npm run build
```
## Usage
Run the server:
```bash
npm start
```
### Available MCP Tools
- **getData**: Retrieve user data from PostgreSQL
```json
{
"tableName": "user"
}
```
- **addUserData**: Add new user to database
```json
{
"email": "[email protected]",
"name": "John Doe",
"age": 30
}
```
- **deleteUserData**: Delete user by ID, email or name
```json
{
"id": "clxyz...",
"email": "[email protected]",
"name": "John Doe"
}
```
- **updateUserData**: Update user information
```json
{
"id": "clxyz...",
"email": "[email protected]",
"name": "New Name"
}
```
## Database Schema
The server uses the following Prisma schema:
```prisma
model User {
id String @id @default(cuid())
email String @unique
name String?
age Int?
createdAt DateTime @default(now())
posts Post[]
}
```
## Development
- Watch mode:
```bash
npm run watch
```
## Dependencies
- [@modelcontextprotocol/sdk](https://github.com/modelcontextprotocol/sdk) - MCP server SDK
- [Prisma](https://www.prisma.io/) - Type-safe database client
- [TypeScript](https://www.typescriptlang.org/) - Type checking
## License
MIT
```
--------------------------------------------------------------------------------
/docker-compose.yml:
--------------------------------------------------------------------------------
```yaml
services:
postgres:
image: "postgres:latest"
container_name: mcp-postgres-db
ports:
- "5432:5432"
environment:
- POSTGRES_USER=postgres
- POSTGRES_HOST=localhost
- POSTGRES_PASSWORD=postgres
- POSTGRES_DB=test
volumes:
- ./db-data/:/var/lib/postgresql/data/
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
{
"compilerOptions": {
"target": "ES2022",
"module": "Node16",
"moduleResolution": "Node16",
"outDir": "./build",
"rootDir": "./src",
"strict": true,
"esModuleInterop": true,
"skipLibCheck": true,
"forceConsistentCasingInFileNames": true
},
"include": ["src/**/*"],
"exclude": ["node_modules", "src/**/__tests__/**"]
}
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
{
"name": "mcp-postgres",
"version": "0.1.0",
"description": "Repeats back whatever it is prompted for testing",
"private": true,
"type": "module",
"bin": {
"echo-server": "./build/index.js"
},
"files": [
"build"
],
"scripts": {
"build": "tsc && node -e \"require('fs').chmodSync('build/index.js', '755')\"",
"prepare": "npm run build",
"watch": "tsc --watch",
"inspector": "npx @modelcontextprotocol/inspector build/index.js",
"test": "node --experimental-vm-modules node_modules/jest/bin/jest.js",
"test:watch": "node --experimental-vm-modules node_modules/jest/bin/jest.js --watch",
"test:name": "node --experimental-vm-modules node_modules/jest/bin/jest.js -t"
},
"dependencies": {
"@modelcontextprotocol/sdk": "0.6.0",
"@prisma/client": "^6.5.0",
"prisma": "^6.5.0"
},
"devDependencies": {
"@types/jest": "^29.5.14",
"@types/node": "^20.11.24",
"jest": "^29.7.0",
"ts-jest": "^29.2.5",
"typescript": "^5.3.3"
}
}
```
--------------------------------------------------------------------------------
/src/tools.ts:
--------------------------------------------------------------------------------
```typescript
// Tool Definitions
export const getDataTool = {
name: "getData",
description: "Retrieve data from PostgreSQL database",
inputSchema: {
type: "object",
properties: {
tableName: {
type: "string",
description: "Name of the table to query",
},
},
required: ["tableName"],
},
};
export const addUserDataTool = {
name: "addUserData",
description: "Add user data to PostgreSQL database",
inputSchema: {
type: "object",
properties: {
email: {
type: "string",
description: "User email address",
},
name: {
type: "string",
description: "User name",
},
age: {
type: "number",
description: "User age",
},
},
required: ["tableName", "email", "name", "age"],
},
};
export const deleteUserDataTool = {
name: "deleteUserData",
description: "Delete user data from PostgreSQL database",
inputSchema: {
type: "object",
properties: {
id: {
type: "string",
description: "User id",
},
email: {
type: "string",
description: "User email address",
},
name: {
type: "string",
description: "User name",
},
},
required: [],
},
};
export const updateUserDataTool = {
name: "updateUserData",
description: "Delete user data from PostgreSQL database",
inputSchema: {
type: "object",
properties: {
id: {
type: "string",
description: "User id",
},
email: {
type: "string",
description: "User email address",
},
name: {
type: "string",
description: "User name",
},
},
required: [],
},
};
```
--------------------------------------------------------------------------------
/src/index.ts:
--------------------------------------------------------------------------------
```typescript
import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import {
CallToolRequestSchema,
ListToolsRequestSchema,
Result,
} from "@modelcontextprotocol/sdk/types.js";
import { z } from "zod";
import { PrismaClient } from "@prisma/client";
import {
addUserDataTool,
deleteUserDataTool,
getDataTool,
updateUserDataTool,
} from "./tools.js";
// Database Client
export const prismaClient = new PrismaClient();
const TableNameSchema = z.object({
tableName: z.string().describe("Name of the table to retrieve data from"),
});
const AddUserSchema = z.object({
email: z.string().email().describe("User email address"),
name: z.string().describe("User name"),
age: z.number().int().positive().describe("User age"),
});
const DeleteUserSchema = z.object({
id: z.string().describe("User Id").optional(),
email: z.string().email().describe("User email address").optional(),
name: z.string().describe("User name").optional(),
});
const tools = [
getDataTool,
addUserDataTool,
deleteUserDataTool,
updateUserDataTool,
];
// Tool Handlers
class DatabaseToolHandlers {
async getData(tableName: string) {
// Validate input
// TableNameSchema.parse({ tableName });
const data = await prismaClient.user.findMany({
take: 100, // Limit results
orderBy: { createdAt: "desc" },
});
return {
content: data.map((user) => ({
type: "text",
text: JSON.stringify(user),
})),
};
}
async addUserData(params: z.infer<typeof AddUserSchema>) {
// Validate input
AddUserSchema.parse(params);
const newUser = await prismaClient.user.create({
data: {
name: params.name,
email: params.email,
age: params.age,
},
});
return {
content: [
{
type: "text",
text: JSON.stringify(newUser),
},
],
};
}
async deleteUserData(params: z.infer<typeof DeleteUserSchema>) {
DeleteUserSchema.parse(params);
const { count } = await prismaClient.user.deleteMany({
where: {
id: params.id,
email: params.email,
name: params.name,
},
});
return {
content: [{ type: "text", text: `${count} has been deleted` }],
};
}
}
// Server Configuration
const server = new Server(
{
name: "mcp-postgres",
version: "0.1.0",
},
{
capabilities: {
tools: {},
},
}
);
// Request Handlers
server.setRequestHandler(ListToolsRequestSchema, async () => ({
tools,
}));
const databaseToolHandlers = new DatabaseToolHandlers();
server.setRequestHandler(
CallToolRequestSchema,
async (request): Promise<Result> => {
const { name, arguments: args } = request.params;
if (name === "getData") {
return await databaseToolHandlers.getData(
(args?.tableName as string) ?? "user"
);
}
if (name === "addUserData") {
return await databaseToolHandlers.addUserData({
email: args?.email as string,
name: args?.name as string,
age: args?.age as number,
});
}
if (name === "deleteUserData") {
return await databaseToolHandlers.deleteUserData({});
}
throw new Error("Unknown tool");
}
);
async function main() {
const transport = new StdioServerTransport();
await server.connect(transport);
}
main().catch((error) => {
console.error("Server error:", error);
process.exit(1);
});
```
--------------------------------------------------------------------------------
/assets/logo.svg:
--------------------------------------------------------------------------------
```
<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>
```