8 ms·

Gitqlite: Query Git Repositories with SQL

166 points by sebazzz 6y ago

icholy 6y ago
You can name the binary git-qlite if you want to invoke it as a subcommand on git. git qlite "SELECT * FROM commits"
- patrickdevivo 6y ago
  that’s great to know, will take a look and add a note to the README
- koolba 6y ago
  How can you pass up naming it “git-sql”?! git sql “SELECT * ...”
  - patrickdevivo 6y ago
    haha you're totally right! if it's just a matter of renaming the binary and dropping it in a particular directory to get git to know about it, I can leave this part up to the user. I'll definitely have to make a note in the README. gitqlite is a little clunky for a name, but I wanted to make sure to convey that it's sqlite doing a lot of the heavy lifting here!
    - klyrs 6y ago
      Not so humble opinion, I'd encourage you to aim for ergonomics and let people read about the project if they want to know about the backend.
searchableguy 6y ago
Edit: I misunderstood that it was for github. Great project for querying git repos.
- detaro 6y ago
  Git repositories outside Github exist.
patrickdevivo 6y ago
Creator here! Thanks for submitting - I hope people find this interesting. I’m excited to explore more use cases beyond listing and reformatting git log output! There’s a similar project called gitbase, but I had difficulty just spinning that up for ad-hoc CLI querying so created this project in hope of a better UX for that type of use case. It uses SQLite virtual tables, which was something I had been meaning to play around with. This seemed like a cool use of them!
- vcryan 6y ago
  Can you query multiple repos at with one query? Interesting tool!
  - patrickdevivo 6y ago
    At the moment, not easily, but someone else just asked about that and I think it's worth looking into! It makes sense and could be a cool use case to run queries on all the key repos in an org or project
    - princesse 6y ago
      This would make the difference between neat and useful for my team.
      - vcryan 6y ago
        same!
- diegoholiveira 6y ago
  A friend of mine did something similar a few years ago: https://github.com/filhodanuvem/gitql https://github.com/filhodanuvem/gitql
- sdesol 6y ago
  Cool. It's taken me years to find an efficient (and most importantly, easily debuggable) way to transfer Git's history to SQL. Since you are looking for use cases, I'll share with you some of the ways that I'm leveraging SQL to help drive code analytics for Git. Once Git's history is in an easy to query state, you can do some interesting things. For example, if you aggregate all the contributors in a Git repo, you can produce something like this: https://imgur.com/tT4DSJc https://imgur.com/tT4DSJc which makes it very easy to identify project investment/commitment. In the above example, you can see that Microsoft is heavily invested in vscode, as a lot of the developers that are contributing to it, have been doing so for more than 3 years. And if you aggregate contributions by file types, you can see how people are contributing to it as well. In the case of vscode, the contributions are mainly TypeScript contributions. Here is another contributor example, which shows GitLab contributions: https://imgur.com/3UU6Kda https://imgur.com/3UU6Kda What the above analytics shows, is GitLab has a lot of contributors and a lot of them are new contributors (6 months or less), which makes sense since they were hiring aggressively not too long ago. Not sure if this is still the case with Covid-19, but this can be easily confirmed 6 months from now, with the same chart. Now for something more interesting in my opinion, which is code review analytics. https://imgur.com/xWH6QFl https://imgur.com/xWH6QFl It has taken a lot of research and development to get to this point, but once you can easily query Git, you can surface very interesting things by cross referencing it with external systems, like GitHub's pull request system. In the pull requests screen shot, I created a window that only considers open pull requests that were updated within the last 30 days. With this type of window, I can see what has changed across dozens, if not hundreds or thousands of pull requests. For example, I can easily identify file collisions, between pull requests. When was their last commit, and so forth. I'm still working on refining my code review analytics, but the goal is to get it to an advanced state, where you can see exactly what is happening between pull requests and to derive insights from those requests. So those are just some of the use cases that I've developed, which leverages being able to query a Git's history with SQL.
xorander 6y ago
I mainly use fossil these days, with git as a secondary option. One of the main reasons I went with fossil is due to it being built on top of SQLite and being able to query against the database, as well as extend it for customized project management. Interesting stuff. When time permits, I'll have to check out how this implementation for git differs from fossil.
simonw 6y ago
I wonder how hard it would be to load up these virtual tables (written in Go) against a Python sqlite3 connection. I imagine this would involve loading Go code as a Python module, or maybe using FFI somehow? Would be really fun to be able to call these virtual tables from Datasette.
dan-robertson 6y ago
See also: Fossil, a version control system backed by a SQLite database. The author (of Fossil and SQLite) points out that a real database gives you more flexible efficient queries for your data. In git in comparison, it is for example much easier to find the parents of a commit than its children.
- microcolonel 6y ago
  > In git in comparison, it is for example much easier to find the parents of a commit than its children. Sure, but both are really cheap.
  - cryptonector 6y ago
    Well, no, in huge repositories with lots of branches it's not. But this is true for Fossil as well. IIRC MSFT did a study with the Windows repository showing that a relational approach to VCS bloated the repository too much, and out of this grew the Bloom filter approach to speeding up with git blame/log.
    - balfirevic 6y ago
      > But this is true for Fossil as well. Since I don't know anything about how Fossil stores commit metadata this might a naive question - but why is that? I would guess that being able to efficiently query all the children of particular commit should be just an index away.
      - microcolonel 6y ago
        > I would guess that being able to efficiently query all the children of particular commit should be just an index away. Yes, but where will you find the space, and when will you compute it?
        balfirevic 6y ago
        I'm not sure I understand what you mean. I was talking about Fossil, which uses SQLite under the hood, which would then maintain the index whenever data in it is modified.
Humphrey 6y ago
This post reminds me of a product idea I've been pondering for a while: A versioned database backed by git. It would only be useful for data that made sense to be represented in individual files (perhaps json files with the file name as the id), with an API that allowed the data to be accessed and written to in a similar way to other databases. Perhaps similar to a nosql database, but because it's in git, everything is versioned and can be reverted.
- jacques_chester 6y ago
  There are a few of these around now (various developers of which are active on HN and who will no doubt pop up in a bit). I prefer a bitemporal approach to data history. Easier for non-nerds to understand and much more powerful to boot.
- ishcheklein 6y ago
  There are two projects that take this direction - Splitgraph and Dolt.
mavsman 6y ago
Really was hoping this would be for finding projects on GitHub, not for searching within a repo. I often find it hard to do advanced searches using some special DSL GitHub has come up with (or really any company/website for that matter) and would love to use a SQL query to find specific git repos. I definitely feel the same way about YouTube channels and videos.
- sitkack 6y ago
  There are a couple github datasets in BigQuery, this might work for your usecase.
setheron 6y ago
Checkout Calcite. It's a library that transforms SQL into relational algebra that can be used with many backends including git.

166 points by sebazzz 6y ago

icholy 6y ago

You can name the binary git-qlite if you want to invoke it as a subcommand on git. git qlite "SELECT * FROM commits"

patrickdevivo 6y ago
that’s great to know, will take a look and add a note to the README
koolba 6y ago
How can you pass up naming it “git-sql”?! git sql “SELECT * ...”
- patrickdevivo 6y ago
  haha you're totally right! if it's just a matter of renaming the binary and dropping it in a particular directory to get git to know about it, I can leave this part up to the user. I'll definitely have to make a note in the README. gitqlite is a little clunky for a name, but I wanted to make sure to convey that it's sqlite doing a lot of the heavy lifting here!
  - klyrs 6y ago
    Not so humble opinion, I'd encourage you to aim for ergonomics and let people read about the project if they want to know about the backend.

searchableguy 6y ago

Edit: I misunderstood that it was for github. Great project for querying git repos.

detaro 6y ago
Git repositories outside Github exist.

patrickdevivo 6y ago

Creator here! Thanks for submitting - I hope people find this interesting. I’m excited to explore more use cases beyond listing and reformatting git log output! There’s a similar project called gitbase, but I had difficulty just spinning that up for ad-hoc CLI querying so created this project in hope of a better UX for that type of use case. It uses SQLite virtual tables, which was something I had been meaning to play around with. This seemed like a cool use of them!

vcryan 6y ago
Can you query multiple repos at with one query? Interesting tool!
- patrickdevivo 6y ago
  At the moment, not easily, but someone else just asked about that and I think it's worth looking into! It makes sense and could be a cool use case to run queries on all the key repos in an org or project
  - princesse 6y ago
    This would make the difference between neat and useful for my team.
    - vcryan 6y ago
      same!
diegoholiveira 6y ago
A friend of mine did something similar a few years ago: https://github.com/filhodanuvem/gitql https://github.com/filhodanuvem/gitql
sdesol 6y ago
Cool. It's taken me years to find an efficient (and most importantly, easily debuggable) way to transfer Git's history to SQL. Since you are looking for use cases, I'll share with you some of the ways that I'm leveraging SQL to help drive code analytics for Git. Once Git's history is in an easy to query state, you can do some interesting things. For example, if you aggregate all the contributors in a Git repo, you can produce something like this: https://imgur.com/tT4DSJc https://imgur.com/tT4DSJc which makes it very easy to identify project investment/commitment. In the above example, you can see that Microsoft is heavily invested in vscode, as a lot of the developers that are contributing to it, have been doing so for more than 3 years. And if you aggregate contributions by file types, you can see how people are contributing to it as well. In the case of vscode, the contributions are mainly TypeScript contributions. Here is another contributor example, which shows GitLab contributions: https://imgur.com/3UU6Kda https://imgur.com/3UU6Kda What the above analytics shows, is GitLab has a lot of contributors and a lot of them are new contributors (6 months or less), which makes sense since they were hiring aggressively not too long ago. Not sure if this is still the case with Covid-19, but this can be easily confirmed 6 months from now, with the same chart. Now for something more interesting in my opinion, which is code review analytics. https://imgur.com/xWH6QFl https://imgur.com/xWH6QFl It has taken a lot of research and development to get to this point, but once you can easily query Git, you can surface very interesting things by cross referencing it with external systems, like GitHub's pull request system. In the pull requests screen shot, I created a window that only considers open pull requests that were updated within the last 30 days. With this type of window, I can see what has changed across dozens, if not hundreds or thousands of pull requests. For example, I can easily identify file collisions, between pull requests. When was their last commit, and so forth. I'm still working on refining my code review analytics, but the goal is to get it to an advanced state, where you can see exactly what is happening between pull requests and to derive insights from those requests. So those are just some of the use cases that I've developed, which leverages being able to query a Git's history with SQL.

xorander 6y ago

I mainly use fossil these days, with git as a secondary option. One of the main reasons I went with fossil is due to it being built on top of SQLite and being able to query against the database, as well as extend it for customized project management. Interesting stuff. When time permits, I'll have to check out how this implementation for git differs from fossil.

simonw 6y ago

I wonder how hard it would be to load up these virtual tables (written in Go) against a Python sqlite3 connection. I imagine this would involve loading Go code as a Python module, or maybe using FFI somehow? Would be really fun to be able to call these virtual tables from Datasette.

dan-robertson 6y ago

See also: Fossil, a version control system backed by a SQLite database. The author (of Fossil and SQLite) points out that a real database gives you more flexible efficient queries for your data. In git in comparison, it is for example much easier to find the parents of a commit than its children.

microcolonel 6y ago
> In git in comparison, it is for example much easier to find the parents of a commit than its children. Sure, but both are really cheap.
- cryptonector 6y ago
  Well, no, in huge repositories with lots of branches it's not. But this is true for Fossil as well. IIRC MSFT did a study with the Windows repository showing that a relational approach to VCS bloated the repository too much, and out of this grew the Bloom filter approach to speeding up with git blame/log.
  - balfirevic 6y ago
    > But this is true for Fossil as well. Since I don't know anything about how Fossil stores commit metadata this might a naive question - but why is that? I would guess that being able to efficiently query all the children of particular commit should be just an index away.
    - microcolonel 6y ago
      > I would guess that being able to efficiently query all the children of particular commit should be just an index away. Yes, but where will you find the space, and when will you compute it?
      - balfirevic 6y ago
        I'm not sure I understand what you mean. I was talking about Fossil, which uses SQLite under the hood, which would then maintain the index whenever data in it is modified.

Humphrey 6y ago

This post reminds me of a product idea I've been pondering for a while: A versioned database backed by git. It would only be useful for data that made sense to be represented in individual files (perhaps json files with the file name as the id), with an API that allowed the data to be accessed and written to in a similar way to other databases. Perhaps similar to a nosql database, but because it's in git, everything is versioned and can be reverted.

jacques_chester 6y ago
There are a few of these around now (various developers of which are active on HN and who will no doubt pop up in a bit). I prefer a bitemporal approach to data history. Easier for non-nerds to understand and much more powerful to boot.
ishcheklein 6y ago
There are two projects that take this direction - Splitgraph and Dolt.

mavsman 6y ago

Really was hoping this would be for finding projects on GitHub, not for searching within a repo. I often find it hard to do advanced searches using some special DSL GitHub has come up with (or really any company/website for that matter) and would love to use a SQL query to find specific git repos. I definitely feel the same way about YouTube channels and videos.

sitkack 6y ago
There are a couple github datasets in BigQuery, this might work for your usecase.

setheron 6y ago

Checkout Calcite. It's a library that transforms SQL into relational algebra that can be used with many backends including git.