Murphy Randle

Software Writer

GitHub CoPilot is Good at SQL


I’m reminded how quickly life can change when I check my blog and see that the last posts were about spending all of my time in Jetbrains products. I’m not using them much now. All my time is spent in VS Code, because I changed teams at https://automattic.com from working on Android to working on web again.

And I began to fall in love with the web platform & tools again.

So, instead of pressing forward on my side project with Kotlin on the server and Swift on iOS, I decided to put aside all the work I had done… and do it over again in Typescript / React Native… again.

I say again twice because I’ve worked on side projects in React Native (or whatever) before, and thrown it all away before, more than once. From what I hear, this is the circle of life for side projects.

Here I am again, re-writing. But this time… this time it’g going to stick, right?

I’m making a lovely little server in Typescript backed by SQLite, designed to run on a single small Linode box and scale up on as little money as possible for as long as I can make it. Part of my experimentation with moving back to Typescript on the server was seeing if I could go a little more minimal than I would have in the past. So I’ve tried to shave a few dependencies off.

For example, instead of installing a bcrypt module from NPM for password hashing, I’m just using Node’s built-in scrypt function. And instead of installing a utility library for working with byte arrays, I just wrote a simple function to compare them by value and put it in a utils file.

Or did I write it?

Enter GitHub CoPilot. “Writing” code is a funny thing to claim in the modern programming world. Here’s more of what that actually looked like:

  • Look up libraries for Uint8Array comparison on Google
  • Realize there aren’t compelling options, and people on stack overflow are giving examples for how to do-it-yourself simply
  • Get an idea of what the examples are doing
  • Go back to code editor, start typing a new function
  • Don’t even type all of the keys necessary to write export const, because the editor offers autocomplete after a few letters
  • type the name of the function uint8ArrayEquals
  • Watch as GitHub copilot suggests the entire rest of the function in autocomplete
  • Accept the suggestion, read through the result to make sure it makes sense

So, if that counts as writing code, I wrote it. If not, then few of us are going to be writing any code as the years roll on.

But the reason I wrote this post in the first place was to point out something neat: GitHub CoPilot is pretty good at writing SQL.

When starting with this server re-write I immediately reached for a SQL query builder like https://knexjs.org, http://csnw.github.io/sql-bricks/, or https://github.com/koskimas/kysely, all of which are cool in their own ways. But since I was on a time budget, and have been jazzed about writing more stuff for myself after listening to people like Casey Muratori, I thought I’d try going without a query builder, or an ORM.

My objects don’t have that many fields, and my whole app doesn’t have that many queries. I didn’t think it’d be too bad to type out the SQL. So I just installed better-sqlite3 and started writing my application logic. It turned out even better than I’d expected.

Thanks to GitHub CoPilot, I start typing a function like:

export function getPostsSince(date: number

And then GitHub suggests the rest of the function for me, including SQL, parameter substitution, and the correct API for better-sqlite3… most of the time. Usually, all I have to do is accept the autocompletion and then tweak a few things to get the behavior I want.

When it works well, it’s like having a very productive pair programmer on-demand.

, ,

Leave a Reply

Your email address will not be published. Required fields are marked *