GQL— Write SQL queries in your Git repo!

José Freitas — Tue, 10 Dec 2024 02:06:31 +0000

TLDR — saving you time!

The article introduces an open-source tool that allows you to execute SQL queries on a Git repository. In other words, you can query commit messages, authors, branches, etc., formatted in a table, as if your repository were a massive database. ;)

There are several ways to obtain specific information about a repository — not all of them are easy, quick, or elegant to execute or visualize. Possibly, the most suitable approach so far would be to use the graphical interface of the platform where your project is hosted (GitHub, GitLab, and the like) and leverage the tools they provide.

All of them cover the basics — but sometimes you need something more specific, and that’s what we’ll explore here. Meet Git Query Language, an SQL-like language for performing queries on .git files!

Installing the tool

Use the command below to install it on Windows:

winget install gitql

or if you're using Mac (or homebrew on Linux):

brew install gql

Let's start with an example repository

...and what better repository to use than the tool's own repository? 😄

Run the command below to clone the repository to your machine through SSH.

git clone git@github.com:AmrDeveloper/GQL.git

Alright, we have our query language, and we have our "database." Let's test it!

Open the terminal and navigate to the tool's project directory. Type the command below:

gitql

This will open an interactive session in the terminal, as shown below:

Let's start small by querying the commit titles. We'll limit it to 5 records since there are many commits.

SELECT title FROM commits LIMIT 5

In this case, the table we want to query is "commits," but at the end of the article, you'll find a list of all the tables available in the tool.
We’ll have:

Pretty cool, isn’t it? But so far, it doesn’t tell us much. Who made the commits? Who’s responsible?! Let’s find out!

SELECT title, name AS author FROM commits LIMIT 5

Phew, that’s better now. We know who made each commit. Now we can better visualize who worked on each feature — or bug — just the way we’re used to. And hey, when was everything done? We need to know that too!

SELECT title, name AS author, datetime AS commit_date FROM commits LIMIT 5

Perfect. We have the commit title, the author, the date/time... Anyone missing the commit ID?

SELECT commit_id AS id, title, name AS author, datetime AS commit_date FROM commits LIMIT 5

There we go. I think that’s enough. Now, how about asking our repository a few more questions?

What was the infamous “first commit” of the repository?

SELECT commit_id AS id, title, name AS author, datetime AS commit_date FROM commits LIMIT 1 ORDER BY commit_date ASC

What were the last 5 commits made by this AmrDeveloper person?

SELECT commit_id AS id, title, name AS author, datetime AS commit_date FROM commits LIMIT 5 WHERE LOWER(author) = "amr
developer" ORDER BY commit_date DESC

Hmm… Now I only want the features! The last 5 features!

SELECT commit_id AS id, title, name AS author, datetime AS commit_date FROM commits WHERE title LIKE "feat%" LIMIT 5 ORDER BY commit_date DESC

Especially useful when your project follows Conventional Commits.

What are the project's tags?

SELECT * FROM tags

And… done! I mean, the tool can do much more — but I think you got a pretty good idea of how it works, right? 😄
You can check out the tables available in the tool here. And the full documentation here.

I'm passionate about "collecting" interesting open-source tools. I plan to gradually share them in a list on my LinkedIn while posting articles diving a bit deeper into specific tools. If you have any questions, feedback, or suggestions, feel free to DM me. See ya. ;)