nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Identifying Backend Jira DB Tables and structure

talha Parvaiz

Hi,

We use Jira for project management across multiple groups.

I want to extract data (related to resource utilization hrs/tickets/etc. by projects/sprints) from backed DB through ETL. can you please direct me to the related DB schemas (assuming it's common for all Jira implementation).

Sorry, I am a new bee and might not have asked the question correctly. All I am looking for is when I log into Jira and see a Ticket number field; then, I should be able to identify/map it to the back-end database.

Thanks in advance

Accepted answers

Mikael Sandberg

Hi @talha Parvaiz,

Welcome to Atlassian Community!

You can find the database schemas for different Jira versions here, Database schema. Please note that this only applies to the Server side, on Cloud you do not have direct access to the Database and can only extract data using the REST API. You can find all developer recourses for Jira Cloud here, and the equivalent on for Jira Server here.

All comments

Mikael Sandberg

Hi @talha Parvaiz,

Welcome to Atlassian Community!

Nic Brough -Adaptavist-

No.

Ok, yes, there's a schema available to look at.

But you really really really do not want to use SQL on a Jira database. Frankly, the database is a bloody mess, do not waste your time with this.

Last week, I got paid for 12 hours work building an SQL query for a client who insisted on doing it with SQL. I got them a query that did exactly what they needed. There were 186 "join" statements in it. It only works for Jira 8.2 to 8.8 from what I can see, and has to be used "out of hours" so it doesn't cripple the database service for other people.

The REST API I suggested they use returned the same data in under a second and it only took a few minutes to work out the exact curl statement to do it. And it was the same call for Jira 6.0 through to 8.12.

Please, move off SQL, just use the REST API for what it was built for.

If nothing else, your question says "assuming it's common for all Jira implementation" - you can only assume that for Jira systems that are exactly the same version, and I do mean exact - there have been versions of Jira that change the database at a point release (for example, a 5.4.1 release has a different database structure to 5.4.2)

talha Parvaiz

Thanks for the suggestions.

just to further elaborate. we are looking to migrate Jira data over to our DB warehouse for further analysis and reporting purposes

I was thinking of joining jiradb.jiraissue to the following tables

jiradb.issuestatus
jiradb.issuetype
jiradb.priority
jiradb.project
jiradb.resolution

to get most of the related information.

Any thoughts?

Thanks

talha Parvaiz

Here is the schema that I am using

https://developer.atlassian.com/server/jira/platform/attachments/jira-7-9-2-database-schema.pdf

Thanks

Nic Brough -Adaptavist-

Stop reading the database. It's slow, clunky, painful, subject to change and really really really the single worst way to look at Jira data.

As for your joins - no, you're missing another 30-40 you need to read a simple issue, and when an issue has a complex config, you're missing a huge pile more.

Please, stop.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

atl-gruland