GitHub Copilot: MCP Server Setup in VS Code

The Model Context Protocol (MCP) is an open standard designed to enable AI models such as GitHub Copilot, to interact seamlessly with external tools and services. MCP acts as a universal connector for AI, allowing language models to fetch information, interact with APIs, and execute tasks beyond their built-in knowledge. MCP follows a client-server architecture, where MCP clients (like VS Code) connect to MCP servers to request actions on behalf of the AI model.

Installing MCP Server for PostgreSQL

Setting up an MCP Server for PostgreSQL involves a few key steps. Here's a detailed guide to help you through the process:

Preparing Your Environment

1. Visual Studio Code (VS Code)
2. Set up GitHub Copilot in Agent mode
3. Install PostgreSQL (pgAdmin)
4. Install Node.js and npm on your system

Manual installation of PostgreSQL MCP server

Step 1: In the search bar at the top of VS Code, type the command >mcp, then click on MCP: Add Server as shown below:

Step 2: Now select "Install from an npm package":

Step 3: Enter package name i.e. "@modelcontextprotocol/server-postgres" and press enter:

Step 4: Click on "ALLOW" to install the package.

Step 5: Once you click ALLOW, the installation of the MCP Server for PostgreSQL will begin. It will ask for the following details:
1.Hostname: Enter your host name and press enter.

2.Database name: Enter your database name and press enter.

3.Port number: Enter the port number.
4.Username: Enter your username.
5.Password: Enter your password.
6.Server ID: Enter any name for the server.

7.Once you have added your username, password, hostname, port number, and server ID, you will need to save the configuration. Choose between:

• User Setting: The configuration will be available in all workspaces.
• Workspace Setting: The configuration will be available in the current workspace.

TA-DA!🎉 The configuration will automatically reflect in settings.json file.

📍 It's crucial that the data you input (username, password, database, etc) is accurate. Any incorrect information will cause the MCP Server to fail to run so check properties of your database before configuring .

Quick Installation of PostgreSQL MCP Server

Step 1: If you don't want to go through the hassle, simply click on "Install with NPX in VS Code" and you will be automatically redirected to VS Code.

Using VS Code:
[Install with NPX in VS Code]

Using VS Code Insiders
[Install with NPX in VS Code Insiders]

Step 2: Click on Install Server

Step 3: Surprise! 🎊 We've completed the configuration!

⚠️CAUTION: Make sure to replace {input:pg_url} with your path URL.

To check if MCP Server is running check OUTPUT :

Use Copilot to execute queries

Some of the things you can experiment with Github Copilot and PostgreSQL server:

• Read-only SQL Queries: Execute SQL queries within a read-only transaction.

GitHub Copilot Prompt: Show tables in postgres database (postgres is name of database)

RESULT:

• Schema Inspection: Access detailed schema information for each table, including column names and data types.
• Automatic Schema Discovery: Automatically discovers schemas from database metadata.
• Database Health Analysis: Analyze index health, connection utilization, buffer cache, vacuum health, sequence limits, replication lag, and more.

GitHub Copilot Prompt: Do postgres database health check up

RESULT:

Based on the health check results, let me analyze each aspect of your PostgreSQL database:

Connection Status: ✅ Successful

Database is accessible on localhost:5433
Credentials are working correctly
Version Information: ✅ Up to date

Running PostgreSQL 17.4 (64-bit)
Windows x86_64 build
Connection Pool: ⚠️ Medium load

14 active connections
This is a moderate number, but should be monitored if it grows significantly
Database Size: ✅ Healthy

Current size: 7859 kB
Relatively small and manageable
Table Count: ℹ️ Note

Only 1 table present
This might be expected if you're just starting with the database
System Uptime: ✅ Stable

Running for 4 days, 8 hours, 9 minutes, 42 seconds
Shows good stability with no recent restarts

CONCLUSION:

Integrating GitHub Copilot with a PostgreSQL MCP server in Visual Studio Code revolutionizes the development process. This setup simplifies complex database interactions by providing seamless real-time data access, executing advanced queries, and offering context-aware code suggestions. This powerful combination transforms database management into a more intuitive and efficient experience, ultimately elevating the quality and speed of software development.

Reference:
https://github.com/modelcontextprotocol/servers