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

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:

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!