How to Get Commit Statistics from Bitbucket Database

Using SQL queries to obtain commit statistics from Bitbucket can provide more flexibility in solving custom use cases than REST API. However, Bitbucket Data Center doesn’t store commits in the database, making it difficult and time-consuming to get their data. In this case, the Awesome Graphs for Bitbucket app may be of help. It stores indexed data about commits, lines of code, and users who made them, making it much faster to get the needed reports.

This article will explore several use cases showing how to get commit statistics from the Bitbucket database using SQL queries and the Awesome Graphs for Bitbucket app.

Get the number of commits for the specific period

One of the use cases that can be easily solved with an SQL query to the Awesome Graphs tables in the Bitbucket database is counting the number of commits over a specific period of time. By analyzing this data, managers can evaluate the efficiency of their development teams, compare it over different time spans, and see fluctuations. Moreover, it provides insights into projects’ overall health and progress. Here is an example of the SQL query made for PostgreSQL to get the commit count for the past 12 months:

Please note that the query above was made for PostgreSQL. Modifying it for other databases may be necessary. Feel free to contact us for assistance to use it correctly.

select count("ID")
from "AO_6292E3_GRAPHS_COMMITS"
where to_timestamp("DATE" / 1000) between '2023-12-31' and '2023-12-31 23:59:59';

You can get the same data using the Awesome Graphs REST API. In this case, you’ll get not only the number of commits over a specific period of time but also the number of LoC (lines of code) added and deleted:

{
  "linesOfCode": {
    "added": 2061114,
    "deleted": 1917168
  },
  "commits": 7262
}

Here is the request to get this data where you can change query parameters, e.g., set a time period using `sinceDate` and `untilDate` in the `YYYY-MM-DD` format.:

curl -X GET -u username:password "https://%bitbucket-host%/rest/awesome-graphs-api/latest/commits/statistics?sinceDate=2023-01-01&untilDate=2023-12-31"

You can also use Awesome Graphs REST API to get commit statistics of a particular project and repository or even build custom reports using Python.

Find the number of unique contributors to a project by month or year

To determine the licensing requirements or evaluate the project engagement, some of our clients need to count the number of unique contributors to a project by month or year. To get this data, the following SQL query can be run:

select to_char(to_timestamp(commits."DATE" / 1000), 'YYYY-MM') as month,
       count(distinct commits."CONTRIBUTOR_ID")                as authors
from "AO_6292E3_GRAPHS_COMMITS" as commits
         join repository on repository.id = commits."REPOSITORY_ID"
         join project on project.id = repository.project_id
where to_timestamp(commits."DATE" / 1000) between '2023-01-01' and '2023-12-31 23:59:59'
  and project.project_key = 'YOUR_PROJECT_KEY'
group by to_char(to_timestamp(commits."DATE" / 1000), 'YYYY-MM');

Please note that to run the query above, you need to add the slug of the project you need to get statistics about. The query was made for PostgreSQL. Modifying it for other databases may be necessary. Feel free to contact us for assistance to use it correctly.

The resulting file contains the number of authors who contributed to the specific project during each month:

See the number of lines of code based on specific commit ID

A single commit statistics from Bitbucket can be retrieved in two ways: through the Awesome Graphs REST API or by running an SQL query to the database.

If you choose to use the REST API, the request will look like:

curl -X GET -u username:password "https://%bitbucket-host%/rest/awesome-graphs-api/latest/projects/{projectKey}/repos/{repositorySlug}/commits/{commitId}"

Using this request, you’ll receive not only the number of lines of code added and deleted but also additional information such as the author’s name and email, repository details, commit date, and project information. Here’s an example of the response you’ll get:

{
  "author": {
    "displayName": "jsmith",
    "emailAddress": "jsmith@stiltsoft.com"
  },
  "authorTimestamp": "2020-03-05T22:58:18Z",
  "linesOfCode": {
    "added": 17,
    "deleted": 5
  },
  "parents": [
    {
      "id": "06572e813597bc7d77c8"
    }
  ],
  "repository": {
    "slug": "Awesome Graphs for Bitbucket Server",
    "name": "awesome-graphs-for-bitbucket-server",
    "project": {
      "key": "AWEGRAPHS",
      "name": "Awesome Graphs",
      "type": "NORMAL"
    }
  },
  "id": "9f2e24a147bb8f5a5a3d10b692703cc5784df8b5"
}

Alternatively, if you prefer using SQL, you can run a query that retrieves only the number of added and deleted lines of code for a particular commit:

select "LINES_ADDED", "LINES_DELETED"
from "AO_6292E3_GRAPHS_COMMITS" as commits
    join repository on repository.id = commits."REPOSITORY_ID"
where "HASH_ID" = '2e3a574db8bca45297dffccb087da9a81c571d00' and repository.slug = 'REPOSITORY_SLUG';

Please note that the query above was made for PostgreSQL. Modifying it for other databases may be necessary. Feel free to contact us for assistance to use it correctly.

Сount the total number of commits in a repo or project, see commits made by a specific user, get the number of LoC per user, and many more use cases can be resolved using SQL queries to the Awesome Graphs tables in the Bitbucket database. That’s why we launched a Premium Support subscription exclusively for Awesome Graphs Data Center customers, where our technical team, with extensive and in-depth knowledge of Bitbucket data, can help resolve your requests.

Share your use cases with us, and we’ll explore how we can assist you.