Database's Data Model?

AI-generated summary

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!

I often prefer the command line.

cd /var/discourse 
sudo ./launcher enter app

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.

I can’t find this file here?
discourse/app/models at main · discourse/discourse · GitHub

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)
#

Ah got it, sorry, was a bit confused :sleeping:

Hoped there was a table_names overview :slight_smile:

Hey there!

Thanks for creating that thread. Got one more core question how to connect to that as a data source in Tableau?