renovate-config-sqlite

README

Renovate Configuration (as an SQLite database)

A tool to retrieve Renovate configuration from source forges and create an SQLite database with the configurations, which then enables follow-up queries.

This makes it useful for at-a-glance queries (see below for some examples) to understand what sort of configuration is in use across various repositories.

This is likely a somewhat overengineered solution, but I wanted to do it, so here it is.

Usage

Installation

The tool is a Go command-line tool, and supports being built from source:

go install gitlab.com/tanna.dev/renovate-config-sqlite@latest

Preparing the database

First, we need to create the database:

renovate-config-sqlite db init --db configs.db

Fetching data

To retrieve Renovate configuration from the oapi-codegen org:

env GITHUB_TOKEN=... renovate-config-sqlite discover github --db configs.db --organisation oapi-codegen

If you have a GitHub App set up, you can also also authenticate with that App:

# via https://www.jvt.me/posts/2023/02/11/pem-environment-variable/
env GITHUB_APP_ID=... GITHUB_APP_KEY="$(sed ':a;N;$!ba;s/\n/\\n/g' ...)" GITHUB_APP_INSTALLATION_ID=... renovate-config-sqlite discover github --db configs.db --organisation oapi-codegen

NOTE: that it's not currently supported to be able to scan all installations for a given GitHub App, so you need to specify the installation ID.

Example queries

To give an idea about the different things that can be achieved with this data, check out the below example queries.

Which repos aren't using our shared configuration?

Let's say that you're using shareable config presets as an organisation, and you want to find which of your repos don't rely upon it.

We could write a query such as the below, assuming that you have a repo, renovate-config in your organisation, your-org:

select
  distinct organisation,
  repo,
  json_extract(renovate_configs.config, '$.extends') as extends
from
  renovate_configs
where
  not exists (
    select
      1
    from
      json_each(
        json_extract(renovate_configs.config, '$.extends')
      )
    where
      json_each.value LIKE '%>your-org/renovate-config%'
  )

Which repos are using branch-based automerge?

Wondering which teams use automerge in branch mode (where a PR won't be raised)?

select
  distinct
  organisation,
  repo
from
  renovate_configs
where
  json_extract(renovate_configs.config, '$.automergeType') = 'branch'

Which repos use custom scheduling?

Want to work out which repos are using custom scheduling of their Renovate builds, i.e. to avoid clashing with other builds?

select
  distinct organisation,
  repo,
  json_extract(renovate_configs.config, '$.schedule') as schedule
from
  renovate_configs
where
  schedule is not null

Which repos are using which pieces of configuration

To get a high-level view of which of the top-level configuration items teams are using in their configurations, we can use the following query:

select
  json_each.key as configuration_key,
  group_concat(
    platform || '/' || organisation || '/' || repo,
    char(13)
  ) as repos,
  count(*) as num_repos_with_config,
  total_repos.c as total_repos
from
  renovate_configs,
  json_each(renovate_configs.config),
  (
    select
      count(*) as c
    from
      renovate_configs
  ) as total_repos
group by
  json_each.key

This gives an indication of which of the repos use configurations such as i.e. hostRules, customManagers, enabledManagers as well as how many out of the total number of repos in the dataset use it.

Known issues

• Unnecessary HTTP requests will be sent:
  • https://gitlab.com/tanna.dev/renovate-config-sqlite/-/issues/7
  • https://gitlab.com/tanna.dev/renovate-config-sqlite/-/issues/6
• Secondary rate limits may be received from GitHub depending on the size of organisation(s) being scanned

License

Licensed under the Apache-2.0.