There are three ways to have a database service for development:
- Install a database service in your system.
- Connect to a Cloud database service like AWS RDS, Google Cloud SQL, Azure SQL, etc.
- Use Docker
This note will provide some steps for the last one: Docker.
When you are using Docker, you don’t have to install a database service in your system. Just use the public database image from Docker Hub, configure, create a container from images by the requirements. And you can run multiple versions of any database if needed.
The most convenient thing when using Docker is, it’s easy to share the project with other developers to start a local development environment without configuration and deploy to any host server in any system or environment.
Before we start, you’ll need to know these terms:
- Image: An object to store the programs and the configurations
- Container: Created from an image, a close environment with its own system and configurations
- Docker Hub: Docker version of GitHub, a hub for storage images on the Cloud
Install Docker from the official website
Or use Homebrew:
brew cask install docker
We can pull the database image from Docker Hub by running
# for amd64 docker pull mysql # for arm64 docker pull mysql/mysql-server
docker pull postgres
There are different versions listed in “Tags” on Docker Hub.
You can choose the version you want, then install it by specifying the version tag:
docker pull postgres:13.2-alpine
# This equals to postgres:latest docker pull postgres
Configuration of Container
After we get the image, we can create and run a container from the image. Before doing that, we should have some configurations for our container.
The environment in a container is separate from the outside, so we can create an independent environment we need to pass the arguments into the container as parameters when we run the container.
To configure the environment variables, add the
-e flag when running the container.
Here are some basic environment variables for MySQL and PostgreSQL:
MYSQL_ROOT_PASSWORD: Required. For setting the password of the superuser(
MYSQL_DATABASE: Optional. Set the database name when creating the container.
MYSQL_PASSWORD: Optional. Create a new user with the password. The user will be the superuser of the database above. See GRANT for more information.
POSTGRES_USER: Optional. The username of the superuser. Default to
POSTGRES_PASSWORD: Required. For setting the password of the superuser.
POSTGRES_DB: The database name when you first launch the container, the default value will equal to
Launch the Container
Run the following command to create and launch the container:
Specify the username, password, and database name:
docker run --name <container_name> -p 3306:3306 -e MYSQL_ROOT_PASSWORD=<root_password> -e MYSQL_USER=<username> -e MYSQL_PASSWORD=<user_password> -e MYSQL_DATABASE=<database_name> -d mysql
*You can replace
mysql/mysql-server for arm64
Use the default superuser, which is
docker run --name <container_name> -p 3306:3306 -e MYSQL_ROOT_PASSWORD=<root_password> -d mysql
Specify the username, password, and database name:
docker run --name <container_name> -p 5432:5432 -e POSTGRES_USER=<username> -e POSTGRES_PASSWORD=<user_password> -e POSTGRES_DB=<database_name> -d postgres
--name: Set the container name
-p: Publish the port, map the port inside and outside the container, so we can reach the port of the database from outside. MySQL uses
3306and PostgreSQL uses
-d: Run the container in the background
For more information about
docker run please visit the Official Document.
You can see the running container by running the following command:
To inspect the log of a running container:
docker logs <container_name> # You can also use the container ID instead of the container name
Execute Commands in A Container
To start a shell inside the container:
# bash docker exec -it <container_name> bash # use `/bin/bash` if `bash` doesn't work # sh docker exec -it <container_name> /bin/sh
Then you can see the prompt of the command line. Once you get into the shell, you can run any commands in the container.
To access Postgres inside the container from the shell:
psql <database> <username>
<username> by your own configurations.
Connection Issue of MySQL
If you want to access MySQL, you may need to use
root with the flag
-p to tell docker to use the external port for connection to the internal port.
When we connect to the MySQL inside the container, MySQL recognizes it as external access, not a local one. So if we would like to access the database as root, we should get into the shell inside the container.
If we want to access MySQL from outside as a superuser, we should create another one as root.
To run the command to access MySQL as root:
mysql -u root -p
Now we can create another superuser for MySQL:
-- MySQL 5.* CREATE USER '<username>'@'%' IDENTIFIED BY '<password>'; -- MySQL 8.* CREATE USER '<username>'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
% means to allow the user we created can be used for an external connection, or you can replace it with IP addresses as a whitelist.
Grant the superuser permission to a user:
GRANT ALL ON "database_name".* TO <username>@'%';
You can also give an existing user the permission to be used for an external connection:
UPDATE mysql.user SET HOST='%' WHERE USER = "username" LIMIT 1;
The last step, if we want to let external users execute the functions inside MySQL, run the following command:
SET GLOBAL log_bin_trust_function_creators=1;
Using Docker to create a database container for development can avoid environmental issues. You should try it!