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?
The example in this question comes from a fast API course on youtube https://www.youtube.com/watch?v=0sOvCWFmrtA&t=65833s
I was following it and ran into same issue.
The issue is that in our main file we have below statement which expects a DB name 'fastapi' from database.py file
In your yml file you are creating database with name fastapi_test, so it doesnt find fastapi and hence errors out
Solution1: Tested and Works fine for me
Keep the database name
POSTGRES_DB: fastapiin yml file as we only need one database for the test and don't need an additional fastapi_test databaseadditionally you need to set a new Env variable
TEST_DATABASE_NAME=fastapi_testand need to set it everwhere in .env file , config.py and in confest.py url should be:Solution2: Not tested but should work fine
Keep the same yml file but Comment below line in main.py. This will prevent auto-creation of new tables but if you are running alembic migration this may not be required.
the GitHub link to the course has same line commented out