Getting an error while trying to run PostgreSQL in container on GitHub Actions runner

Question

Error:

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

   sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "***" (::1), port *** failed: FATAL:  password authentication failed for user "***"

I'm trying to do some testing for my fastapi application. I have some test cases. I run my CI/CD using GitHub Actions. I am trying to spin a temporary database inside a container on the GitHub Actions runner. I am using pytest for testing my fastapis.

I have my conftest.py placed with my tests folder and that looks like this:

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("\n 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": "[email protected]",
                 "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

My deploy.yml file looks like this:

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

Initially, I set my DATABASE_NAME: fastapi, postgres db : fastapi_test, SQLALCHEMYURL pointing to fastapi_test.

The CI pipeline failed with error:

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

I had to point all three variables to the same database fastapi_test in order for it to succeed.

Is this the right way to accomplish it?