Performing cross-database queries with SQLite
![Featured image for sharing metadata for article](https://media.jvt.me/0cc01860d8.png)
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.