How to use databases in the Docker world

We can run DB engines locally on our machine without installing them by using Docker containers.

💡 Remember that the container's data is removed with the container itself, so, we're going to store the DB data into a specific Docker volume (--volume) to keep it through time.

MongoDB

We can start a MongoDB instance with this simple command:

docker run --detach --name mongo \
  --publish 27017:27017 \
  --volume mongo:/data/db \
  mongo

This starts a Docker container that runs the mongod process and listens on the default MongoDB port 27017. By default MongoDB uses the test database and only accepts connections from localhost without authentication.

The Docker run command

Let's explain the docker run arguments:

1. --detach runs the container in background, like a service.
2. --name assigns a specific name to the created container.
3. --publish forwards a specific port in localhost to the given container.
4. --volume creates or reuses a volume and attaches it to the given container at the specified path.

By default MongoDB uses the /data/db directory to store the databases (remember to mount a Docker volume in that path).

A MongoDB root user

You can specify a root user to the DB adding the following environment variables to your the container

docker run --detach --name mongo \
  --env MONGO_INITDB_ROOT_USERNAME=mongoadmin \
  --env MONGO_INITDB_ROOT_PASSWORD=secret \
  --publish 27017:27017 \
  --volume mongo:/data/db \
  mongo

For more variables check the mongo image page in Docker hub.

Using the MongoDB shell

You can connect to your DB via terminal by using the mongosh command in the same container.

docker exec -it mongo mongosh

Use --username and --password if those values are required.

How to update the engine

We can update a DB instance easily if we have previously stored the DB data in a Docker volume‼️ This is important, otherwise we'll lose data.

1. Pull the latest MongoDB image
   

   docker pull mongo
   

2. First stop and remove the Docker container
   

   docker stop mongo
   docker rm mongo
   

3. Recreate the container with the same given arguments, remember to include the --env variables (in this case, just MONGO_INITDB_ROOT_USERNAME and MONGO_INITDB_ROOT_PASSWORD).
   

   docker run --detach --name mongo \
     --publish 27017:27017 \
     --volume mongo:/data/db \
     mongo
   

It's done! your database is now up to date. 🙌

PostgreSQL

For Postgres remember to set the root user password.

docker run --detach --name pg \
  --env POSTGRES_PASSWORD=secret \
  --volume pgdata:/var/lib/postgresql/data \
  --publish 5432:5432 \
  postgres

By default the username is postgres but you can override that value using the POSTGRES_USER variable.

For more variables check the Docker Hub page.

Connecting via terminal

You can connect via terminal using the same container with the correct username instead of installing the client on your machine.

docker exec -it pg psql --username=postgres

The password is not required to connections from the same host.

MySQL

MySQL follows the Postgres rules with

docker run --detach --name mysql \
  --env MYSQL_ROOT_PASSWORD=secret \
  --volume mysql:/var/lib/mysql \
  --publish 3306:3306 \
  mysql

More information in Docker hub.

Connecting via terminal

docker exec -it mysql mysql -p

The client will prompt for the root password.

Microsoft SQL Server

Microsoft needs that you declare that you have a license to the SQL Server that you want to use with ACCEPT_EULA=Y. 🙄

docker run --name mssql \
  --env ACCEPT_EULA=Y \
  --env 'SA_PASSWORD=yourStrong(!)Password' \
  --volume mssql:/var/opt/mssql \
  --publish 1433:1433 \
  mcr.microsoft.com/mssql/server

Also, the SA password has stronger constraints than the other engines.

SQL Server editions

By default the Developer edition is the SQL Server edition used by this Docker image. If you need a SQL Express edition you should use MSSQL_PID=Express.

docker run --name mssql \
  --env ACCEPT_EULA=Y \
  --env MSSQL_PID=Express \
  --env 'SA_PASSWORD=yourStrong(!)Password' \
  --volume mssql:/var/opt/mssql \
  --publish 1433:1433 \
  mcr.microsoft.com/mssql/server

More information about SQL server editions in Docker hub.

Connecting via terminal

The sqlcmd command is not in PATH, so you should use the absolute path to execute it inside the container.

docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -U sa -P 'yourStrong(!)Password'

Conclusion

Now, we know how to handle MongoDB and some SQL engines using Docker containers, using volumes to store the data.

We can connect to the database terminal using the container itself, and we must use Docker volume to prevent data loss.