How to retrieve pull request comments from the Bitbucket database

Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.

Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Summary

The content on this page relates to platforms that are not supported. Consequently, Atlassian Support cannot guarantee providing any support for it. Please be aware that this material is provided for your information only and using it is done so at your own risk.

The purpose of this article is to query the Bitbucket database for a particular Pull Request's comments. The comments are visible under the Overview tab of the Pull Request.

Environment

  • Bitbucket Server or Data Center

Solution

A sample Pull Request with comments is below.

(Auto-migrated image: description temporarily unavailable)

Info

  • The SQLs are tested on PostgreSQL 16.0.

Step 1

Use this SQL to obtain the Pull Request ID for a particular Project and Repository name. Please replace the <PROJECT-NAME>  and <REPO-NAME>  with the project and repository you are interested in.

1 2 3 4 5 6 7 8 9 10 SELECT spr.id FROM project AS p, repository AS r, sta_pull_request AS spr WHERE p.id = r.project_id AND spr.to_repository_id = r.id AND p.name = '<PROJECT-NAME>' /*replace project name here*/ AND r.name = '<REPO-NAME>' /*replace repo name here*/ AND spr.from_branch_fqn = 'refs/heads/test2' /*replace source branch name here*/ AND spr.to_branch_fqn = 'refs/heads/master'; /*replace target branch here*/

The sample output of the above would look like this.

1 2 3 4 id ---- 42 (1 row)

Step 2

The following SQL will return all the comments and the authors under that particular Pull Request ID obtained from the above query. Please replace the <PULL_REQUEST_ID> with the required pull request that you are interested in.

1 2 3 4 5 6 7 8 9 10 SELECT snu.name AS username, bc.comment_text AS comment FROM sta_pr_activity AS spa, bb_pr_comment_activity AS bpca, bb_comment AS bc, sta_normal_user AS snu WHERE spa.pr_id = <PULL_REQUEST_ID> /*replace PR ID from previous SQL here*/ AND spa.pr_action = 1 AND spa.activity_id = bpca.activity_id AND bpca.comment_id = bc.id and bc.author_id = snu.user_id;

The sample output of the above query would look like below.

1 2 3 4 5 username | comment ----------+----------------- admin | test admin | comment comment (2 rows)

Updated on February 27, 2025

Still need help?

The Atlassian Community is here for you.