AskGit - SQL Queries on Git Repository History and Data

AskGit is a service that allows developers to run SQL queries on the history and data stored in BitBucket git repos. It creates a new section in the BitBucket repo page with a query editor and runner.

Comment

Inspiration

In mid-June, I built a command-line tool called gitqlite, which enables SQL querying of git data. When I heard about the Codegeist Hackathon and read a bit about what's possible with Atlassian Connect + BitBucket, I thought an integration that would allow users to run SQL queries on their git history and data from BitBucket would be really cool!

What it does

AskGit adds a section called SQL Query to your repository. This includes a SQL editor that permits you to execute arbitrary SQL queries against the commit history and code in your repository. It works with both private and public BitBucket repos, authentication is handled in the background.

The use cases are very broad since this project allows arbitrary querying:

  • Who checked in code this week? This month? This year?
  • How many lines are they adding/removing? In what files? In what types of files?
  • Which files have seen the most activity/commit churn?
  • What's the average time between commits on master?
  • Who are the most recently active authors?
  • etc. etc

If the data is in git, and you have a question about it, I hope to make it query-able!

How I built it

This app has two parts:

  1. A query API backend (that uses gitqlite) to expose a way to execute SQL queries over HTTP
  2. A (React + AtlasKit) front-end app that serves the UI, handles authentication with BitBucket, makes calls to the query API, and displays results.

The query API is a go service, the UI is a Next.js app, that exposes a description JSON document, the UI that appears in BitBucket, and a homepage to allow users to install the app via a button.

Challenges I ran into

Handling authentication was difficult - needing to learn Atlassian Connect auth flows - validating and producing JWTs, which eventually become access tokens for cloning repositories was one of the main challenges. The documentation was a great help, and so was reading questions/answers from other Atlassian developers, so I'm grateful I was able to figure things out with publicly available resources/documentation and get things working well!

Accomplishments that I'm proud of

I'm really proud of how this app came out generally - in particular the user experience from installation -> first query I find really cool. I'm super impressed by how easy Atlassian has made it for developers to embed additional functionality in their products. A user can begin executing SQL queries against their git data really quick, with authentication handled in the background (works on private repos as well).

What I learned

I learned a lot about what's possible in Atlassian Connect and AtlasKit, as well as what's involved in building an MVP style app integrated with Atlassian products. Doing so has definitely inspired more ideas for potential apps, as well as further improvements to this one!

What's next for AskGit - SQL Queries on Git Repository History and Data

I would love to continue the development of this project. Some very immediate next steps include:

  • A syntax highlighting SQL editor
  • Ability to save queries for re-use
  • More example queries to showcase what's possible
  • Ability to download/export results in various formats (csv, json, etc)

Improvements to the open-source gitqlite project will also improve AskGit. In particular, better query performance, more tables, more SQL functions (for working with git data) will all make the experience of using AskGit + BitBucket awesome.

I'd love to invest in turning this project into a product that serves developers on BitBucket (and other platforms)!

I developed this app as an individual (just me!), but am submitting it as an organization - Augmentable Software, a company I founded earlier this year to house and build developer tools like this one!

Built With

Share this project:

Updates