Run Database and GUI Clients in Docker

DiponRoy

5.00/5 (8 votes)

6 Jul 2022CPOL2 min read

20.3K

158

Quick start example to run DB and clients in docker

The document presents a guide on setting up multiple databases and GUI clients using Docker Compose files. It covers PostgreSQL with pgAdmin, MySQL with phpMyAdmin and MySQL Workbench, MongoDB with Nosqlclient, Oracle with CloudBeaver, SQLServer, and MariaDB.

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:

SQL

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

Coming Up

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

History

6^th July, 2022: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)