Designing a Data Source Discovery App - Part 18: Deploying the kds_discovery_2025 Database

by DL Keeshin

April 21, 2025

Database ERD

Deploying the kDS Data Source Discovery Database

In my last post, I described how the kDS Discovery App is now using Jinja2 to manage dynamic prompts and templates. Today, I want to talk about a behind-the-scenes piece that’s just as important: how we deploy the app’s PostgreSQL database in a clean, repeatable, and efficient way.

Preparing the Scripts

To get a new, up-to-date version of the database running, we rely on two key scripts. One handles the structural backbone of the database—schemas, tables, constraints—while the other focuses on the programmable components like stored procedures, views, and functions that exist in the development environment.

Script 1: Export from dbdiagram.io

We use dbdiagram.io from Holistics as our modeling tool. It’s an online schema designer that lets you visually lay out your database using their Database Markup Language (DBML). It’s readable, intuitive, and interestingly, LLMs like ChatGPT and Claude AI understand it. The diagram at the top of this blog is generated with it. The tool is very affordable and has a FREE plan that would meet the needs of many users.

The export from dbdiagram.io gives us all the basics:

  • Schemas
  • Tables
  • Columns
  • Primary and foreign key constraints

Once exported, this pSQL script is applied to a clean PostgreSQL database named kds_discovery_2025_template.

Script 2: Delta from dbForge

Next, we use dbForge Schema Compare for PostgreSQL to compare this fresh template database with our fully loaded development database, kds_discovery_2025. This development version contains all the programmable objects—views, functions, stored procedures—that dbdiagram doesn’t track.

The delta script from dbForge adds those programmable components to the clean base. This approach allows us to separate structural design from logic and behavior components, which are maintained in our development environment

Deploying with a Bash Script

We bring everything together with a deployment script: 0065_install_database_2025.sh.

Here’s what it does:

#!/bin/bash

# Set up variables
dbname="kds_discovery_2025"
ddl_script_dbdiagram="kds_discovery_2025_template.sql"
ddl_script_kds="template_synch.sql"

# Connect to PostgreSQL and apply scripts
psql -h $(hostname) -U postgres <<EOF
CREATE DATABASE $dbname;
\c $dbname;
$(cat "$ddl_script_dbdiagram")\g
$(cat "$ddl_script_kds")\g
...
EOF

It creates the new database, applies both the schema and delta scripts, and loads configuration tables like admin.prompt_type, reference.industry, and more.

This ensures that each fresh deployment includes both the structure and the logic needed to run the app.

Cloud Deployment

We chose to deploy the database on a DigitalOcean Droplet. We decided DigitalOcean over AWS, Azure, or Google Cloud for a few reasons:

  • It’s simple and fast to get up and running.
  • It’s cheaper for small-to-mid projects.
  • You get full control over your VM.

Instead of using a managed PostgreSQL instance that Digital Ocean offers, we opted for our own install. This gave us more control and avoided some platform-specific limitations.

Installing PostgreSQL Manually

Here’s the basic setup:

  1. Install PostgreSQL: 
    sudo apt install postgresql
  2. Edit postgresql.conf: 
    sudo nano /etc/postgresql/16/main/postgresql.conf
    Change: 
    listen_addresses = '*'
  3. Edit pg_hba.conf: 
    sudo nano /etc/postgresql/16/main/pg_hba.conf
    Add the following line and replace nnn.nnn.nnn.nnn with your IP address 
    host    all    all    nnn.nnn.nnn.nnn/32    md5
  4. Set the password in PostgreSQL: 
    sudo -i -u postgres
psql
ALTER USER postgres WITH PASSWORD 'your_secure_password';

Bringing It All Together

After PostgreSQL is installed and configured, we:

  1. Install Git: 
    sudo apt install gh
  2. Clone the repository: 
    git clone https://github.com/kds-github/data_discovery.git
  3. Run the installer: 
    cd data_discovery
bash 0065_install_database_2025.sh
    This bash script sets up the database, loads essential configuration data, and gets the app ready to run. It’s lightweight, repeatable, and simple enough to debug or adjust as the database evolves.

Final Thoughts

Today we explored our approach deploying the kDS Discovery App's PostgreSQL database on DigitalOcean. By combining structural exports from dbdiagram.io with programmable components from our development environment, we've created a repeatable process that ensures consistency across deployments. This method gives us the control we need while keeping the setup process straightforward and maintainable. In a future post, we'll discuss how to deploy the application server that manages the kDS Discovery App.

Also, don't forget that kDS LLC is actively seeking organizations interested in adopting a beta version of the kDS Data Source Discovery App. If your organization is interested in exploring how our app can streamline your data source discovery process, please let us know. We’d love to collaborate. Tell us in the comments below or email us at info@keeshinds.com

Until next time, thanks for stopping by.

Leave a Comment: