Visualizing PostgreSQL Databases
Customized entity-relationship diagrams
using an intermediate JSON-table-schema representation

ibu radempa

Python user group Vienna
2015-09-27

DEMO

Create some tables using psql and visualize them

Existing tools
Most useful tools are (semi-)proprietary or for proprietary OSs

I PostgreSQL GUI design tools
I PostgreSQL GUI tools
Examples of free tools

I PostgreSQL autodoc: stable perl tool since 2002 (commits)
I schemaspy: displays a single table with its relations (sample)
I Autodoc-Generator: generate HTML (rather inactive)
Diagramming software

I dia
I yEd

erwizexample: input format (excerpt):[Book] {color:orange}*Book ID
Subtitle
AuthorPublisher
Language
Summary
Item ID*# Relationships[Item] *--* [Source][Item] 1--1 [Location][Item] 1--* [Lending][Item] *--* [Attachment][Event] *--1 [Item] <contains->[Book] ---- [Item] <is a->

Existing tools - missing features

Combination of features I was missing

I visualize information in table/column comments,
in particular 'soft' foreign key relations

I let (foreign key relation) links end at the respective columns
I display foreign key relations for multiple columns
I support PostgreSQL-specic features (e.g., data types); noORM

I visual grouping of tables (color, position)
I individual styling of relations
I free software license
I easily hackable, ideally written in python

Tooling modularity

pg_jts

jts_erdPostgreSQL −−−−→ JSON −−−−→ SVG

I pg_jts produces a generalized JSON table schema
data structure from a live PostgreSQL database

I jts_erd visualizes a database described by a generalized JSONtable schema using PyGraphviz
Advantages of using JSON table schema as intermediate format

I jts_render can be used with other adapters (e.g., MySQL)I JSON description of database can be used for other purposes

JSON table schema (JTS)

I simple DDL for describing a single table using JSON notation
I not just for SQL tables, but tabular data in general
I readable and well-structured
I can be extended in many ways
I current version: 1.0-pre9
I maybe an upcoming standard for table description
I part of dataprotocols suite, okfn.orgI has python package json-table-schema
I needs to be extended in case of many coherent tables

pg_jts

I version 0.0.1
I uses PostgreSQL system catalog (pg_% tables)
I needs a live database connection (read only)
I exatracts various information from a single database
I →source

Generalized JTS, jts-pg

JSON object for a single PostgreSQL database

I namespaces
Itables (extension of JTS)
II

columns
foreign keys

Produces a jts-pg object as output
Dierences from current draft

I use 'column' instead of 'field' (cf. issue 134)

jts_erd

I version 0.0.1
I takes a jts-pg object as input
I uses PyGraphviz to render tables a nodes (of shape record)and (foreign key) relations as edges

I multicolumn relations are rendered with one or two
intermediate nodes (of shape point)

I takes various options (f.i., omitting column information)
I →sourceMissing features

I no manual (table) node positioning, no node pinning
I no memory of node positions
I views not displayed
I table inheritanceI sequences, triggers, functions not displayed
I (polishing)Roadmap

I put source on github
I add unit tests for pg_jts; support other versions ofPostgreSQL, Python3

I add diagram title, created with ... footer, table status('deprecated')

I add Javascript functionality for highlighting
links/columns/tables: click on a column highlights it andshows details, also for edges (foreign keys)

I add options for showing tables statistics (number of rows,max/min value for seleted elds (e.g. update_time), ...)

I add input check for jts_erd using voluptuous
I improve pg_jts (e.g., table inheritance/EERD)
I add Javascript functionality for positioning (table) nodes
I add SQL live editing functionality (with JSON RPC server)Outlook

Somewhen there might be

I converters from other RDBMSs to JTS
I a di tool for JSON table schemas; schema versioning

Thanks.

Questions?