Set up Your Favorite Database using Docker

Tshenolo Mos
9 min readMay 16, 2024

--

This guide is tailored for developers and system administrators who need to deploy MySQL, PostgreSQL, MongoDB, Microsoft SQL Server, Oracle, or Redis databases quickly and reliably.

Table of Contents

  • Introduction
  • Prerequisites
  • MySQL
  • PostgreSQL
  • MongoDB
  • Redis
  • Microsoft SQL Server
  • Oracle

Introduction

Welcome to the guide on setting up your favorite databases using Docker. Docker simplifies the management of software dependencies and ensures that your database runs the same way on every machine.

You can watch the video version of this article here:

Prerequisites

To follow this guide, you will need:

  • A basic understanding of database concepts and SQL.
  • Docker installed on your machine. You can download it from the Docker website.
  • Command line interface (CLI) knowledge, as most commands will be run in a terminal or command prompt.

MySQL

MySQL is a popular open-source relational database management system (RDBMS) that is widely used for managing structured data. In this section, we will walk you through the process of setting up MySQL using Docker, from pulling the MySQL Docker image to performing basic operations like creating databases, inserting data, and querying data.

Pull the Docker Image

Run the following command to download the official MySQL Docker image from Docker Hub to your local machine for use in creating and managing the MySQL container.

docker pull mysql

Run the Container

Use the following command to run the MySQL Docker container. The following command will not persist data.

docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=YOUR_PASSWORD -p 3306:3306 -d mysql

Run the following command if you would like to persist data.

docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=YOUR_PASSWORD -v mysql-data:/var/lib/mysql -p 3306:3306 -d mysql
  • — name mysql-container: Assigns the name “mysql-container” to the new container.
  • -e MYSQL_ROOT_PASSWORD=YOUR_PASSWORD: Sets the environment variable MYSQL_ROOT_PASSWORD to “YOUR_PASSWORD”, which is required to set the password for the MySQL root user.
  • -v mysql-data:/var/lib/mysql: Maps a Docker volume named mysql-data to the directory /var/lib/mysql inside the container. This directory is where MySQL stores its database files, ensuring that your data persists across container restarts and removals.
  • -p 3306:3306: Maps port 3306 on the host to port 3306 on the container, making the MySQL server accessible at localhost:3306 on your host machine.
  • -d: Runs the container in detached mode, meaning it runs in the background.
  • mysql: Specifies that the container should be created from the official mysql Docker image.

Connect to MySQL

You can use tools such as MySQL Workbench to connect to the database. Use the following connection details:

Host: localhost
Port: 3306
Username: root
Password: YOUR_PASSWORD

If you have MySQL client software installed on your system use the following command to connect to the database:

mysql -h localhost -P 3306 -u root -p

The MySQL client software is already installed in the MySQL Docker container and can be accessed using the following command:

docker exec -it mysql-container mysql -u root -p

Database Operations

This section guides you through setting up a MySQL database in Docker, creating tables, inserting data, and querying it.

-- Create acmecorpdbdatabase
CREATE DATABASE acmecorpdb;

-- Use database
USE acmecorpdb;

-- Create employees table
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
age INTEGER,
PRIMARY KEY (id)
);

-- Insert data into employees table
INSERT INTO employees (name, age)
VALUES ('Jane Smith', 35),
('Michael Johnson', 40),
('Emily Brown', 28);

-- Select data from employees table
SELECT name, age FROM employees;

PostgreSQL

PostgreSQL is a powerful open-source relational database management system known for its reliability, robustness, and extensive features.

Pull the Docker Image

Execute the following command to download the latest version of the PostgreSQL Docker image from the Docker Hub repository to your local machine.

docker pull postgres

Run the Container

Use the following command to run the PostgreSQL Docker container.

docker run --name my-postgres -e POSTGRES_PASSWORD=YOUR_PASSWORD -p 5432:5432 -d postgres

If you prefer to create a container that will retain data even if the container is stopped, deleted, or recreated run the following command instead.

docker run --name my-postgres -e POSTGRES_PASSWORD=YOUR_PASSWORD -v my-postgres-data:/var/lib/postgresql/data -d postgres
  • --name my-postgres: Names the container “my-postgres”.
  • -e POSTGRES_PASSWORD=YOUR_PASSWORD: Sets the environment variable POSTGRES_PASSWORD to “YOUR_PASSWORD”, which is required to set the initial password for the PostgreSQL root user.
  • -v my-postgres-data:/var/lib/postgresql/data: Maps a Docker volume named my-postgres-data to the directory /var/lib/postgresql/data inside the container. This directory is where PostgreSQL stores its data, so mapping it to a Docker volume ensures that the data persists.
  • -d: Runs the container in detached mode, meaning it runs in the background.
  • postgres: Specifies the Docker image to use, in this case, the official postgres image

Connect to PostgreSQL

You can use pgAdmin to connect to PostgreSQL database.
Use the following details to connect to the database:

Host: localhost
Port: 5432
Username: postgre
Password: YOUR_PASSWORD

The PostgreSQL client software is already installed in the PostgreSQL Docker container and can be accessed using the following command:

docker exec -it my-postgres psql -U postgres

Database Operations

-- Create acmecorpdb database
CREATE DATABASE acmecorpdb;

-- Use acmecorpdb database
\c acmecorpdb;

-- Create employees table
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INTEGER
);

-- Insert data into employees table
INSERT INTO employees (name, age)
VALUES ('Jane Smith', 35),
('Michael Johnson', 40),
('Emily Brown', 28);

-- Select data from employees table
SELECT name, age FROM employees;

MongoDB

MongoDB is a popular, open-source NoSQL database that offers flexibility and scalability for modern applications. Its document-oriented data model makes it easy to store and manage data in a JSON-like format.

Pull the Docker Image

docker pull mongo

Run the Container

docker run --name my-mongodb -p 27017:27017 -v mongo-data:/data/db -e MONGO_INITDB_ROOT_USERNAME=admin -e MONGO_INITDB_ROOT_PASSWORD=YOUR_PASSWORD -d mongo
  • — name my-mongodb: This option assigns a custom name to the running container (my-mongodb in this case).
  • -p 27017:27017: This maps port 27017 of the container to port 27017 on the host
  • -v mongo-data:/data/db: This option mounts a volume, associating the named volume mongo-data with the container’s /data/db directory. MongoDB stores its data files in /data/db by default.
  • -e MONGO_INITDB_ROOT_USERNAME=admin: This sets an environment variable inside the container named MONGO_INITDB_ROOT_USERNAME. Here, it’s set to admin.
  • -e MONGO_INITDB_ROOT_PASSWORD=YOUR_PASSWORD: Similar to the username, this sets an environment variable MONGO_INITDB_ROOT_PASSWORD that defines the password for the MongoDB root user that you specified with the username. Replace YOUR_PASSWORD with a strong, secure password.
  • -d: This option runs the container in detached mode, meaning the container runs in the background and does not block the terminal or command line interface from which it was started.

Connect to MongoDB

You can use MongoDB Compass to connect to MongoDB.
Use the following details to connect to the database:

Host: localhost
Port: 27017
Username: admin
Password: YOUR_PASSWORD

Connection URL:

mongodb://admin:YOUR_PASSWORD@localhost:27017/database?authSource=admin

You can use the Mongo-cli installed within the docker container as well

docker exec -it my-mongodb mongo

Database Operations

-- Use acmecorpdb
use acmecorpdb;

-- Create employees collection
db.createCollection("employees");

-- Insert data into employees collection
db.employees.insertMany([
{ name: "Jane Smith", age: 35 },
{ name: "Michael Johnson", age: 40 },
{ name: "Emily Brown", age: 28 }
]);

-- Select data from employees Collection
db.employees.find({});

Redis

Redis is an open-source, in-memory data structure store that can be used as a database, cache, and message broker. It supports various data structures such as strings, hashes, lists, sets, sorted sets with range queries, bitmaps, hyperloglogs, geospatial indexes, and streams.

Pull the Docker Image

docker pull redis

Run the Container

docker run --name my-redis -p 6379:6379 -v redis-data:/data -d redis redis-server --appendonly yes
  • — name my-redis: Names the container “my-redis”.
  • -p 6379:6379: Maps port 6379 on the host to port 6379 on the container, which is the default port used by Redis. This makes Redis accessible from outside the Docker container, allowing connections to localhost:6379 from your host machine.
  • -v redis-data:/data: Maps a Docker volume named redis-data to the /data directory inside the container. Redis will store its data in this directory if persistence is enabled, which it is in this case.
  • -d: Runs the container in detached mode, meaning it runs in the background.
  • redis: Specifies the use of the official Redis image.
  • redis-server — appendonly yes: Starts the Redis server with append-only mode enabled, which is a persistence mode that provides durability. Every write operation received by the server will be appended to the “append only file” (AOF). This file can be used to reconstruct the state of the data after a restart.

Connect to Redis

docker exec -it my-redis redis-cli

Database Operations

# Store data
SET employee:1 '{"name": "Jane Smith", "age": 35}'
SET employee:2 '{"name": "Michael Johnson", "age": 40}'
SET employee:3 '{"name": "Emily Brown", "age": 28}'

# Retrieve data
GET employee:1
GET employee:2
GET employee:3

Microsoft SQL Server

Microsoft SQL Server is a powerful relational database management system widely used in enterprise environments for storing, managing, and analyzing data

Pull the Docker Image

docker pull mcr.microsoft.com/mssql/server

Run the Container

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YOUR_PASSWORD" -p 1433:1433 --name sql_server_container -v sql_server_data:/var/opt/mssql -d mcr.microsoft.com/mssql/server
  • -e “ACCEPT_EULA=Y”: Sets an environment variable to accept the End User License Agreement, which is required to run SQL Server.
  • -e “SA_PASSWORD=YOUR_PASSWORD”: Sets the system administrator (SA) password for SQL Server. It’s important that the password meets SQL Server’s password requirements for complexity.
  • -p 1433:1433: Maps port 1433 on the host to port 1433 on the container, which is the default port used by SQL Server. This mapping makes SQL Server accessible from outside the Docker container, allowing connections to localhost:1433 from your host machine.
  • — name sql_server_container: Names the container “sql_server_container”.
  • -v sql_server_data:/var/opt/mssql: Maps a Docker volume named sql_server_data to the /var/opt/mssql directory inside the container, which is the directory where SQL Server stores its data files.
  • -d: Runs the container in detached mode, meaning it runs in the background.
    mcr.microsoft.com/mssql/server: Specifies the Docker image to use, in this case, the official Microsoft SQL Server image hosted on Microsoft Container Registry.

Connect to SQL Server

You can use SSMS or Azure Data Studio to connect to SQL Server Database.
Use the following details to connect to the database:

Host: localhost
Port: 1433
Username: SA
Password: YOUR_PASSWORD

You can use sqlcmd installed in the docker container.

docker exec -it sql_server_container sqlcmd -S localhost -U SA -P YOUR_PASSWORD

Database Operations

-- Create ACMECORPDB Database
CREATE DATABASE ACMECORPDB;

-- Use ACMECORPDB Database
USE ACMECORPDB;

-- Create employees table
CREATE TABLE employees (
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(100),
age INT
);

-- Insert data into employees table
INSERT INTO employees (name, age)
VALUES ('Jane Smith', 35),
('Michael Johnson', 40),
('Emily Brown', 28);

-- Select data from employees
SELECT name, age FROM employees;

Oracle

The Oracle Docker image we’ll be using contains an Oracle Express Edition database, which is ideal for development, testing, and small-scale production use.

Pull the Docker Image

docker pull container-registry.oracle.com/database/express:latest

Run the Container

docker run --name oracle-container -d -p 1521:1521 -e ORACLE_SID=xe -e ORACLE_PDB=xe -e ORACLE_PWD=YOUR_PASSWORD -v oracle-data:/opt/oracle/oradata container-registry.oracle.com/database/express:latest
  • — name oracle-container: Names the container “oracle-container”.
  • d: Runs the container in detached mode, meaning it runs in the background.
  • -p 1521:1521: Maps port 1521 on the host to port 1521 on the container, which is the default port used by Oracle Database. This makes the Oracle Database accessible from outside the Docker container, allowing connections to localhost:1521 from your host machine.
  • -e ORACLE_SID=xe: Sets the Oracle System ID (SID) environment variable to “xe”.
  • -e ORACLE_PDB=xe: Sets the Oracle Pluggable Database (PDB) name to “xe”.
  • -e ORACLE_PWD=YOUR_PASSWORD: Sets the password for the SYS, SYSTEM, and PDBADMIN user accounts to “YOUR_PASSWORD”.
  • -v oracle-data:/opt/oracle/oradata: Maps a Docker volume named oracle-data to the /opt/oracle/oradata directory inside the container, which is where Oracle stores its database files.

Connect to Oracle

You can use SQL Developer to connect to Oracle Database.
Use the following details to connect to the database:

Host: localhost
Port: 1521
Role: SYSDBA
Username: sys
Password: YOUR_PASSWORD
SID: xe

You can use sqlplus installed in the docker container

docker exec -it oracle-container sqlplus sys/welcome123@//localhost:1521/XE as sysdba

Database Operations


-- Create Sequence
CREATE SEQUENCE employee_seq START WITH 1 INCREMENT BY 1;

-- Create employees table
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY,
name VARCHAR2(100),
age INTEGER
);

-- Insert data into employees table
INSERT INTO employees (name, age) VALUES (employee_seq.NEXTVAL, 'Jane Smith', 35);
INSERT INTO employees (name, age) VALUES (employee_seq.NEXTVAL, 'Michael Johnson', 40);
INSERT INTO employees (name, age) VALUES (employee_seq.NEXTVAL, 'Emily Brown', 28);

-- Select data from employees
SELECT name, age FROM employees;

Conclusion

This comprehensive guide provides the steps necessary to set up a variety of popular databases within Docker containers. Leveraging Docker enhances your workflow by offering consistent, reproducible setups across different environments. Remember to prioritize security in your database configurations to protect your data effectively.

Thank you for reading this blog post. If you found the post helpful or interesting, here are a few ways you can show your support:

Your support and engagement mean a lot to me as an open-source developer.

--

--

Tshenolo Mos

Integration Developer passionate about Open Source, Cloud, and AI/ML. Sharing tech insights on my YouTube: https://www.youtube.com/@tshenolo