My workflow for writing SQL(ite) queries (2024 edition)

Featured image for sharing metadata for article

In the last ~18 months I've been writing a lot of SQL due to my work on dependency-management-data.

As I wrote about on dependency-management-data's birthday, I've been learning quite a few things about SQL and becoming much more fluent with writing it.

I'll end up writing an SQL query against the dependency-management-data data at least once per day, and so I want to make sure that my workflow works for me.

I've recently made some improvements to the local workflow I've been using for authoring SQL and thought it would be useful to share.

Note: I've called this "2024 edition" but I've never written one of these before. However, I am expecting that in future years this process will be improved and I'll add new posts.

Initial setup

My original setup was that I would write queries directly into the sqlite3, which were often copied from my Neovim editor where at least I had syntax highlighting and could easily create multi-line queries.

Not long after, I learned about the excellent Datasette database browser, and started using that as my main interface for the queries I was writing.

But in the recent months, I've been finding that I'll be writing a query, and then need to join across one of a couple of tables. Writing this by hand got so repetitive that I've now got a snippet in my Neovim which completes to:

left join owners on renovate.platform = owners.platform
and renovate.organisation = owners.organisation
and renovate.repo = owners.repo

However, this ends up with me starting to write the query in Datasette, then copy-pasting the query I've written so far into Neovim to add the owners snippet and merge that in, then copy that back into Datasette.

This was getting a little awkward, especially in the case that I'm sharing my screen with folks, so there's an awkward "BRB" moment.

I was also looking at whether I could get Datasette to provide autocomplete when writing queries, but it seems like it's not yet complete.

I'd started to consider looking at trying out firenvim again, which is best for screen sharing, but I found a few rough edges when I was using it last (in 2023?).

Instead, I remembered that it's been a while since I've tried to follow the Language Server Protocol (LSP) setup for SQL, and so thought I'd give that a go.

My new, pure Neovim, setup

I tried sql-language-server and sqls and found that sqls works super nicely for what I want, and it's now my daily driver.

It's got to the point where I'm now barely using Datasette as my interface, and instead using sqls and a local copy of the database, and then only moving to Datasette if I want to share the query URLs with other people.

Not only does sqls give me full autocomplete and hover comments based on columns, it also has the ability to execute the queries, so I can completely stay in Neovim.

For instance, this is what it looks like trying to write a query, then using the LSP functionality to execute that query:

(Note: there doesn't appear to be any colours shown in the Neovim editor due to a known issue, but I promise there is, I'm not a monster who doesn't use syntax highlighting πŸ₯²)

This setup is largely helped by the fact that the databases I'm working with are SQLite databases that I can run locally, and that I have tooling to make it easier to sync those local copies of the database from where they're produced.

You'll notice that we can use the inbuilt LSP functionality to execute queries, or in my case I've bound this to <leader>x for easier execution.

But sometimes it's useful to be able to share a query with someone else, which as mentioned is usually deployed centrally via Datasette.

For instance, for a given query I can call the :Datasette function (bound to <leader>D) and it'll construct the URL I need to access it on a given Datasette URL πŸ‘ like so:

Does it work for me?

This new setup gives me all the benefits I want out of being able to author and execute SQL:

  • I can write queries with syntax highlighting
  • I can write queries with autocomplete for tables/columns
  • I can execute queries
  • I can construct shareable URLs
  • I don't have to switch between editors to achieve writing a single query

I'm loving my new setup and it's making it a really nice experience for all the SQL I'm doing!

Written by Jamie Tanna's profile image Jamie Tanna on , and last updated on .

Content for this article is shared under the terms of the Creative Commons Attribution Non Commercial Share Alike 4.0 International, and code is shared under the Apache License 2.0.

#blogumentation #sql #sqlite #neovim #datasette #lsp.

This post was filed under articles.

Interactions with this post

Interactions with this post

Below you can find the interactions that this page has had using WebMention.

Have you written a response to this post? Let me know the URL:

Do you not have a website set up with WebMention capabilities? You can use Comment Parade.