Does Github Copilot fly with PL/SQL?

Unless you have been living under a rock for the last year you will have heard about GitHub Copilot. Announced in June 2021, the VSCode extension is powered by OpenAI.

As an APEX developer (check out some of the work we do here), I wanted to find out how this cutting-edge AI technology would fare with a programming language fast approaching its 27th birthday. After 4 months of heavy use, it is safe to say Copilot has more than earned its wings and it will be a bumpy flight if GitHub ever triggers the ejector seat on my free trial!

Who are OpenAI?

Founded partly by Elon Musk and Sam Altman, the company conduct research in the field of developing AI systems and applications that are “safe, secure and socially beneficial.”

The company was originally set up as a non-profit organisation but in 2019, changed to a “capped-profit” company to attract investment. It worked - In 2019 Microsoft invested $1billion in OpenAI to become their sole cloud provider.

Arguably their greatest achievement to date is GPT-3 (3rd generation Generative Pre-Trained Transformer). This is a neural network machine learning model that can be trained on publicly available data online. Accessed via an API, GPT-3 is being used to create incredible results with minimal input from the user.

Some trending uses of the technology can be found here and include things like designing an application, describing what a certain block of code does and creating a REGEX, all from a short text instruction from the user.

Copilot is a sub-product of this GPT-3 technology and as well as being trained on text from the internet, it has also been trained on billions of lines of code from the public repositories on GitHub. This means that if you have any public repo stored on GH, you have played your part in the creation of this extension. So when you wrote that to-do list app in 2017, you probably never imagined it would be used to help produce cutting-edge general AI - but here we are.

OpenAI’s mission statement is to “ensure that artificial general intelligence benefits all of humanity”, a bold claim. The last time I checked I was part of humanity, so I wanted to find out if OpenAI – with their “AI pair programmer” extension could benefit me in my role as an APEX developer. I signed up for the technical preview waiting list and after a few months, I was granted access.

How does Github Copilot work?

The extension links your VSCode environment with the OpenAI Codex Model to provide suggestions and improve future suggestions based on your selections and edits as you code.

We are told Copilot works “especially well” with Python, JavaScript, TypeScript, Ruby, Java and Go but it understands dozens of languages including PL/SQL.

I’ll use JS to show how you interact with the extension to generate the suggestions.

• Write a descriptive function name and Copilot will suggest the rest. Just hit Tab to confirm:

• Type a comment in plain English of what you what the function to do and it will do the rest:

• Toggle through selections in line, or hit ctrl + enter (cmd + enter on mac) to see the top 10 synthesized solutions available for you to select:

Quite impressive right? So how does it deal with PL/SQL, it’s not mentioned anywhere on the blurb for Copilot so I’m not expecting much.

Let’s apply the same approach to writing a simple comment to create a function and see how it copes:

Not great, I couldn’t have asked for a simpler function, and it has failed quite miserably in terms of PL/SQL syntax.

When I first got access to the trial, I tried a few similar PL/SQL examples and was severely underwhelmed. I pretty much dismissed it at that point – it looks useful if you are using one of the stated languages, but otherwise it’s not going to help me day-to-day.

I moved on and quickly forgot about the extension. Then, whilst I was getting on with my day job (writing PL/SQL business logic to compliment the APEX application I was creating for a client) I noticed something pretty cool – the autocomplete on VSCode seemed to be on steroids! Usually, it will complete the names of variables (that exist already in the file you are working on), but it started completing my SQL statements, suggesting (correctly) the tables and columns I wanted to join on:

The more code I wrote the more it surprised me, for example here - I was adding comments before writing some code to create an HTML table and it spat out the exact code I was going to write.

It even formats the code in your style:

Here, I just started typing the debug message and it knew exactly the items I wanted to debug and completed the code for me:

I nearly fell off my chair with this one – I literally typed ‘case’ and it completed the rest. This was 100% accurate and the code I was going to write. There was no similar statement to this in the file I was using, but there was a reference to the table and columns in question, so with some descriptive parameter names it was able to complete my case statement:

Copilot does not connect to the database so it will only know table names if you have used them in the current file. It would be nice if it enabled the features to look at all files in your repo to get contextual information but that might come in the future.

Github Copilot Summary

There has been a lot of hyperbole around AGI and tools like Copilot replacing developers in the future. Even taking into consideration the undoubted progression that will occur with the technology, for developers to be replaced clients would need to be able to write a short statement declaring exactly what they want from every aspect of an application. This is an almost impossible task and the nuance required to progress complex business logic from client requirements to functional specification to code will never be accurately automated in my view.

“Copilot - Your AI Pair Programmer” is exactly that - a tool to work with you.

In the 4 months or so I have been using it I can see some key benefits:

• Boosts productivity – incredibly smart autocomplete cuts out a lot of typing.
• Allows people with lesser skills in a language to learn new techniques and achieve more.
• Less ‘copy and paste’ from stack overflow for boilerplate code, more time to focus on other more creative and less mundane tasks.

CoPilot is only available for a small group of testers during the technical preview but there are plans to roll out a commercial version in the future. Try it for yourself – join the waiting list here.

Interested in our services? Check out our APEX Development and discover unique solutions for your business. You can also email us at enquiries@dsp.co.uk or book a meeting!

If you liked this blog, make sure to check out the rest of our blogs here, which include content on Oracle Database, SQL Server, Google Cloud, Open Source Databases, Data Science and much more.