Setup containerized database

MySQL

We usually use the Percona fork of the MySQL database.

Use the following commands to set up the environment variables; make sure to replace the values if needed:

export DOCKER_NAME=percona;
export ROOT_PWD=root;
export MYSQL_PORT=3306;

To setup the database, perform the following steps:

docker run --name $DOCKER_NAME -e MYSQL_ROOT_PASSWORD=$ROOT_PWD -d -p $MYSQL_PORT:3306 percona:8.0;
docker exec -it $DOCKER_NAME mysql -uroot -p$ROOT_PWD;
CREATE DATABASE corteza;
CREATE USER 'corteza'@'172.17.0.1' IDENTIFIED BY 'corteza';
GRANT ALL PRIVILEGES ON corteza.* TO 'corteza'@'172.17.0.1';
FLUSH PRIVILEGES;

Use the following template to construct the DB_DSN .env variable:

DB_DSN="corteza:corteza@tcp(localhost:$MYSQL_PORT)/corteza?collation=utf8mb4_general_ci"

PostgreSQL

We usually use the official PostgreSQL image.

Use the following commands to set up the environment variables; make sure to replace the values if needed:

export DOCKER_NAME=pgsql2;
export ROOT_PWD=root;
export PGSQL_PORT=5432;

To setup the database, perform the following steps:

docker run --name $DOCKER_NAME -e POSTGRES_PASSWORD=$ROOT_PWD -d -p $PGSQL_PORT:5432 postgres:13;
docker exec -it $DOCKER_NAME psql -U postgres;
CREATE DATABASE corteza;
CREATE USER corteza WITH PASSWORD 'corteza';
GRANT ALL PRIVILEGES ON DATABASE corteza TO corteza;

Use the following template to construct the DB_DSN .env variable:

DB_DSN="postgres://corteza:corteza@localhost:$PGSQL_PORT/corteza?sslmode=disable"

SQLServer

This is a draft so these instructions don’t get lost; will refine at a later stage.

Use one of the following images:

SQLServer 2022: mcr.microsoft.com/mssql/server:2022-latest
SQLServer 2017: mcr.microsoft.com/mssql/server:2017-latest
SQLServer 2019: mcr.microsoft.com/mssql/server:2019-CU10-ubuntu-20.04

Example docker-compose for all three images:

  mssql_2022:
    image: mcr.microsoft.com/mssql/server:2022-latest
    restart: on-failure
    ports: [ "127.0.0.1:1433:1433" ]
    volumes:
      - "dbdata_mssql_2022:/var/opt/mssql"
    environment:
        - ACCEPT_EULA=Y
        - SA_PASSWORD=SUPERSECRET123

  mssql_2017:
    image: mcr.microsoft.com/mssql/server:2017-latest
    restart: on-failure
    ports: [ "127.0.0.1:1433:1433" ]
    volumes:
      - "dbdata_mssql_2017:/var/opt/mssql"
    environment:
        - ACCEPT_EULA=Y
        - SA_PASSWORD=SUPERSECRET123

  mssql_2019:
    image: mcr.microsoft.com/mssql/server:2019-CU10-ubuntu-20.04
    restart: on-failure
    ports: [ "127.0.0.1:1433:1433" ]
    volumes:
      - "dbdata_mssql_2019:/var/opt/mssql"
    environment:
        - ACCEPT_EULA=Y
        - SA_PASSWORD=SUPERSECRET123

To setup the database, perform the following steps:

docker compose exec -it mssql_2022 sh
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "SUPERSECRET123"
CREATE DATABASE corteza;
GO

Example DB DSN: sqlserver://sa:SUPERSECRET123@localhost:1433?database=corteza