Database diagram for tables, keys, columns, etcetera, in Discourse?


(KajMagnus) #1

Hi! Is there any database diagram for Discourse available somewhere? (GitHub for example.)
That shows tables and relationships etcetera.

I did find this file: https://github.com/discourse/discourse/blob/master/db/structure.sql
But it’s 2500 rows and … not a terribly fun read :-/
(I did find tables topics and posts anyway.)

(Background: I’m writing some database tables for pages and comments, and was thinking it’d be interesting to have a look at Discourse’s tables. — And I suppose it’d be useful for people contributing to Discourse.)


Database Design Diagram?
Discourse schema - is there an ER diagram available anywhere?
MyBB import doesn't process on quoted posts PID and date
(Jeff Atwood) #2

Is there any open source software that takes that file and converts it to a diagram?


(Sam Saffron) #3

There are plenty open source software that creates db diagram.


(Alejandro Lujan) #4

Just for the record, it seems the url to the sql structure script has changed to https://github.com/discourse/discourse/blob/master/dbs/sql/empty.sql.gz


(Robin Ward) #5

This has come up again a couple of times. For now there is no diagram as I personally use them in my workflow. But if someone from the community wants to step up and contribute / maintain one that might help out others!


(PeFerg) #6

Hi,

Sorry for reviving the topic, but since the urls have changed, where could we find the current sql db schema?


(Robin Ward) #7

We don’t check it in anymore, but if you get a development instance of discourse and migrate the database it will be generated for you.


(Christopher Heald) #8

I used SchemaSpy to create a diagram, with limited success. I get all tables and columns, but SchemaSpy claims that ‘No relationships were detected in the schema’.

If anybody has any ideas how to get the relationships detected, I’d be happy to share an up-to-date diagram.

Steps I took:

Enter the Discourse container and dump the database:
sudo -u postgres pg_dump discourse --no-owner | gzip > discoursedb.gz

Transfer the file to my dev machine, and restore the dump:
psql -d discourse -f discoursedb.sql

Restore seems to have worked with no warnings other than these two errors at the end:
psql:discoursedb.sql:15459: ERROR: role "discourse" does not exist
psql:discoursedb.sql:15460: ERROR: role "discourse" does not exist

These are the lines that caused the error (not too concerned about them):
REVOKE ALL ON SCHEMA public FROM discourse;
GRANT ALL ON SCHEMA public TO discourse;

This is the SchemaSpy command I used to generate the diagram:

schemaSpy_5.0.0.jar -dp postgresql-9.4-1201.jdbc4.jar -t pgsql -db discourse -s public -host localhost -port 5432 -u pguser -o ~/DiscourseDb

(Kane York) #9

That all looks right. I think you’d need to extract the FK relationships from the Ruby code.

I think that ActiveRecord made the decision to not turn relationships into FK constraints to make it easier to perform a transaction in the order it wants to, instead of ensuring FK consistency in every single step.


(Christopher Heald) #10

Thanks @riking. Having to extract the relationships from the Ruby code makes it more complex for non-(Ruby)-dev folks who may want to write badge SQL scripts.

However, it’s not too hard to figure out the relationships from the table list. I have put the SchemaSpy output here, minus the pretty interconnecting arrows. It could help people who want to get a quick idea of the database structure.


Visual map of users
(PeFerg) #11

Thank you very much!