Docker API Platform from PostgreSQL to MySQL
Developers understand that PostgreSQL and MySQL are relational database management systems. A closer distinction would classify PostgreSQL as an object-relational database and MySQL as a purely relational database. However, this article is not to decide which is better or even to discuss their major differences or benefits. It is to help developers change the default PostgreSQL to MySQL in API Platform.
Some Background
Docker is a platform that helps developers build and run containers on physical hardware. Docker containers are built up of various images with specific configurations. As an example, there is a Docker image for PHP which has a specific PHP Version along with the PHP runtime. These configurations help build a whole environment to run an individual project.
We are using React Admin and API Platform in this project. React Admin and API Platform work simultaneously to retrieve and show data. React Admin is a front end framework in React that uses different components and hooks for quick development. API Platform is a framework that helps with building and managing APIs in PHP. Using both of these tools helps for easy configuration and bootstrapping of a project.
In this example, we are using Docker API Platform Version 3.0.10. This includes API Platform and React Admin configuration all set up that can be found here.
Docker Configuration Adjustments
Within this configuration it has docker-compose yaml files which we would like to convert to MySQL. Follow along:
docker-compose.yml
First we have to change the image for docker to be mysql.
database:
image: postgres:${POSTGRES_VERSION:-14}-alpine
environment:
- POSTGRES_DB=${POSTGRES_DB:-app}
# You should definitely change the password in production
- POSTGRES_PASSWORD=${POSTGRES_PASSWORD:-!ChangeMe!}
- POSTGRES_USER=${POSTGRES_USER:-app}
volumes:
- db_data:/var/lib/postgresql/data
# you may use a bind-mounted host directory instead, so that it is harder to accidentally remove the volume and lose all your data!
# - ./api/docker/db/data:/var/lib/postgresql/data
Alt text: Step 1 in converting PostgreSQL to MySQL in Docker API platform.
database:
image: mysql
command: --default-authentication-plugin=mysql_native_password
restart: always
environment:
- MYSQL_DATABASE=app
- MYSQL_ROOT_PASSWORD=!ChangeMe!
- MYSQL_USER=app
- MYSQL_PASSWORD=!ChangeMe!
volumes:
- db_data:/var/lib/mysql:rw
# you may use a bind-mounted host directory instead, so that it is harder to accidentally remove the volume and lose all your data!
# - ./api/docker/db/data:/var/lib/mysql:rw
ports:
- target: 3306
published: 3306
protocol: tcp
Alt text: Step 2 in converting PostgreSQL to MySQL in Docker API platform.
docker-compose.override.yml
Next we have to change the ports on the docker compose override document.
database:
ports:
- target: 5432
published: 5432
protocol: tcp
Alt text: Step 3 in converting PostgreSQL to MySQL in Docker API platform.
database:
ports:
- target: 3306
published: 3306
protocol: tcp
Alt text: Step 4 in converting PostgreSQL to MySQL in Docker API platform.
api/Dockerfile
In the Dockerfile we have to install PDO_MySQL to enable access from PHP to MySQL.
RUN set -eux; \
install-php-extensions pdo_pgsql
Alt text: Step 5 in converting PostgreSQL to MySQL in Docker API platform.
RUN set -eux; \
install-php-extensions pdo_mysql
Alt text: Step 6 in converting PostgreSQL to MySQL in Docker API platform.
api/.env
Lastly we have to change the environment variables on the .env file to be MySQL.
DATABASE_URL="postgresql://app:[email protected]:5432/app?serverVersion=15&charset=utf8"
Alt text: Step 7 in converting PostgreSQL to MySQL in Docker API platform.
DATABASE_URL="mysql://app:[email protected]:3306/app?serverVersion=8&charset=utf8"
Alt text: Step 8 in converting PostgreSQL to MySQL in Docker API platform.
Simple and Clean Process
There you have it! Overall, if you can follow these steps it is very simple to switch from PostgreSQL to MySQL. Visit our blog for more interesting solutions and information.