We are excited to announce the public preview of the brand-new PostgreSQL extension for Visual Studio Code (VS Code), designed to simplify PostgreSQL database management and development workflows. With this extension, you can now manage database objects, draft queries with intelligent assistance from context-aware IntelliSense and our ‘@pgsql’ GitHub Copilot agent—all without ever leaving your favorite code editor.
Many of you face hurdles in managing time effectively, with 41% of developers struggling with task-switching, according to the 2024 StackOverflow Developer Survey. Additionally, the 2024 Stripe Developer Coefficient Report reveals that developers spend up to 50% of their time debugging and troubleshooting code and databases. These inefficiencies are further compounded by the absence of integrated tools that unify database management and application development.
The PostgreSQL extension for VS Code addresses these challenges head-on by integrating Postgres database tools and the @pgsql GitHub Copilot agent, providing a unified application development and database management experience. By integrating robust features such as Entra ID authentication for centralized identity management and deep Azure Database for PostgreSQL integration, this extension empowers you to focus on building innovative applications rather than wrestling with fragmented workflows.
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 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.
- 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.
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 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.
- 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.
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.
- 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.
- Provides a structured view of database objects such as schemas, tables, and functions.
- Enables creation, modification, and deletion of database objects.
Single click to expand.
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.
- 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.
Single click to expand.
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.
Installing the PostgreSQL extension for VS Code is simple:
- Open the Extensions view in VS Code.
- Search for "PostgreSQL" in the Extensions Marketplace.
- Select and install the Preview PostgreSQL extension with the blue elephant seen in the screenshot below.
Also available in the online Visual Studio Code Marketplace.
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.
We value your insights. Use the built-in feedback tool in VS Code to share your thoughts and report issues. Your feedback will help us refine the extension and ensure it meets the needs of the developer community.
The PostgreSQL extension for VS Code offers significant enhancements to development efficiency and productivity. We encourage you to explore the public preview today and experience improved workflows with PostgreSQL databases.
To learn more and get started, visit: https://aka.ms/pg-vscode-docs
Special thanks to Jonathon Frost, Principal PM for all of his work on the @pgsql GitHub Copilot.