Edit

Share via

Quickstart: Connect and query a database with the PostgreSQL extension for Visual Studio Code preview

The PostgreSQL extension for Visual Studio Code is a powerful tool designed to streamline the development and management of PostgreSQL databases. This guide walks you through connecting to a PostgreSQL database and executing queries within the Visual Studio Code environment.

Prerequisites

Before you begin, verify you have the proper tools and resources downloaded and installed.

These tools and resources help you follow along with this article and make the most of the GitHub Copilot integration for the PostgreSQL extension in Visual Studio Code.

Install the PostgreSQL extension

To install the PostgreSQL extension:

  1. Open Visual Studio Code and navigate to the Extensions view by selecting the Extensions icon in the Activity Bar or using the View: Extensions command.
  2. Search for PostgreSQL in the Extensions Marketplace.
  3. Select the PostgreSQL extension authored by Microsoft and select Install.

Once installed, the PostgreSQL page, represented by an elephant icon, appears in the Visual Studio Code sidebar.

Add a connection to PostgreSQL

Whether hosted locally or on a remote server, connecting to a PostgreSQL database is a fundamental step in managing and interacting with your data. This process involves providing the necessary connection details, such as the server address, port, and authentication credentials, to establish a secure link between your client application and the database. With the PostgreSQL extension for Visual Studio Code, you can seamlessly connect to your database and use powerful tools to query, manage, and explore your data efficiently.

  1. Select the elephant icon on the sidebar to open the PostgreSQL extension page.

  2. Select the Add Connection button.

  3. Enter your connection details manually or use the connection string format:

      psql -h <server>.postgres.database.azure.com -p 5432 -U

  4. If using Azure, sign in to your Azure account and browse for the database instance. Filter by subscription, resource group, server, and database name.

Authentication

The extension supports two authentication methods:

  • Username/Password: Enter your database credentials directly into the connection fields.
  • Microsoft Entra ID Authentication: Add your Microsoft Entra ID account for Azure-hosted databases.

Screenshot of PostgreSQL extension for VS Code connection dialog.

Test and save the connection

  1. Select "Test Connection" to verify your connection details.
  2. Upon successful testing, the test box displays a checkmark.
  3. Select "Connect" to establish the connection.
  4. Your connection automatically saves and appear in the Connections window.

Explore database objects

The Object Explorer provides a hierarchical view of your database objects:

  • Expand the database item to view schemas, tables, views, functions, and stored procedures.
  • Right-click on the database, and you see options to:   - Launch a New Query   - Chat with this database (starts the pgsql chat experience)   - Connect with PSQL to launch a psql terminal connection
  • Right-click on a table and select "Select Top 1000" to view its data. The query opens in the Query Editor, and the results appear in the Results Viewer tab.

Execute queries

Use the Query Editor to draft and execute SQL queries:

  • Take advantage of context-aware IntelliSense to autocomplete SQL keywords and object names.
  • Syntax highlighting and autoformatting ensure better readability and accuracy.
  • Access previously executed queries via the Query History pane.

Review query results

The Results Viewer offers advanced features to interact with your query results:

  • Export results to CSV, JSON, or Excel formats for further analysis.
  • Use search, filter, and sort options to refine your data.
  • Persistent data views maintain context while switching between tabs.

Use GitHub Copilot for advanced assistance

The GitHub Copilot integration enhances your PostgreSQL development experience by providing AI-powered code suggestions, query optimization tips, and interactive database assistance. This feature allows you to streamline your workflow, reduce development time, and gain deeper insights into your database operations. This section guides you through the steps to activate and utilize Copilot within the PostgreSQL extension for Visual Studio Code.

Ensure the GitHub Copilot and Copilot Chat extensions are installed.

Sign in to your GitHub account and enable the @pgsql Copilot Chat agent in the extension settings.

Interactive database prompts

Right-click on a database and choose Chat with this database to interact with Copilot.

Write prompts like the example below to receive detailed insights and suggestions.

@pgsql tell me about the tables in the HR schema

To learn more, visit Configure GitHub Copilot

Connect to an Azure Database for PostgreSQL Flexible Server instance with VS Code from the Azure portal

To connect to an Azure Database for PostgreSQL flexible server instance using Visual Studio Code, follow these steps:

  1. Sign in to the Azure portal and locate your Azure Database for PostgreSQL flexible server instance.
  2. Go to the Overview page of your server instance.

Connect with VS Code

The "Connect with Visual Studio Code" option in the Azure portal simplifies connecting to your Azure Database for PostgreSQL Flexible Server. This feature streamlines the setup by guiding you through the necessary prerequisites and automatically configuring connection details. By using this integration, you can quickly establish a connection and manage your database directly within the Visual Studio Code environment.

  • Select the prominent Connect with VS Code button on the Overview page.
  • A side pane listing the requirements to connect using VS Code appears.

Screenshot of Azure portal showing an Azure Database for PostgreSQL instance with the Connect with VS Code button.

Confirm requirements

  • In the side pane, confirm that all prerequisites (VS Code and PostgreSQL extension) are satisfied by selecting the appropriate checkboxes.

  • If needed, download Visual Studio Code and the extension using the links in the side pane.

  • Optionally, fill out connection parameters such as the default database name, authentication method, and connection pooling.

    Screenshot of Azure portal showing an Azure Database for PostgreSQL instance with the Connect with VS Code panel. The open in Visual Studio Code button is highlighted.

Open in Visual Studio Code

  • Select the Open in Visual Studio Code button in the side pane.
  • A "Retry Opening in Visual Studio Code" button appears in the side pane in case Visual Studio Code and the extension are taking more than 40 seconds to open.

Launch Visual Studio Code

  • If VS Code is installed and running, the PostgreSQL extension's connection dialog box launches automatically.
  • If VS Code is installed but not open, it launches within approximately 40 seconds, followed by the connection dialog box.

Autofill connection details

The connection dialog box in the PostgreSQL extension opens, and the Server Name field automatically populates your server endpoint.

Screenshot of the PostgreSQL extension for VS Code Connection Dialog with the server name details prepopulated.

Provide authentication details

Select your preferred authentication method:

  • Password Authentication: Enter your username and password manually.
  • Entra Authentication: Enter your Azure account credentials.

Specify other connection details such as Profile Name, Database Name (optional), Server Group, and Port Number.

Save and connect

Select the Save & Connect button to establish the connection. Once connected, expand the server in the Object Explorer tree to view databases, schemas, and tables, or use the built-in query tool to manage your database objects.

Troubleshoot

If you encounter issues during the connection process:

  • Verify that VS Code and the PostgreSQL extension are installed and enabled.

  • Ensure the IP address is allowed in the firewall settings of your Azure Database for PostgreSQL.

  • If prerequisites aren't met, the side pane provides feedback and steps to resolve the issue, including retrying the connection.

Feedback and support

For bugs, feature requests, and issues, use the built-in feedback tool in Visual Studio Code. You can complete this via the VS Code Help menu or the PGSQL command palette.

  • Help menu

    • Go to Help > Report Issue

  • Command palette

    • Open the Command Palette with Ctrl + Shift + P and run: PGSQL: Report Issue

Related content