Leverage GitHub Copilot to work with MySQL in Azure Data Studio

[mk_sunitha]

If you’re a developer who wants to learn MySQL or improve your productivity while developing with MySQL, then GitHub Copilot on Azure Data Studio can help get you started.

GitHub Copilot is an AI-powered code completion tool developed by GitHub and powered by OpenAI. It leverages machine learning models trained on vast amounts of code to generate context-aware code suggestions directly within your integrated development environment (IDE). You can use GitHub Copilot as an extension in Azure Data Studio and Visual Studio Code.

In this blog post, I’ll demonstrate how GitHub Copilot can act as an AI pair programmer by providing intelligent suggestions and more while coding with MySQL.

Prerequisites

Before getting started, I'll set up the environment on a local machine as follows:

1. Install the latest Azure Data Studio release.
2. From the Azure Data Studio extension marketplace, install the
   1. MySQL extension.
   2. GitHub Copilot extension.
3. Sign up for Github Copilot using your GitHub account.
4. Create an Azure Database for MySQL Flexible Server database including a sample table with some data. Check out the samples. You can also use an existing MySQL database on the local machine.

In this blog post, I'll be using Azure Database for MySQL - Flexible Server as an example to work with GitHub Copilot.

Connect to and query a MySQL database

Get the connection details for an instance of a MySQL database server, and then add the connection to Azure Data Studio by performing the following steps.

1. Start Azure Data Studio. 
   If the Connection dialog box does not open, on the Connections tab, in the SERVERS view, select New Connection.
2. In the New Connection dialog window, select Connection type, and then, in the drop-down list, select MySQL.
3. Enter the MySQL server name, select the preferred authentication method, and then enter the credentials to connect to the MySQL server.
4. If the MySQL server requires SSL encryptions, select Advanced…, in the Advanced Properties window, enter the SSL configuration details, and then select OK.
   By default, SSL mode is configured as Require. For more information on SSL encryption and modes, see Configuring MySQL to Use Encrypted Connections.
5. Review the connection details, and then select Connect.
   After a connection is successfully established, the server opens in the SERVERS sidebar.

You can view your databases and table, and then create a new query to start using GitHub Copilot in Azure Data Studio.

GitHub Copilot + MySQL

Next, I want test out a few scenarios for using prompts to create new queries to run against the MySQL table.

Scenario 1: Get all rows from a table

In the query editor, use the statement -- get all rows from customers table to show the results of a SELECT statement for the customers table.

Scenario 1: Get all rows from a table

To accept the suggestion, press TAB, and then run your statement.

Scenario 1: Output of get all rows from a table

Scenario 2: Create an index on a table

Copilot is context-aware, so there's no need to be too specific in prompts. For example, let's ask to create an index on the customers table.  Use the statement -- create an index on this table primary key to allow it to reference the previous statement and create an index.

Scenario 2: Create an index on a table

Scenario 3: Create a new table

When building the schema, start with a base template for the table by using the statement -- create new table for person details, which will generate the following output:

Scenario 3: Create a new table

You can use this as a starting point and add more fields as needed.

Scenario 4: Complete your queries

Copilot will provide suggestions to complete a query, which allows for building queries more quickly. 

Scenario 4: Complete your queries

You can also use natural language to help Copilot create the following query:

Scenario 4: Complete your queries using natural language

Conclusion

Copilot takes advantage of natural language instruction, as in the examples above, to create more complex queries, say to pivot the results of a query.  Copilot can sometimes provide multiple suggestions to choose from, and you can choose whichever suggestion is most useful to you.

Get started today with GitHub Copilot in Azure Data Studio by installing the latest Azure Data Studio release and installing GitHub Copilot from the extension marketplace. Form information about GitHub Copilot, see the documentation for Azure Data Studio, MySQL extension, and GitHub Copilot. 

If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!