Performing cross-database queries with SQLite

Featured image for sharing metadata for article

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;

  json_extract(renovate_configs.config, '$.labels') as labels
  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
  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.

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 #sqlite.

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.