Database documentation

[genedan]

I have been thinking about ways to document the FASLR backend database. Using Sphinx seemed simple enough, but it has its drawbacks, like having to rearrange the ER diagrams, manually fill out metadata tables, and lack of interactivity.

I came across this project which led me down the route of looking for third-party solutions:

https://dbdocs.io/jimbrigf0cdc6204e/Actuarial-Database

This was made using dbdocs and it looks pretty slick and you can drag and drop the tables in the ER diagram. The downside is there is no self-hosted solution, but I guess we can keep an eye out if one ever emerges, because I do like how it looks. @jimbrig, I would be curious to hear your thoughts on ease of use and how well it integrates with your project - or if you've encountered other similar tools that might be useful.

A self-hosted solution is desirable for me, since I would like to keep everything as open source as possible and under the same domain name, so I came across schemaspy I gave it a go and here's the resulting page for the db:

https://faslr.com/db/

The resulting diagrams aren't movable but you can visit relevant documentation by clicking on the tables, so it seems good enough for now. I'll have to figure out how to replace the dot notation name with just FASLR as well.

[jimbrig]

@genedan funny you mention it because I actually already implemented the entire PCDM schema with DBML back in March just out of curiosity and exploration.

Here's the relevant links:

• PCDM - DBDiagram - this will contain the DBML source code and diagram (note you can export to SQL also). I also sent you an invite to the project using your gmail email.

• PCDM - DBDocs - this is the actual published documentation site for the entire schema. I published this via CLI but looks like you can do it directly from dbdiagram now which is cool. (DBML Official Docs | CLI)

I agree with how you approached the documentation / observability side of the data model and from past experience with generating many data models and having to clearly document them for a variety of stakeholders (unfortunately all for clients so not shareable). I left some notes outlining my workflow at the bottom of this comment.

---

Notes:

• Regarding the fact that one cannot (yet) self-host dbdocs.io content - there are some repos out there that attempt to replicate the dbdocs interface using DBML related tooling:

  • IllusiveBagel/DBMLDocs generates a standalone self-served site for diaplaying database docs from DBML files
  • TruDan/dbdiagram-oss is an Open-Source Implementation of dbdiagram.io - see also: trudan.github.io/dbdiagram-oss/
  • softwaretechnik-berlin/dbml-renderer can render DBML to SVG or HTML.

• You can integrate a DBML and other diagrams (mermaid, platUML, etc.) with SchemaCrawler/Spy also. See The Official Documentation on Diagramming at the bottom of the page. Some other references:

  • The SchemaCrawler Diagramming Docs from AzureCloudMonk.
  • SchemaCrawler - Other Diagrams Example

---

• Some other useful tools related to this:
  • Vanderhoof/PyDBML (github.com): DBML parser and builder for Python.
  • jataware/dbml-builder builds SQLAlchemy models from DBML files.
  • butschster/dbml-parser (github.com) Parser for DBML syntax
  • aviallon/dbml2dot (github.com) converts DBML to GraphViz .dot format.
  • foliant-docs/foliantcontrib.dbmldoc another attempt at customizing docs output with DBML input (uses Jinja2 and PyDBML).
  • Kludex/dbml (github.com): Convert SQLAlchemy/SQLModel into DBML, and the other way around. Could be useful since you already have the specification implemented as SQLAlchemy models (could implement CI/CD, etc.)
  • Simlarly, here's a couple Django specific libraries, but also follow the general idea of Python class models <-> DBML:
    • hamedsj/DbmlForDjango (github.com): A converter between django models.py and dbml file.
    • makecodes/django-dbml (github.com): This app can generate a DBML output for all installed models.

---

My Data Model Documentation Workflow

I typically document the data model using

• a) DBML for a portable version of the database / model in a markup language that can be converted to/from various SQL dialects and also published to dbdocs.io or edited from dbdocs.io (with password protection if necessary). I usually automate this workflow via the npm packages for dbml and scripts to auto-sync the model across the various formats and output and necessary artifacts (SQL, DBML, Python, CSVs, Excel, GraphViz, Images, Mermaid, ERD, HTML Reports, etc.).

• b) SchemaSpy + SchemaCrawler documentation for a fully-featured HTML deployable website with an integration to include or embed the docs from DBML also. (see below, you can also simply embed as iframe into the HTML directly). Also use a custom schemacrawler.config.properties file to accelerate the implementation with CI/CD and docker.

• c) Excel based data model / data dictionary and model metadata (because that's what most business stakeholders prefer). For this I use some custom C#, VBA, and PowerQuery to use the Excel Data Model and automatically create a workbook with the same features as something like schemaspy. (probably disregard this aspect)

I tie it all together with Docker and a root docker-compose.yml to create/seed/test/migrate/deploy the database or data model, generate various database artifacts such as SQL, CSVs, etc., and finally, publish the docs created via DBML and SchemaSpy to a static site.

I can sift through my old work projects and find some examples if you think they could be useful.

---

Lastly, I have also implemented a preliminary openapi specification for the model as well using JSONSchema for the components and some demo endpoints to do various insurance related things, but its tough to work with being such a large model (the final compiled openapi specification was like 150,000 lines long in YAML).

Let me know if you would be interested in this also.

[jimbrig]

Also, I already have an example embedding of the dbdiagram in my online knowledge base here: DbDiagram Embed - PCDM - KaaS - Obsidian Publish.

PS: All the information above is from the same Obsidian Vault also.

[odddkidout]

This looks so beautiful and well documented for open-source contribution.

[jimbrig]

If I’ve learned anything being an engineer for ~8yrs it’s that docs (specifically technical) are one of the primary 🔑s to success, even if just for yourself, especially in the data engineering and API realm

[genedan]

Hey @jimbrig, thanks for the super-informative post, much of this information will be handy. I would like to link to your diagram via the PCDM repo to be the official documentation, instead of the stub docs site I created a while back. It really is what I imaged the documentation to be - interactive with detailed descriptions on what the tables and attributes are.

Is this DBML code for your PCDM diagram stored in a repository anywhere?

[jimbrig]

Sorry @genedan I somehow missed this in my notifications and am just now seeing it.. apologies. But yes, you can access the DBML for the data model using the PCDM - DBDiagram link from above (Left sidebar is the DBML code).

I've also attached the DBML and various SQL DDL dialect extracts here as well.

PCDM.DBML.zip.

---

I would add the PCDM.dbml file to the project root and you could use a NPM package.json with the dbdocs and @dbml/cli/@dbml/core dependencies and some scripts to automate things a little.

For example:

{
    "scripts": {
        "validate": "dbdocs validate PCDM.dbml",
        "login": "dbdocs login",
        "deploy": "dbdocs build PCDM.dbml",
        "render": "dbml-renderer PCDM.dbml -o PCDM.svg",
        "build:dbml": "sql2dbml --postgres PCDM.sql -o PCDM.dbml",
        "build:mssql": "dbml2sql PCDM.dbml --mssql -o SQL/PCDM.mssql.sql",
        "build:postgres": "dbml2sql PCDM.dbml --postgres -o SQL/PCDM.postgres.sql",
        "build:mysql": "dbml2sql PCDM.dbml --mysql -o SQL/PCDM.mysql.sql"
    },
    "dependencies": {
        "dbdocs": "latest",
        "@dbml/cli": "latest",
        "@dbml/core": "latest",
        "@softwaretechnik/dbml-renderer": "latest"
    }
}