Docker-compose Build nextjs crashes on db pool

Sorry for a lot of text but ive tried to provide all details because ive spended hours of debugging and i guess im missing something hope you can help me with this problem
GHA .yml:

name: Build and Push Docker Images

on:
  push:
    branches:
      - main

jobs:
  build:
    runs-on: ubuntu-latest

    services:
      docker:
        image: docker:19.03.12
        options: --privileged
        ports:
          - 2375:2375
        env:
          DOCKER_TLS_CERTDIR: ""

    steps:
    - name: Checkout code
      uses: actions/checkout@v2

    - name: Set up Docker Buildx
      uses: docker/setup-buildx-action@v1

    - name: Login to Docker Hub
      uses: docker/login-action@v2
      with:
        username: ${{ secrets.DOCKER_USERNAME }}
        password: ${{ secrets.DOCKER_PASSWORD }}

    - name: Create .env file for docker-compose
      run: echo "${{ secrets.MAIN_ENV }}" >> .env
      working-directory: ./

    - name: Create external network if not exists
      run: docker network create my_net || true

    - name: Start Database Service
      run: |
        docker-compose -f docker-compose-dev.yaml up -d dbdwh

    - name: Wait for Database to be ready
      run: |
        echo "Waiting for dbdwh to be ready..."
        for i in {1..30}; do
          docker run --network=my_net --rm curlimages/curl:7.73.0 -s http://dbdwh:5432 && break || sleep 1
        done

    - name: Build and Push Docker Images
      run: |
        SERVICES="python-parser strapi-dashboard ses-crm"
        for SERVICE in $SERVICES
        do
          IMAGE_NAME="toxicblade/next_crm_project:$SERVICE-latest"
          echo "Building and pushing $IMAGE_NAME"
          docker-compose -f docker-compose-dev.yaml build $SERVICE
          docker tag $SERVICE:latest $IMAGE_NAME
          docker push $IMAGE_NAME
        done

I have this git hub actions builder

it uses this docker-compose file:

version: '3'
services:
  dbdt:
    image: postgres:15.4
    container_name: strapi-db
    restart: always
    env_file:
      - .env
    ports:
      - '5433:5432'
    environment:
      POSTGRES_USER: ${DATABASE_USERNAME}
      POSTGRES_PASSWORD: ${DATABASE_PASSWORD}
      POSTGRES_DB: ${DATABASE_NAME}
    volumes:
      - postgres-data:/var/lib/postgresql/data
    networks:
      - my_net

  dbdwh:
    image: postgres:15.4
    container_name: dbdwh
    restart: always
    env_file:
      - .env
    ports:
      - '5434:5432'
    environment:
      POSTGRES_USER: ${DWH_USERNAME}
      POSTGRES_PASSWORD: ${DWH_PASSWORD}
      POSTGRES_DB: ${DWH_NAME}
    volumes:
      - ./dwh.sql:/docker-entrypoint-initdb.d/dwh.sql
      - postgres-dwh:/var/lib/postgresql/data
    networks:
      - my_net

  python-parser:
    build: ./python_parser/
    image: python-parser
    container_name:  python-parser
    restart: always
    env_file: .env
    environment:
      DATABASE_CLIENT: ${DATABASE_CLIENT}
      DATABASE_HOST: dbdt
      DATABASE_PORT: ${DATABASE_PORT}
      DATABASE_NAME: ${DATABASE_NAME}
      DATABASE_USERNAME: ${DATABASE_USERNAME}
      DATABASE_PASSWORD: ${DATABASE_PASSWORD}
      SMTP_EMAIL: ${SMTP_EMAIL}
      SMTP_PASSWORD: ${SMTP_PASSWORD}
      GPT_API_KEY: ${GPT_API_KEY}
    ports:
      - "4000:80"
    volumes:
      - .:/usr/src/app
    depends_on:
      - dbdt
    networks:
      - my_net


  strapi-dashboard:
    container_name: strapi-dashboard
    build: ./strapi/
    image: strapi-dashboard
    restart: unless-stopped
    env_file: .env
    environment:
      DATABASE_CLIENT: ${DATABASE_CLIENT}
      DATABASE_HOST: dbdt
      DATABASE_PORT: ${DATABASE_PORT}
      DATABASE_NAME: ${DATABASE_NAME}
      DATABASE_USERNAME: ${DATABASE_USERNAME}
      DATABASE_PASSWORD: ${DATABASE_PASSWORD}
      JWT_SECRET: ${JWT_SECRET}
      ADMIN_JWT_SECRET: ${ADMIN_JWT_SECRET}
      APP_KEYS: ${APP_KEYS}
      NODE_ENV: ${NODE_ENV}
    ports:
      - '1337:1337'
    depends_on:
      - dbdt
    volumes:
      - ./strapi/src:/opt/app/src
      # - strapi-data:/opt/app/public
    networks:
      - my_net

  ses-crm:
    container_name: ses-crm
    image: ses-crm
    restart: unless-stopped
    build: ./next_crm/
    env_file:
      - .env
    environment:
      BASE_URL: ${BASE_URL}
      NEXT_PUBLIC_BASE_URL: ${NEXT_PUBLIC_BASE_URL}
      STRAPI_API_TOKEN: ${STRAPI_API_TOKEN}
      NEXT_PUBLIC_STRAPI_API_TOKEN: ${NEXT_PUBLIC_STRAPI_API_TOKEN}
      BACKEND_URL: ${BACKEND_URL}
      NEXT_PUBLIC_BACKEND_URL: ${NEXT_PUBLIC_BACKEND_URL}
      AUTH_SECRET: ${AUTH_SECRET}
      AUTH_SECRET1: ${AUTH_SECRET1}
      AUTH_URL: ${AUTH_URL}
      AUTH_GOOGLE_ID: ${AUTH_GOOGLE_ID}
      AUTH_GOOGLE_SECRET: ${AUTH_GOOGLE_SECRET}
      AUTH_TRUST_HOST: ${AUTH_TRUST_HOST}
      DATABASE_HOST: dbdt
      DATABASE_NAME: ${DATABASE_NAME}
      DATABASE_USERNAME: ${DATABASE_USERNAME}
      DATABASE_PASSWORD: ${DATABASE_PASSWORD}
      WAREHOUSE_HOST: dbdwh
      DWH_NAME: ${DWH_NAME}
      DWH_USERNAME: ${DWH_USERNAME}
      DWH_PASSWORD: ${DWH_PASSWORD}

    ports:
      - '3000:3000'
    networks:
      - my_net
    depends_on:
      - strapi-dashboard

  n8n:
    container_name: ses-n8n
    image: n8nio/n8n
    restart: always
    env_file:
      - .env
    environment:
      N8N_OAUTH_CERTIFICATE_CLIENT_ID: ${N8N_OAUTH_CERTIFICATE_CLIENT_ID}
      N8N_OAUTH_CERTIFICATE_CLIENT_SECRET: ${N8N_OAUTH_CERTIFICATE_CLIENT_SECRET}
      N8N_SECURE_COOKIE: ${N8N_SECURE_COOKIE}
      N8N_STRAPI_API_TOKEN: ${N8N_STRAPI_API_TOKEN}
      N8N_STRAPI_URL: ${N8N_STRAPI_URL}
    ports:
      - '5678:5678'
    volumes:
      - n8n-data:/home/node/.n8n
    networks:
      - my_net

  caddy:
    image: caddy:2-alpine
    container_name: caddy
    restart: unless-stopped
    ports:
      - "80:80"
      - "443:443"
      - "443:443/udp"
    volumes:
      - /opt/next_crm_project-main/caddy/Caddyfile:/etc/caddy/Caddyfile
      - /var/docker_volumes/caddy/data:/data
      - /var/docker_volumes/caddy/config:/config
    environment:
      - CADDY_EMAIL=mail
    networks:
      - my_net

volumes:
  n8n-data:
  strapi-data:
  postgres-data:
  postgres-dwh:

networks:
  my_net:
    external: true

I have this connection in my crm :

import { Pool } from 'pg';

const poolConfig = {
  user: process.env.DWH_USERNAME,
  host: process.env.WAREHOUSE_HOST,
  database: process.env.DWH_NAME,
  password: process.env.DWH_PASSWORD,
  port: 5432,
};

const pool = process.env.CI === 'true' ? null : new Pool(poolConfig);

export default pool;

and api routes like this:

import { NextRequest } from 'next/server';
import pool from '@/lib/pool';

export async function GET(request: NextRequest) {

  if (!pool) {
    return new Response(JSON.stringify('Error during connection'), {
      status: 500,
      headers: { 'Content-Type': 'application/json' },
    });
  }

  try {
    const client = await pool.connect();
    const result = await client.query(`
    WITH sales_counts AS (
      SELECT
          COUNT(*) AS current_month_sales,
          NULL::int AS previous_month_sales
      FROM sales
      WHERE date_trunc('month', date) = date_trunc('month', CURRENT_DATE)
      UNION ALL
      SELECT
          NULL::int AS current_month_sales,
          COUNT(*) AS previous_month_sales
      FROM sales
      WHERE date_trunc('month', date) = date_trunc('month', CURRENT_DATE - INTERVAL '1 month')
  )
  SELECT
      MAX(current_month_sales) AS current_month_sales,
      MAX(previous_month_sales) AS previous_month_sales,
      (MAX(current_month_sales) - MAX(previous_month_sales)) AS difference,
      CASE
          WHEN (MAX(current_month_sales) - MAX(previous_month_sales)) > 0 THEN
              '+' || (MAX(current_month_sales) - MAX(previous_month_sales))::text
          WHEN (MAX(current_month_sales) - MAX(previous_month_sales)) = 0 THEN
              '+0'
          ELSE
              (MAX(current_month_sales) - MAX(previous_month_sales))::text
      END AS difference_with_sign
  FROM sales_counts;




    `);
    client.release();
    return new Response(JSON.stringify(result.rows), {
      status: 200,
      headers: { 'Content-Type': 'application/json' },
    });
  } catch (err) {
    console.error(err);
    return new Response(JSON.stringify('Error during connection'), {
      status: 500,
      headers: { 'Content-Type': 'application/json' },
    });
  }
}

I pass in env this:

CI=true
WAREHOUSE_HOST=dbdwh (this is how called my docker service)
DWH_NAME=database db
DWH_USERNAME=username
DWH_PASSWORD=password

During build process when static pages is generated and api routes:

Error: connect ECONNREFUSED 127.0.0.1:5432
#16 52.89     at /app/node_modules/pg-pool/index.js:45:11
#16 52.89     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
#16 52.89     at async p (/app/.next/server/app/api/analytics/manager_analyse/route.js:1:610)
#16 52.89     at async /app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:6:42484
#16 52.89     at async eI.execute (/app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:6:32486)
#16 52.89     at async eI.handle (/app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:6:43737)
#16 52.89     at async exportAppRoute (/app/node_modules/next/dist/export/routes/app-route.js:77:26)
#16 52.89     at async exportPageImpl (/app/node_modules/next/dist/export/worker.js:175:20)
#16 52.89     at async /app/node_modules/next/dist/export/worker.js:239:16
#16 52.89     at async Span.traceAsyncFn (/app/node_modules/next/dist/trace/trace.js:151:20) {
#16 52.89   errno: -111,
#16 52.89   code: 'ECONNREFUSED',
#16 52.89   syscall: 'connect',
#16 52.89   address: '127.0.0.1',
#16 52.89   port: 5432
#16 52.89 }
#16 53.03 Error: connect ECONNREFUSED 127.0.0.1:5432
#16 53.03     at /app/node_modules/pg-pool/index.js:45:11
#16 53.03     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
#16 53.03     at async p (/app/.next/server/app/api/analytics/region_analyse/route.js:1:610)
#16 53.03     at async /app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:6:42484
#16 53.03     at async eI.execute (/app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:6:32486)
#16 53.03     at async eI.handle (/app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:6:43737)
#16 53.03     at async exportAppRoute (/app/node_modules/next/dist/export/routes/app-route.js:77:26)
#16 53.03     at async exportPageImpl (/app/node_modules/next/dist/export/worker.js:175:20)
#16 53.03     at async /app/node_modules/next/dist/export/worker.js:239:16
#16 53.03     at async Span.traceAsyncFn (/app/node_modules/next/dist/trace/trace.js:151:20) {
#16 53.03   errno: -111,
#16 53.03   code: 'ECONNREFUSED',
#16 53.03   syscall: 'connect',
#16 53.03   address: '127.0.0.1',
#16 53.03   port: 5432
#16 53.03 }
#16 53.04 

Firstly for some reason it adresses to local host and not to dbdwh and scondly it says that econrefused. I’ve tried even to up a dbdwh service in github actions to ensure that he is up.
If i trie to build locally even when dbdwh is down everything working fine, but here for some reason not.
Docker file for next app if needed:

` FROM node:18-alpine AS deps

# Check https://github.com/nodejs/docker-node/tree/b4117f9333da4138b03a546ec926ef50a31506c3#nodealpine to understand why libc6-compat might be needed.

RUN apk add --no-cache libc6-compat

WORKDIR /app

# Install dependencies based on the preferred package manager

COPY package.json yarn.lock* package-lock.json* pnpm-lock.yaml* ./

RUN \

if [ -f yarn.lock ]; then yarn --frozen-lockfile; \

elif [ -f package-lock.json ]; then npm ci; \

elif [ -f pnpm-lock.yaml ]; then yarn global add pnpm && pnpm i; \

else echo "Lockfile not found." && exit 1; \

fi

# 2. Rebuild the source code only when needed

FROM node:18-alpine AS builder

WORKDIR /app

COPY --from=deps /app/node_modules ./node_modules

COPY . .

# This will do the trick, use the corresponding env file for each environment.

RUN yarn build

# 3. Production image, copy all the files and run next

FROM node:18-alpine AS runner

WORKDIR /app

ENV NODE_ENV=production

#--- next 2 line is chrome engine for puppeteer library which is used to convert html to pdf

RUN apk add --no-cache chromium

ENV PUPPETEER_EXECUTABLE_PATH=/usr/bin/chromium-browser

RUN addgroup -g 1001 -S nodejs

RUN adduser -S nextjs -u 1001

COPY --from=builder /app/public ./public

# Automatically leverage output traces to reduce image size

# https://nextjs.org/docs/advanced-features/output-file-tracing

COPY --from=builder --chown=nextjs:nodejs /app/.next/standalone ./

COPY --from=builder --chown=nextjs:nodejs /app/.next/static ./.next/static

USER nextjs

EXPOSE 3000

ENV PORT 3000

#RUN npm install pm2 -g

CMD ["node", "server.js"]`

i use nextjs 14

You have a build pipeline, a huge docker-compose.yml and also bind mount external config files. That seems complex :sweat_smile:

Try to break it down. Which container fails, try to add some debug to output the env vars, to see if they are set correctly.

1 Like

I’ve spen 1 more day of debbuging let me paste code which is only needed and explain a littile bit more

.env is loaded correctly i fixed that it tried to connect in localhost instead of dbdwh(my container)

now i use prisma instead of pg for better debuging and general use
it connects via url so doesnt changed a lot


DATABASE_URL="postgresql://name:password@dbdwh:5432/db-name"

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

And i missed 1 thing, docker-compose doesnt build neither locally for me

from docker compose we are interested only in 2 services:

   ses-crm:
    container_name: ses-crm
    image: ses-crm
    restart: unless-stopped
    build:
      context: ./next_crm
      dockerfile: Dockerfile
      args:
        - DATABASE_URL=${DATABASE_URL}
    env_file:
      - .env
    environment:
      BASE_URL: ${BASE_URL}
      NEXT_PUBLIC_BASE_URL: ${NEXT_PUBLIC_BASE_URL}
      STRAPI_API_TOKEN: ${STRAPI_API_TOKEN}
      NEXT_PUBLIC_STRAPI_API_TOKEN: ${NEXT_PUBLIC_STRAPI_API_TOKEN}
      BACKEND_URL: ${BACKEND_URL}
      NEXT_PUBLIC_BACKEND_URL: ${NEXT_PUBLIC_BACKEND_URL}
      AUTH_SECRET: ${AUTH_SECRET}
      AUTH_SECRET1: ${AUTH_SECRET1}
      AUTH_URL: ${AUTH_URL}
      AUTH_GOOGLE_ID: ${AUTH_GOOGLE_ID}
      AUTH_GOOGLE_SECRET: ${AUTH_GOOGLE_SECRET}
      AUTH_TRUST_HOST: ${AUTH_TRUST_HOST}
      DATABASE_HOST: ${DATABASE_HOST}
      DATABASE_NAME: ${DATABASE_NAME}
      DATABASE_USERNAME: ${DATABASE_USERNAME}
      DATABASE_PASSWORD: ${DATABASE_PASSWORD}
      DWH_HOST: ${DWH_HOST}
      DWH_NAME: ${DWH_NAME}
      DWH_USERNAME: ${DWH_USERNAME}
      DWH_PASSWORD: ${DWH_PASSWORD}
      DATABASE_URL: ${DATABASE_URL}
    ports:
      - '3000:3000'
    networks:
      - my_net
    depends_on:
      - dbdwh
      - strapi-dashboard

ses-crm is my nextjs application which have errors on build(it build but with errors so pages which contained data from data warehouse where i used pool(prisma now) will not be displayed)

with my new Dockerfile:

# 1. Install dependencies
FROM node:18-alpine AS deps
RUN apk add --no-cache libc6-compat
WORKDIR /app
COPY package.json yarn.lock* package-lock.json* pnpm-lock.yaml* ./
RUN \
  if [ -f yarn.lock ]; then yarn --frozen-lockfile; \
  elif [ -f package-lock.json ]; then npm ci; \
  elif [ -f pnpm-lock.yaml ]; then yarn global add pnpm && pnpm i; \
  else echo "Lockfile not found." && exit 1; \
  fi

# 2. Rebuild the source code only when needed
FROM node:18-alpine AS builder
WORKDIR /app
COPY --from=deps /app/node_modules ./node_modules
COPY . .

# Add the environment variables for the build stage
ARG DATABASE_URL
ENV DATABASE_URL=${DATABASE_URL}

# Use the environment variable during the build
RUN npx prisma generate
RUN yarn build

# 3. Production image, copy all the files and run next
FROM node:18-alpine AS runner
WORKDIR /app
ENV NODE_ENV=production

# Add the environment variables for runtime
ENV DATABASE_URL=${DATABASE_URL}

RUN apk add --no-cache chromium
ENV PUPPETEER_EXECUTABLE_PATH=/usr/bin/chromium-browser
RUN addgroup -g 1001 -S nodejs
RUN adduser -S nextjs -u 1001
COPY --from=builder /app/public ./public
COPY --from=builder --chown=nextjs:nodejs /app/.next/standalone ./
COPY --from=builder --chown=nextjs:nodejs /app/.next/static ./.next/static
USER nextjs
EXPOSE 3000
ENV PORT 3000
CMD ["node", "server.js"]

and second one is my dwh:

  dbdwh:
    image: postgres:15.4
    container_name: dbdwh
    restart: always
    env_file:
      - .env
    ports:
      - '5432:5432'
    environment:
      POSTGRES_USER: ${DWH_USERNAME}
      POSTGRES_PASSWORD: ${DWH_PASSWORD}
      POSTGRES_DB: ${DWH_NAME}
    volumes:
      - ./dwh.sql:/docker-entrypoint-initdb.d/dwh.sql
      - postgres-dwh:/var/lib/postgresql/data
    networks:
      - my_net
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${DWH_USERNAME}"]
      interval: 10s
      timeout: 5s
      retries: 5

i’ve changed ports to 5432 internal and container both for better accesebility.
this is just my pg data warehouse

i run command docker-compose-dev build ses-crm dbdwh

Here what is the problem. When i build my nextjs container with Dockerfile which i provided above it do
yarn build. Nextjs generate static pages and api routes etc.
and when it build it requires db connection for sure.
If i will do directly yarn build inside of crm folder, and use instead of dbdwh:5432 localhost:5432(means here i go to local maping of docker) it will build fine for sure because on localhost:5432 it sees a database.
But if i run build of docker file doesnt matter which creds i will use it wont build, because if its localhost there is no database inside of building container, if its dbdwh:5432 there is no dabase because this container is still not in “my_net” network of my compose file during build.
so it returns me:

#17 67.23 PrismaClientInitializationError: 
#17 67.23 Invalid `prisma.$queryRaw()` invocation:
#17 67.23 
#17 67.23 
#17 67.23 Can't reach database server at `dbdwh:5432`
#17 67.23 
#17 67.23 Please make sure your database server is running at `dbdwh:5432`.
#17 67.23     at In.handleRequestError (/app/node_modules/@prisma/client/runtime/library.js:122:7177)
#17 67.23     at In.handleAndLogRequestError (/app/node_modules/@prisma/client/runtime/library.js:122:6211)
#17 67.23     at In.request (/app/node_modules/@prisma/client/runtime/library.js:122:5919)
#17 67.23     at async l (/app/node_modules/@prisma/client/runtime/library.js:127:11167)
#17 67.23     at async u (/app/.next/server/app/api/analytics/region_analyse/route.js:1:562)
#17 67.23     at async /app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:6:42484
#17 67.23     at async eI.execute (/app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:6:32486)
#17 67.23     at async eI.handle (/app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:6:43737)
#17 67.23     at async exportAppRoute (/app/node_modules/next/dist/export/routes/app-route.js:77:26)
#17 67.23     at async exportPageImpl (/app/node_modules/next/dist/export/worker.js:175:20) {
#17 67.23   clientVersion: '5.14.0',
#17 67.23   errorCode: undefined
#17 67.23 }

it says that he cant reach dbdwh:5432, and i understand why he cant because he is not a container in network where database is but for sure he cant reach localhost:5432 because inside of this container there is no database
Sadly or i dont know how to google or i cant find related topics for this kinda thing. I guess solutions us somewhere but i cant find it, because for example my strapi app is builded by dockerfile too and it uses a db but they some-how avoid it and i dont know how

to be honest i’ve literelly just cried straight for 10 minutes
Solution was so easy that im just feeling so bad that idk.
Problem was as i said in nextjs app and how it works and etc.
I still didnt got how to create api routes for nextjs on build-time right way(made backend only on node + express before)
What i did is:

import { PrismaClient } from '@prisma/client'

declare global {
  var prisma: PrismaClient | undefined
}

export const db = globalThis.prisma || new PrismaClient()

if (process.env.NODE_ENV !== "production") globalThis.prisma = db

this typo if init and moved all my logic to file fetchData.ts and fetched all data there instead of api routes
so now my app dont need to have this connection during build.because static pages dc about connectiong(in cause of api) so yea, just needed to read whole freaking docs of nextjs go get it right.
But thanks to it ive created super flexible front of non-fetced data and upgraded like all dependecies and now use prisma which is really cool.
So my solution for this problem is not to use db in api routes
feels badman to be honest