What is the best way to get JIRA tables/columns definitions ?
Hi Shawn - Welcome to the Atlassian Community!
Are you using Jira Cloud or Data Center?
If you're on Jira Cloud, there's no direct access to the underlying database tables. The best approach is to use the Jira REST API to retrieve fields, issues, and their definitions.
For Data Center, Atlassian provides database schema documentation that can help you understand the tables and relationships.
Hope this helps!
Hi @Shawn B
In Jira, "tables and columns" can refer to very different layers depending on whether you are working in Jira Cloud or Jira Data Center/Server as Jovânio Junior had mentioned, and whether you are accessing raw SQL databases, an analytics data lake, or APIs.
Here are few ways to define based on Jira Cloud or Datacenter:
SELECT table_name, column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema = 'public' AND table_name LIKE 'jira%' ORDER BY table_name, ordinal_position;
I hope this answers your question. 🙂
Thanks, Anwesha
Adding to what Jovânio said, here is where each one actually lives.
Cloud: you can’t get the database itself, but if you have Atlassian Analytics or a Data Lake data share, Atlassian publishes the schema for it. It covers every table and column with its SQL name, data type and foreign keys: Schema for Jira family of apps. In the Analytics schema browser, turn on “Show full schema” or you won’t see all the tables. Without Analytics, the REST endpoint GET /rest/api/3/field is the closest you’ll get to a field dictionary (field IDs, names, types).
GET /rest/api/3/field
Data Center: see Database schema. The full definition for your exact version is in <jira-install>/atlassian-jira/WEB-INF/classes/entitydefs/entitymodel.xml. Atlassian recommends REST over direct SQL, so treat the DB as read only.
<jira-install>/atlassian-jira/WEB-INF/classes/entitydefs/entitymodel.xml
which one are you on, and is this for reporting or for an integration? That changes which one I’d point you to.
Hi Shawn,
It depends on where Jira runs, because only Data Center gives you access to the database itself.
Atlassian advises against querying or changing the database directly. Use it read-only for reporting, use the REST APIs for any changes, and always back up first.
It looks like you're new here. Sign in or register to get started.