Database schema visualization

Project goal

The aim of this project is to generate database documentation from sql schema.

Supported databases

• MySQL
• MariaDB
• Apache Cassandra (Basics)

Supported Output formats

• PNG, SVG image
• Plantuml raw text
• Json
• Markdown

Installation

$ composer require pongee/database-schema-visualization

Commands

Every command reads a schema file and writes the result to stdout, so redirect it into a file. Foreign keys are resolved automatically from the schema.

Each command exists for both MySQL (mysql:) and Apache Cassandra (cassandra:):

Command	Output
mysql:json / cassandra:json	JSON
mysql:plantuml / cassandra:plantuml	PlantUML raw text
mysql:markdown / cassandra:markdown	Markdown
mysql:image / cassandra:image	PNG / SVG image

Argument and options:

Name	Description
file	Path to the schema file. Required.
--type	Image format for the image commands: png (default) or svg.
--template	Twig template used for rendering. Defaults to the bundled template.

Usage

The command, argument and options are the same across every run mode below — see Commands for the full list.

Command line

$ php ./database-schema-visualization <command> [options] <file>> output

For example:

$ php ./database-schema-visualization mysql:image --type png ./example/schema/sakila.sql > ./example/output/sakila/sakila.png

Output:

Docker

The image is published to Docker Hub, built for both linux/amd64 and linux/arm64.

$ docker pull pongeepublic/database-schema-visualization:latest

Mount your schema into the container and pass the same command and options as on the CLI:

$ docker run --rm -v "$PWD/schema.sql:/app/schema.sql" \
pongeepublic/database-schema-visualization mysql:image --type png schema.sql > diagram.png

List the available commands:

$ docker run --rm pongeepublic/database-schema-visualization list

PHP

Png export

<?phpdeclare(strict_types=1);
usePongee\DatabaseSchemaVisualization\DataObject\Sql\Database\Connection\ConnectionCollection;
usePongee\DatabaseSchemaVisualization\Export\Plantuml;
usePongee\DatabaseSchemaVisualization\Generator\ImageGenerator;
usePongee\DatabaseSchemaVisualization\Generator\ImageType;
usePongee\DatabaseSchemaVisualization\Parser\MysqlParser;
include__DIR__ . '/../../vendor/autoload.php';
$sqlSchema = ' CREATE TABLE IF NOT EXISTS `foo` ( `id` INT(10) UNSIGNED NOT NULL COMMENT "The id" ) ENGINE=innodb DEFAULT CHARSET=utf8;';
$parser = newMysqlParser(); // or CassandraParser(), MariadbParser(); $plantumlExport = newPlantuml(file_get_contents(__DIR__ . '/../../src/Template/Plantuml/v1.twig'));
$forcedConnectionCollection = newConnectionCollection();
$imageGenerator = newImageGenerator(
ImageType::Png,
__DIR__ . '/../../bin/plantuml-mit-1.2026.6.jar'
);
$schema = $parser->run($sqlSchema, $forcedConnectionCollection);
print$imageGenerator->generate($plantumlExport->export($schema));

Json export

<?phpdeclare(strict_types=1);
usePongee\DatabaseSchemaVisualization\DataObject\Sql\Database\Connection\ConnectionCollection;
usePongee\DatabaseSchemaVisualization\Export\Json;
usePongee\DatabaseSchemaVisualization\Parser\MysqlParser;
include'./vendor/autoload.php';
$sqlSchema = ' CREATE TABLE IF NOT EXISTS `foo` ( `id` INT(10) UNSIGNED NOT NULL COMMENT "The id" ) ENGINE=innodb DEFAULT CHARSET=utf8;';
$parser = newMysqlParser();
$jsonExport = newJson();
$forcedConnectionCollection = newConnectionCollection();
$schema = $parser->run($sqlSchema, $forcedConnectionCollection);
print$jsonExport->export($schema);

This will generate:

{
"tables": {
"foo": {
"columns": [
{
"name": "id",
"type": "INT",
"typeParameters": [
"10"
],
"otherParameters": "UNSIGNED NOT NULL",
"comment": "The id"
}
],
"indexs": {
"simple": [],
"spatial": [],
"fulltext": [],
"unique": []
},
"primaryKey": []
}
},
"connections": []
}

Markdown export

<?phpdeclare(strict_types=1);
usePongee\DatabaseSchemaVisualization\DataObject\Sql\Database\Connection\ConnectionCollection;
usePongee\DatabaseSchemaVisualization\Export\Markdown;
usePongee\DatabaseSchemaVisualization\Parser\MysqlParser;
include'./vendor/autoload.php';
$sqlSchema = ' CREATE TABLE IF NOT EXISTS `foo` ( `id` INT(10) UNSIGNED NOT NULL COMMENT \'The id\' ) ENGINE=innodb DEFAULT CHARSET=utf8;';
$parser = newMysqlParser();
$markdownExport = newMarkdown(file_get_contents('./src/Template/Markdown/v1.twig'));
$forcedConnectionCollection = newConnectionCollection();
$schema = $parser->run($sqlSchema, $forcedConnectionCollection);
print$markdownExport->export($schema);

This will generate:

Schema

foo table

Columns

Name	Type	Parameters	Comment
id	INT ( 10 )	UNSIGNED NOT NULL	The id