容器化Next.js+Flask+PostgreSQL应用的数据库架构加载问题

尝试将由Next.js前端、Flask后端和PostgreSQL数据库组成的应用进行容器化，核心问题是PostgreSQL的数据库架构无法正确加载，同时伴随其他相关错误。

当前配置 #

目录结构 #

|-- docker-compose.yaml
|-- backend
|   |-- Dockerfile
|   `-- app.py
|-- database
|   |-- 01-schema.sql
|   |-- 02-init.sql
|   `-- Dockerfile
`-- frontend
    |-- Dockerfile
    `-- (...Next.js应用文件)

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脚本 #

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

错误现象 #

1. 运行docker-compose up时，出现错误：

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

服务退出码为1。
2. 访问http://127.0.0.1:5000/api调用查询时，Flask返回错误，数据库提示：

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

可行配置方案 #

以下是调整后的可用配置：

优化后目录结构 #

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

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文件 #

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

调整后app.py #

import psycopg2

# Note: 环境变量通过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

操作命令：

• 构建容器：docker-compose up --build
• 重置环境：docker-compose down --volumes
• 启动容器：docker-compose up

遗留问题 #

• 未实现前端热重载，使用yarn会引发其他问题。
• 无法通过Dockerfile的COPY指令将根目录的.env文件传入后端容器，只能手动复制。

内容的提问来源于stack exchange，提问作者Orcadeum