Generate PlantUML ER diagram textual description from PostgreSQL tables

Overview

planter

test GitHub license Go Report Card

Generate PlantUML ER diagram textual description from PostgreSQL tables

Why created

A team with only software engineers doesn't need ER diagram that much as long as they have decent experience in Relational Database modeling. However, it becomes very helpful to have always-up-to-date ER diagram when marketing/promotion/operation teams consisting of those who are fluent in writing/reading SQL, join to the game.

PlantUML supports ER diagram in the latest version with this awesome pull request. The tool, planter, generates textual description of PlantUML ER diagram from pre-existing PostgreSQL tables, and makes it easy to share visual structure of relations with other teams.

Installation

go get -u github.com/achiku/planter

Quick Start

$ planter postgres://[email protected]/planter?sslmode=disable -o example.uml
$ java -jar plantuml.jar -verbose example.uml

er diagram

Specify table names

planter postgres://[email protected]/planter?sslmode=disable \ 
    -t order_detail \
    -t sku \
    -t product

Help

$ planter --help
usage: planter [] 

Flags:
      --help             Show context-sensitive help (also try --help-long and --help-man).
  -s, --schema="public"  PostgreSQL schema name
  -o, --output=OUTPUT    output file path
  -t, --table=TABLE ...  target tables (can handle regular expressions)
  -x, --xtable=TABLE ... exclude target tables (can handle regular expressions

Args:
    PostgreSQL connection string in URL format

Test

setup database.

create database planter;
create user planter;

run go test ./... -v

Comments
  • Add a dockerfile

    Add a dockerfile

    This is useful for build scripts to generate a UML diagram but a local go lang environment is not desirable.

    Usage is like

    docker run --rm planter postgres://user:[email protected]_host:dbname\?sslmode=disable
    

    Txh for the code!

    opened by weltonrodrigo 3
  • All relations are duplicated

    All relations are duplicated

    Hi, Thank you so very much for this very useful program!

    Everything works well with one exception: all relations are duplicated and I cannot figure why. Here is a simple example from a much larger PostgreSQL database:

    • I have a table TAG, with a PK tag_key
    • I have a table CTG (= categories), with a PK ctg_key
    • There is a relationship of many to 0/1 between TAG and CTG, defined using a foreign key tag_fk: each tag belongs or not to a category
    • both table belong to a schema kuq

    Here is the TAG DDL for TAG:

    
    CREATE TABLE kuq.tag (
    	tag_key int4 NOT NULL,
    	ctg_key int4 NULL,
    	tag varchar(60) NULL,
    	CONSTRAINT tag_pk PRIMARY KEY (tag_key),
    	CONSTRAINT tag_un UNIQUE (tag_key)
    );
    CREATE INDEX tag_tag_idx ON kuq.tag USING btree (tag);
    
    ALTER TABLE kuq.tag ADD CONSTRAINT tag_fk FOREIGN KEY (ctg_key) REFERENCES kuq.ctg(ctg_key);
    
    

    When I execute:

    planter postgres://<connection>?sslmode=disable \
    -s kuq    \
    -t tag    \
    -t ctg    \
    -o test.uml
    
    

    I get this PlantUML schema where the many to one link between TAG and CTG is duplicated:

    image

    And, in effect, the link is duplicated in the UML definition file:

    @startuml
    
    entity "ctg" {
      Tag Categories
      ..
      + ctg_key [PK]
      --
      ctg_key
      nam
      cmt
    }
    
    entity "tag" {
      Tags
      ..
      + tag_key [PK]
      --
      tag_key
      ctg_key
      tag
    }
    
    tag }-- ctg
    tag }-- ctg
    
    @enduml
    
    

    What am I doing wrong? Best, Stephen

    opened by asitemade4u 3
  • Add table exclude option

    Add table exclude option

    This pull request add a -x option that selects all tables not on the list. It uses the same logic passing a flag to FilterTables to determine the type of match.

    opened by apple314159 3
  • Minor aesthetic improvements for use in documentation

    Minor aesthetic improvements for use in documentation

    • Add --title,-T option to add a diagram title
    • Use Creole formatting to:
      • Make entity names bold
      • Make column names monospace
      • Make column descriptions italic
    • Update test.sh to be more portable
      • Locate bash with /usr/bin/env
      • Specify server location with PGHOST fallback to localhost
      • Updated example with new output format
    opened by mmauger 2
  • Docker support

    Docker support

    Hi, thanks for the great tool. I made a docker file which packages planter and plantuml in one docker image: https://github.com/kimmobrunfeldt/planter-docker. This makes using the tool a one-liner assuming docker is installed.

    It exposes planter and plantuml directly, but also implements er command which allows selecting tables with a matching pattern. Useful for databases with tens or hundreds of tables.

    opened by kimmobrunfeldt 2
  • Not working column | ad.adsrc does not exist

    Not working column | ad.adsrc does not exist

    exception planter postgres://user:[email protected]/adatabase?sslmode=disable 2019/11/30 16:16:48 failed to get columns of chat: failed to load table def: pq: column ad.adsrc does not exist

    environment I am running PostgreSQL inside docker from the official docker image version 12.1-alpine I have discovered it local and tried it inside the container so docker is probably not the issue.

    Database structure It also happens in the example from the repo.

    opened by micmine 2
  • Fix CI

    Fix CI

    It seems that CI has been failed for long time. This PR will fix it.

    This PR includes following changes.

    • Remove gom install to fix CI
      • planter no longer uses gom.
    • Run test with Go version 1.14, 1.15 and 1.16 in CI.
      • According to Go Release policy, each major Go release is supported until there are two newer major releases. Now that 1.16 has been released, 1.16 and 1.15 are supported. However, I'm sure there are a lot of 1.14 users. So I added 1.14, 1.15 and 1.16 (and tip).
    opened by hiroakis 1
  • Remove Gomfile

    Remove Gomfile

    https://github.com/mattn/gom is a super awesome tool. However, today Go has Go's module system itself go mod. This pull request will remove Gomfile with respect.

    opened by hiroakis 1
  • plantuml to SQL

    plantuml to SQL

    Can we do revers feature? ;) PlantUml->PostgreSQL (like this one for MySQL https://github.com/grafov/plantuml2mysql) Would be awesome complete tool

    opened by paunin 1
  • Replace Travis CI with GitHub Actions

    Replace Travis CI with GitHub Actions

    I'd like to add test.yml to enable GitHub Actions for CI.

    Look at https://github.com/hiroakis/planter/actions/runs/620985013. It shows the result of GitHub Actions CI on forked repository.

    opened by hiroakis 0
  • Show `serial` instead of `int` if the column is associated with the sequence

    Show `serial` instead of `int` if the column is associated with the sequence

    planter generates a UML like following.

    @startuml
    hide circle
    skinparam linetype ortho
    
    entity "bar" {
      + foo_id:bigint [PK][FK]
      --
      *bar_id:bigint
    }
    
    entity "foo" {
      + id:bigint [PK]
      --
      *name:text
      *created_at:timestamp with time zone
    }
    
     bar ||-|| foo
    @enduml
    

    This pull request will show serial type if the column is associated with the sequence. Look at foo.id column.

    @startuml
    hide circle
    skinparam linetype ortho
    
    entity "bar" {
      + foo_id:bigint [PK][FK]
      --
      *bar_id:bigint
    }
    
    entity "foo" {
      + id:bigserial [PK]
      --
      *name:text
      *created_at:timestamp with time zone
    }
    
     bar ||-|| foo
    @enduml
    

    Table definition

    create table foo (id bigserial primary key, name text not null, created_at timestamp with time zone not null);
    create table bar (foo_id bigint primary key, bar_id bigint not null, foreign key(foo_id) references foo (id));
    
       Column   |           Type           | Collation | Nullable |             Default
    ------------+--------------------------+-----------+----------+---------------------------------
     id         | bigint                   |           | not null | nextval('foo_id_seq'::regclass)
     name       | text                     |           | not null |
     created_at | timestamp with time zone |           | not null |
    Indexes:
        "foo_pkey" PRIMARY KEY, btree (id)
    Referenced by:
        TABLE "bar" CONSTRAINT "bar_foo_id_fkey" FOREIGN KEY (foo_id) REFERENCES foo(id)
    
     Column |  Type  | Collation | Nullable | Default
    --------+--------+-----------+----------+---------
     foo_id | bigint |           | not null |
     bar_id | bigint |           | not null |
    Indexes:
        "bar_pkey" PRIMARY KEY, btree (foo_id)
    Foreign-key constraints:
        "bar_foo_id_fkey" FOREIGN KEY (foo_id) REFERENCES foo(id)
    
    opened by hiroakis 0
  • Can not install solution

    Can not install solution

    Hey!

    I try to install the solution by command, without success.

    go get -u github.com/achiku/planter
    # github.com/alecthomas/kingpin
    go-1/src/github.com/alecthomas/kingpin/values.go:142:25: undefined: str2duration.Str2Duration
    
    opened by don-flamingo 1
  • Support PostgreSQL 10+ partitioning

    Support PostgreSQL 10+ partitioning

    Include partitioned tables, but ignore their partitions. Without this change, it would fail on database schemas involving partitioning.

    Note the use of the row_to_json() function is to permit backward compatibility with PostgreSQL 9 and 10. The relispartition column does not exist until PostgreSQL 10 and the conparentid column does not exist until PostgreSQL 11. Directly referencing these columns, while simpler, would cause the query to fail in those earlier versions. While another approach would be to generate different SQL for different PostgreSQL versions, that seems like it would be more work. Note however that this use of JSON means it will no longer work on PostgreSQL 9.2 and earlier, since the necessary JSON support was introduced in 9.3. That's hopefully not a big issue, given 9.2 reached its end-of-support date almost 5 years ago.

    opened by skissane 0
  • Option to generate high-level diagram

    Option to generate high-level diagram

    For large schemas, it would be useful to generate the ERD without the list of columns to just show the relationships. Perhaps with an additional option to just show PKs and FKs Would this be considered?

    opened by dannief 0
  • Extracting cross-schema tables

    Extracting cross-schema tables

    Hi!

    Great project, I'm really liking it! I just used to to run it on our live system and it's absolutely fantastic, specially for onboarding new folks :)

    I just just wanted to flag with this ticket that I wasn't able to extract diagrams for tables that reference tables in other schemas. Not sure if the is a way or not? Nonetheless, still enjoying it a lot!

    Best, Javier

    opened by Manzanit0 2
  • Would like to see more info about columns

    Would like to see more info about columns

    Would like to see more info about columns

    • Type
    • If field[s] is indexed
    • Comment

    My personal rules:

    • column definition: {name} {SQL type} [-- {comment}]
    • Primary keys should be in the header of each table
    • Foreign keys should be marked with + (will be displayed as green dot) with reference in comment
    • Indexed fields should be marked as bold
    • Comments starts with --
    • Comments for table should be put in header of each table
    • Relationships should be placed
    opened by paunin 4
Owner
Akira Chiku
๐Ÿถ ๐Ÿฃ
Akira Chiku
API for generate sequence diagram

Gen-sequence-diagram API for generate sequence diagram Requirements Golang 1.17+ Endpoint Method Router Request Body POST http://localhost:8080/ {"for

Eder Costa 0 Jan 4, 2022
Termbar - Generate terminal charts in Go

termbar Generate terminal bar charts in Go. a: โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡โ–‡ 50 b: โ–‡

MergeStat 14 Mar 2, 2022
PlantUML Class Diagram Generator for golang projects

GoPlantUML PlantUML Class Diagram Generator for golang projects. Generates class diagram text compatible with plantuml with the information of all str

Javier Feliu 1.2k Nov 18, 2022
Converts a trace of Datadog to a sequence diagram of PlantUML (Currently, supports only gRPC)

jigsaw Automatically generate a sequence diagram from JSON of Trace in Datadog. โš ๏ธ Only gRPC calls appear in the sequence diagram. Example w/ response

Yu SERIZAWA 6 Jul 12, 2022
Generate plantuml diagrams from go source files or directories

go-plantuml go-plantuml generates plantuml diagrams from go source files or directories. Installation go get -u github.com/bykof/go-plantuml Please co

Michael Bykovski 278 Nov 24, 2022
Generate plantuml diagrams from go source files or directories

go-plantuml go-plantuml generates plantuml diagrams from go source files or directories. Installation go get -u github.com/bykof/go-plantuml Please co

Michael Bykovski 278 Nov 24, 2022
Generate PlantUML diagrams from Chrome or Firefox network inspections

hoofli Generate PlantUML diagrams from Chrome or Firefox network inspections This tool reads browser HAR files stored on your local disk and transform

Pascal Dennerly 6 Nov 15, 2022
API for generate sequence diagram

Gen-sequence-diagram API for generate sequence diagram Requirements Golang 1.17+ Endpoint Method Router Request Body POST http://localhost:8080/ {"for

Eder Costa 0 Jan 4, 2022
CloudQuery extracts, transforms, and loads your cloud assets into normalized PostgreSQL tables.

The open-source cloud asset inventory backed by SQL. CloudQuery extracts, transforms, and loads your cloud assets into normalized PostgreSQL tables. C

CloudQuery 2.5k Nov 19, 2022
Generate CRUD gRPC backends from single YAML description.

Pike Generate CRUD gRPC backends from single YAML description. Check out Playground! Pike generates: gRPC Protobuf service description with basic Crea

null 267 Nov 18, 2022
k6 extension supporting avro textual and binary representations

xk6-avro This extension wraps the goavro library into a k6 extension. You can build the extension using: xk6 build --with github.com/xvzf/xk6-avro Exa

Matthias Riegler 0 Dec 8, 2021
Cheap/fast/simple XLSX file writer for textual data

xlsxwriter Cheap/fast/simple XLSX file writer for textual data -- no fancy formatting or graphs go get github.com/mzimmerman/xlsxwriter data := [][]s

Matthew Zimmerman 0 Feb 8, 2022
GOSNIFF - A Textual User-Interface Network Sniffer

GOSNIFF - A Textual User-Interface Network Sniffer gosniff-gif.mp4 GOSNIFF is a TUI-based, tcpdump-inspired tool used to provide some graphical insigh

null 347 Nov 23, 2022
Dj13SDDownloader - command line downloader sequence diagram

dj13SDDownloader command line downloader sequence diagram from https://sequence.davidje13.com/ Download Download Link curl https://github.com/xh-dev-g

null 0 Jan 2, 2022
CLI tool (hcron) and Go library (cron) to convert CRON expression into human readable description.

cron cron is a Go library that parses a cron expression and outputs a human readable description of the cron schedule. For example, given the expressi

Quy Le 74 Nov 12, 2022
A simple command line time description parser

Zeit Zeit is an extremely simple command line application to read a natural language time description and output it as a timestamp. The main usecase f

ElKowar 6 Aug 21, 2021
W3C WoT Thing Description Directory (TDD)

TinyIoT Thing Directory This is an implementation of the W3C WoT Thing Description Directory (TDD), a registry of Thing Descriptions. This project is

null 8 Jul 22, 2022
Mpq Decoder Golang TODO: MAke Description

MPQ MPQ archive codec Report Bug ยท Request Feature About This package provides a MPQ archive codec, for compressing and decompressing MoPaQ archives.

null 0 Nov 2, 2021
A Simple JSON Description of Telegraph API

telegraph-api-spec Introduction This golang program generates a JSON description of methods, types and docstrings of the Telegraph API. Features Easy

Anony 10 Nov 21, 2022
Cheat sheet for Go language with Syntax and their Description.

Go Lang Syntax Cheat Sheet for Go Lang Index General Syntax Formate For Contributers 1. Name of Syntax 2. Syntax with Explaination 3. (Extra Explaina

null 0 Jan 14, 2022