I am working on a new django project which will use a MySQL database. Obviously there are several tables and attributes items in those tables have. I realize I could just document those attributes in the code itself, but more than one codebase may be accessing this database. I would rather have a more comprehensive solution to document relationships, expected CASEing of the text, allowed characters, etc.
I know UML exists, but it seems there are 1,000+ tools which do UML modeling, not all of which will gracefully do an SQL database.
Examples of things I want to document:
- For a “user profile” there are various attributes: username (primary key), friendly name, etc
- For a “task” - id (primary key), name (letters numbers and spaces only, max 56 characters), owner (a single username (foreign key(), assignees (zero or more usernames (list of foreign keys)), etc
Here’s what I need:
- GUI for building flowchart/model/whatever you call it showing each table and each attribute in each table, with ability to add notes to table or attribute. Attributes must have ability to be relational just like in a database.
- FOSS only, must run on Linux. No “free” web-based garbage that will end up behind a paywall 5 years from now ie draw.io
- Must store source files for this model in a text/xml/json/something file which can easily be put into our git repo
- Must not be so tightly coupled to MySQL that is requires a database connection to work or couldn’t be used if we switch to a non-MySQL backent. If it has templates for and knowledge about MySQL databases that’s great but it shouldn’t require them to be useful.
What do you suggest for this?
I don’t have a specific recommendation (I could just give out names of tools that seem to match your needs but I can’t really say I’ve used any), but in your place I’d reconsider your last requirement of “no connection to the DB”.
In my view the best docs are the ones that take the least effort to keep up-to-date. Consider a tool that’s able to dynamically list and extract schemas from your existing tables and generate a nice HTML or diagram that can be used as a quick and up-to-date reference. That will require little or zero maintenance compared to a manual diagramming tool. So I’d start looking there.
Depending on the project and usage I’d also consider exposing the database through an API. This makes documentation much easier too, as there are tools to auto generate API docs and you’ll decouple your DB schema + access from its usage, which has its own benefits.
The ones I found that actually worked and allowed for documenting schema, as well as creating schema and live monitor/update database instances have been commercial.
- Navicat: https://navicat.com
- Jetbrains Datagrip: https://www.jetbrains.com/datagrip/
Here are some open-source ones that might get closer to what you want. You may not find a perfect solution: https://www.holistics.io/blog/open-source-data-modeling-tools/
Generally, I consider using your database schema as an API like this to be a bad architectural choice, especially if you are using an ORM like Django where you don’t have direct control over how the schema is structured.
If it was me, I’d prefer to design an interface to allow the other applications access to the data they need, so as to maintain the abstraction between the logical structure of your models and the messy reality of SQL. Build an actual REST API, define an Open API schema to document it.