Performing cross-database queries with SQLite

June 19, 2024, 12:54 p.m.

As you may be aware if you're a reader of my blog, I'm currently heavily on the SQLite train (to productivity) and as such I spend a lot of time interacting with SQLite, recently with a new and improved workflow.

The main database I spend my time in is the database from dependency-management-data but I'm also finding that I'm starting to interact a lot with Renovate configurations.

One problem with these is that there's information in dependency-management-data (in the repository_metadata table) that's useful to have when querying Renovate configuration, and after hitting this problem again this morning, I thought I'd look into a better way of doing this.

Naturally, I landed on Simon Willison's blog, on a post about cross-database queries in SQLite who indicated that it was possible to do so.

For instance, let's say that I want to say "which of my non-fork public repositories have the labels configuration set in their Renovate configuration"?

Earlier this morning I tried to answer a similar question and ended up writing several queries across the two databases, but it turns out that we can do this with a single query, with an attach'd database.

For instance, if we run:

sqlite3 /path/to.dmd.db

Then run the following:

-- one time thing, when opening the database
attach '/path/to/renovate-configs.db' as rc;

select
 repository_metadata.repo,
 visibility,
 is_fork,
 json_extract(renovate_configs.config, '$.labels') as labels
from
 repository_metadata
 inner join rc.renovate_configs
 on repository_metadata.platform = rc.renovate_configs.platform
 and repository_metadata.organisation = rc.renovate_configs.organisation
 and repository_metadata.repo = rc.renovate_configs.repo
where
 visibility = 'PUBLIC'
 -- NOTE that in this case, the `is_fork` field is defined as:
 --
 -- is_fork boolean not null
 --
 -- which is then stored in the database as TEXT, so we need to do string comparison here
 and is_fork = 'false'
 and labels is not null
order by repository_metadata.repo
;

This then queries across the two databases 🤓

There are other more complex queries we can write, but I'll leave it at this one for now.