Performing cross-database queries with SQLite

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.