I have published a new project on my Github account called Jukenook Db.
Jukenook Db is a reference project that demonstrates how to:
- create a PostgreSQL environment using Docker and Docker-Compose
- configure database versioning and migration using Flyway
About
Jukenook Db is based on the Chinook Database Project. The Chinook Database Project is a sample database that is available to be used with a number of database engines such as Postgres, SQL Server, and MySQL for example. The Chinook data model represents a digital media store, including tables for artists, albums, media tracks, invoices and customers. To see the full description and supported databases, please visit the Chinook Database Project. All credit goes to the Chris Woodruff Chinook Database Project.
The reason for creating this project is due to a continuous need for a consistent database structure that I can use when experimenting with different application development ideas. I searched for a data model that is simple, yet has the potential to evolve into something more sophisticated over time. After investigating a number of different options, I decided on the Chinook Database Project. I have taken the original Chinook Database Project and changed it to work specifically with a Postgres database engine using Flyway migrations. I will also continue to evolve Jukenook Db as I experiment with different ideas.
Why The Name
Firstly, I chose not to keep the original name 'Chinook (Chinook Database Project)'. This is primarily due to reasons involving the evolution of the database model over time.
Secondly, Because the primary database concept is focused around music, I decided to take inspiration from a musical Jukebox. Therefore, I combined the naming of Jukebox and Chinook to form the new name 'Jukenook'. A 'nook' also happens to be a corner or recess, and I think that makes a good place to locate a Jukebox 🤔
Purpose
Primary Objective
The primary objective for this project is to provide a Postgres database that can be easily deployed into one's local development environment.
Secondary Objectives
- Show how to use Flyway to manage database versioning and migration
- Show how to setup a Postgres database using Docker and Docker-Compose
- Defining docker-compose file to create local Postgres environment using the Postgres 12 and pgAdmin
- Defining docker-compose files that provide an easy way to work with Flyway using Flyway Docker Image
Supported Platforms
This project has been successfully tested on the following platforms:
- Ubuntu 20.04 - Ubuntu is an open source software operating system that runs from the desktop, to the cloud, to all your internet connected things.
- Windows 10 Professional (2004) - Latest version of Windows 10 operating system. For this particular platform I used Powershell Command Line to execute project tasks.
- Windows Subsystem For Linux 2 - WSL 2 is based on a new architecture that provides full Linux binary application compatibility and improved performance. WSL 2 is powered by a real Linux kernel in a lightweight virtual machine that boots in under two seconds. Specifically, the Ubuntu 20.04 distribution was used.
General Tools
The following list of tools are required in order to run this project correctly:
- Docker - Docker is a computer program that performs operating-system-level virtualization also known as containerization. In other words it allows one to containerize applications.
- Docker-Compose - Compose is a tool for defining and running multi-container Docker applications.
- Flyway - Version control for your database by making database migrations repeatable and easy
- Postgres - PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.
- pgAdmin - Open Source administration and development platform for PostgreSQL
- Visual Studio Code - Visual Studio Code is a source code editor developed by Microsoft for Windows, Linux and macOS. It includes support for debugging, embedded Git control, syntax highlighting, intelligent code completion, snippets, and code refactoring.
- Npm - A package manager for the Javascript programming language. In this project it is used as a task runner to simplify running various infrastructural tasks
Getting Started
In order to successfully run the Jukenook database project, you will require the following tools and frameworks to be installed:
For this project, every database definition will be described using a number of database migration scripts. The database migration scripts are defined as per the Flyway database versioning and migration tool guidelines. Flyway is an open-source database migration tool that favors simplicity and convention over configuration. For more information, please see the following links:
Get the repository
git clone https://github.com/drminnaar/jukenook-db.git
Manage Database
I have created docker-compose files to help with the management of bringing up and taking down a PostgreSQL database environment. I also provide docker-compose files to help manage the database migrations and versioning. For example:
- docker-compose.yml - Defines the PostgreSQL and pgAdmin services
- docker-compose-dev-flyway:info.yml - Runs Flyway migration to obtain information about migrations
- docker-compose-dev-flyway:migrate.yml - Runs Flyway migration to do actual database migrations
- docker-compose-dev-flyway:validate.yml - - Runs Flyway migration to validate migrations
In order to make it simpler to manage the environment, I have created an NPM package file wherein I have defined a number of tasks. For example:
"scripts": {
"dev-db:up": "docker-compose up -d",
"dev-db:down": "docker-compose down --volumes && docker-compose -f ./flyway/docker-compose-dev-flyway:info.yml -f ./flyway/docker-compose-dev-flyway:validate.yml -f ./flyway/docker-compose-dev-flyway:migrate.yml down",
"dev-db:migrate": "docker-compose -f ./flyway/docker-compose-dev-flyway:migrate.yml up",
"dev-db:info": "docker-compose -f ./flyway/docker-compose-dev-flyway:info.yml up",
"dev-db:validate": "docker-compose -f ./flyway/docker-compose-dev-flyway:validate.yml up",
"start": "npm run dev-db:down && npm run dev-db:up"
}
I generally prefer using NPM as a task runner unless I have more advanced requirements.
Spinning Up
This step will create an empty Jukenook database running in a docker container. The docker-compose.yml file is used to:
- create a Postgres12 (running on Alpine Linux) database in a docker container
- load pgAdmin application via docker container. The pgAdmin application is accessible at http://localhost:8080
$ npm run dev-up
> jukenook-db@1.0.0 dev-db:up /home/dmin/projects/jukenook/jukenook-db
> docker-compose up -d
Creating network "jukenook_net" with the default driver
Creating jukenook_db ... done
Creating jukenook_admin ... done
Tearing Down
This step will:
- stop and remove all related docker containers
- remove all related docker volumes
- remove all related docker networks
$ npm run dev-down
> jukenook-db@1.0.0 dev-db:down /home/dmin/projects/jukenook/jukenook-db
> docker-compose down --volumes && docker-compose -f ./flyway/docker-compose-dev-flyway:info.yml -f ./flyway/docker-compose-dev-flyway:validate.yml -f ./flyway/docker-compose-dev-flyway:migrate.yml down
Stopping jukenook_admin ... done
Stopping jukenook_db ... done
Removing jukenook_admin ... done
Removing jukenook_db ... done
Removing network jukenook_net
Network jukenook_net is external, skipping
Manage Database Migrations
For the following commands, the Flyway database versioning and migration tool is used.
Get Database Migrations Info
This command will return a list of migrations with relevant information like the migration state.
$ npm run dev-db:info
> jukenook-db@1.0.0 dev-db:info /home/dmin/projects/jukenook/jukenook-db
> docker-compose -f ./flyway/docker-compose-dev-flyway:info.yml up
Creating jukenook_flyway ... done
Attaching to jukenook_flyway
Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://jukenook_db/jukenook (PostgreSQL 12.3)
Schema version: << Empty Schema >>
+------------+---------+------------------------------+------+--------------+---------+
| Category | Version | Description | Type | Installed On | State |
+------------+---------+------------------------------+------+--------------+---------+
| Versioned | 1.1 | Create operations schema | SQL | | Pending |
| Versioned | 1.2 | Create employee table | SQL | | Pending |
| Versioned | 1.3 | Create catalog schema | SQL | | Pending |
| Versioned | 1.4 | Create genre table | SQL | | Pending |
| Versioned | 1.5 | Create artist table | SQL | | Pending |
| Versioned | 1.6 | Create album table | SQL | | Pending |
| Versioned | 1.7 | Create media type table | SQL | | Pending |
| Versioned | 1.8 | Create track table | SQL | | Pending |
| Versioned | 1.9 | Create playlist table | SQL | | Pending |
| Versioned | 1.10 | Create composition table | SQL | | Pending |
| Versioned | 1.11 | Create sales schema | SQL | | Pending |
| Versioned | 1.12 | Create customer table | SQL | | Pending |
| Versioned | 1.13 | Create invoice table | SQL | | Pending |
| Versioned | 1.14 | Create invoice line table | SQL | | Pending |
| Repeatable | | 001 Install extensions | SQL | | Pending |
| Repeatable | | 002 Insert genre data | SQL | | Pending |
| Repeatable | | 003 Insert media type data | SQL | | Pending |
| Repeatable | | 004 Insert artist data | SQL | | Pending |
| Repeatable | | 005 Insert album data | SQL | | Pending |
| Repeatable | | 006 Insert track data | SQL | | Pending |
| Repeatable | | 007 Insert employee data | SQL | | Pending |
| Repeatable | | 008 Insert customer data | SQL | | Pending |
| Repeatable | | 009 Insert invoice data | SQL | | Pending |
| Repeatable | | 010 Insert invoice line data | SQL | | Pending |
+------------+---------+------------------------------+------+--------------+---------+
Validate Database Migrations
This command will validate pending migrations and return information relating to the validity thereof.
$ npm run dev-db:validate
> jukenook-db@1.0.0 dev-db:validate /home/dmin/projects/jukenook/jukenook-db
> docker-compose -f ./flyway/docker-compose-dev-flyway:validate.yml up
Recreating jukenook_flyway ... done
Attaching to jukenook_flyway
Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://jukenook_db/jukenook (PostgreSQL 12.3)
ERROR: Validate failed:
Detected resolved migration not applied to database: 1.1
Detected resolved migration not applied to database: 1.2
Detected resolved migration not applied to database: 1.3
Detected resolved migration not applied to database: 1.4
Detected resolved migration not applied to database: 1.5
Detected resolved migration not applied to database: 1.6
Detected resolved migration not applied to database: 1.7
Detected resolved migration not applied to database: 1.8
Detected resolved migration not applied to database: 1.9
Detected resolved migration not applied to database: 1.10
Detected resolved migration not applied to database: 1.11
Detected resolved migration not applied to database: 1.12
Detected resolved migration not applied to database: 1.13
Detected resolved migration not applied to database: 1.14
Detected resolved repeatable migration not applied to database: 001 Install extensions
Detected resolved repeatable migration not applied to database: 002 Insert genre data
Detected resolved repeatable migration not applied to database: 003 Insert media type data
Detected resolved repeatable migration not applied to database: 004 Insert artist data
Detected resolved repeatable migration not applied to database: 005 Insert album data
Detected resolved repeatable migration not applied to database: 006 Insert track data
Detected resolved repeatable migration not applied to database: 007 Insert employee data
Detected resolved repeatable migration not applied to database: 008 Insert customer data
Detected resolved repeatable migration not applied to database: 009 Insert invoice data
Detected resolved repeatable migration not applied to database: 010 Insert invoice line data
Run Database Migrations
This command will run the actual migrations on the database.
$ npm run dev-db:migrate
> jukenook-db@1.0.0 dev-db:migrate /home/dmin/projects/jukenook/jukenook-db
> docker-compose -f ./flyway/docker-compose-dev-flyway:migrate.yml up
Recreating jukenook_flyway ... done
Attaching to jukenook_flyway
Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://jukenook_db/jukenook (PostgreSQL 12.3)
Successfully validated 24 migrations (execution time 00:00.264s)
Creating Schema History table "public"."flyway_schema_history" ...
Current version of schema "public": << Empty Schema >>
Migrating schema "public" to version 1.1 - Create operations schema
Migrating schema "public" to version 1.2 - Create employee table
Migrating schema "public" to version 1.3 - Create catalog schema
Migrating schema "public" to version 1.4 - Create genre table
Migrating schema "public" to version 1.5 - Create artist table
Migrating schema "public" to version 1.6 - Create album table
Migrating schema "public" to version 1.7 - Create media type table
Migrating schema "public" to version 1.8 - Create track table
Migrating schema "public" to version 1.9 - Create playlist table
Migrating schema "public" to version 1.10 - Create composition table
Migrating schema "public" to version 1.11 - Create sales schema
Migrating schema "public" to version 1.12 - Create customer table
Migrating schema "public" to version 1.13 - Create invoice table
Migrating schema "public" to version 1.14 - Create invoice line table
Migrating schema "public" with repeatable migration 001 Install extensions
Migrating schema "public" with repeatable migration 002 Insert genre data
Migrating schema "public" with repeatable migration 003 Insert media type data
Migrating schema "public" with repeatable migration 004 Insert artist data
Migrating schema "public" with repeatable migration 005 Insert album data
Migrating schema "public" with repeatable migration 006 Insert track data
Migrating schema "public" with repeatable migration 007 Insert employee data
Migrating schema "public" with repeatable migration 008 Insert customer data
Migrating schema "public" with repeatable migration 009 Insert invoice data
Migrating schema "public" with repeatable migration 010 Insert invoice line data
Successfully applied 24 migrations to schema "public" (execution time 00:02.983s)
Connect Using pgAdmin
As part of the database setup and configuration in the docker-compose file. The web based PosrgreSQL database management tool is also run within a container.
Step 1 - Open pgAdmin Tool
- Open browser and enter the address 'http://localhost:8080' to access pgAdmin
- For username, enter 'admin@example.com'
- For password, enter 'password'
- Logon
- You should be logged in with the following view open
Step 2 - Create Server Group
- Create a server group called 'jukenook'
Step 3 - Create new server
- Create a server called 'jukenook-docker'
- Select the 'jukenook' server group
Step 4 - Provide Connection Details
- Select 'Connection' tab
- For host, because we are using docker to run pgAdmin, we need to obtain the IP Address of the Postgres docker container. Therefore, open up a terminal and type the following commands:
docker container inspect -f '{{ .NetworkSettings.Networks.jukenook_net.IPAddress }}' jukenook_db
-- Result
172.18.0.2 (you may recieve a different IP Address)
- Enter the connection details
- Host - see above to obtain IPAddress
- Username - specified in docker-compose.yml file
- Password - specified in docker-compose.yml file
- Port - leave as the default 5432
- Role - enter 'admin'. The admin role is created as part of database initialization script 'create-jukenook-db (defined in entrypoint-scripts folder)'.
- Select 'Save' connection
- After saving connection, you should see a view similar to the following:
Step 5 - Execute a Query
- Create a new query window by selecting the 'Query Tool' button.
- Enter a query and execute
Entrypoint Scripts
You may be wondering where I create the database and configure users and roles. I make use of entrypoint scripts to perform onceoff database initialization. For example, I currently have a single entrypoint script called 'create-jukenook-db' that looks as follows:
#!/bin/bash
set -e
psql -v ON_ERROR_STOP=1 --username "postgres" --dbname "postgres" <<-EOSQL
-- create database
CREATE DATABASE jukenook;
-- restrict public access
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE jukenook FROM PUBLIC;
-- create admin role
CREATE ROLE admin;
GRANT ALL PRIVILEGES ON DATABASE jukenook TO admin;
-- create readonly role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE jukenook TO readonly;
-- create readwrite role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE jukenook TO readwrite;
-- create admin users
CREATE USER jukenookboss WITH PASSWORD 'password';
GRANT admin TO jukenookboss;
-- create reader user
CREATE USER reader WITH PASSWORD 'password';
GRANT readonly TO reader;
-- create writer user
CREATE USER writer WITH PASSWORD 'password';
GRANT readwrite TO writer;
EOSQL
The entrypoint scripts can be found in the 'entrypoint-scripts' folder as follows:
There is also an entry added to the volumes section of the Postgres service in the docker-compose.yml file:
version: '3.5'
services:
jukenook_db:
.
.
.
volumes:
- ./entrypoint-scripts:/docker-entrypoint-initdb.d/
.
.
.
Schema
The database schema is composed of 4 schemas:
- music_catalog (yellow)
- sales (green)
- operations (cyan)
- public (orange)
Top comments (0)