Querying your organisation's Renovate configuration using SQL(ite)

In what will seem very topical on this blog (after my post Why I recommend Renovate over any other dependency update tools the other day) I've got another post about Renovate.

Over the last few years I've worked a lot with Renovate, and at the last two companies I've been largely focussed on enabling teams to use Renovate better.

However, something recently came up at work where I needed an easy way to list which teams were using a configuration value, so we could make some follow-up changes.

As we have well over 2000 repos, I wanted to search across all repos for relevant config settings, and then get PRs raised for the relevant work associated for those teams. As the lowest barrier to entry, I used the GitHub search for it, raised my PRs and went about my day.

About an hour later I got a message to say "hey, you're missing this repo, and this one, oh and this one". For whatever reason, the GitHub search didn't surface quite a few repos, which was a little frustrating as I hoped I had everything sorted.

To avoid this, as well as to give me a more structured interface for querying the data using my new favourite thing, SQLite, and its JSON querying I've decided to build a smallish Go command-line tool which helps sync Renovate's JSON/JSON5 configuration to an SQLite database for further querying.

This is now live as the Go command-line tool renovate-config-sqlite.

Right now I've got support for GitHub.com - as that's where I'm using it at work - but I'm open to adding support for GitLab, as it's where my personal projects are hosted, and I'm sure it'll be useful for folks in their companies too.

(aside: since building this tool, I've found even more cases of repos with Renovate configuration that need the mentioned changes applied to them)

(additional aside: after writing this I looked around for anyone else doing similar and found this blog post from MergeStat which seemed cool!)

Example

For a bit of an idea of how this tool could be useful, let's look at a few examples of what this data can be used for.

In the below example, I've pulled in public repos from the oapi-codegen org, Giant Swarm org and the oapi-codegen project.

For instance, we can look at "who is using the oapi-codegen presets" we could write the following query:

select
  distinct
  organisation,
  repo
from
  renovate_configs,
  json_each(
    json_extract(renovate_configs.config, '$.extends')
  )
  where json_each.value LIKE '%oapi-codegen/renovate-config%'

Which results in:

Alternatively, to look at which repos have defined the most packageRules:

select
  distinct organisation,
  repo,
  json_array_length(
    json_extract(renovate_configs.config, '$.packageRules')
  ) as num_package_rules
from
  renovate_configs
where
  json_extract(renovate_configs.config, '$.packageRules') is not null
order by
  num_package_rules desc

Which results in:

Hopefully this gives an idea of what you can use this to query, and even if it's not useful to others, I'll be using it, even if it's a way to force me to remember how SQLite's JSON operations work.