Run Database and GUI Clients in Docker

• Download source code - 5.7 KB

Background

When setting up a new project, there is sometimes a comprehensive list of prerequisites to be able to run the project, this could lead us to use multiple databases even different versions. Docker is great for running local databases in a development environment. Here, we will explore docker-compose.yml files to set up multiple DB and GUI clients in docker. This will be a very quick and basic workaround considering multiple databases.

Things we need:

• Docker installed locally
• Basic Docker knowledge
• Experiences using DB
• Experiences using the GUI clients

PostgreSQL

Database: PostgreSQL

GUI Client: pgAdmin

This will create a database named postgres in the Db instance with credentials admin and secret.

version: "3.7"

services:
  pgsql:
    image: postgres:12.2
    container_name:  PostgreSQL
    restart: always
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secret
      PGDATA: /var/lib/postgresql/data
    volumes:
      - pgsql-data:/var/lib/postgresql/data
    ports:
      - 5010:5432

  pgadmin:
    image: dpage/pgadmin4:4.18
    container_name:  PgAdmin
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@admin.com
      PGADMIN_DEFAULT_PASSWORD: secret
      PGADMIN_LISTEN_PORT: 80
    ports:
      - 5011:80
    volumes:
      - pgadmin-data:/var/lib/pgadmin
    links:
      - pgsql:pgsql-server

volumes:
  pgsql-data:
  pgadmin-data:

Client

pdAdmin will be available at http://localhost:5011/login.

admin@admin.com and secret as credential.

Access Db

Host name or Server as DB container name PostgreSQL.

Port 5432

DB credentials admin and secret

MySQL

Database: MySQL

GUI Client: phpMyAdmin

This will create a database named quotes in the Db instance with credentials citizix_user and An0thrS3crt:

version: "3.7"

services:
  mysql:
    image: mysql:8.0
    container_name: MySQL
    restart: always
    cap_add:
      - SYS_NICE
    environment:
      - MYSQL_DATABASE=quotes
      - MYSQL_USER=citizix_user
      - MYSQL_PASSWORD=An0thrS3crt
      - MYSQL_ROOT_PASSWORD=mauFJcuf5dhRMQrjj
    volumes:
      - mysql-data:/var/lib/mysql
    ports:
      - 5012:3306

  phpmyadmin:
    image: phpmyadmin:5.2.0
    container_name: PhpMyAdmin   
    restart: always
    ports:
      - 5013:80
    environment:
      - PMA_ARBITRARY=1
    
volumes:
  mysql-data:

Another GUI Client: MySQL Workbench

version: "3.7"

services:
  mysql-workbench:
    image: lscr.io/linuxserver/mysql-workbench:8.0.29
    container_name: MySQL-Workbench
    environment:
      - PUID=1000
      - PGID=1000
      - TZ=Europe/London
    volumes:
      - mysql-workbench-data:/config
    ports:
      - 5014:3000
    cap_add:
      - IPC_LOCK
    restart: unless-stopped

volumes:
  mysql-workbench-data:

Server name as DB container name MySQL and other DB credentials.

Client

PhpMyAdmin will be available at http://localhost:5013/.

Access Db

Server name as DB container name MySQL

DB credentials citizix_user and An0thrS3crt

MongoDB

Database: MongoDB

GUI Client: Nosqlclient

This will create a database named chats in the Db instance without credentials.

version: '3.7'

services:
  mongo:
    image: mongo:4.4.2
    container_name: MongoDB
    restart: always
    environment:
      - MONGO_INITDB_DATABASE=chats
    ports:
      - 5015:27017
    volumes:
      - mongodb-data:/data/db

  mongo_client:
    image: mongoclient/mongoclient:4.0.1
    container_name: Nosqlclient
    restart: always
    depends_on:
      - mongo
    ports:
      - 5016:3000

volumes:
  mongodb-data:

Client

Nosqlclient will be available at http://localhost:5016/

Access Db

Host name as DB container name MongoDB

Port 27017

We can also use connection string like mongodb://userName:password@containerName:27017/dbname

Oracle

Database: Oracle

GUI Client: CloudBeaver

This will create a Db instance with credentials.

version: '3.7'

services:
  oracle:
    image: wnameless/oracle-xe-11g-r2:18.04-apex
    container_name: OracleDb
    restart: always
    environment:
      - ORACLE_ALLOW_REMOTE=true
      - ORACLE_DISABLE_ASYNCH_IO=true
      - ORACLE_ENABLE_XDB=true
    ports:
      - 5019:1521
      - 5020:8080

  dbeaver:
    image: dbeaver/cloudbeaver:22.1.1
    container_name: CloudBeaver
    restart: unless-stopped
    volumes:
      - dbeaver-data:/opt/cloudbeaver/workspace
    ports:
      - 5021:8978

volumes:
  dbeaver-data:

Oracle APEX

http://localhost:5020/apex/apex_admin
username: ADMIN
password: Oracle_11g

Client

CloudBeaver will be available at http://localhost:5021/

Initially, it will ask to set a credential so I used adminuser and adminpass.

Access Db

Host name as DB container name OracleDb

Port 1521

Db or sid xe

DB credentials system and oracle

SQLServer

Database: SQLServer

GUI Client: CloudBeaver

This will create a Db instance with credentials.

version: '3.7'

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    container_name: SQLServer
    restart: always
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Express
      - SA_PASSWORD=MyPass@word     #user sa
    #volumes:
      #- sqlserver-data:/var/opt/mssql/data         #not working in windows
      #- "./sqlserver-data/:/var/opt/mssql/data"    #this working in windows
    ports:
      - 5017:1433 

Client

Same like Oracle

Access Db

Host name as DB container name SQLServer

Port 1433

Db(default) master

DB credentials sa and MyPass@word

Access Db Using Management Studio

Server name localhost, 5017

Credentials sa and MyPass@word

Set TrustServerCertificate = True (https://stackoverflow.com/a/50504551/2948523)

MariaDB

Database: MariaDB

This will create a database named quotes in the Db instance with credentials citizix_user and An0thrS3crt.

version: "3.7"

services:
  maria:
    image: mariadb:10.6
    container_name: MariaDB
    restart: always
    environment:
      - MARIADB_DATABASE=quotes
      - MARIADB_USER=citizix_user
      - MARIADB_PASSWORD=An0thrS3crt
      - MARIADB_ROOT_PASSWORD=mauFJcuf5dhRMQrjj
    volumes:
      - maria-data:/var/lib/mysql
    ports:
      - 5018:3306
    
volumes:
  maria-data:

Client

PhpMyAdmin or CloudBeaver

Access Db

Server name as DB container name MariaDB.

DB credentials citizix_user and An0thrS3crt

Others

Commands

PostgreSQL
docker-compose -f docker-compose.pgsql.yml up -d

MySQL
docker-compose -f docker-compose.mysql.yml up -d
docker-compose -f docker-compose.mysql.workbench.yml up -d

MongoDB
docker-compose -f docker-compose.mongo.yml up -d 

SQLServer
docker-compose -f docker-compose.sqlserver.yml up -d

MariaDB
docker-compose -f docker-compose.maria.yml up -d

Oracle
docker-compose -f docker-compose.oracle.yml up -d 

Ports

5010, 5011           pgsql
5012, 5013, 5014     mysql
5015, 5016           mongo
5017                 sqlserver
5018                 maria
5019, 5020, 5021     oracle 

Check Database Version

Here are a few SQL queries to check our working database versions:

SELECT @@VERSION;            /*sql server*/       /*also works with mysql*/
SELECT * FROM V$VERSION;     /*oracle*/
SELECT VERSION();            /*mysql*/            /*also works with postgre*/
SELECT VERSION();            /*mariadb*/
SELECT VERSION();            /*postgre sql*/      /*also works with mysql*/ 

Online Playground

• http://sqlfiddle.com/
• https://www.db-fiddle.com/

Coming Up

• SQLite
• Set credentials for MongoDB
• Use multiple DB in the same instance

History

• 6^th July, 2022: Initial version