Introduction

Not long ago, I discovered how to query databases using natural language with a tool on VS Code, utilizing Agent Mode combined with PostgreSQL's MCP server. Since then, I've had a strong feeling that the era of GenAI-enhanced development tools is imminent. And now, it's here...

Microsoft recently announced a new IDE for PostgreSQL in VS Code. You can read more about it here. Additionally, you can find the GitHub repository for the VS Code extension and some helpful guidance here.

Today's post aims to demonstrate the connection between my work on querying PostgreSQL using natural language and achieving the same results with Microsoft's PostgreSQL VS Code extension.

Prepare and connect to the PostgreSQL DB server

Install PostgreSQL's VSCode extensions

Make sure you install the VS Code extension as the picture below.

Provisioning a PostgreSQL database server

git clone [email protected]:thangchung/mcp-labs.git
cd mcp-labs/vscode-mcp-servers
dotnet build
dotnet run --project AppHost1/AppHost1.csproj

This process will provision a PostgreSQL database, create a complete copy of the Northwind database, and seed it with data. Once you've completed these steps, you should see the following view in .NET Aspire.

Wire it up

Open VS Code IDE, and click on the PostgreSQL icon (just installed in the previous steps), and try to input the information of server name, username, password, and database port. Make sure you click Test Connection and it should work as expected.

After connecting, you should see all table schemas as the picture below.

Database schema visualizer

Now, let's explore the cool feature in this extension. At the root of the database server, you can right-click on postgres, and choose Visualize Schema:

You should see all the database table schemas displayed in the beautiful visualizer as follows:

Query in VS Code with MCP tools in Agent mode

Overview of how we can make a prompting and smart query in GitHub Copilot in VS Code (Agent Mode with Claude Sonnet 4 model) as below:

Let's deep dive a little bit into it:

Look at my simple prompt: Show me the top 10 orders with the best prices in T-SQL, and query it out

And it generated as below:

SELECT
    o.order_id,
    o.customer_id,
    c.company_name,
    o.order_date,
    o.shipped_date,
    -- Calculate total order value considering discount
    ROUND(
        SUM(
            od.unit_price * od.quantity * (1 - od.discount)
        )::numeric, 
        2
    ) AS total_order_value,
    -- Additional details
    COUNT(od.product_id) AS total_items,
    o.freight,
    o.ship_country
FROM orders o
    INNER JOIN order_details od ON o.order_id = od.order_id
    LEFT JOIN customers c ON o.customer_id = c.customer_id
GROUP BY 
    o.order_id, 
    o.customer_id, 
    c.company_name,
    o.order_date, 
    o.shipped_date, 
    o.freight, 
    o.ship_country
ORDER BY total_order_value DESC
LIMIT 10;

If you look at the T-SQL above, you'll notice it's quite intelligent compared to my previous setup. With just a zero-shot prompt, it accurately identifies the valid tables in the database. In the previous setup, I had to specify the correct table names and the price column.

Now, let's examine the result in detail:

1. Asking for confirmation to use PostgreSQL's query MCP tool.
2. Using this tool to query to database server and get data out.
3. Submit to the LLM model to enrich the result and add more insightful views.

Conclusion

In conclusion, the integration of GenAI into development tools is revolutionizing the way we work, making tasks more intuitive and efficient (this time is PostgreSQL IDE). As we stand on the brink of this technological evolution, it's clear that the future holds even more exciting advancements. The proliferation of GenAI-enhanced tools will undoubtedly transform our workflows, driving innovation and productivity to new heights. Stay tuned, as this journey is just beginning, and the best is yet to come.