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.
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)Was this helpful?