GitHub - dwyl/dbschema-demo: 🍔 Showcase of a Postgres database schema generated by DbSchema via GitHub Actions

A quick demo showing how to use dbschema to explore the schema of a Postgres database
and generate an Entity Relationship Diagram (ERD) via GitHub Actions.

What? 💭
Why? 🤷‍♀️
Who? 👤
How? 🛠
- Local Schema Visualization
- Headless schema visualization
Star this repo ⭐️

What? 💭

Visualizing the schema of a database is essential for understanding its structure and relationships. An Entity-Relationship Diagram (ERD) is a graphical representation of the database schema that shows tables, columns, and relationships between them. ERDs help developers, database administrators, and stakeholders visualize the database design, identify potential issues, and optimize performance.

By using tools like DbSchema, we can generate ERDs automatically from your database, saving time and effort in documenting and analyzing the database schema, facilitating onboarding and better comprehension of the database structure amongst teams.

This tutorial focuses on visualizing the schema of a database.

See the many other features of DbSchema at: dbschema.com/features

Why? 🤷‍♀️

One of the many features DbSchema provides is the ability to generate HTML5 documentation for your database.

Exporting interactive HTML5 documentation of your database serves several critical purposes. HTML5 documentation is particularly beneficial because it offers a web-friendly format that can be easily shared and embedded on the web.

This is perfect for creating and maintaining up-to-date documentation, thus ensuring that your database’s design and architecture are transparent and accessible, facilitating better decision-making and collaboration. 🎉

Who? 👤

This tutorial is specifically designed for technical writers who are tasked with creating detailed and user-friendly documentation for database systems.

This guide will help you speedrun the process of exporting HTML5 documentation using DbSchema of an existing database, and making it accessible through GitHub Actions.

How? 🛠

️Before proceeding, ensure you have PostgreSQL installed in your machine.

Optionally install DBeaver, to help manage your databases and execute SQL queries easier.

Note

a DBeaver intro/tutorial is beyond the scope of this demo;
for that, YouTube is your friend: youtube.com/DBeaver+Tutorial

Local Schema Visualization

We'll first focus on running DbSchema and generating the HTML5 documentation of a sample database locally.

Afterwards, we'll focus on running DbSchema headless.

1. Download and install `DbSchema`

Let's start by downloading and installing DbSchema. Go to: dbschema.com/download and download and install DbSchema.

Note

Don't worry, you don't need a license to follow this tutorial. We will use the free version of DbSchema. To unlock pro features, you can always upgrade if you need to.

After that, open DbSchema. You should see a screen like this:

Congratulations, you have successfully installed DbSchema!

Note

At the time of writing, we're using DbSchema version 9.6.2.

2. Create database and create tables

Let's create a sample database and tables to visualize the schema.

Open your terminal and run the following commands:

psql -U postgres

You will now be in the psql shell. Create a new database with any name. For this tutorial, we will name it "dbschema_demo". Type this into the terminal.

CREATE DATABASE dbschema_demo;

Now, let's connect to the database.

\c dbschema_demo

Now, let's run a SQL script to create some tables to visualize them! DbSchema's free trial has a cap of 12 tables that we can generate an HTML5 page for. Therefore, use a script that creates 12 tables.

To facilitate this process, we have create one for you. Save the script 12_tables and run it!

\i /path/to/12_tables.sql

After this, all the tables should be created! You can now exist the psql interface by running:

\q

3. Use `DbSchema` to visualize the schema

Now that we have created the database and tables, let's visualize the schema using DbSchema.

Open DbSchema and click on the Connect to Database button.

Follow the wizard. Choose PostgreSQL as the database type and enter the connection details.

Click on Connect. You will be asked which schema you want to visualize. Choose the default public schema.

And you're in! You will see the tables in a diagram.

4. Exporting HTML5 documentation

To export an HTML5 page, go over to Model > Export As ... > Generate HTML5/PDF Documentation.

You can leave the settings on (or customize to your liking) and select a path to save the HTML page. Click on Generate.

And you're done! You have successfully exported the HTML5 documentation of your database! 🎉 You can open your HTML page in a browser and interact with the schema. You can see the details of the diagram, of the tables and columns, and the relationships between them.

Hurray! 🥳

Headless schema visualization

Now that we have seen how to visualize the schema of a database through DbSchema's GUI, let's see how we can do this headless.

This is particularly useful when you want to automate the process of generating HTML5 documentation of your database and make it accessible through Github Actions.

In this section, we're going to focus

1. Automating downloading `DbSchema`'s installer

To run DbSchema headlessly, we are going to need the installer. You can find the the installers for different OS in https://dbschema.com/download.html.

In our case, we are going to make use of Docker to run the installer and run it in a Linux-based system inside a container.

Let's first automate the downloading of the installer. We are going to create a Makefile to help us do this. Create a file called Makefile and use the following content:

# Define variables
DOCKERUSER = userino

MAJOR = 9
MINOR = 6
PATCH = 3

DBSCHEMA_TAR = dbschema_$(MAJOR)_$(MINOR)_$(PATCH).tar.gz
DBSCHEMA_URL = https://www.dbschema.com/download/DbSchema_unix_$(MAJOR)_$(MINOR)_$(PATCH).tar.gz

# Default target
all: download extract

# Download the DbSchema `tar.gz` file if it doesn't exist
download:
	if [ ! -f $(DBSCHEMA_TAR) ]; then \
		curl -L $(DBSCHEMA_URL) --output $(DBSCHEMA_TAR); \
	fi

# Extract the downloaded `tar.gz` file
extract: $(DBSCHEMA_TAR)
	tar xvf $(DBSCHEMA_TAR)

# Clean up downloaded files (optional)
clean:
	rm -f $(DBSCHEMA_TAR)
	rm -rf DbSchema

.PHONY: all download extract clean

This defines the following variables to be used:

DOCKERUSER = userino: Defines the Docker user.
MAJOR = 9, MINOR = 6, PATCH = 3: Defines version numbers for DbSchema.
DBSCHEMA_TAR = dbschema_$(MAJOR)_$(MINOR)_$(PATCH).tar.gz: Constructs the tar file name using the version numbers.
DBSCHEMA_URL = https://www.dbschema.com/download/DbSchema_unix_$(MAJOR)_$(MINOR)_$(PATCH).tar.gz: Constructs the download URL using the version numbers. It uses the download URL for Unix-based systems that was mentioned before.

We are also defining a default target all that runs download and extract. These two methods check if the tar file does not exist and downloads it using curl if necessary. Then, it extracts the tar file, respectively.

We've also defined a clean target that removes the downloaded files.

Here how the flow looks like when you run the command make.

When make is run, it triggers the all target.
The all target depends on download and extract.
The download target checks for the existence of the tar file and downloads it if it doesn't exist.
The extract target extracts the contents of the tar file.
Optionally, the clean target can be run to remove the downloaded and extracted files.

With this, we can always download and extract the latest version of DbSchema by just running make! 🎉

2. Creating the `monocle.jar` file

According to https://dbschema.com/documentation/dbschemacli.html#docker, we need to create a monocle.jar file and place it in the lib directory of DbSchema if we want to run DbSchema in headless mode.

To create the monocle.jar file, we have two options. In either of them, we need to build it in the correct Java JDK version, which in our case, it's JDK 17.

visit: github.com/TestFX/Monocle and follow the instructions and build the monocle.jar file for JDK 17.
download the monocle.jar file from Maven: https://central.sonatype.com/artifact/org.testfx/openjfx-monocle/17.0.10

In our case, we went with the first option and the resulting monocle.jar file is available in this repository, called openjfx-monocle-17.0.10.jar.

We are going to be using this file within the Docker container, in the following section. This is because, according to the DbSchema docs, we need to place it in the lib directory of DbSchema installer files (after extraction).

3. Defining our `Docker` container

Now that we have the installer, we are going to need it to install it and run DbSchema headless using a Docker container running Linux.

With this in mind, in the same directory as the Makefile, create a Dockerfile with the following contents:

FROM --platform=linux/amd64 ubuntu:22.04

# Install JDK 17, OpenJFX, and other dependencies
RUN apt update -q && \
    apt install -y -q openjdk-17-jre openjdk-17-jdk-headless wget unzip libgl1-mesa-glx libgl1-mesa-dri postgresql-client

# Download and extract the JavaFX SDK (compatible with JDK 17 version, required by DbSchema)
RUN wget https://download2.gluonhq.com/openjfx/17.0.2/openjfx-17.0.2_linux-x64_bin-sdk.zip -O /tmp/openjfx.zip && \
    unzip /tmp/openjfx.zip -d /opt && \
    rm /tmp/openjfx.zip

# Set JavaFX environment variable for library path and copy Monocle jar to JavaFX lib directory
ENV PATH_TO_FX=/opt/javafx-sdk-17.0.2/lib
COPY openjfx-monocle-17.0.10.jar $PATH_TO_FX/

# Set work directory to `/home/dbschema` - DbSchema's home directory
WORKDIR /home/dbschema

# Copy DbSchema installer files to the container
# Copy the monocle jar to the lib directory (as per https://dbschema.com/documentation/dbschemacli.html#docker)
# Copy the example script to the current directory
COPY DbSchema ./app
COPY openjfx-monocle-17.0.10.jar ./app/lib/openjfx-monocle-17.0.10.jar
COPY example_script.groovy .

# Create a simple script that runs DbSchema and propagates the exit code.
# This is done to ensure that the container exits with the correct exit code (if the script fails, the container should fail too).
RUN echo '#!/bin/sh\n' \
    'set -e\n' \
    'java -Djava.awt.headless=true -Dglass.platform=Monocle -Dmonocle.platform=Headless -Dprism.order=sw -Djavafx.platform=monocle --module-path /opt/javafx-sdk-17.0.2/lib --add-modules javafx.controls,javafx.base,javafx.graphics,javafx.swing --add-exports=javafx.graphics/com.sun.glass.ui=ALL-UNNAMED --add-exports=javafx.graphics/com.sun.javafx.util=ALL-UNNAMED --add-exports=javafx.graphics/com.sun.javafx.logging=ALL-UNNAMED --add-exports=javafx.base/com.sun.javafx.logging=ALL-UNNAMED -cp ./app/lib/*:/opt/javafx-sdk-17.0.2/lib/openjfx-monocle-17.0.10.jar com.wisecoders.dbs.DbSchema -x  ./example_script.groovy\n' \
    'EXIT_CODE=$?\n' \
    'if [ $EXIT_CODE -ne 0 ]; then\n' \
    '  echo "Script execution failed with exit code $EXIT_CODE."\n' \
    '  exit $EXIT_CODE\n' \
    'else\n' \
    '  echo "Script executed successfully."\n' \
    '  exit 0\n' \
    'fi' > /home/dbschema/run_dbschema.sh

# Run the script as the entry point
ENTRYPOINT ["sh", "/home/dbschema/run_dbschema.sh"]

# Uncomment to keep the container running after the script execution
#RUN sh /home/dbschema/run_dbschema.sh || true
#CMD ["tail", "-f", "/dev/null"]

Let's break down what's happening in the Dockerfile:

We specify the base image as Ubuntu 22.04 for the AMD64 architecture. This is better for compatibility with DbSchema. It then updates the package list and installs JDK 17, OpenJFX, and other necessary dependencies such as wget, unzip, and PostgreSQL client. These dependencies are crucial for running DbSchema headlessly and to run the rest of the commands ahead! Because DbSchema implements JDK v17 and OpenJFX for its GUI, we need to install these dependencies to run it headlessly.
Next, it downloads the JavaFX SDK compatible with JDK 17 and extracts it to the /opt directory. JavaFX is used by DbSchema and it fails if we don't install it and define the environment variables correctly, hence why setting up the PATH_TO_FX env variable.
In addition to this, we are copying the monocle.jar file we've created in the previous section. This file goes into the lib directory of the extracted DbSchema installer, as defined in DbSchema's documentation.
The working directory is set to /home/dbschema. Afterwards, DbSchema installer files, Monocle jar file, and an example Groovy script are copied to the container. This Groovy script is what we are going to automate tasks and interact with DbSchema without having to use the GUI. More info: https://dbschema.com/documentation/automation-api.html
Next, we create a small shell script to run DbSchema in headless mode. This script only exists so that the container exits with the correct exit code. For example, if DbSchema running the Groovy script fails, the shell script fails too. This is important in the context of Github Actions, where we want to know if the process failed or not.
Finally, the created shell script is set as the entry point for the container.

Note

Do note that we haven't created a non-root user in the Docker container. This is because we found out that when running this in Github Actions, it was causing permission issues when generating files when executing the script.

Therefore, we are running the DbSchema Java process as root.

Awesome! 🎉

We are now really close to running an instance of DbSchema in headless mode!

However, we are missing one thing: the example_script.groovy file that we are going to use!

Let's go over that now.

4. Creating a `docker-compose.yml` file

We are going to use Docker Compose to run the Docker container we've created above. This container will run DbSchema, which in turn, will run a Groovy script that will generate the HTML5 docs of the database.

DbSchema will need to connect to a PostgreSQL database inside the Groovy script. Therefore, we will need to define another Docker container that runs a PostgreSQL database.

Create a docker-compose.yml file with the following content:

# Run `sudo docker compose up --build` to build and start the services

services:
  # PostgreSQL database service
  postgres-db:
    image: postgres:14
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "psql -h localhost -p 5432 -U postgres -d postgres -c '\\q'"] # Check if the database is ready
      interval: 3s
      timeout: 5s
      retries: 5
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./12_tables.sql:/docker-entrypoint-initdb.d/12_tables.sql
    networks:
      - my-network

  # DbSchema service
  app:
    build: .
    depends_on:
      postgres-db:
        condition: service_healthy
    networks:
      - my-network
    environment:
      DB_HOST: postgres-db
      DB_USER: postgres
      DB_PASSWORD: postgres
      DB_NAME: postgres
      DB_PORT: 5432
      OUTPUT_PATH: /tmp/output
    volumes:
      - ./output:/tmp/output # Mount the output directory.

volumes:
  postgres-data:

networks:
  my-network:

Let's break this down:

in the postgres-db service, we are defining the PostgreSQL database container.
- It uses the PostgreSQL 14 image.
- We define the database, user and password as environment variables.
- We expose the 5432 port, which is the default PostgreSQL port.
- We define a health check to ensure the database is ready. This health check will be used by the other service (running the Dockerfile image we've created earlier) to ensure the database is ready before running the DbSchema script. For this, we use the psql command to check if we can connect to the database.
- We mount the postgres-data volume to persist the database data. In addition to this, we mount the 12_tables.sql file to the /docker-entrypoint-initdb.d directory. This 12_tables.sql file will be used to create the tables in the database as an example. We map this script to the docker-entrypoint-initdb.d directory inside the container, which is used by PostgreSQL to run initial scripts
in the app service, run the Dockerfile image defined earlier.
- Define a dependency on the postgres-db service. This dependency ensures that the DbSchema service only starts when the PostgreSQL database is ready.
- Define the environment variables that the DbSchema script will use to connect to the database. These env variables will be used in the Groovy script.
- Mount the output directory to /tmp/output inside the container. This will create an output directory on our local machine that contains everything that is inside the /tmp/output directory inside the container. Within the container, the HTML5 doc page is generated in /tmp/output.
both of these services run within the same my-network network, so they are able to communicate with each other.

And that's it! If we run sudo docker compose up --build, both the images will be built and the services will start running. postgres-db will start first, while app waits for it. Once postgres-db is ready, app will start running the DbSchema script, and exits if it's executed successfully.

You can check the output directory to see the generated HTML5 documentation page!

All that's left now is to create the Groovy script. Let's crack on with that! 🏃‍♂️

5. Creating our `Groovy` script

Important

DbSchema's docs have some documentation on these scripts so you can know what features you can automate. Check https://dbschema.com/documentation/automation-api.html for more information.

In addition to this, you have access to DbSchema API documentation, which provides a list of classes and methods you can use in your scripts.

DbSchema provides us with some example of scripts so that we can dip our toes into the automation process. In conjunction with the DbSchema API, we can create scripts that automate tasks and interact with DbSchema and do all sorts of stuff!

You can explore some scripts by opening the DbSchema GUI, go to Tools > Automation Scripts, and peruse the scripts available.

For this tutorial, we are going to create a script that will generate an HTML5 documentation page. This page will have a diagram with all the tables of the database, where we can customize this layout fully.

Create a file called example_script.groovy with the following content:

import com.wisecoders.dbs.diagram.model.Callout
import com.wisecoders.dbs.diagram.model.Depict
import com.wisecoders.dbs.diagram.model.Group
import com.wisecoders.dbs.diagram.model.Point
import com.wisecoders.dbs.schema.*
import javafx.scene.paint.Color

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.stream.Stream;

// Connection parameters
def conn_alias = "pg"
def conn_dbmsName = "PostgreSQL"
def conn_driver = "org.postgresql.Driver"
def conn_jdbcUrl = "jdbc:postgresql://" + System.getenv("DB_HOST") + ":" + System.getenv("DB_PORT") + "/" + System.getenv("DB_NAME")
def conn_username = "postgres"
def conn_password = "postgres"

// Color maps according to table prefixes
def backgroundColorMap = [
        "dim" :"#c4e0f9",
        "fact" :"#feea9b",
]

def foregroundColorMap = [
        "dim" :"#96c8f4",
        "fact" :"#fddf69",
]


try {
    // Make sure to have the PostgreSQL driver
    com.wisecoders.dbs.cli.command.connectivity.DownloadDriverCommand.downloadDriver(conn_dbmsName)

    // Create sample project
    Project project = new Project("sample", conn_dbmsName);

    // Create a connection to the PostgreSQL database
    Connector con = ConnectorManager.createConnector(
        conn_alias,
        conn_dbmsName,
        conn_driver,
        conn_jdbcUrl,
        conn_username,
        conn_password
    )

    // Import "public" schema
    def dbSchema = "public"
    Schema schema = con.importSchema(project, dbSchema)
    println "processing schema: ${schema}"

    // Creating layout and groups
    Layout layout = project.createLayout("ERD-${schema}")
    def grps = ["defaultKey": null]

    // Create layout in the project. We are creating a single one.
    // See https://dbschema.com/documentation/layouts.html for more information,
    for (Table table: schema.tables){

        // We expect all table name to have a "dim_" or "fact_" prefix
        String tableType = table.getName().split('_')[0]
        table.setComment("COMMENT ON TABLE ${table.getName()}")
        bgColor = backgroundColorMap.get(tableType)
        fgColor = foregroundColorMap.get(tableType)

        if(!grps.containsKey(tableType)){
            Group grp = layout.diagram.createGroup(tableType)
            grps.put(tableType, grp)
            println "Add layout group for domain ${tableType}"
        }

        // Create depict for the table
        Depict depict = layout.attach( table )
        depict.setComment("COMMENT STRING")
        //depict.hideColumnsIfDepictIsLarge()

        // Get the group and attach the depict to the group and setting its color
        grp = grps.get(tableType)

        grp.attachDepict( depict )
        grp.setColor(Color.web(bgColor))
        println "Add table ${table}"
    }
    layout.autoArrange()

    // Export HTML5 documentation
    def outputDir = new File(System.getenv("OUTPUT_PATH"))
    def outputFile = outputDir.getPath() + "/erd_" + dbSchema.toLowerCase() + ".html"

    // Create the output directory in case it doesn't exist
    if (!outputDir.exists()) {
        outputDir.mkdirs()
    }

    println "general html file: ${outputFile}"
    htmlFile = new File(outputFile)
    layout.generateHtmlDocumentation( htmlFile, [layout] )
}

// Ensure the script exits with a non-zero status on failure
catch (Exception e) {
    println "Script failed: ${e.message}"
    e.printStackTrace()
    System.exit(1)
}

// Explicitly exit with 0 if the script succeeds
System.exit(0)

Note

We recommend you read the DbSchema API in tandem with this script to understand what each method does.

This script connects to a PostgreSQL database and generates the ERD with color-coded tables based on their prefixes. If we check 12_tables.sql file, we can see that the tables have prefixes dim_ and fact_, for dimension and facts tables, respectively.

Before starting, we import necessary libraries and define connection parameters using environment variables for the database. We initialize the needed parameters to connect to the database, including alias, the dbmsName (which must be the same as defined by DbSchema), the driver used, the jdbcUrl to connect to the database, the database username and password. Additionally, some color maps are defined for table prefixes.

The script starts by downloading the PostgreSQL driver if it's not already available.
A new project is created, and a connection to the PostgreSQL database is established using the provided connection parameters.
After, we import the "public" schema from the database and create a layout for the ERD.
We iterate over the tables in the schema, assigning colors and comments based on their prefixes, and group them accordingly.
the layout is then auto-arranged, and HTML5 documentation is generated and saved to a specified 'output' directory.

If any errors occur during the process, the script catches the exception, prints an error message, and exits with a non-zero status. If the script completes successfully, it exits with a status of 0.

We make use of the environment variables defined in the docker-compose.yml file to create the connection and output paths.

6. Create a `Github Actions` Workflow to Generate Artifacts

Now we have all we need to run DbSchema headless and generate the HTML5 docs of the database inside a Github Actions workflow. We will be generating an artifact, which will contain the HTML5 docs page.

Create your workflow file (e.g.: generate_html.yml) in the .github/workflows directory:

name: DbSchema Workflow

on: [push]

jobs:
  dbschema-job:
    runs-on: ubuntu-22.04

    steps:
    - name: Checkout code
      uses: actions/checkout@v4

    - name: Install Make
      run: sudo apt-get install -y make

    - name: Run Makefile
      run: make

    - name: Build and Run Docker Compose
      run: |
        # Run Docker Compose in detached mode
        sudo docker compose up --build --exit-code-from app

    - name: Upload Artifacts
      uses: actions/upload-artifact@v4
      with:
        name: dbschema-html-output
        path: output/*

As you can see, it's really simple! We've done all the work inside the Docker containers in conjunction with the docker-compose.yml file. The Github Actions workflow file is just a few steps:

checking out the code.
installing Make.
running the Makefile to download and extract the DbSchema installer.
building and running the Docker Compose services.
uploading the output directory as an artifact.

And that's it! If you push your code to the repository, the Github Actions workflow will run, and you will have an artifact with the HTML5 documentation page.

Great job! Give yourself a pat on the back! ✌️

Star this repo ⭐️

If this was useful to you, please give it a star! 😉

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

What? 💭

Why? 🤷‍♀️

Who? 👤

How? 🛠

Local Schema Visualization

1. Download and install `DbSchema`

2. Create database and create tables

3. Use `DbSchema` to visualize the schema

4. Exporting HTML5 documentation

Headless schema visualization

1. Automating downloading `DbSchema`'s installer

2. Creating the `monocle.jar` file

3. Defining our `Docker` container

4. Creating a `docker-compose.yml` file

5. Creating our `Groovy` script

6. Create a `Github Actions` Workflow to Generate Artifacts

Star this repo ⭐️

About

Contributors 2

Languages

Name		Name	Last commit message	Last commit date
Latest commit History 67 Commits
.github/workflows		.github/workflows
.gitignore		.gitignore
12_tables.sql		12_tables.sql
Dockerfile		Dockerfile
LICENSE		LICENSE
Makefile		Makefile
README.md		README.md
docker-compose.yml		docker-compose.yml
example_script.groovy		example_script.groovy
index.html		index.html
openjfx-monocle-17.0.10.jar		openjfx-monocle-17.0.10.jar

License

dwyl/dbschema-demo

Folders and files

Latest commit

History

Repository files navigation

What? 💭

Why? 🤷‍♀️

Who? 👤

How? 🛠

Local Schema Visualization

1. Download and install DbSchema

2. Create database and create tables

3. Use DbSchema to visualize the schema

4. Exporting HTML5 documentation

Headless schema visualization

1. Automating downloading DbSchema's installer

2. Creating the monocle.jar file

3. Defining our Docker container

4. Creating a docker-compose.yml file

5. Creating our Groovy script

6. Create a Github Actions Workflow to Generate Artifacts

Star this repo ⭐️

About

Resources

License

Stars

Watchers

Forks

Contributors 2

Languages

1. Download and install `DbSchema`

3. Use `DbSchema` to visualize the schema

1. Automating downloading `DbSchema`'s installer

2. Creating the `monocle.jar` file

3. Defining our `Docker` container

4. Creating a `docker-compose.yml` file

5. Creating our `Groovy` script

6. Create a `Github Actions` Workflow to Generate Artifacts