feat: Automatic database schema documentation to GitHub Pages

mauritz-lovgren commented 5 months ago

Introduction:

We lack a way to automatically document our database(s) schemas, and having access to the documentation from a hosted site for easy browsing. Manually maintaining such a documentation using Confluence (or other location) is cumbersome and error-prone.

Purpose:

Documentation for the database design is important for understanding the underlying data model when implementing data-centric or data-dependent solutions. The documentation should be available without having to log in to a database.

Benefits:

Increase productivity for both frontend and backend developers by making database documentation quickly and easily available.

Research:

Looked in to several similar database documentation tools that surfaced through Google search or through references from other projects:

https://github.com/schemaspy/schemaspy (free open-source, mature and active project), support for all databases that have JDBC driver (includes no-SQL DBs like Cosmos DB)
https://dbschema.com (HTML export only available in PRO edition)

Highlighted Features:

Ability to export database documentation that can be used with GitHub Pages (HTML)

Cost Analysis:

Only development adaptation costs for community licensed edition.

Risks and Mitigations:

tool needs access to database to be able to extract metadata from it, ensure that access is granted through short-lived token (e.g. IAM auth mechanism with AWS).

Alternatives Considered:

https://dbeaver.com (seems difficult to automate through CI / CD)
exporting schema from local database manually and pushing to GitHub Pages as part of development process (not satisfactory and probably error-prone)

Dependencies:

Docker image for schema tool
GitHub Actions to use in workflow for schema export and deploy to GitHub pages
Need VPN access to AWS resources, same as with db migration test workflow.

Attachments:

https://medium.com/javarevisited/spring-schemaspy-db-docs-and-github-pages-a966caae347e

lena-osterhus commented 4 months ago

Looks good to me 👌 Would be nice to have this for MDS and Cosmos databases as well!

mauritz-lovgren commented 4 months ago

Looks good to me 👌 Would be nice to have this for MDS and Cosmos databases as well!

Most definitely! The workflow as implemented in ADAT-1480 (https://github.com/HMS-Kontoret/github-workflows/pull/89) should be possible to port to databases in Azure as well (would need some work on the authentication part, otherwise the basic principles should be the same).

HMS-Kontoret / .github