Snippets

Appearance

Local Database With Scripts (PostgreSQL) ​

When developing apps locally it can be really useful to have a dockerised database unique to the application.

Often rather than just running a pre-built image, you'll want to run a database with some initial data, tables, or a schema.

For this purpose we can create our own image that extends the base image and adds our own scripts.

Setup ​

For most applications the directory structure will look something like this:

database/
  Dockerfile
  scripts/
    01-create-tables.sql
development/
  compose.yml
src/
  ...
tests/
  ...

Dockerfile ​

Create a dockerfile in the database/ directory:

dockerfile
FROM postgres:17

# Setup the postgres environment variables
ENV POSTGRES_USER=myuser
ENV POSTGRES_PASSWORD=mypassword
ENV POSTGRES_DB=mydatabase

# Setup port
EXPOSE 5432

# Copy all the scripts into the container
COPY ./scripts /docker-entrypoint-initdb.d/

DANGER

As this is a local development database, we're using the a simple username and password. Do not use this in production.

Scripts ​

Create any scripts you need in the database/scripts/ directory. PostgreSQL will run these scripts in alphabetical order against the database specified in the POSTGRES_DB environment variable.

sql
CREATE TABLE MyTable (
    Id INT NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

Compose ​

Lastly we need to create a docker-compose.yml file in the development/ directory:

yaml
services:
  db:
    build:
      context: ../database
      dockerfile: Dockerfile
    volumes:
      - db-data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  db-data: