Issues with creating a containerised Next.js + Flask + PostgreSQL application

Question

Problem:

I've been trying to containerise my application consisting of a Next.js frontend, Flask backend and PostgreSQL database but I've been having a lot of problems. The main issue right now is that my PostgreSQL schema is not being loaded up correctly.

Directory Structure:

|-- docker-compose.yaml
|-- backend
|   |-- Dockerfile
|   `-- app.py
|-- database
|   |-- 01-schema.sql
|   |-- 02-init.sql
|   `-- Dockerfile
`-- frontend
    |-- Dockerfile
    `-- (...Next.js App Files)

docker-compose.yaml:

services:
  database:
    image: postgres:latest
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=admin
    ports:
      - "5432:5432"
    volumes:
      - data:/var/lib/postgresql/data
      - ./database/1-schema.sql:/docker-entrypoint-initdb.d/01-schema.sql
      - ./database/2-init.sql:/docker-entrypoint-initdb.d/02-init.sql

volumes:
  data:

SQL Scripts:

01-schema.sql

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
);

02-init.sql

INSERT INTO users (username) VALUES ("John Doe");
INSERT INTO users (username) VALUES ("Jane Doe");

app.py:

from flask import Flask
import psycopg2

app = Flask(__name__)

@app.route("/api")
def index():
    conn = psycopg2.connect("postgresql://postgres:admin@localhost:5432/postgres")

    cursor = conn.cursor()

    cursor.execute("SELECT * FROM users;")
    result = cursor.fetchone()

    conn.commit()
    conn.close()

    return result

When I run docker-compose up I get the following error message:

psql:/docker-entrypoint-initdb.d/01-schema.sql: error: could not read from input file: Is a directory

exited with code 1

And here is the full output:

Output-1 Output-2

Then when I try to access the results of my query in app.py, by going to http://127.0.0.1:5000/api I get an error saying the relation doesn't exist:

Flask Output

The database successfully received the query but also says the relation doesn't exist:

ERROR: relation "users" does not exist at character 15

STATEMENT: SELECT * FROM users;

Answer:

Below is the working configuration for my project.

Directory Structure:

|-- .env
|-- docker-compose.yaml
|-- backend
|   |-- .env
|   |-- Dockerfile
|   |-- app.py
|   `-- dependencies.txt
|-- database
|   |-- 01-schema.sql
|   |-- 02-init.sql
|   `-- Dockerfile
`-- frontend
    |-- Dockerfile
    `-- (...Next.js App Files)

docker-compose.yaml:

services:
  frontend:
    container_name: frontend
    build: ./frontend
    image: node:18-alpine
    restart: always
    ports:
      - "3000:3000"
    volumes:
      - ./frontend/:/app
      - /app/node_modules
      - /app/.next

  backend:
    container_name: backend
    build: ./backend
    image: flask:2.2.3
    restart: always
    ports:
      - "5000:5000"
    volumes:
      - ./backend/:/backend
    depends_on:
      database:
        condition: service_healthy

  database:
    container_name: database
    build: ./database
    image: postgres:15.2-alpine
    restart: always
    ports:
      - "5432:5432"
    volumes:
      - data:/var/lib/postgresql/data
    env_file:
      - ./.env
    healthcheck:
      test:
        ["CMD", "pg_isready", "-U", "${POSTGRES_USER}", "-d", "${POSTGRES_DB}"]
      interval: 5s
      timeout: 10s
      retries: 120

volumes:
  data: null

Flask Dockerfile:

FROM python:3.11.2-slim-buster

WORKDIR /backend

ENV FLASK_APP app.py
ENV FLASK_RUN_HOST 0.0.0.0
ENV FLASK_DEBUG 1

ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

COPY dependencies.txt .
COPY .env .

RUN pip install --upgrade pip && pip install -r dependencies.txt --no-cache-dir

COPY . .

CMD ["flask", "run"]

Database Dockerfile:

FROM postgres:15.2-alpine

COPY ./*.sql /docker-entrypoint-initdb.d/

Frontend Dockerfile:

FROM node:18-alpine AS base

# Install dependencies only when needed
FROM base AS deps
RUN apk add --no-cache libc6-compat
WORKDIR /app

COPY package.json package-lock.json ./

# Uncomment for production build
# RUN npm install --omit=dev
RUN npm install --legacy-peer-deps

# Rebuild the source code only when needed
FROM base AS builder
WORKDIR /app
COPY --from=deps /app/node_modules ./node_modules
COPY . .

ENV NEXT_TELEMETRY_DISABLED 1

RUN npm run build

FROM base AS runner
WORKDIR /app

ENV NEXT_TELEMETRY_DISABLED 1

COPY --from=builder --chown=nextjs:nodejs /app/.next ./.next
COPY --from=builder /app/node_modules ./node_modules
COPY --from=builder /app/package.json ./package.json

EXPOSE 3000

CMD ["npm", "start"]

.env File:

POSTGRES_DB=docker
POSTGRES_USER=docker
POSTGRES_PASSWORD=docker
POSTGRES_HOST=database
POSTGRES_PORT=5432

app.py:

import psycopg2

# Note: I loaded the environment variables using python-dotenv
conn = psycopg2.connect(
    database=env.POSTGRES_DB,
    user=env.POSTGRES_USER,
    password=env.POSTGRES_PASSWORD,
    host=env.POSTGRES_HOST,
    port=env.POSTGRES_PORT,
)

cur = conn.cursor()
...

dependencies.txt:

flask
psycopg2-binary
python-dotenv

Build the containers using docker-compose up --build and reset using docker-compose down --volumes. Start the containers using docker-compose up.

Lingering Issues

1. I did not manage to get hot reloading to work for the frontend without using yarn which broke some things.

2. I couldn't figure out how to get the .env file from the main directory into the container for the backend using COPY in the Dockerfile so I just made a manual copy.

Answer 1

The culprit should be this part of your compose file:

volumes:
  - data:/var/lib/postgresql/data
  - ./database/1-schema.sql:/docker-entrypoint-initdb.d/01-schema.sql
  - ./database/2-init.sql:/docker-entrypoint-initdb.d/02-init.sql

As this other answer also says, if the file you're trying to mount does not exist, docker will create an empty directory inside the container, and so that error message with "is a directory".

Please verify that the file exists, the path is right and also try to replace the relative path with an absolute path. If that doesn't work either, try mounting the entire directory, like this:

  - ./database/:/docker-entrypoint-initdb.d/