Include DB diagrams in documentation

[kmcelwee]

Spike

Autogenerating dbml

I have not found dbml generators that do not cause syntax errors.

From Django

• https://pypi.org/project/django-dbml/
  • There were duplicate refs and unescaped quotes that caused dbdocs to error. These were easy to correct by hand, but I'm unsure how we'd automate it.
  • It does not have widespread adoption.

From the database

• sql2dbml is a node service to generate dbml from a database.
  • I tested against a cdh-web production dump.
    • It did not recognize LOCK and UNLOCK sql functions.
  • I tested against our new postgres db. There were more syntax errors.

Autogenerating visualizations

dbdocs may be the best platform to help us automatically generate db diagrams. Though they are still in Beta. It's a team of two, and there doesn't seem to be too widespread adoption. If we have a feature request we'd like, they encourage reaching out: tuan.nguyen@holistics.io

• They require a interactive login for every build, which we can override with:

  echo DBDOCS_KEY | dbdocs login

• I grabbed DBDOCS_KEY during login. I do not know if this key expires.
• They don’t have a REST API

Progress is being tracked: https://github.com/Princeton-CDH/cdh-web/tree/dbdocs

[rlskoeser]

Documenting @kmcelwee comment from Slack:

I mean ideally the commands python manage.py dbml > cdh-web.dbml dbdocs build cdh-web.dbml would be enough, but unfortunately, I needed to go in by hand to correct some syntax errors that wouldn’t be easy to code around.

When I ran this locally, I found two types of problems:

• nested quotes inside help text that needs to be escaped
• repeated references (e.g. for auth group/user, user permission; the references show up twice)

Fixing the help text escaping looks pretty simple. Not sure what's causing the repeat references yet.

I'd prefer to generate dbml from Django models than from sql, because we have more information in Django. In particular, I'm interested in adding table groups based on apps at some point (not immediate).

[rlskoeser]

@kmcelwee I forked django_dbml, fixed the parse errors, and added a small improvement — model docstrings are now set as table notes (I also opened a PR with the changes). If you install the develop branch of this repo, you should be able to generate dbml that will parse. https://github.com/Princeton-CDH/django-dbml

And we can give the dbdocs command a project parameter so it doesn't need input: dbdocs build cdhweb.dbml --project=cdhweb

I think that even though the full dbdiagram generated by dbdocs isn't useful right now, being able to browse the tables and see the fields and diagrams of related tables is useful enough I'd like to pursue this.

Can we set up a github action to generate dbml, login to dbdocs, and then build dbdocs? The readme can link to the documentation (and maybe/probably it should be linked from our sphinx docs also). It should probably only build on commits to main, once we get it working properly. We should use the cdh github account for the dbdocs account/token so it isn't tied to one of our individual accounts.

[rlskoeser]

PR has been merged! So we can just install/require django_dbml>=0.4.0

[rlskoeser]

Looks good.