DB query for fetching list of public repositories in Bitbucket Server
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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
Important Note on DB Queries
This Database Query / Functionality is not officially supported!
Atlassian is not responsible for translating queries for different databases.
This query should only be used as an example. Any desired adjustments should be checked with your local Database Administrator, including queries related to different DBMS that are not present/listed in this KB article, like Oracle Database.
Atlassian will not be providing support for changes, adjustments, or inquiries regarding the below queries, as this is not part of our Atlassian Support Offerings.
Atlassian does not provide any support to sample database queries provided in our Knowledge Base documents. Extraction of data from Bitbucket should be performed using our REST API Documentation because we do not keep all data relationships in the DB. Many connections to data are handled in the code. The REST API can go through the code paths to get the data. A SQL would have to do that manually or, in some cases, it can’t be done at all.
There are certain scenarios when you wish to know which public repositories inside your Bitbucket instance are public. A repository can be made public in the following ways:
Turn a project public, and all the repositories inside this project will be turned public unless you change any of them specifically.
Turn a specific repository public, this repository can be a private or a personal repository.
Environment
Bitbucket Server 7.2
Solution
This query may not work in the Future releases
The following query has been tested on Bitbucket Server 7.2.x and may not work in other releases, since there is a chance of change in DB schema with the other releases.
Query for Postgres
SELECT
prj.name AS "Project Name"
, prj.project_key AS "Project Key"
, per.is_public AS "Is Project Public"
, rep.name AS "Repository Name"
, rep.slug AS "Repository Slug"
,rep.is_public "Is Repository Public"
FROM sta_normal_project per
RIGHT JOIN project prj ON per.project_id = prj.id
INNER JOIN repository rep ON rep.project_id = prj.id where per.is_public=true OR rep.is_public=trueOutput
You should see an output like the following on executing the above query
Project Name | Project Key | Is Project Public | Repository Name | Repository Slug | Is Repository Public |
|---|---|---|---|---|---|
Pr2 | PR | f | publicrepo | publicrepo | t |
Puball | PUB | t | repo3 | repo3 | f |
Puball | PUB | t | repo2 | repo2 | f |
Puball | PUB | t | repo1 | repo1 | f |
~john.doe | ~JOHN.DOE | perrepo1 | perrepo1 | t |
Was this helpful?