Are you in the loop? Keep up with the latest by making sure you're subscribed to Community Announcements. Just click Watch and select Articles.

Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


How to query bitbucket's database using SQL from a plugin

We are developing a plugin for bitbucket that creates it's own tables in bitbucket's database. It is done using Atlassian's active objects library. However, this library has many limitations.

For example, we would like to join multiple tables and get data from all of them in a single SQL request to assure the coherence between the data received from each table. The database can be accessed from multiple threads so we want to ensure that the data retrieved from the first table and the data retrieved from the second table come from the same version of the database. Therefore, we would like to retrieve all the data needed in a single request.

Here is what we have tried so far :

1- Using the active objects library's find function and the object we can send sql queries to the database. However, when performing a join in the java query, the active object's find function automatically overrides the select parameter to only choose the value of a single table. We haven't found a way to retrieve data from multiple tables using active objects.

2- Importing that pocketknife querydsl library to perform more powerful SQL queries. However, when injecting that DatabaseAccessor object from the application, it gives an UnsupportedTransactionalExecutorFactory and when running a query an exception is thrown with the message indicating that SAL's TransactionalExecutor is not supported with bitbucket.

3- Using native QueryDsl to access the database directly. However, for this to work, it requires a way for us to access the database's connection parameters (URL, username, password). Although we can hardcode those for testing purposes, we need a way to retrieve that information from bitbucket through the plugin, but we aren't sure if that's at all possible.

We are not sure which of the 3 approaches is the best one, or if any of them can actually work for what we are trying to do. Is there any way to actually run SQL queries on our own tables in bitbucket's database ?

Thank you for your help

1 answer

1 accepted


We reached similar pain points with AO very quickly and decided to go with the pocketknife QueryDSL wrapper. Be advised that by using raw SQL this way, edge cases of different DB systems and versions have tendencies to creep up  :D 

 Here some points that made it work for us:

  • Using SpringScanner 2, we disabled the scanning of some classes to be able to provide our own DatabaseAccessor, for us it looked like:
  • Most layers of Pocketknife are straight forward to replace, the core of it is the HostConnectionAccessor. In Scala we did it this way:
  • All in all we implemented the following classes:
    • DatabaseMigrationListener to disable the plugin when a DB migration succeeds to completely clean all caches
    • Event listener to clear pocketknife caches  with PKQCacheClearer
    • DatabaseAccessor that uses our HostConnectionAccessor from above (almost same implementation as pocketknife but using another class)
    • ProductSchemaProvider that gets the schema from our HostConnectionAccessor

After the tedious setup, the usage is easier. I suggest to use AO for the schema management of the tables, and use QueryDSL for everything else. 

Hopefully that gets you going!

Suggest an answer

Log in or Sign up to answer
Site Admin
AUG Leaders

Atlassian Community Events