Microsoft recently made an exciting announcement: they are about to launch a new PostgreSQL IDE in VS Code! This highly anticipated tool will bring unprecedented experience and convenience to PostgreSQL database users.

This new PostgreSQL IDE will be integrated into VS Code, providing users with fast and powerful database management capabilities. Whether it is querying the database, editing tables, or performing other database operations, users will be able to easily do it all in one integrated environment. In addition, the IDE will support a variety of features such as version control, auto-completion, and visualization tools that will enable users to manage and operate PostgreSQL databases more efficiently. Now let's look at the specific features.

Key Features

The public preview release of the PostgreSQL extension for VS Code introduces a suite of powerful new capabilities that enhance productivity and streamline development for application developers working with Postgres.

Schema Visualization

Schema visualization is a breeze with our ‘right-click’ context menu options.

o   Right-click on the database entry in the Object Explorer and select “Visualize Schema”

Figure 1: Right-click on the database entry in the Object Explorer and select “Visualize Schema”
Single click to expand.

Database aware GitHub Copilot

• AI assistance directly within VS Code providing PostgreSQL database context reduces the PostgreSQL learning curve and improves developer productivity.

• Simplified interaction with PostgreSQL databases and development tools using natural language.

• Commands such as "@pgsql" enable you to query databases, optimize schemas, and execute SQL operations with ease.

• Context menus, such as “Rewrite Query”, “Explain Query”, “Analyze Query Performance” provide AI Intelligence inside the query editor window.

• Real-time, expert-level guidance to help keep PostgreSQL databases performant and secure and improve code quality.

PostgreSQL Copilot Context Menu Options

Figure 2: Screenshot of the PostgreSQL Copilot Context Menu.
Single click to expand.

Using the PostgreSQL Copilot Context Menu, 

Figure 3: PostgreSQL Copilot Explain Query Context Menu in action.
Single click to expand.

GitHub Copilot Chat Agent Mode

GitHub Copilot Chat agent mode provides a database context aware intelligent assistant that can perform multi-stage tasks, moving beyond the question-and-answer chat experience. Agent mode allows the Copilot to bring in additional context from your workspace and, with permission, it can write and debug code on its own. Agent mode transforms PostgreSQL development by providing real-time, AI-driven guidance that simplifies complex tasks like app prototyping, debugging, schema optimization, and performance tuning.  

In this example, we’ll ask the agent to create a new database on a specific server in my Saved Connections and enable the PostGIS extension.

Figure 4: Using the @pgsql GitHub Copilot Chat in agent mode to create a new database from a natural language prompt.
Single click to expand.

The @pgsql agent begins by listing the server connections, connecting to the server ‘postgis’, drafts the script to modify the database and waits for permission to continue before making changes. Database modifications require explicit permission from the user.

Add Database Connections with Ease

• Simplified connection management for local and cloud-hosted PostgreSQL instances.

• Support for multiple connection profiles and connection string parsing for easy setup.

• Direct browsing and filtering of Azure Database for PostgreSQL deployments.

• Integration with Entra ID for centralized security and identity management.

Connect with ease to your existing Azure Database for PostgreSQL deployments with the “Browse Azure” option in the “Add New Connection” menu.

Figure 5: Connecting to an Azure Database for PostgreSQL instance using the Browse Azure option with Entra ID authentication.
Single click to expand.

Connect to local Docker deployments with the Parameters or Connection String option.

Figure 6: Connect to PostgreSQL in a local Docker deployment.
Single click to expand.

Password-less authentication with Entra Id

• Streamlined Authentication: Eliminates the need for manual login, offering a seamless integration experience for you.

• Automatic Token Refresh: Ensures uninterrupted connectivity and minimizes the risk of authentication timeouts during development.

• Enhanced Security: Provides robust protection by leveraging Entra-ID's secure authentication protocols.

• Time Efficiency: Reduces overhead by automating token management, allowing you to focus on coding rather than administrative tasks.

• Enterprise Compatibility: Aligns with corporate security standards and simplifies access to PostgreSQL databases in enterprise environments.

• User Consistency: You can use your existing Entra-ID credentials, avoiding the need to manage separate accounts.

Database Explorer

• Provides a structured view of database objects such as schemas, tables, and functions.

• Enables creation, modification, and deletion of database objects.

Figure 7: View, manage, and query database objects within the Database Explorer.
Single click to expand.

Query History

Session query history is available below the Object Explorer. This allows you to quickly review previously run queries for reuse. 

Figure 8: Query History context menu detail.
Single click to expand.

Query Editing with Context-aware IntelliSense

• Context-aware IntelliSense for auto-completion of SQL keywords, table names, and functions.

• Syntax highlighting and auto-formatting for improved query readability.

• Query history tracking for reusing previously executed queries.

Figure 9: Query editing with database context-aware IntelliSense.
Single click to expand.

What Sets the PostgreSQL Extension for VS Code Apart?

The PostgreSQL extension for VS Code stands out in the crowded landscape of developer database management tools due to its unparalleled functionality and intuitive design. Here’s what makes it special:

• Enhanced Productivity: Features like context-aware IntelliSense and SQL formatting save time and minimize errors.

• pgsql GitHub Copilot Chat agent: Database and workspace context awareness, enabling smarter and more contextually relevant assistance for developers – combined with the ability to perform multi-step tasks.

• Streamlined Onboarding: The Connection Manager ensures you can get started within minutes.

• Improved Security: Entra ID integration provides robust access control and centralized identity management, including the ability to browse your Azure Database for PostgreSQL instances. 

• Comprehensive Toolset: You can manage database objects, execute queries, and deploy instances all within VS Code.

• Seamless Cloud Integration: Deep integration with Azure Database for PostgreSQL simplifies cloud database management.

Getting Started with the PostgreSQL extension for Visual Studio Code

Installing the PostgreSQL extension for VS Code is simple:

1. Open the Extensions view in VS Code.

2. Search for "PostgreSQL" in the Extensions Marketplace.

3. Select and install the Preview PostgreSQL extension with the blue elephant seen in the screenshot below. 

Figure 10: PostgreSQL extension available in the Marketplace. Extension ID: (ms-ossdata.vscode-pgsql)

Also available in the online Visual Studio Code Marketplace. 

Enabling the PostgreSQL GitHub Copilot Chat

You will need the GitHub Copilot and GitHub Copilot chat extensions installed in VS Code to be able to log into their GitHub Account and use "@pgsql" in the chat interface to interact with their PostgreSQL database.

Getting Started

The PostgreSQL extension for VS Code dramatically improves development efficiency and productivity. Explore the public preview today and experience the improved workflow of the PostgreSQL database.

To learn more and get started, visit: https://aka.ms/pg-vscode-docs