SchemaSpy

Quickly run SchemaSpy on a Postgres, MySQL, or SQLite3 database in order to generate a browsable visualization of the tables, columns, and relationships.

Based on openjdk:jre-alpine the resulting image generates the database documentation using SchemaSpy and serves the resulting html using Caddy, and is compatible with OpenShift.

The open source SchemaSpy project is located here: https://github.com/schemaspy/schemaspy

The open source Caddy project is located here; https://github.com/mholt/caddy

Configuration

Configuration is performed using environment varables.

Configuration - Backward Compatibility

The following environment variables are provided for drop-in backward compatibility with the previous SchemaSpy container implementation that only supported PostgreSQl databases (https://github.com/bcgov/SchemaSpy).

Running in OpenShift

The Dockerfile was designed to generate an image that can be used in OpenShift.

As a quick-start (example), the following command will create a BuildConfig, DeploymentConfig, and ancillary resources (service, etc.) in your current OpenShift project.

oc new-app https://github.com/bcgov/SchemaSpy -e DATABASE_TYPE=pgsql -e DATABASE_NAME=default -e DATABASE_HOST=postgresql -e DATABASE_USER=django -e DB_PASSWORD=xyz1234 

For more a more structured build and deployment environment, OpenShift templates can be found in the OpenShift templates folder.

Running in Docker

Build Command

docker build -t schemaspy https://github.com/bcgov/SchemaSpy

Sample MySQL Usage

docker run -ti --rm --name schemaspy \
    -p 8080:8080 \
    -e DATABASE_TYPE=mysql \
    -e DATABASE_HOST=mysql -e DATABASE_NAME=mydatabase \
    -e DATABASE_USER=root -e DATABASE_PASSWORD=mysecretpassword \
    --link mysql \
    schemaspy

Sample Postgres Usage

docker run -ti --rm --name schemaspy \
    -p 8080:8080 \
    -e DATABASE_TYPE=pgsql \
    -e DATABASE_HOST=postgres -e DATABASE_NAME=mydatabase \
    -e DATABASE_USER=postgres -e DATABASE_PASSWORD=mysecretpassword \
    --link postgres \
    schemaspy

Sample SQLite3 Usage

mkdir data && cp mydatabase.sqlite3 data/
docker run -ti --rm --name schemaspy \
    -p 8080:8080 \
    -v "$PWD/data":/app/data \
    -e DATABASE_TYPE=sqlite \
    -e DATABASE_NAME=/app/data/mydatabase.sqlite3 \
    schemaspy

Use on other databases

Oracle

Due to licensing limitations, the JDBC drivers for Oracle are not included in the repository.

Links to the drivers can be found here;

• https://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
• A link to the latest (ojdbc8.jar) drivers can be found here; https://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html

Using the Oracle Thin drivers it is easy to connect to an Oracle database.

The following configuraiton assumes you have downloaded the jar and copied it into the ./lib folder of your working copy and run the following commands to generate the base image followed by the image containing the Oracle JDBC drivers.

docker build -t 'schema-spy' .
docker build -f Dockerfile.oracle -t 'schema-spy-with-oracle-jdbc' .

You can then use the oc-push-image.sh from OpenShift Scripts to push the image to your OpenShift project.

For example:

oc-push-image.sh -i schema-spy-with-oracle-jdbc -n devex-von-bc-registries-agent-tools

Configuration:

The resulting SchemaSpy command looks something like this;

java -jar lib/schemaspy.jar -t "orathin" -db "CUAT" -dp "lib/ora-jdbc.jar" -s "COLIN_MGR_UAT" -cat "CUAT.bcgov" -u "username" -p "*****" -host "hostname:portnumber" -o /var/www/html

Oracle - Through PostgreSQL oracle-fdw

Work in progress ...

A custom database configuration, pgsql-oracle-fdw.properties, has been started for this purpose.

This allows SchemaSpy to query and graph the Foreign tables imported into PostgreSQL by oracle-fdw.

Current Limitations

The oracle-fdw import process does not import any of the table constraints/relationships for any of the imported tables: since it wouldn't make sense to have constraints on the foreign tables, because PostgreSQL cannot guarantee that these constraints would be satisfied.

Therefore, SchemaSpy will see the tables, but will be unable to wire up the relationships between the tables. The result is a set of orphaned tables.

The recommended approach to fix this issue is to define a set of foreign tables on Oracle's catalog tables. From there it should be possible to write a custom selectCheckConstraintsSql query for the pgsql-oracle-fdw.properties file that can provide SchemaSpy with the information it requires to wire the relationships between the tables.

This requires updates to the following projects;

• openshift-postgresql-oracle_fdw

  • Update to import the Oracle catalog tables.

• SchemaSpy - this project

  • Write and test a custom selectCheckConstraintsSql query for the pgsql-oracle-fdw.properties file.

Example Configuration:

Code of Conduct

Please refer to the Code of Conduct

Contributing

What to add support for additional database types, or add additional features?

For information on how to contribute, refer to Contributing

License

The source code contained in this repository is released under the Apache License, Version 2.0.