Edit

Share via

Quickstart: Configure GitHub Copilot for PostgreSQL extension in Visual Studio Code preview

The PostgreSQL extension for Visual Studio Code now includes GitHub Copilot integration, enhancing your database workflows with AI-assisted development. Copilot accesses contextual information from your live connection, once connected to a PostgreSQL database. This enables the @pgsql Copilot chat participant to generate accurate, schema-aware SQL queries and insights, streamlining development and minimizing context-switching within Visual Studio Code.

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 GitHub Copilot and GitHub Copilot Chat extensions

  1. If you don't already have the GitHub Copilot extension installed in Visual Studio Code:

  2. Select the Extensions icon in Visual Studio Code, search for GitHub Copilot, and select Install.

  3. The GitHub Copilot Chat extension is automatically installed along with GitHub Copilot.

Sign in to GitHub in Visual Studio Code

  1. Ensure you have a GitHub account and an active GitHub Copilot subscription:

  2. In Visual Studio Code, select the Account icon and choose Sign in with GitHub to use GitHub Copilot.

Get started with GitHub Copilot

Follow the steps to begin using GitHub Copilot with the PostgreSQL Visual Studio Code extension.

  1. Right-click on a PostgreSQL database and select Chat with this database.

  2. If prompted, select Allow to enable GitHub Copilot to access the database connection context.

  3. When the Copilot chat interface opens, you can start asking questions using the @pgsql prefix to specify that you want to interact with the PostgreSQL database.

Try a prompt like:

@pgsql tell me about the tables in the HR schema

Screenshot of Copilot Chat response showing a detailed breakdown of tables and columns in the HR schema of a PostgreSQL database.

  1. Copilot responds with a detailed description of your schema's tables.

Using read/write capabilities

The GitHub Copilot integration for the PostgreSQL extension in Visual Studio Code enables seamless read and write capabilities, allowing developers to interact with their databases more efficiently. By leveraging AI-powered suggestions, you can perform tasks such as querying data, modifying schemas, and updating records directly from the editor. This feature simplifies complex operations, reduces manual effort, and enhances productivity while maintaining accuracy and context-awareness.

Note

The GitHub Copilot Chat integration for PostgreSQL is a powerful tool that can make changes to your database. It's important to use this feature with caution, especially in staging and production environments. Always review the generated SQL code before executing it, and consider testing it in a safe environment first.

Try a more advanced prompt.

@pgsql convert the hr.employees table to use a JSONB column for the address field

Copilot might respond with SQL suggestions and ask permission to make changes.

To approve execution:

@pgsql Yes, please make the JSONB column for me

Then Copilot asks for confirmation:

@pgsql Yes, I confirm

Using context menu options

  • You can select SQL code in the editor and right-click to access GitHub Copilot context menu options like Explain Query, Rewrite Query, or Analyze Query Performance.

Additional ideas and prompt recipes

There are many prompts and things you can do with GitHub Copilot for PostgreSQL - the limit is only your imagination! To help jumpstart some ideas, here are some concept prompts you can try or modify to match your database context and development environment:

Query optimization

Here are some prompts you can use to guide Copilot in addressing specific query optimization challenges, helping you achieve efficient and reliable database operations.

I'm working on optimizing my database for high-concurrency workloads. The table is called transactions with millions of records, and I'm experiencing deadlocks under a heavy load. Help me optimize my table schema and queries.

I need help writing a query. The data is stored in the orders table, which uses the columns customer_id, order_date, and total_price. I also need to include a rolling 3-month average of customer spending using a window function.

I'm getting this error: 'ERROR: column `orders.total_price` must appear in the GROUP BY clause or be used in an aggregate function.

Performance optimization

Here are some prompts you can use to guide Copilot in addressing specific performance optimization challenges, helping you achieve faster and more efficient database operations.

Provide the Explain Plan for my most recent query, and please explain each step.

Can you run some performance metrics on my database and tell me how it performs?

My orders table has 10 million records, and queries on customer_id and order_date are slow. How can I optimize indexing, partitioning, and schema design for performance?

App development

Here are some prompts you can use to guide Copilot in addressing app development challenges.

Generate a FastAPI endpoint to fetch orders from the ecom.orders table with pagination.

Generate an ETL pipeline script to clean and normalize the customer table data.

Generate a FastAPI project with my database using SQLAlchemy.

Clean up

To ensure a smooth experience, clean up any temporary resources or configurations created during this quickstart. For example:

  • Disconnect from the PostgreSQL database in Visual Studio Code.
  • Remove any test databases or tables created during the session.
  • Close any open connections to avoid unnecessary resource usage.

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