GitHub Actions中Prisma连接PostgreSQL偶发P1001错误求助

问题现象 #

在GitHub Actions中通过Prisma执行集成测试时，偶尔会出现数据库连接失败的错误，重启1-2次后恢复正常。错误信息如下：

Run npm run test:migrate

> fictadvisor-api@0.0.1 test:migrate
> dotenv -e .testing.env -- prisma migrate deploy

Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "localhost:5432"

Error: P1001: Can't reach database server at localhost:5432

Please make sure your database server is running at localhost:5432.
Error: Process completed with exit code 1.

已尝试在DATABASE_URL中设置connection_timeout，但无效果。

当前配置 #

GitHub Actions集成测试步骤 #

integration-test:
  runs-on: ubuntu-latest
  steps:
    - name: Checkout
      uses: actions/checkout@v3
    - name: Install Node and dependencies
      uses: ./.github/actions/install
    - name: Start PostgreSQL Database
      run: |
        docker-compose up -d
    - name: Start migration
      run: npm run test:migrate
    - name: Start generating
      run: npm run test:generate
    - name: Start seeding
      run: npm run test:seed
    - name: Run integration tests
      run: npm run test:integration

docker-compose.yml #

version: '3'

services:
  db:
    image: postgres:15
    container_name: testing-db
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: postgres

package.json脚本配置 #

"scripts": {
    "prebuild": "rimraf dist",
    "build": "nest build",
    "format": "prettier --write \"src/**/*.ts\" \"test/**/*.ts\"",
    "start": "nest start",
    "start:dev": "dotenv -e .development.env -- cross-env NODE_ENV=development nest start --watch",
    "start:debug": "nest start --debug --watch",
    "start:prod": "cross-env NODE_ENV=production node dist/main",
    "lint": "eslint . --ext .ts",
    "lint:fix": "eslint . --ext .ts --fix",
    "test:unit": "cross-env NODE_ENV=testing dotenv -e .testing.env -- jest -c jest.unit.config.json",
    "test:integration": "cross-env NODE_ENV=testing dotenv -e .testing.env -- jest -c jest.integration.config.json -i",
    "test:migrate": "dotenv -e .testing.env -- prisma migrate deploy",
    "test:seed": "dotenv -e .testing.env -- prisma db seed",
    "test:watch": "jest --watch",
    "test:cov": "jest --coverage",
    "test:debug": "node --inspect-brk -r tsconfig-paths/register -r ts-node/register node_modules/.bin/jest --runInBand",
    "test:e2e": "jest --config ./test/jest-e2e.json",
    "test:generate": "dotenv -e .testing.env -- prisma generate",
    "integration:full": "docker compose up -d && yarn test:migrate && yarn test:seed && yarn test:integration && docker compose down",
    "generate:dev": "dotenv -e .development.env -- npx prisma generate",
    "migrate:sql": "dotenv -e .development.env -- npx prisma migrate dev --create-only --name",
    "migrate:dev": "dotenv -e .development.env -- npx prisma migrate dev"
  },

.testing.env #

SECRET=testing-secret
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/postgres
TIME_DIFFERENCE=3
TZ=UTC

package.json依赖项 #

"dependencies": {
    "@nestjs-modules/mailer": "^1.8.1",
    "@nestjs/common": "^9.2.1",
    "@nestjs/config": "^2.2.0",
    "@nestjs/core": "^9.2.1",
    "@nestjs/jwt": "^10.0.3",
    "@nestjs/passport": "^9.0.0",
    "@nestjs/platform-express": "^9.2.1",
    "@nestjs/schedule": "^3.0.1",
    "@nestjs/swagger": "^6.3.0",
    "@nestjs/typeorm": "^9.0.1",
    "@prisma/client": "4.16.2",
    "@types/bcrypt": "^5.0.0",
    "@types/jsdom": "^21.1.0",
    "@types/nodemailer": "^6.4.7",
    "@types/passport-local": "^1.0.34",
    "axios": "^1.2.1",
    "bcrypt": "^5.1.0",
    "class-transformer": "^0.5.1",
    "class-validator": "^0.14.0",
    "cross-env": "^7.0.3",
    "docxtemplater": "^3.37.12",
    "dotenv-cli": "^7.2.1",
    "handlebars": "^4.7.7",
    "jsdom": "^22.0.0",
    "nodemailer": "^6.9.0",
    "passport": "^0.6.0",
    "passport-jwt": "^4.0.0",
    "passport-local": "^1.0.0",
    "pizzip": "^3.1.4",
    "prisma": "4.16.2",
    "reflect-metadata": "^0.1.13",
    "rimraf": "^5.0.1",
    "rxjs": "^7.6.0",
    "uuid": "^9.0.0"
  },
  "devDependencies": {
    "@nestjs/cli": "^9.1.5",
    "@nestjs/schematics": "^9.0.3",
    "@nestjs/testing": "^9.2.1",
    "@types/cron": "^2.0.1",
    "@types/dateformat": "^5.0.0",
    "@types/express": "^4.17.15",
    "@types/jest": "^29.5.1",
    "@types/multer": "^1.4.7",
    "@types/node": "^20.2.5",
    "@types/passport-jwt": "^3.0.8",
    "@types/supertest": "^2.0.12",
    "@types/uuid": "^9.0.0",
    "@typescript-eslint/eslint-plugin": "5.59.7",
    "@typescript-eslint/parser": "5.59.7",
    "eslint": "^8.32.0",
    "eslint-config-prettier": "^8.5.0",
    "eslint-plugin-import": "^2.26.0",
    "jest": "^29.5.0",
    "prettier": "^2.8.1",
    "supertest": "^6.3.3",
    "ts-jest": "29.1.0",
    "ts-loader": "^9.4.2",
    "ts-node": "10.9.1",
    "tsconfig-paths": "^4.1.1",
    "typescript": "5.0.4"
  }

解决方案 #

1. 等待数据库完全启动后再执行迁移 #

问题根源是docker-compose up -d启动容器后，PostgreSQL可能还未完成初始化就开始执行迁移脚本。添加等待逻辑确认数据库可连接：

修改GitHub Actions中的数据库启动步骤：

- name: Install PostgreSQL client
  run: sudo apt-get update && sudo apt-get install -y postgresql-client

- name: Start PostgreSQL Database and wait for it to be ready
  run: |
    docker-compose up -d
    # 循环检查数据库是否就绪
    until pg_isready -h localhost -p 5432 -U postgres; do
      echo "Waiting for database to be ready..."
      sleep 2
    done

2. 增强数据库连接配置 #

在DATABASE_URL中添加连接超时和重试相关参数：

DATABASE_URL=postgresql://postgres:postgres@localhost:5432/postgres?connect_timeout=10&pool_timeout=10&connection_limit=10

同时在Prisma schema中配置连接池参数：

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  pool {
    min = 1
    max = 10
    create_timeout = 10s
    idle_timeout = 30s
    acquire_timeout = 10s
  }
}

3. 使用GitHub官方PostgreSQL Action替代Docker Compose #

官方Action会自动处理数据库初始化等待，稳定性更高：

- name: Start PostgreSQL
  uses: actions/setup-postgres@v2
  with:
    postgres-version: '15'
    password: postgres
    database: postgres

替换后无需再使用Docker Compose，直接使用该Action提供的数据库即可。

4. 给迁移步骤添加重试逻辑 #

在GitHub Actions中对迁移步骤设置重试，避免偶发连接失败：

- name: Start migration with retries
  run: npm run test:migrate
  continue-on-error: true
  id: migrate

- name: Retry migration if failed
  run: npm run test:migrate
  if: steps.migrate.outcome == 'failure'

总结 #

最直接有效的方案是添加数据库就绪等待逻辑，确保PostgreSQL完全初始化后再执行迁移脚本。如果追求更高稳定性，推荐替换为官方PostgreSQL Action。

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