github-to-sqlite: my tool for creating a SQLite database of data from the GitHub API

Wanted to share a project I’ve been working on for a while now: github-to-sqlite

It’s a command-line utility written in Python that fetches data from the GitHub API (with or without authentication) and saves it to a SQLite database file.

This means you can query your GitHub project data - repositories, issues, issue comments, releases, starred repos and more - using SQL.

I designed the tool to work well with my Datasette web application, which provides a UI and API for browsing SQLite databases and executing queries against them.

The tool includes a scheduled GitHub Action which runs daily and publishes data from a number of my projects as a live demo: https://github-to-sqlite.dogsheep.net/

Here’s a SQL view that shows recent releases across ALL of my projects: https://github-to-sqlite.dogsheep.net/github/recent_releases

And here’s a custom SQL query that shows which GitHub users have starred the most of my repos, and which repos they starred: https://github-to-sqlite.dogsheep.net/github?sql=select%0D%0A++%27https%3A%2F%2Fgithub.com%2F%27+%7C%7C+users.login%2C%0D%0A++count%28repos.id%29%2C%0D%0A++group_concat%28repos.name%2C+%27%2C+%27%29%0D%0Afrom%0D%0A++stars%0D%0A++join+users+on+stars.user+%3D+users.id%0D%0A++join+repos+on+stars.repo+%3D+repos.id%0D%0Agroup+by%0D%0A++users.id%0D%0Aorder+by%0D%0A++count%28repos.id%29+desc

@garethr used github-to-sqlite to build a database for exploring cloud native projects - his database can be seen here: https://cloud-native-tools.herokuapp.com/ - it’s built and deployed by GitHub Actions running in this repo: https://github.com/garethr/cloud-native-tools

I’m finding this to be a really useful way of tying together data across multiple repositories. I hope other people find it useful as well!