nktks/spanner-er

generate er diagram from spanner schemas

spanner-er

spanner-er is a command-line tool to generate ER diagram from DDL schama file.

Install

Install to host

spanner-er depends on graphviz. Install graphviz on your host platform.

brew install graphviz

or

apk add --no-cache graphviz ttf-freefont

or

apt-get update && apt-get install graphviz

Install spanner-er

go get -u github.com/nktks/spanner-er

Use docker

you can use docker image

docker run --rm -v `pwd`:/go/src/github.com/nktks/spanner-er --workdir="/go/src/github.com/nktks/spanner-er" ghcr.io/nktks/spanner-er -h

or

git clone [email protected]:nktks/spanner-er.git
cd ./spanner-er
./scripts/spanner-er-docker.sh -h

In this case, you should use relative path for -s, -o option.

Usage

spanner-er -h
Usage:
  -T string
    	output file type. default is png(pass to dot option -T) (default "png")
  -h	print help
  -o string
    	output file name.default is spanner_er.<type>(pass to dot option -o)
  -s string
    	spanner schema file

Sample image

spanner-er's Issues

JSON fields are not supported

Error:
2022/12/29 22:13:10 :10: got "JSON", want scalar type
JSON is a normal type and should be supported, at least as a black box

ALTER TABLE ADD CONSTRAINT is ignored

At the export of schema from Spanner web console some FK could be exported by a separate ALTER TABLE instructions like

ALTER TABLE ASSESSMENT ADD CONSTRAINT FK_ASSESSMENT_ACTIVITY FOREIGN KEY(CARE_ACTIVITY_ID) REFERENCES CARE_ACTIVITY(CARE_ACTIVITY_ID);

Such FK-constraints are completely skipped. It is not comfortable. I have to review and manually edit the DDL script to fix it. It could be dozens of such constraints in DDL export.

Diagramm is not created when FK has no name

The constraint name of FK is optional. This example is fully correct. When you doing schema export in Spanner web console with the "Show Equivalent DDL" button you can easily get something like this.

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);

But the diagram will be not created for it

migrate docker hub to Container registry

• I configured automated build in docker hub to nakatamixi/spanner-er
• But this config already disabled.
• Instead of this, I'll migrate to Github Container registry

When a table has several FK all constraints to a first table are skipped

When we have DDL like this

CREATE TABLE PATIENT (
  MASTER_PATIENT_IDENTIFIER STRING(MAX) NOT NULL,
  DATE_OF_BIRTH TIMESTAMP,
  FIRST_NAME STRING(MAX),
  LAST_NAME STRING(MAX),
  MIDDLE_NAME STRING(MAX),
  GENDER STRING(MAX),
) PRIMARY KEY(MASTER_PATIENT_IDENTIFIER);

CREATE TABLE FACILITY (
  FACILITY_NUMBER STRING(MAX) NOT NULL,
  FACILITY_NAME STRING(MAX),
  PRIMARY_PHONE STRING(MAX),
) PRIMARY KEY(FACILITY_NUMBER);

CREATE TABLE CARE_ACTIVITY (
  PATIENT_CARE_ACTIVITY_ID STRING(MAX) NOT NULL,
  MASTER_PATIENT_IDENTIFIER STRING(MAX) NOT NULL,
  FACILITY_NUMBER_CREATED_AT STRING(MAX),
  FACILITY_NUMBER_COMPLETED_AT STRING(MAX),
  CONSTRAINT FK_1 FOREIGN KEY(MASTER_PATIENT_IDENTIFIER) REFERENCES PATIENT(MASTER_PATIENT_IDENTIFIER),
  CONSTRAINT FK_2 FOREIGN KEY(FACILITY_NUMBER_CREATED_AT) REFERENCES FACILITY(FACILITY_NUMBER),
) PRIMARY KEY(MASTER_PATIENT_IDENTIFIER, PATIENT_CARE_ACTIVITY_ID);

the constraint FK_1 will be skipped.
If you will switch positions of the TABLE PATIENT and TABLE FACILITY in this DDL the FK_2 will be skipped.