PostgreSQL on Docker Compose

This directory boots up a PostgreSQL container using docker compose, intended for local development purposes.

Goal

This is a template to develop against a real database in an easy way, instead of an in-memory one like H2 or HSQL. Using containers and environment variables where possible, the goal is to stop maintaining local v. test v. production configuration, code and runtime profiles in your application (see Store config in the environment in The Twelve-Factor App).

Initialisation

The database schema and data is populated by the application project during its first bootstrap, assuming we're intending to build an application capable to do so.

• Copy the files .env.example to .env and .env.db.example to .env.db.
• Modify the values of .env and .env.db as appropriate.
• Do not change the names of the variables.
• Create the directory mapped as a volume for the data, mkdir -p data

Container Name, Image Version and User

Check the content of the file .env.

Database

The container is provided with a small script in ./initdb/initdb/init-user-db.sh that will create the application's database, username and password. It will also grant privileges to the user in the database, but also in the schema public, so that the user can create tables (needed since PostgreSQL 15).

This way, an application can modify (e.g.: initialise) the database schema using an application user instead of the super user (postgres).

The values used in the aforementioned script are set in the file .env.db, after the super-user's (postgres') password:

## Application Database Init
APP_DB="application"
APP_USER="application"
APP_PASSWORD="changeme-application"

Nothing more is initalised. The database schema and data will be populated by the application project during its first bootstrap. At subsequent bootstraps the database should not be re-initialised unless we delete it manually.

If this is not what you want, and you intend to initialise the schema differently, edit the ./initdb/initdb/init-user-db.sh to remove or comment the following lines:

	\c ${APP_DB}
	GRANT USAGE ON SCHEMA public TO ${APP_USER};
	GRANT CREATE ON SCHEMA public TO ${APP_USER};

Configure your IDE

If you're using an Integrated Development Environment that supports environment variables, add the following environment variables to your launch configurations to connect your debugging sessions with this database:

Name	With Value	Example
APP_DB	see file .env.db	application
APP_USERNAME	see file .env.db	hamapplicationster
APP_PASSWORD	see file .env.db	changeme-application
DB_HOST	localhost
DB_PORT	see file .env	5432

⚠️ Refer to those variable names, with the same name, in the application configuration