Synchronizes GitHub data (issues, labels, ...) to a MySQL database
Switch branches/tags
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
app
.env.dist
.gitignore
README.md
composer.json
composer.lock
github-to-mysql

README.md

GitHub to MySQL

Synchronizes GitHub data (issues, labels, ...) to a MySQL database.

Features:

  • synchronize issues and pull requests
    • support multiple pages of results
  • synchronize labels
  • synchronize milestones

Getting started

  • clone the repository or download a stable release and unzip it
  • run composer install
  • copy .env.dist to create a .env file
  • create the DB tables by running ./github-to-mysql db-init --force You can check which DB queries will be run by removing the --force option (the queries will NOT be run if the option is missing).

You can also simply run ./github-to-mysql without arguments and follow the instructions.

The .env file contains the configuration to connect to the MySQL database as well as the GitHub token. Alternatively to using this file you can set up all the environment variables it contains.

Usage

./github-to-mysql sync user/repository

Use cases

With GitHub data in MySQL, you can extract interesting metrics. Here are a few examples:

  • discover labels with the most issues or pull requests

  • follow number of issues planned per milestone

  • ratio of pull requests VS issues

    SELECT count(*) AS count, IF(is_pull_request, 'Pull request', 'Issue') AS is_pull_request
    FROM github_issues
    GROUP BY is_pull_request
    ORDER BY is_pull_request ASC

  • average number of days to merge a pull request over the past weeks

    SELECT
        YEARWEEK(IFNULL(closed_at, NOW())) AS week,
        AVG(TIMESTAMPDIFF(DAY, created_at, IFNULL(closed_at, NOW())))
    FROM github_issues
    WHERE is_pull_request = 1
    GROUP BY week;

  • average number of pull requests open every day

    SELECT
    	DATE(updated_at) AS day,
    	(SELECT COUNT(*) FROM github_issues WHERE is_pull_request = 1 AND created_at < day AND (closed_at >= day OR open = 1)) AS pr_open
    FROM github_issues
    WHERE updated_at IS NOT NULL
    GROUP BY day;

  • total number of issues

Those are just examples to illustrate the possibilities, we hope it will give you some ideas.