We've been big fans of Renovate, an open-source tool and GitHub app that automates dependency management by opening PRs into your repos. In particular, we've loved:
- The breadth of supported dependency types, including Dockerfiles, Helm Charts and GitHub Actions
- PRs are rate limited, so that we're not overwhelmed with code changes to review
- Respect for conventional commits
Recently, we wanted to know which of our repos had Renovate installed, which is a use case several folks have now shared with us! This post showcases some of the queries we put together around our use of Renovate.
We spun up an instance of MergeStat and begin writing some queries 🎉.
Which Codebases Have Renovate Installed?
Get the list of repos that have Renovate installed (by looking for a renovate.json config file in the root of the repo).
-- which repos have a renovate.json file in the root
SELECT repo, path FROM git_files
JOIN repos ON git_files.repo_id = repos.id
WHERE path = 'renovate.json'
Retrieve when the renovate.json file was added to a codebase, by looking at the commit history.
-- how long ago (in days) were renovate.json files added to our repos
SELECT
repo,
max(author_when) AS last_modified,
ROUND(EXTRACT(EPOCH FROM now() - max(author_when))/86400, 2) AS last_modified_days_ago,
min(author_when) AS first_added,
ROUND(EXTRACT(EPOCH FROM now() - min(author_when))/86400, 2) AS first_added_days_ago
FROM git_commit_stats
JOIN git_commits ON git_commit_stats.commit_hash = git_commits.hash
JOIN repos ON git_commit_stats.repo_id = repos.id
WHERE file_path = 'renovate.json'
GROUP BY repo
ORDER BY first_added ASC
How Many Renovate PRs Have Been Opened?
-- total count of renovate PRs across repos
SELECT count(*) FROM github_pull_requests
WHERE author_login = 'renovate'
-- count of renovate PRs across repos by state
SELECT state, count(*)
FROM github_pull_requests
WHERE author_login = 'renovate'
GROUP BY state
ORDER BY count(*) DESC
What Files Does Renovate Typically Modify?
When a Renovate PR merges, what files is it typically making changes to?
-- count of modifications made by Renovate to specific files
SELECT file_path, count(*) FROM git_commits
JOIN git_commit_stats ON git_commits.hash = git_commit_stats.commit_hash
JOIN repos ON git_commits.repo_id = repos.id
WHERE author_name = 'renovate[bot]'
GROUP BY file_path
ORDER BY count(*) DESC
Here's example output from our codebases:
| file_path | count |
|---|---|
| package-lock.json | 259 |
| package.json | 231 |
| go.mod | 37 |
| go.sum | 36 |
| .nvmrc | 12 |
| .github/workflows/linter.yaml | 6 |
| .github/workflows/lint-test.yaml | 6 |
| renovate.json | 6 |
| .github/workflows/release.yml | 5 |
| .github/workflows/chromatic.yml | 4 |
Show the Cumulative Sum of Merged Renovate PRs
How much has Renovate actually been up to - and how much value are we getting from it over time?
-- calculate the cumulative sum of merged renovate PRs over time
WITH data As (
SELECT
date_trunc('day', created_at) AS day,
count(1)
FROM github_pull_requests
WHERE author_login = 'renovate' AND state = 'MERGED'
GROUP BY 1
)
SELECT
day,
sum(count) OVER (ORDER BY day ASC rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM data
How Quickly Are Renovate PRs Merged?
What's the distribution of time-to-merge (measured as the number of days between PR creation and merge) of Renovate PRs.
SELECT
FLOOR((EXTRACT(EPOCH FROM merged_at - created_at)/86400)/5)*5 as bin_floor,
count(*)
FROM github_pull_requests
WHERE author_login = 'renovate' AND state = 'MERGED'
GROUP BY 1
ORDER BY 1
Conclusion
We were curious to play around with this data to learn about our use of Renovate and the value we've been getting from it. If you're a Renovate user, hopefully it's interesting to you as well!
If you found this interesting, hop in our community Slack! We're always happy to chat about MergeStat there 🎉.