The following examples show simple queries that make use of the
List all commits in the currently checked out history:
SELECT * FROM commits('https://github.com/mergestat/mergestat')
We're specifying the HTTP git repo as an argument to the
commits table-valued function, by passing it as the first parameter.
In the CLI, if you supply the
--repo flag or if the current working directory is a git repo,
commits (and other git tables) will use that repo instead.
SELECT * FROM commits -- repo is derived from context such as the --repo flag or the current directory
List all commits from a specific history
SELECT * FROM commits('', 'some-other-branch') -- list commits of a specific branch
SELECT * FROM commits('', 'COMMIT_SHA') -- list commits starting from a commit hash
Passing an empty string (
'') as the first parameter to most
git tables indicates that the default repository should be used (which is inferred from the context, such as the current directory or the
This can be necessary when an additional parameter is passed, but you want to infer the repositoryå.
List all commits by a specific author
SELECT * FROM commits('https://github.com/mergestat/mergestat') WHERE author_name = 'firstname.lastname@example.org'
SELECT * FROM commits('https://github.com/mergestat/mergestat') WHERE author_name LIKE '%Patrick%'
See here for more context on git references.
SELECT * FROM refs('https://github.com/mergestat/mergestat')
SELECT * FROM refs WHERE type = 'branch'
-- select all branches and the timestamp of their last commit
SELECT refs.name, author_when
FROM refs, commits
WHERE type = 'branch' AND commits.hash = refs.hash
SELECT * FROM refs WHERE type = 'tag'
-- version tags only
SELECT * FROM refs WHERE type = 'tag' AND name LIKE 'v%'
files table valued function lists all the files in a given commit tree.
It can be joined with the
commits table to traverse the files in the history of a repository, across many commits.
SELECT * FROM files
This can be an expensive query if
* is used, as the
contents columns contains the full contents of a file.
This can cause problems in memory constrained execution environments, especially if you're returning many files over many commits.
List all file paths in all commits
-- second parameter indicates what commit tree to list files from
SELECT commits.hash, files.path FROM commits, files('', commits.hash)