[Generating ER Diagrams directly from *.sql files without even installing mysql](https://ckhung.medium.com/drawing-er-diagram-from-sql-file-alone-e7bef8cfe34b) / [在命令列上從 *.sql 檔直接產生 ER Diagram](https://newtoypia.blogspot.com/2022/04/sql-er-diagram.html)

json2erd.py

#!/usr/bin/python3

# convert the output (as json) of
# https://github.com/iamcal/SQLParser
# to the input of
# https://github.com/BurntSushi/erd
# or to the input of
# https://github.com/ehne/ERDot
#
# For example, suppose you have ~/sakila-schema.sql from https://downloads.mysql.com/docs/sakila-db.tar.gz
# php pmysql.php ~/sakila-schema.sql | python3 ~/json2erd.py -t busu | erd -f svg -e spline > ~/sakila.busu.svg
# or
# php pmysql.php ~/sakila-schema.sql | python3 ~/json2erd.py -t ehne > ~/sakila.erd.json
# erdot ~/sakila.erd.json
# dot -Tsvg ~/sakila.dot > ~/sakila.ehne.svg

import argparse, fileinput, json
from warnings import warn

parser = argparse.ArgumentParser(
    description='SQLParser-generated .json to erd .er converter',
    formatter_class=argparse.ArgumentDefaultsHelpFormatter)
parser.add_argument('-t', '--type', type=str, default='busu',
    help='output type: busu or ehne')
parser.add_argument('jsonfile', nargs='*',
    help='input .json file generated by iamcal/SQLParser, or stdin if omitted')
args = parser.parse_args()

all_tables = json.loads(''.join(fileinput.input(args.jsonfile)))
all_relationships = []
for tn in all_tables:
    # convert the 'fields' array to the 'fields' dict,
    # and add a 'prefix' field to each item
    new_fields = {}
    for f in all_tables[tn]['fields']:
        new_fields[f['name']] = f
        f['prefix'] = ''
    all_tables[tn]['fields'] = new_fields
    for index in all_tables[tn]['indexes']:
        if index['type'] == 'PRIMARY':
            for field in index['cols']:
                all_tables[tn]['fields'][field['name']]['primary'] = True
        elif index['type'] == 'FOREIGN':
            for field in index['cols']:
                all_tables[tn]['fields'][field['name']]['foreign'] = True
    for field in all_tables[tn]['fields'].values():
        if 'primary' in field: field['prefix'] = '*'
        if 'foreign' in field: field['prefix'] += '+'

# print(json.dumps(all_tables, indent=4))

for tn in all_tables:
    for index in filter(lambda x : x['type'] == 'FOREIGN', all_tables[tn]['indexes']):
        rel = {
            't1':tn, 'f1':index['cols'][0]['name'], 'm1':'*',
            't2':index['ref_table'], 'f2':index['ref_cols'][0]['name']
        }
        rel['m2'] = '?' if any(all_tables[index['ref_table']]['fields'][f['name']]['null'] for f in index['ref_cols']) else '1'
        all_relationships.append(rel)

# print(json.dumps(all_relationships, indent=4))

if args.type == 'busu':
    print('entity {bgcolor: "#ececfc", size: "20"}\n')
    for (tn, tab) in all_tables.items():
        print('[', tn, ']')
        for field in tab['fields'].values():
            print(field['prefix'] + field['name'])
        print()
    for rel in all_relationships:
        print('{} {}--{} {}'.format(rel['t1'], rel['m1'], rel['m2'], rel['t2']))
elif args.type == 'ehne':
    out = {
        'tables': {
            tn: {
                field['prefix']+field['name']: field['type'] \
                    for field in all_tables[tn]['fields'].values()
            } for tn in all_tables
        },
        'relations':[
            '{}:{} {}--{} {}:{}'.format(rel['t1'], rel['f1'], rel['m1'], rel['m2'], rel['t2'], rel['f2']) \
                for rel in all_relationships
        ],
        'rankAdjustments':'',
        'label':''
    }
    print(json.dumps(out, indent=4))
else:
    warn('unknown output type "' + args.type + '"')

pmysql.php

<?php
	ini_set('display_errors', 1);
	error_reporting(E_ERROR | E_WARNING | E_PARSE);

	# in shell: composer require iamcal/sql-parser
        # see "Installation" section of https://github.com/iamcal/SQLParser
	require_once 'vendor/autoload.php';
	use iamcal\SQLParser;

	$N = $argc - 1;
	if ($N != 1) {
	    error_log("need exactly one argument (a .sql mysql ddl file) but $N was found");
	    exit(1);
	}

	$sql = file_get_contents($argv[1]);

	$obj = new SQLParser();

	#$tokens = $obj->lex($sql);
	#print_r($tokens);
	#exit;

	$obj->parse($sql);
	#echo json_encode($obj->tables['bounces']['fields'], JSON_PRETTY_PRINT)."\n";
	echo json_encode($obj->tables, JSON_PRETTY_PRINT)."\n";

sakila.busu.svg

sakila.ehne.svg

Presently SQLParser does not support the BOOLEAN type. To save you some troubles, I have created and attached php pmysql.php ~/sakila-schema.sql > sakila.SQLParse.json so that you can skip the first step and start from python3 ~/json2erd.py -t xxx sakila.SQLParse.json

SQLParser now supports BOOLEAN. The large intermediate json file is no longer needed and therefore removed.