# Directory Structure
```
├── .cursor
│ └── mcp.json
├── .gitignore
├── package-lock.json
├── package.json
├── README.md
└── server.js
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | # Dependency directories
2 | node_modules/
3 | jspm_packages/
4 |
5 | # Logs
6 | logs
7 | *.log
8 | npm-debug.log*
9 | yarn-debug.log*
10 | yarn-error.log*
11 | lerna-debug.log*
12 | .pnpm-debug.log*
13 |
14 | # Environment variables
15 | .env
16 | .env.local
17 | .env.development.local
18 | .env.test.local
19 | .env.production.local
20 |
21 | # Build outputs
22 | dist/
23 | build/
24 | out/
25 |
26 | # Virtual environment
27 | venv/
28 | .venv/
29 |
30 | # Coverage directory used by tools like istanbul
31 | coverage/
32 | *.lcov
33 |
34 | # TypeScript cache
35 | *.tsbuildinfo
36 |
37 | # Optional npm cache directory
38 | .npm
39 |
40 | # Optional eslint cache
41 | .eslintcache
42 |
43 | # Optional REPL history
44 | .node_repl_history
45 |
46 | # Output of 'npm pack'
47 | *.tgz
48 |
49 | # Yarn Integrity file
50 | .yarn-integrity
51 |
52 | # dotenv environment variable files
53 | .env
54 | .env.development.local
55 | .env.test.local
56 | .env.production.local
57 | .env.local
58 |
59 | # Mac OS
60 | .DS_Store
61 |
62 | # IDE specific files
63 | .idea/
64 | .vscode/
65 | *.swp
66 | *.swo
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # Model Context Protocol PostgreSQL Server
2 |
3 | This project implements a Model Context Protocol (MCP) server that connects to a PostgreSQL database. It allows AI models to interact with your database through a standardized protocol.
4 |
5 | ## Features
6 |
7 | - Connects to a PostgreSQL database using connection pooling
8 | - Implements the Model Context Protocol for AI model interaction
9 | - Provides database schema information as resources
10 | - Allows executing SQL queries with retry logic
11 | - Handles connection errors gracefully
12 |
13 | ## Prerequisites
14 |
15 | - Node.js 20 or higher
16 | - PostgreSQL database
17 | - Access credentials for the database
18 |
19 | ## Installation
20 |
21 | 1. Clone this repository
22 | 2. Install dependencies:
23 |
24 | ```bash
25 | npm install
26 | ```
27 |
28 | ## Configuration
29 |
30 | The server reads database credentials from a `.env` file in the project root directory. You need to add your database credentials as a JSON string in the `DB_CREDENTIALS` environment variable:
31 |
32 | 1. Create a `.env` file in the project root:
33 |
34 | ```bash
35 | touch .env
36 | ```
37 |
38 | 2. Add the following line with your actual database credentials:
39 |
40 | ```bash
41 | export DB_CREDENTIALS='{"DB_USER":"your-username","DB_PASSWORD":"your-password","DB_HOST":"your-host","DB_PORT":"5433","DB_NAME":"your-database"}'
42 | ```
43 |
44 | ### Fallback to Shell Config Files
45 |
46 | If the `.env` file is not present or the credentials variable is not found, the server will automatically look for the credentials in your shell configuration files in the following order:
47 |
48 | 1. `~/.zshrc`
49 | 2. `~/.bashrc`
50 | 3. `~/.bash_profile`
51 | 4. `~/.profile`
52 |
53 | This is especially useful in environments where shell config files are not automatically sourced, such as the Cursor MCP environment.
54 |
55 | To set up credentials in any of your shell config files:
56 |
57 | 1. Open your preferred shell config file, for example:
58 |
59 | ```bash
60 | nano ~/.zshrc
61 | # or
62 | nano ~/.bashrc
63 | ```
64 |
65 | 2. Add the following line with your actual database credentials:
66 |
67 | ```bash
68 | export DB_CREDENTIALS='{"DB_USER":"your-username","DB_PASSWORD":"your-password","DB_HOST":"your-host","DB_PORT":"5433","DB_NAME":"your-database"}'
69 | ```
70 |
71 | The server will automatically detect and use these credentials when the `.env` file is not available.
72 |
73 | ### Custom Credentials Variable
74 |
75 | You can also use a custom environment variable name instead of `DB_CREDENTIALS` by using the `--credentials-var` flag when starting the server:
76 |
77 | ```bash
78 | node server.js --credentials-var MY_CUSTOM_DB_CREDS
79 | ```
80 |
81 | In this case, you would define `MY_CUSTOM_DB_CREDS` in your `.env` file instead.
82 |
83 | ### Combining Options
84 |
85 | You can combine different command-line options as needed:
86 |
87 | ```bash
88 | # Use custom credentials and enable verbose mode
89 | node server.js --credentials-var MY_CUSTOM_DB_CREDS --verbose
90 |
91 | # Short form also works
92 | node server.js -c MY_CUSTOM_DB_CREDS -v
93 | ```
94 |
95 | ## Usage
96 |
97 | Start the MCP server:
98 |
99 | ```bash
100 | # Directly with Node.js
101 | node server.js
102 |
103 | # Or with npm
104 | npm start
105 | ```
106 |
107 | ### Logging Options
108 |
109 | By default, the server runs in silent mode, displaying only error messages. If you want to see all log messages, you can use the verbose flag:
110 |
111 | ```bash
112 | # With verbose logging
113 | node server.js --verbose
114 |
115 | # Or with npm
116 | npm start -- --verbose
117 | ```
118 |
119 | You can also use the short flag `-v`:
120 |
121 | ```bash
122 | node server.js -v
123 | ```
124 |
125 | The server will:
126 | 1. Test the database connection
127 | 2. Start the MCP server using stdio transport
128 | 3. Handle requests from AI models
129 |
130 | ## Integration with Cursor
131 |
132 | This server supports the Model Context Protocol (MCP) and integrates with Cursor AI.
133 |
134 | ### Automatic Configuration
135 |
136 | This project includes a pre-configured `.cursor/mcp.json` file for automatic setup within Cursor.
137 |
138 | ### Manual Configuration
139 |
140 | To manually add this server to Cursor:
141 |
142 | 1. Go to Cursor Settings → Features → MCP
143 | 2. Click "+ Add New MCP Server"
144 | 3. Enter the following details:
145 | - **Name**: Postgres MCP
146 | - **Type**: stdio
147 | - **Command**: `node /full/path/to/server.js`
148 |
149 | For more information on MCP integration with Cursor, see the [official documentation](https://cursor.sh/docs/mcp).
150 |
151 | ## Available Tools
152 |
153 | The server provides the following tools to AI models:
154 |
155 | - `query`: Execute SQL queries with retry logic
156 |
157 | ## Resources
158 |
159 | The server exposes database tables as resources, allowing AI models to:
160 |
161 | - List all tables in the database
162 | - View schema information for each table
163 |
164 | ## Error Handling
165 |
166 | The server includes:
167 |
168 | - Connection retry logic
169 | - Detailed error logging
170 | - Graceful shutdown handling
171 |
172 | ## Troubleshooting
173 |
174 | ### Connection Issues
175 |
176 | 1. **Database Connection Failed**
177 | - Check if PostgreSQL is running: `pg_isready -h localhost -p 5433`
178 | - Verify your credentials in the `.env` file are correct
179 | - Make sure your IP address has access to the database (check pg_hba.conf)
180 | - Try connecting with another tool like `psql` to verify credentials
181 |
182 | 2. **Environment Variable Problems**
183 | - Make sure your `.env` file is in the project root directory
184 | - Check that the JSON structure in `DB_CREDENTIALS` is valid
185 | - Verify there are no extra spaces or line breaks in the JSON string
186 | - Test with: `node -e "console.log(JSON.parse(process.env.DB_CREDENTIALS))" < .env`
187 |
188 | 3. **Node.js Version Issues**
189 | - Check your Node.js version: `node -v`
190 | - This server requires Node.js 20+
191 | - If using an older version, install Node.js 20: `nvm install 20 && nvm use 20`
192 |
193 | ### Cursor Integration
194 |
195 | 1. **Server Not Showing in Cursor**
196 | - Make sure the `.cursor/mcp.json` file exists and is properly formatted
197 | - Try restarting Cursor to detect the project-specific configuration
198 | - Check Cursor logs for any error messages
199 |
200 | 2. **"Failed to create client" Error**
201 | - This usually indicates the server crashed during startup
202 | - Run the server manually with verbose logging to see the error: `node server.js -v`
203 | - Check if the database credentials are accessible in the Cursor environment
204 |
205 | 3. **No Tools Available in Cursor**
206 | - Ensure the server is running properly (check logs)
207 | - Try clicking the refresh button in the MCP tool panel
208 | - Restart Cursor and try again
209 |
210 | ### PostgreSQL Specific Issues
211 |
212 | 1. **Permission Denied Errors**
213 | - Make sure the database user has appropriate permissions for the tables
214 | - Try granting required permissions: `GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;`
215 |
216 | 2. **"Relation does not exist" Errors**
217 | - Verify that the table exists: `\dt tablename` in psql
218 | - Check if you're connecting to the correct database
219 | - Ensure the user has access to the schema where the table is located
220 |
221 | 3. **Performance Issues**
222 | - Large query results may cause lag, consider adding LIMIT clauses
223 | - Check if your database needs optimization (indexes, vacuuming)
224 |
225 | For additional help, you can run the server with verbose logging (`-v` flag) to see detailed error messages and operation logs.
226 |
227 | ## License
228 |
229 | MIT
230 |
```
--------------------------------------------------------------------------------
/.cursor/mcp.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "mcpServers": {
3 | "ndao-db-prod": {
4 | "command": "node",
5 | "args": [
6 | "./server.js"
7 | ],
8 | "env": {
9 | "CURSOR_MCP": "1"
10 | }
11 | }
12 | }
13 | }
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "name": "mcp-gcloud-mysql",
3 | "version": "1.0.0",
4 | "description": "Model Context Protocol server for PostgreSQL database",
5 | "main": "server.js",
6 | "type": "module",
7 | "scripts": {
8 | "start": "node server.js"
9 | },
10 | "dependencies": {
11 | "@modelcontextprotocol/sdk": "^1.6.1",
12 | "body-parser": "^1.20.2",
13 | "cors": "^2.8.5",
14 | "dotenv": "^16.3.1",
15 | "express": "^4.18.2",
16 | "pg": "^8.11.3"
17 | },
18 | "engines": {
19 | "node": ">=20.0.0"
20 | }
21 | }
22 |
```
--------------------------------------------------------------------------------
/server.js:
--------------------------------------------------------------------------------
```javascript
1 | import { Server } from "@modelcontextprotocol/sdk/server/index.js";
2 | import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
3 | import {
4 | CallToolRequestSchema,
5 | ListResourcesRequestSchema,
6 | ListToolsRequestSchema,
7 | ReadResourceRequestSchema,
8 | } from "@modelcontextprotocol/sdk/types.js";
9 | import pg from "pg";
10 | // Import and configure dotenv to load .env file
11 | import dotenv from 'dotenv';
12 | import path from 'path';
13 | import { fileURLToPath } from 'url';
14 | import fs from 'fs';
15 | import os from 'os';
16 |
17 | // Get the directory of this file
18 | const __filename = fileURLToPath(import.meta.url);
19 | const __dirname = path.dirname(__filename);
20 |
21 | // Load environment variables from .env file
22 | dotenv.config({ path: path.join(__dirname, '.env') });
23 |
24 | // Parse command line arguments
25 | const parseArgs = () => {
26 | const args = process.argv.slice(2);
27 | const options = {
28 | credentialsVar: 'DB_CREDENTIALS', // Default value
29 | silent: true // Default to silent mode
30 | };
31 |
32 | for (let i = 0; i < args.length; i++) {
33 | if (args[i] === '--credentials-var' || args[i] === '-c') {
34 | if (i + 1 < args.length) {
35 | options.credentialsVar = args[i + 1];
36 | i++; // Skip the next argument as it's the value
37 | } else {
38 | console.error('Error: --credentials-var flag requires a value');
39 | process.exit(1);
40 | }
41 | } else if (args[i] === '--verbose' || args[i] === '-v') {
42 | options.silent = false;
43 | } else if (args[i] === '--help' || args[i] === '-h') {
44 | console.log(`
45 | Usage: node server.js [options]
46 |
47 | Options:
48 | -c, --credentials-var VAR Specify environment variable name containing DB credentials
49 | (default: DB_CREDENTIALS)
50 | -v, --verbose Enable console logs (silent by default)
51 | -h, --help Show this help message
52 |
53 | Environment Variables:
54 | The specified environment variable (or DB_CREDENTIALS as fallback) should contain
55 | a JSON string with the following structure:
56 | {
57 | "DB_USER": "username",
58 | "DB_PASSWORD": "password",
59 | "DB_HOST": "hostname",
60 | "DB_PORT": "5432",
61 | "DB_NAME": "database_name"
62 | }
63 |
64 | Fallback Logic:
65 | If the environment variable is not found in the .env file, the server will
66 | look for it in shell config files (~/.zshrc, ~/.bashrc, ~/.bash_profile, ~/.profile).
67 | This is especially useful for Cursor MCP environments where shell config files
68 | are not automatically sourced.
69 |
70 | Examples:
71 | 1. Using environment variable:
72 | node server.js --credentials-var MY_CUSTOM_DB_CREDS
73 |
74 | 2. Using .env file (default):
75 | node server.js
76 | `);
77 | process.exit(0);
78 | }
79 | }
80 |
81 | return options;
82 | };
83 |
84 | // Check if we're being run under Cursor MCP
85 | const isCursorMCP = process.env.CURSOR_MCP === '1';
86 |
87 | const options = parseArgs();
88 |
89 | // Create custom logger to respect silent mode
90 | const logger = {
91 | log: (...args) => {
92 | if (!options.silent) {
93 | console.log(...args);
94 | }
95 | },
96 | error: (...args) => {
97 | // Always show errors, even in silent mode
98 | console.error(...args);
99 | }
100 | };
101 |
102 | // Special handling for Cursor environment
103 | if (isCursorMCP) {
104 | logger.log('Running in Cursor MCP environment');
105 | }
106 |
107 | // Function to parse DB_CREDENTIALS from shell config files
108 | const parseShellConfigForCredentials = () => {
109 | // List of common shell config files to check
110 | const configFiles = [
111 | '.zshrc',
112 | '.bashrc',
113 | '.bash_profile',
114 | '.profile'
115 | ];
116 |
117 | for (const configFile of configFiles) {
118 | try {
119 | const configPath = path.join(os.homedir(), configFile);
120 |
121 | if (!fs.existsSync(configPath)) {
122 | logger.log(`~/${configFile} file not found`);
123 | continue;
124 | }
125 |
126 | const configContent = fs.readFileSync(configPath, 'utf8');
127 |
128 | // Enhanced regex to handle different export formats:
129 | // - export VAR='value'
130 | // - export VAR="value"
131 | // - export VAR=value
132 | const patterns = [
133 | // Single quotes
134 | new RegExp(`export\\s+(${options.credentialsVar}|[A-Z_]+DB[A-Z_]*CREDENTIALS[A-Z_]*)\\s*=\\s*'(.+?)'`, 's'),
135 | // Double quotes
136 | new RegExp(`export\\s+(${options.credentialsVar}|[A-Z_]+DB[A-Z_]*CREDENTIALS[A-Z_]*)\\s*=\\s*"(.+?)"`, 's'),
137 | // No quotes
138 | new RegExp(`export\\s+(${options.credentialsVar}|[A-Z_]+DB[A-Z_]*CREDENTIALS[A-Z_]*)\\s*=\\s*({.+?})`, 's')
139 | ];
140 |
141 | for (const regex of patterns) {
142 | const match = configContent.match(regex);
143 |
144 | if (match && match.length >= 3) {
145 | const varName = match[1];
146 | const credentialsJson = match[2];
147 |
148 | logger.log(`Found ${varName} in ~/${configFile}`);
149 |
150 | try {
151 | const credentials = JSON.parse(credentialsJson);
152 | return credentials;
153 | } catch (parseError) {
154 | logger.error(`Failed to parse JSON from ${varName} in ~/${configFile}: ${parseError.message}`);
155 | // Continue trying other patterns or files
156 | }
157 | }
158 | }
159 | } catch (error) {
160 | logger.error(`Error reading ~/${configFile}: ${error.message}`);
161 | // Continue with next config file
162 | }
163 | }
164 |
165 | return null;
166 | };
167 |
168 | // Parse DB credentials from environment variable, .env file, or shell config files
169 | let dbCredentials;
170 | try {
171 | // Try to get credentials from environment variable
172 | const credentialsJson = process.env[options.credentialsVar];
173 |
174 | if (!credentialsJson) {
175 | logger.log(`${options.credentialsVar} environment variable not found. Checking shell config files as fallback...`);
176 |
177 | // Try to get credentials from shell config files
178 | dbCredentials = parseShellConfigForCredentials();
179 |
180 | if (!dbCredentials) {
181 | throw new Error(`${options.credentialsVar} not found in environment variables or shell config files.`);
182 | }
183 |
184 | logger.log('Successfully parsed database credentials from shell config file');
185 | } else {
186 | try {
187 | dbCredentials = JSON.parse(credentialsJson);
188 | if (!options.silent) {
189 | logger.log(`Successfully parsed database credentials from ${options.credentialsVar} environment variable`);
190 | }
191 | } catch (parseError) {
192 | throw new Error(`Failed to parse JSON from ${options.credentialsVar}: ${parseError.message}. Ensure it contains valid JSON.`);
193 | }
194 | }
195 | } catch (error) {
196 | logger.error('Error with database credentials:', error.message);
197 | logger.error('Make sure your .env file is set up correctly with the DB_CREDENTIALS variable,');
198 | logger.error('or ensure DB_CREDENTIALS is properly exported in your shell config file (~/.zshrc, ~/.bashrc, etc.).');
199 | logger.error('Run with --help for more information on the required format.');
200 | process.exit(1);
201 | }
202 |
203 | // Validate required database credentials
204 | const validateCredentials = () => {
205 | const requiredFields = ['DB_USER', 'DB_PASSWORD', 'DB_HOST', 'DB_PORT', 'DB_NAME'];
206 | const missingFields = requiredFields.filter(field => !dbCredentials[field]);
207 |
208 | if (missingFields.length > 0) {
209 | logger.error(`Error: Missing required fields in ${options.credentialsVar}: ${missingFields.join(', ')}`);
210 | logger.error(`The ${options.credentialsVar} environment variable must contain a JSON object with these fields.`);
211 | logger.error('Run with --help for more information on the required format.');
212 | return false;
213 | }
214 |
215 | // Validate that port is a number
216 | const port = parseInt(dbCredentials.DB_PORT, 10);
217 | if (isNaN(port)) {
218 | logger.error(`Error: DB_PORT must be a valid number, got: "${dbCredentials.DB_PORT}"`);
219 | return false;
220 | }
221 |
222 | return true;
223 | };
224 |
225 | if (!validateCredentials()) {
226 | logger.error('Database credentials validation failed. Exiting.');
227 | process.exit(1);
228 | }
229 |
230 | // Database connection configuration
231 | const config = {
232 | user: dbCredentials.DB_USER,
233 | password: dbCredentials.DB_PASSWORD,
234 | host: dbCredentials.DB_HOST,
235 | port: parseInt(dbCredentials.DB_PORT, 10),
236 | database: dbCredentials.DB_NAME,
237 | // Add connection timeout and retry settings
238 | connectionTimeoutMillis: 10000,
239 | idleTimeoutMillis: 30000,
240 | max: 10, // Maximum number of clients in the pool
241 | };
242 |
243 | // Ensure localhost is using IPv4
244 | if (config.host === 'localhost') {
245 | config.host = '127.0.0.1';
246 | logger.log('Changed localhost to 127.0.0.1 to ensure IPv4 connection');
247 | }
248 |
249 | // Log connection details (without password)
250 | if (!options.silent) {
251 | logger.log('Database configuration:', {
252 | user: config.user,
253 | host: config.host,
254 | port: config.port,
255 | database: config.database,
256 | });
257 | }
258 |
259 | // Create a connection pool
260 | const pool = new pg.Pool(config);
261 |
262 | // Add event listeners for pool errors
263 | pool.on('error', (err) => {
264 | logger.error('Unexpected error on idle client', err);
265 | });
266 |
267 | // Create the MCP server
268 | const server = new Server(
269 | {
270 | name: "cursor-mcp-postgres",
271 | version: "0.1.0",
272 | },
273 | {
274 | capabilities: {
275 | resources: {},
276 | tools: {},
277 | },
278 | },
279 | );
280 |
281 | // Construct a resource base URL for the database
282 | const resourceBaseUrl = new URL(`postgres://${config.user}@${config.host}:${config.port}/${config.database}`);
283 | resourceBaseUrl.protocol = "postgres:";
284 | resourceBaseUrl.password = "";
285 |
286 | const SCHEMA_PATH = "schema";
287 |
288 | // Test database connection
289 | async function testDatabaseConnection() {
290 | let testClient = null;
291 | try {
292 | logger.log('Testing connection to PostgreSQL database...');
293 | testClient = await pool.connect();
294 | const result = await testClient.query('SELECT 1 AS connection_test');
295 | logger.log('Database connection test successful:', result.rows[0]);
296 | return true;
297 | } catch (err) {
298 | logger.error('Database connection test failed:', err.message);
299 |
300 | // Provide more specific error messages based on error codes
301 | if (err.code === 'ECONNREFUSED') {
302 | logger.error('Connection refused. Please check if the database server is running and accessible.');
303 | } else if (err.code === '28P01') {
304 | logger.error('Authentication failed. Please check your database credentials.');
305 | } else if (err.code === '3D000') {
306 | logger.error('Database does not exist. Please check the database name.');
307 | }
308 |
309 | logger.error('Error details:', err);
310 |
311 | // Don't exit immediately in Cursor environment to allow showing the error
312 | if (!isCursorMCP) {
313 | process.exit(1);
314 | }
315 | return false;
316 | } finally {
317 | if (testClient) {
318 | testClient.release();
319 | }
320 | }
321 | }
322 |
323 | // Execute a query with retry logic
324 | async function executeQueryWithRetry(sql, maxRetries = 3) {
325 | let retries = 0;
326 | let lastError = null;
327 |
328 | while (retries < maxRetries) {
329 | const client = await pool.connect();
330 | try {
331 | logger.log(`Executing query (attempt ${retries + 1}/${maxRetries}):`, sql);
332 | const result = await client.query(sql);
333 | return result;
334 | } catch (err) {
335 | lastError = err;
336 | logger.error(`Query error (attempt ${retries + 1}/${maxRetries}):`, err.message);
337 |
338 | // Check if this is a connection-related error that might be resolved by retrying
339 | if (err.code === 'ECONNREFUSED' || err.code === '57P01' || err.code === '08006' || err.code === '08001') {
340 | retries++;
341 | await new Promise(resolve => setTimeout(resolve, 1000 * retries)); // Exponential backoff
342 | } else {
343 | // For other errors, don't retry
344 | throw err;
345 | }
346 | } finally {
347 | client.release();
348 | }
349 | }
350 |
351 | // If we've exhausted all retries
352 | throw lastError;
353 | }
354 |
355 | // Handler for listing database resources (tables)
356 | server.setRequestHandler(ListResourcesRequestSchema, async () => {
357 | const client = await pool.connect();
358 | try {
359 | const result = await client.query(
360 | "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'",
361 | );
362 | return {
363 | resources: result.rows.map((row) => ({
364 | uri: new URL(`${row.table_name}/${SCHEMA_PATH}`, resourceBaseUrl).href,
365 | mimeType: "application/json",
366 | name: `"${row.table_name}" database schema`,
367 | })),
368 | };
369 | } catch (error) {
370 | logger.error('Error listing resources:', error.message);
371 | throw error;
372 | } finally {
373 | client.release();
374 | }
375 | });
376 |
377 | // Handler for reading resource details (table schema)
378 | server.setRequestHandler(ReadResourceRequestSchema, async (request) => {
379 | const resourceUrl = new URL(request.params.uri);
380 |
381 | const pathComponents = resourceUrl.pathname.split("/");
382 | const schema = pathComponents.pop();
383 | const tableName = pathComponents.pop();
384 |
385 | if (schema !== SCHEMA_PATH) {
386 | throw new Error("Invalid resource URI");
387 | }
388 |
389 | const client = await pool.connect();
390 | try {
391 | const result = await client.query(
392 | "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = $1",
393 | [tableName],
394 | );
395 |
396 | return {
397 | contents: [
398 | {
399 | uri: request.params.uri,
400 | mimeType: "application/json",
401 | text: JSON.stringify(result.rows, null, 2),
402 | },
403 | ],
404 | };
405 | } catch (error) {
406 | logger.error('Error reading resource:', error.message);
407 | throw error;
408 | } finally {
409 | client.release();
410 | }
411 | });
412 |
413 | // Handler for listing available tools
414 | server.setRequestHandler(ListToolsRequestSchema, async () => {
415 | return {
416 | tools: [
417 | {
418 | name: "query",
419 | description: "Run a SQL query against the PostgreSQL database",
420 | inputSchema: {
421 | type: "object",
422 | properties: {
423 | sql: { type: "string" },
424 | },
425 | required: ["sql"],
426 | },
427 | },
428 | ],
429 | };
430 | });
431 |
432 | // Handler for executing tool calls (SQL queries)
433 | server.setRequestHandler(CallToolRequestSchema, async (request) => {
434 | if (request.params.name === "query") {
435 | // Use type assertion in a way that works in JavaScript
436 | const sql = request.params.arguments?.sql;
437 |
438 | if (!sql || typeof sql !== 'string') {
439 | throw new Error("SQL query is required and must be a string");
440 | }
441 |
442 | try {
443 | // Use our retry logic for query execution
444 | const result = await executeQueryWithRetry(sql);
445 |
446 | return {
447 | content: [{ type: "text", text: JSON.stringify(result.rows, null, 2) }],
448 | isError: false,
449 | };
450 | } catch (error) {
451 | logger.error('Query execution error:', error.message);
452 | return {
453 | content: [{ type: "text", text: `Error executing query: ${error.message}` }],
454 | isError: true,
455 | };
456 | }
457 | }
458 | throw new Error(`Unknown tool: ${request.params.name}`);
459 | });
460 |
461 | // Main function to run the server
462 | async function runServer() {
463 | try {
464 | // Test database connection before starting
465 | const connectionSuccess = await testDatabaseConnection();
466 |
467 | // In Cursor, we proceed even if the connection test fails to show errors
468 | if (!isCursorMCP || connectionSuccess) {
469 | logger.log('Starting MCP server for PostgreSQL...');
470 |
471 | // Create the transport
472 | const transport = new StdioServerTransport();
473 |
474 | try {
475 | // Connect to the transport
476 | await server.connect(transport);
477 | logger.log('MCP server connected and ready to use');
478 | } catch (transportError) {
479 | logger.error('Failed to connect to MCP transport:', transportError.message);
480 | if (!isCursorMCP) {
481 | process.exit(1);
482 | }
483 | }
484 | }
485 | } catch (error) {
486 | logger.error('Server startup error:', error.message);
487 | logger.error('Error details:', error);
488 | if (!isCursorMCP) {
489 | process.exit(1);
490 | }
491 | }
492 | }
493 |
494 | // Handle graceful shutdown
495 | process.on('SIGINT', async () => {
496 | logger.log('Closing database connection pool...');
497 | try {
498 | await pool.end();
499 | logger.log('Database connection pool closed.');
500 | } catch (err) {
501 | logger.error('Error closing pool:', err.message);
502 | }
503 | process.exit(0);
504 | });
505 |
506 | // Start the server
507 | runServer().catch(err => {
508 | logger.error('Unhandled error:', err);
509 | logger.error('Error details:', err);
510 | if (!isCursorMCP) {
511 | process.exit(1);
512 | }
513 | });
```