GitHub Actions容器运行PostgreSQL测试报错，统一数据库名是否正确？

问题背景 #

我正在为FastAPI应用编写测试用例，使用GitHub Actions作为CI/CD工具，尝试在GitHub Actions runner的容器中搭建临时PostgreSQL数据库，通过pytest执行测试。

测试代码（conftest.py） #

from fastapi.testclient import TestClient
import pytest
from app.main import app
from app import schemas ,models
from app.database import Base, get_db
from app.config import settings
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from app.oauth2 import create_access_token


SQLALCHEMY_DATABASE_URL_TEST = f"postgresql://{settings.database_username}:{settings.database_password}@{settings.database_hostname}:{settings.database_port}/fastapi_test"

engine = create_engine(SQLALCHEMY_DATABASE_URL_TEST)

TestingSessionLocal = sessionmaker(autocommit =False, 
                                autoflush=False, bind=engine)


@pytest.fixture()#scope="module")
def session():
    #run our code before we return our tests
    #print("
 session fixture ran")
    Base.metadata.drop_all(bind=engine)
    # run our code after our test finishes
    Base.metadata.create_all(bind=engine) #create tables 
    db = TestingSessionLocal()
    try:
        yield db
    finally:
        db.close()

@pytest.fixture()#(scope="module")
def client(session):
    
    def override_get_db():
      try:
          yield session
      finally:
          session.close()
          #overriding dependency overide get_db with this 
          #Dependency

    app.dependency_overrides[get_db] = override_get_db  
    yield TestClient(app)

@pytest.fixture
def test_user_two(client):

    user_data = {"email": "email123.com",
                 "password":"email123"}
    res = client.post("/users/",json = user_data)
    assert res.status_code == 201
    #print(res.json())
    new_user = res.json()
    new_user['password'] = user_data["password"]
    return new_user

@pytest.fixture
def test_user(client):

    user_data = {"email": "email123@ymail.com",
                 "password":"email123"}
    res = client.post("/users/",json = user_data)
    assert res.status_code == 201
    #print(res.json())
    new_user = res.json()
    new_user['password'] = user_data["password"]
    return new_user

@pytest.fixture
def token(test_user):
    return create_access_token({"user_id":test_user["id"]})

@pytest.fixture
def authorized_client(client,token):
    client.headers = {
        **client.headers,
        "Authorization":f"Bearer {token}"
    }

    return client

@pytest.fixture
def test_posts(test_user,session,test_user_two):
    posts_data = [{
        "title" :"secondpost",
        "content":"second post created by user",
        "owner_id":test_user["id"]
        },
        {
        "title" :"pizza places",
        "content":"pizzahut , dominoes",
        "owner_id":test_user["id"]
        },        
        {
        "title" :"third",
        "content":"this is my third post",
        "owner_id":test_user_two["id"]
        }]
    
    def create_posts_model(post):
      return models.Post(**post) 
    
    post_map =  map(create_posts_model,posts_data)
    posts = list(post_map)
    
    session.add_all(posts)
    session.commit()
    posts = session.query(models.Post).all()
    return posts

GitHub Actions配置（deploy.yml） #

name : Build and Deploy Code

on: [push,pull_request]

jobs : 
  job1: 
    env : 
      DATABASE_HOSTNAME: ${{secrets.DATABASE_HOSTNAME}}
      DATABASE_PORT: ${{secrets.DATABASE_PORT}}
      DATABASE_USERNAME: ${{secrets.DATABASE_USERNAME}}
      DATABASE_PASSWORD: ${{secrets.DATABASE_PASSWORD}}
      DATABASE_NAME: fastapi #changed to fastapi_test
      SECRET_KEY: ${{secrets.SECRET_KEY}}
      ACCESS_TOKEN_EXPIRE_MINUTES: ${{secrets.ACCESS_TOKEN_EXPIRE_MINUTES}}
      ALGORITHM: ${{secrets.ALGORITHM}}
    services: 
      postgres:
        image: postgres:13
        env:
          POSTGRES_PASSWORD: ${{secrets.DATABASE_PASSWORD}}
          POSTGRES_DB: fastapi_test
          POSTGRES_USER: ${{secrets.DATABASE_USERNAME}}
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    runs-on :  ubuntu-latest
    steps : 
      - name : pulling git repo
        uses:  actions/checkout@v2
      - name : say hello to aishwarya
        run : echo "hi aishwarya"
      - name : install python version 3.10
        uses: actions/setup-python@v4
        with:
          python-version: '3.10' 
      - name : update pip
        run : python -m pip install --upgrade pip
      - name: install all dependencies
        run : pip install -r requirements.txt
      - name: Print database name
        run: echo $DATABASE_NAME
      - name : test with pytest
        run : | 
            pip install pytest
            pytest -v

初始配置错误 #

初始配置时，DATABASE_NAME设为fastapi，PostgreSQL容器的POSTGRES_DB设为fastapi_test，SQLALCHEMY_DATABASE_URL_TEST指向fastapi_test，此时CI流水线报错：

E sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "" (::1), port *** failed: FATAL: database "" does not exist

同时还出现过password authentication failed for user ***的错误。

当我将DATABASE_NAME、POSTGRES_DB以及SQLALCHEMY连接URL中的数据库名统一设置为fastapi_test后，测试成功执行。请问这种做法是否正确？

解答 #

这种做法完全正确，原因如下：

• 数据库存在性匹配：PostgreSQL容器启动时，通过POSTGRES_DB指定的数据库会被自动创建。如果测试代码连接的数据库名和容器中创建的不一致，必然会出现“数据库不存在”的错误。统一命名后，代码连接的是容器中已存在的数据库，直接解决了连接失败的问题。
• 认证信息一致性：初始出现的密码认证错误，本质也是配置中用户名、密码与容器初始化的信息不匹配。统一所有配置中的数据库用户名、密码、数据库名后，认证信息完全对齐，避免了权限验证失败的问题。
• 测试环境隔离：使用单独的fastapi_test数据库作为测试环境，能避免与生产数据库混淆，保证测试数据不会污染生产环境，这是测试环境的标准最佳实践。

另外可以做两处优化：

1. 把SQLALCHEMY_DATABASE_URL_TEST中的数据库名通过settings.database_name获取，而非硬编码fastapi_test，这样只需修改环境变量就能切换数据库名，更灵活：

   SQLALCHEMY_DATABASE_URL_TEST = f"postgresql://{settings.database_username}:{settings.database_password}@{settings.database_hostname}:{settings.database_port}/{settings.database_name}"
   

2. 定期检查GitHub Secrets中的数据库相关信息（用户名、密码等），确保和容器初始化的参数完全一致，避免手动输入错误。

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