Database schema diagrams in 2022

Suggestions

• Whenever you publish a diagram, also include a comment explaining which software/website was used.

  • If you need to log into a website in order to update the diagram, post the login details… somewhere.

    • Ideally you would first create an account that is intended to be shared within you team.

• If the diagram was generated from source code, publish the source code (or a link to it) along with the diagram.

  • For bonus points, include this information in the image itself.

• These suggestions apply equally to email, chat, wiki, printouts, etc.

• Don’t send the source to clients, only to colleagues.

• Read The C4 model for visualising software architecture for a good system and suggestions on diagrams (then look at using plantUML with the C4 extentions).

  • This is a good talk on the topic:

---

Types of diagram

• Database schema diagram

• Flowchart

• Sequence diagram

• Component/Class diagram

• Code workflow

• …

---

Desktop software

Flowcharts

• VIsual-only

  • Dia - beware, it's Dia by name and dire by nature

  • Omnigraffle - Mac only

• Source + Visual

  • VS Code - has nice plantuml  integration - live preview

  • PlantUML (example)

    • Summary: Full featured language designed for visualising software architecture, build multiple diagrams from a single model, etc.

    • See also Structurizr

  • GraphViz

    • Summary: Simpler, general purpose language for diagrams (dot language)

Database

• …

---

Online software

Flowcharts

• ASCIIFlow

• Gliffy.com (now paid)

• moqups (has a free plan)

• PlantUML

  • PlantUML Webserver

  • Online Plant UML Editor

• Graphviz

  • Graphviz online

  • Edotor

  • Graphviz Visual Editor

Database

• diagrams.net (formerly draw.io)
• Quite heavy
• Like an online omnigraffle
• Doesn't specialise in databases
• No import function for SQL

• dbdiagram.io

• Free for up to 10 diagrams :-|
• Fairly good
• Has multiple import/export
• Importing is quite easy (although doesn't support all syntax)
• Diagrams don't look quite as good as dbdesigner.net
• dbdesigner.net
• Free for 2 diagrams? :-/
• Slightly better looking diagrams
• But more buggy (Login & save before starting!)
• Importing is more painful than dbdiagram.io

Hardware

• Pencil and paper - consider this for a first draft (scan/photo if necessary)

---

Comments

There are a number of ways to evaluate these software packages:

• Flowchart vs Database

• Online vs Desktop

• Visual-only vs Generated from source

• Source language: General (e.g. Dot) vs Specific (e.g. PlantUML + C4)

 

Source-generated diagrams have many benefits:

Dot is the easiest to learn, and PlantUML has the most features.

• Reply
• Edit
• Delete

There are online visual editors for both PlantUML and Graphviz! Best of both worlds

• 
  
• 
  

More PlantUML resources:

• Welcome to The Hitchhiker’s Guide to PlantUML! — The Hitchhiker's Guide to PlantUML documentation

• PlantUML Language Reference Guide

 

Note: PlantUML uses Graphviz to draw some diagrams. 

(sorry, the formatting of this post is a complete mess)

How to manage schema migrations (track database changes)

A list. Alternatives to the popular/enterprise options:

• DBIx::Class::Migration - Perl. works but fiddly

• App::JESP - Perl. Simpler

• Flyway - popular

• Liquibase - popular

• MyBatis migrations - popular

• DBDeploy - popular

• Active Record Migrations - Ruby?

• Squitch - standalone

• Skeema - stands out

How to use DBIx::Class::Migration

There is a tutorial (note: links are broken), but this is a cheat sheet:

Use case A: Developing - initial setup

Step 0: Set up a database instance

Use dbdeployer, it's great.

Step 1: Generate the DBIx::Class Result classes

• dbicdump
• dbicdump -o dump_directory=lib -o components='["InflateColumn::DateTime"]' Smurf::Foo::DB 'dbi:mysql:database=foo;host=127.0.0.1;port=8022;user=msandbox;password=msandbox'
• ...this will create:
• lib/Smurf/Foo/DB.pm
• Manually add: our $VERSION = 1; to this at the end.
• lib/Smurf/Foo/DB/Result/Table1.pm, etc.

Step 2: Generate the DBIx::Class::Migration files

• dbic-migration prepare
• dbic-migration -I lib prepare --schema_class Smurf::Foo::DB --target_dir=share --dsn='dbi:mysql:database=foo;...etc'
• ...this will create:
• share/migrations/_source/deploy/1/001-auto.yml
• share/migrations/_source/deploy/1/001-auto-__VERSION.yml
• share/migrations/MySQL/deploy/1/001-auto-__VERSION.sql
• share/migrations/MySQL/deploy/1/001-auto.sql
• share/fixtures/1/conf/all_tables.json

Notes and gotchas

• If you see a definition for dbix_class_deploymenthandler_versions anywhere, i.e. in lib/Smurf/Foo/DB/Result/DbixClassDeploymenthandlerVersion.pm then you're gonna have a bad time. This could mean you accidentally ran dbicdump after dbic-migration prepare. It will cause migrations to fail when they try to create the dbix_class_deploymenthandler_versions table from both 001-auto-__VERSION.sql and 001-auto.sql

Use case B: Making changes to the database

Step 3: Make changes and update files

• Do not write any SQL
• Make changes to the DBIC classes under `lib/Smurf/Foo/DB/Result`, but DO NOT MODIFY THE FIRST PART OF THE FILE (see DBIx::Class::Schema::Loader comments in those files)
• Bump the version in `lib/Smurf/Foo/DB.pm` (or add it - at the bottom outside the auto-generated section: `our $VERSION = 2;`
• Run `dbic-migration prepare --schema_class Smurf::Foo::DB --target_dir=share --dsn='dbi:mysql:database=foo;...etc'` to create a new migration in the `share` directory
• Run `dbicdump -o dump_directory=lib -o components='["InflateColumn::DateTime"]' Smurf::Foo::DB 'dbi:mysql:database=foo;...etc'` to see if it makes any updates to the first part of the DBIC classes under `lib/Smurf/Foo/DB/Result`

Use case C: Not making changes to the database

Step 4: Install and upgrade as needed

• Run dbic-migration install --schema_class Smurf::Foo::DB --target_dir=share --dsn='dbi:mysql:database=foo;...etc'
• Run dbic-migration upgrade ...etc

Hack your own MySQL to log in as root

$> sudo service mysql stop

$> mysqld --skip-grant-tables

$> mysql

mysql> FLUSH PRIVILEGES;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

mysql> SELECT host, user, plugin FROM mysql.user;

(source)

How to install DBD::Pg Perl module on Mac OSX

Make sure `pg_config` is in your path, and that's it! DBD::Pg will build normally.

How to use dbdeployer to easily deploy multiple versions of MySQL for testing

Download dbdeployer, it is awesome.

Caveat: For best results, you should have a recent version of your operating system installed.

dbdeployer cheat sheet

export PATH=/path/to/dbdeployer:$PATH

dbdeployer downloads tree --flavour mysql --OS linux

dbdeployer downloads get-unpack [filename here]

dbdeployer deploy single 8.0.22

dbdeployer sandboxes

dbdeployer use msb_8_0_22

dbdeployer sandboxes --full-info

Note: Your databases will be installed into e.g. ~/opt/mysql

And a special set of controls will be put in ~/sandboxes

Working with dbdeployer

~/sandboxes/msb_8_0_22/my sql -h 127.0.0.1 --port 8022 ...etc

~/sandboxes/msb_8_0_22/metadata socket

Compiling DBD::mysql for Perl on Mac OSX

I will tell you how:

Idea: Automated testing of database schema design

When writing or maintaining a MySQL database schema, there are a number quality checks that could conceivably be automated:

• Run the test suite and print out all the SQL statements it runs (e.g. for Perl set DBIC_TRACE=1). Now check that every column mentioned in a `where` clause has an index on it.
• Indexes are defined by: `KEY` or `UNIQUE` next to them
• Indexes are probably most important on large data tables, so identify those manually.

Perl Postgres test fixtures

Test::PostgreSQL approach:

Test::DBIx::Class approach:

MySQL basics - creating a user, etc.

Creating a user:

CREATE USER 'user'@'hostname';

ALTER USER 'user'@'hostname' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON dbName.* To 'user'@'localhost' IDENTIFIED BY 'password';

(source)

Install postgres on Ubuntu 16

How to do it:

• sudo apt-get install postgresql postgresql-contrib
• sudo -i -u postgres
• psql

(source)