SchemaSpyでER図を秒で作る！！(PostgreSQL Hands-on)

## はじめに
業務要件や設計変更に応じて設計書をメンテナンスするのは大変ですよね。特にER図はそもそも書きたくないし、メンテナンスなんてもっての他。でも、ER図があると開発者間でコミュニケーションも取りやすいし、保守する時に何かと便利。そこで、SchemaSpyで既存のTBLからER図を作成できるSchemaSpyを紹介します！！なかなか便利ですよ！！！下記のような事ができるようになります！！

https://twitter.com/kkfactory0705/status/1519228993136136193

## SchemaSpyとは
既存のTBLからER図をリバースエンジニアリングしてくれるツール。Java環境で稼働しHTMLファイルを簡単に作成できます。
![スクリーンショット 2022-04-27 16.55.04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/599049/b20b76b9-b084-6eea-aa08-9bee999dedad.png)

https://schemaspy.org/

### 開発環境
```
OS：Mac OS
SchemaSpy：6.1.0
Java:1.8.0_222
DB：PostgreSQL 
※Javaが1.8以上ならば動きます！！
```

### SchemaSpyを実行する
```yml:docker-compose.yml
version: '3.1'

services:
  postgres:
    container_name: postgres
    image: postgres:13
    ports:
      - 5433:5432
    volumes:
      - ./.data/postgres:/var/lib/postgresql/data
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
```

```sql:ER図を作成する基となるTBL
CREATE TABLE IF NOT EXISTS  users(
   "id" serial PRIMARY KEY,
   "name" VARCHAR (256) NOT NULL,
   "gender" VARCHAR (256) NOT NULL,
   "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS  companies(
   "id" serial PRIMARY KEY,
   "name" VARCHAR (256) NOT NULL,
   "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS  departments(
   "id" serial PRIMARY KEY,
   "name" VARCHAR (256) NOT NULL,
   "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS  companies_departments(
   "id" serial PRIMARY KEY,
   "company_id" int NOT NULL,
   "department_id" int NOT NULL,
   "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS  companies_users(
   "id" serial PRIMARY KEY,
   "company_id" int NOT NULL,
   "user_id" int NOT NULL,
   "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE "companies_departments" ADD FOREIGN KEY ("company_id") REFERENCES "companies" ("id") ON DELETE SET NULL ON UPDATE NO ACTION;
ALTER TABLE "companies_departments" ADD FOREIGN KEY ("department_id") REFERENCES "departments" ("id") ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE "companies_users" ADD FOREIGN KEY ("company_id") REFERENCES "companies" ("id") ON DELETE SET NULL ON UPDATE NO ACTION;
ALTER TABLE "companies_users" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE SET NULL ON UPDATE NO ACTION;

```

```shell: SchemaSpyを実行する
~/go/src/go_pg_lesson $ docker run -v "$PWD/schema:/output" --net="host" schemaspy/schemaspy:6.1.0 -t pgsql \\ -host localhost:5433 -db example -u postgres -p postgres -connprops useSSL\\\\=false -all
Running Main-Class org.springframework.boot.loader.JarLauncher
With drivers:jtds-1.3.1.jar, mariadb-java-client-1.1.10.jar
mysql-connector-java-6.0.6.jar, postgresql-42.1.1.jre7.jar
  ____       _                          ____
 / ___|  ___| |__   ___ _ __ ___   __ _/ ___| _ __  _   _
 \___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
  ___) | (__| | | |  __/ | | | | | (_| |___) | |_) | |_| |
 |____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
                                             |_|    |___/

                                              6.1.0

SchemaSpy generates an HTML representation of a database schema's relationships.
SchemaSpy comes with ABSOLUTELY NO WARRANTY.
SchemaSpy is free software and can be redistributed under the conditions of LGPL version 3 or later.
http://www.gnu.org/licenses/

INFO  - Starting Main v6.1.0 on docker-desktop with PID 1 (/schemaspy-6.1.0.jar started by java in /)
INFO  - The following profiles are active: default
INFO  - Started Main in 2.732 seconds (JVM running for 4.196)
INFO  - Analyzing schemas that match regular expression '.*'. (use -schemaSpec on command line or in .properties to exclude other schemas)
INFO  - Analyzing schemas: 
information_schema
pg_catalog
public
INFO  - Analyzing information_schema
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 13.6 (Debian 13.6-1.pgdg110+1)
INFO  - Gathering schema details
Gathering schema details...WARN  - Failed to retrieve stored procedure/function details using sql 'select r.routine_name, case when p.proisagg then 'AGGREGATE' else 'FUNCTION' end as routine_type, case when p.proretset then 'SETOF ' else '' end || case when r.data_type = 'USER-DEFINED' then r.type_udt_name else r.data_type end as dtd_identifier, r.external_language as routine_body, r.routine_definition, r.sql_data_access, r.security_type, r.is_deterministic, d.description as routine_comment from information_schema.routines r left join pg_namespace ns on r.routine_schema = ns.nspname left join pg_proc p on ns.oid = p.pronamespace and r.routine_name = p.proname left join pg_description d on d.objoid = p.oid where r.routine_schema = :schema'
ERROR: column p.proisagg does not exist
  Hint: Perhaps you meant to reference the column "p.prolang".
  Position: 34
(1sec)
Connecting relationships...WARN  - No tables or views were found in schema 'information_schema'.
ERROR - The schema exists in the database, but the user you specified 'postgres'might not have rights to read its contents.
ERROR - Another possibility is that the regular expression that you specified for what to include (via -i) didn't match any tables.
(0sec)
Writing/graphing summary.INFO  - Gathered schema details in 0 seconds
INFO  - Writing/graphing summary
INFO  - Graphviz rendered set to ''
........(6sec)
Writing/diagramming detailsINFO  - Completed summary in 6 seconds
INFO  - Writing/diagramming details
INFO  - No tables to output, nothing written to disk
(0sec)
Wrote relationship details of 0 tables/views to directory '/output' in 8 seconds.
View the results by opening /output/index.html
INFO  - Wrote table details in 0 seconds
INFO  - Wrote relationship details of 0 tables/views to directory '/output/information_schema' in 8 seconds.
INFO  - View the results by opening /output/information_schema/index.html
INFO  - Analyzing pg_catalog
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 13.6 (Debian 13.6-1.pgdg110+1)
INFO  - Gathering schema details
Gathering schema details...WARN  - Failed to retrieve stored procedure/function details using sql 'select r.routine_name, case when p.proisagg then 'AGGREGATE' else 'FUNCTION' end as routine_type, case when p.proretset then 'SETOF ' else '' end || case when r.data_type = 'USER-DEFINED' then r.type_udt_name else r.data_type end as dtd_identifier, r.external_language as routine_body, r.routine_definition, r.sql_data_access, r.security_type, r.is_deterministic, d.description as routine_comment from information_schema.routines r left join pg_namespace ns on r.routine_schema = ns.nspname left join pg_proc p on ns.oid = p.pronamespace and r.routine_name = p.proname left join pg_description d on d.objoid = p.oid where r.routine_schema = :schema'
ERROR: column p.proisagg does not exist
  Hint: Perhaps you meant to reference the column "p.prolang".
  Position: 34
(1sec)
Connecting relationships...WARN  - No tables or views were found in schema 'pg_catalog'.
ERROR - The schema exists in the database, but the user you specified 'postgres'might not have rights to read its contents.
ERROR - Another possibility is that the regular expression that you specified for what to include (via -i) didn't match any tables.
(0sec)
Writing/graphing summary.INFO  - Gathered schema details in 0 seconds
INFO  - Writing/graphing summary
INFO  - Graphviz rendered set to ''
........(5sec)
Writing/diagramming detailsINFO  - Completed summary in 5 seconds
INFO  - Writing/diagramming details
INFO  - No tables to output, nothing written to disk
(0sec)
Wrote relationship details of 0 tables/views to directory '/output' in 14 seconds.
View the results by opening /output/index.html
INFO  - Wrote table details in 0 seconds
INFO  - Wrote relationship details of 0 tables/views to directory '/output/pg_catalog' in 14 seconds.
INFO  - View the results by opening /output/pg_catalog/index.html
INFO  - Analyzing public
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 13.6 (Debian 13.6-1.pgdg110+1)
INFO  - Gathering schema details
Gathering schema details.........WARN  - Failed to retrieve stored procedure/function details using sql 'select r.routine_name, case when p.proisagg then 'AGGREGATE' else 'FUNCTION' end as routine_type, case when p.proretset then 'SETOF ' else '' end || case when r.data_type = 'USER-DEFINED' then r.type_udt_name else r.data_type end as dtd_identifier, r.external_language as routine_body, r.routine_definition, r.sql_data_access, r.security_type, r.is_deterministic, d.description as routine_comment from information_schema.routines r left join pg_namespace ns on r.routine_schema = ns.nspname left join pg_proc p on ns.oid = p.pronamespace and r.routine_name = p.proname left join pg_description d on d.objoid = p.oid where r.routine_schema = :schema'
ERROR: column p.proisagg does not exist
  Hint: Perhaps you meant to reference the column "p.prolang".
  Position: 34
(0sec)
Connecting relationships.........(0sec)
Writing/graphing summary.INFO  - Gathered schema details in 0 seconds
INFO  - Writing/graphing summary
INFO  - Graphviz rendered set to ''
........(5sec)
Writing/diagramming detailsINFO  - Completed summary in 5 seconds
INFO  - Writing/diagramming details
......(1sec)
Wrote relationship details of 6 tables/views to directory '/output' in 23 seconds.
View the results by opening /output/index.html
INFO  - Wrote table details in 1 seconds
INFO  - Wrote relationship details of 6 tables/views to directory '/output/public' in 23 seconds.
INFO  - View the results by opening /output/public/index.html
INFO  - StackTraces have been omitted, use `-debug` when executing SchemaSpy to see them
```

実行後は、schemaディレクトリ配下にファイルが作成されます。index.htmlでER図やTBL構成などの確認ができます。