Copilot for SQL Development 1-Pager

7/3/24

Using GitHub Copilot for SQL Development

Summary

GitHub Copilot can assist with SQL-focused development in a variety of ways, leveraging both the in-line suggestion functionality of Copilot, as well as the Chat interface. That being said, Chat does offer some advantages for SQL development in particular over the in-line suggestions, as it is easier to direct the context Chat gathers, as well as to iterate on and tweak the suggestions returned.

GitHub Copilot needs context in order to give quality suggestions, and this is especially true for SQL development. Unlike traditional programming languages, SQL code is tightly coupled with databases, and if Copilot does not have any context from the database being referenced, the suggestions will be of a poor quality.

Resources

For example

If you open a new Python file and give Copilot the command: “write me a function that reverses a linked list,” it will be able to make inferences even without context to come up with a decent suggestion. It may not know exactly how your presumed linked list class looks, but it will be able to make a more useful suggestion than if you open a new SQL file and give Copilot the command: “write a query to find a list of all my customers who have open opportunities worth more than $1M,” because in that case, the quality of the suggestion is even more dependant on your data model, which Copilot would not have any idea of.

So what should I do? First off…give Copilot context

To improve suggestion quality, you need to have your schema in a file and have that file open in the IDE for Copilot to pull from as part of its prompt/context data. Additionally, it will benefit from other relevant SQL-related files being open, such as files containing queries.

A note on Copilot Enterprise

Unless you’re using Copilot Enterprise (now in private beta, Copilot Enterprise is a new licensing tier above Copilot Individual and Copilot Business), where you could actually train the model on that information, so then Copilot would have an awareness of your data model without you needing to have any relevant files open in your IDE.

What about Copilot Chat?

Once you’ve made sure you have relevant files available in your IDE for Copilot to pull context from, you’re ready to start prompting it for suggestions. Remember: the more guiding details you include in your prompts, the better your suggestion quality will be (prompt engineering). If you’re dealing with Oracle PL-SQL, specifying so will help! ("Write a query for Oracle that..." will help avoid getting ANSI SQL).

For instance, a colleague shared this comment: I noticed that it seems to try to write a lot of queries that would use outer joins as INNER JOIN, which clearly means I haven't found the right way of expressing the requirements for those particular scenarios. Oracle has changed its support for JOIN types over the various releases, so that may factor in. It also seems to have challenges putting code in stored procedures or views. Things like that are where I need to spend more time formulating and testing different prompts, ones that call out specific releases, etc.

When using Chat, specifically, you can see in the interface, when Copilot responds, it shows which files in the IDE are being included in the prompt being sent to the Copilot server. This gives developers a means to ensure that appropriate schema files, examples of queries, etc. are being parsed. If you’re using VS Code, you can use the “@workspace” command to specifically instruct Copilot Chat to parse all files available in the project navigation, even those that aren’t necessarily open in the IDE, as detailed here.