How to Retrieve Bitbucket Server Repository Paths Using SQL Queries PostgreSQL or EDB

Bitbucket Server, developed by Atlassian, is a powerful tool for managing Git repositories. For system administrators and developers, managing and querying repository information directly from the database can be essential for various maintenance and administrative tasks. In this tutorial, we'll walk you through the process of retrieving repository paths by project key using a simple SQL query designed for PostgreSQL and EDB (EnterpriseDB). This guide is particularly useful for those who need to handle multiple repositories and project keys within Bitbucket Server.

Prerequisites

Before diving into the SQL query, ensure you have the following prerequisites:

1. Access to Bitbucket Server Database: You should have the necessary permissions to query the Bitbucket Server database.
2. SQL Client: Any SQL client tool such as pgAdmin, DBeaver, or another client that supports PostgreSQL/EDB.
3. Basic SQL Knowledge: Understanding basic SQL commands and syntax will help you follow along with the tutorial.

Understanding the Database Structure

Bitbucket Server stores its data in a relational database, and PostgreSQL or EDB is commonly used as the database management system. The main tables we will focus on are:

• repository: Contains information about each repository, including its ID and slug.
• project: Contains information about projects, including the project key and ID.

The relationship between these tables is established via the project_id field in the repository table, which links to the ID field in the project table.

The SQL Query

To retrieve the repository paths based on project keys, we will use the following SQL query suitable for PostgreSQL/EDB:

SELECT

    '/repositories-path/' || r.ID AS PATH,

    r.slug,

    p.project_key

FROM

    repository r

JOIN project p ON r.project_id = p.ID

WHERE

    p.project_key IN ('PROJKEY1', 'PROJKEY2');

Explanation of the Query

• SELECT Clause: This part of the query specifies the columns we want to retrieve.
• '/repositories-path/' || r.ID AS PATH: Concatenates the repository ID with the base path to form the full repository path.
• r.slug: Retrieves the slug (short name) of the repository.
• p.project_key: Retrieves the project key associated with the repository.
• FROM Clause: Specifies the tables we are querying from, which are repository and project.
• JOIN Clause: Links the repository table to the project table using the project_id field.
• WHERE Clause: Filters the results to include only the repositories that belong to specific project keys ('PROJKEY1', 'PROJKEY2').