Creating databases when starting the Postgres Docker container

One of the first things we usually ask people to do when they set up a project is to create the needed databases. While we often directly use the default database for development to avoid that problem, it remains that we would like at least one other database for testing purposes. Using the same database for development and testing is a nightmare of data inconsistencies.

The PostgreSQL Docker container allows a nice trick to avoid any manual steps. It’s entry point, the script that is run when the container starts, looks for scripts inside a specific directory, /docker-entrypoint-initdb.d, and runs them.

You can mount an SQL script to create the testing database if it doesn’t exist yet.
To create a ‘testing’ database automatically, you can use the following script:

SELECT 'CREATE DATABASE testing'  
    WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'testing')\gexec

And here is an example of what to change in à docker-compose.yaml file:

db:  
    image: postgres:latest
    volumes:    
        - ./infrastructure/database/pgsql/create-testing-database.sql:/docker-entrypoint-initdb.d/10-create-testing-database.sql

The entry point only runs the scripts when the storage volume is empty

Adding a script to create the testing database on an existing project where the database container already has a volume will not work. Two solutions here: run the script by hand or drop the volume and restart the container.

I learned that trick working on a project using Laravel Sail, so thank you to them!