In a discussion titled “Database’s Data Model?” under the Support category, zapata_36 asks for a data dictionary or updated schema to help export and query the Discourse database, especially for use in Tableau. gerhard notes that formal diagrams are rare and outdated, suggesting users explore the database directly via the Data Explorer plugin or other tools. riking adds that model files in the source code (e.g., app/models/table_name.rb) contain schema comments explaining columns. Mittineague shares CLI commands to inspect tables and columns via psql, cautioning users to be careful. Bas initially misunderstands the model file reference, but Mittineague clarifies that filenames correspond to singular table names (e.g., user.rb for the users table), and each includes a schema comment block. Bas acknowledges the clarification and expresses hope for a centralized table overview. Finally, Konrad_Sopala asks how to connect the database as a data source in Tableau, indicating ongoing interest in practical integration. The thread reflects a community effort to help users navigate Discourse’s database structure without official documentation.
If we want to export the database and run certain queries against it, say in Tableau, would it just be guess work or can someone point to a community resource on the database’s data model?
I’ve found an older post with a diagram/schema of the database’s tables, but does anyone know if there’s a data dictionary that explains each of the individual items and what they do?
I’m not an expert on this sort of stuff, so apologies in advance if I’m using the wrong language. Hopefully my question makes sense!
I don’t think something like that exists as such diagrams tend to get outdated quite fast.
But the table and column names are quite self explanatory. I recommend you take a look at the database with the Discourse Data Explorer or your favorite database tool. You can always come back with specific questions if you don’t find the data you are looking for.
And if you have any questions that the column name / Data Explorer enum explanation doesn’t resolve, check out app/model/table_name.rb in the Discourse source code!
enter password, then sudo -u postgres psql discourse
Then, being very careful I run queries like
SELECT table_name, table_type
FROM INFORMATION_SCHEMA.TABLES
WHERE table_catalog = 'discourse'
AND table_schema = 'public';
* currently 138 rows
SELECT table_name, column_name, data_type, column_default, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_catalog = 'discourse'
AND table_schema = 'public'
ORDER BY table_name;
* currently 1258 rows, a good idea to have a WHERE table_name = '{{some table name here}}' or a LIMIT in that query.
The “table_name” is a place holder for an actual file name. The file names are the singular of the table names. eg. user.rb - the users table, group.rb - the groups table.
at the bottom of each is similar to
# == Schema Information
#
# Table name: drafts
#
# id :integer not null, primary key
# user_id :integer not null
# draft_key :string not null
# data :text not null
# created_at :datetime not null
# updated_at :datetime not null
# sequence :integer default(0), not null
# revisions :integer default(1), not null
#
# Indexes
#
# index_drafts_on_user_id_and_draft_key (user_id,draft_key)
#