FASTAPI testing database not creating database

Question

I'm trying to test my FASTAPI app. Seems to me, all settings are correct.

test_users.py

engine = create_engine(
    f"postgresql"
    f"://{settings.database_username}"
    f":{settings.database_password}"
    f"@{settings.database_hostname}"
    f":{settings.database_port}"
    f"/test_{settings.database_name}"
    )
TestingSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base.metadata.create_all(bind=engine)

def override_get_db():
    try:
        db = TestingSessionLocal()
        yield db
    finally:
        db.close()

app.dependency_overrides[get_db] = override_get_db

client = TestClient(app)


def test_create_user():
    response = client.post(
        "/users/",
        json={"email": "nikita@gmail.com", "password": "password"}
    )
    new_user = schemas.UserOutput(**response.json())

    assert response.status_code == 201
    assert new_user.email == "nikita@gmail.com"

When I run pytest, I get this error:

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

Why is the code not creating the database?

Do you mean is localserver with fastapi up or something else? uvicorn server is down — Nikita Kurabtsev, Jan 27 '23 at 16:14
No. You are trying to connect to a local postgres database located at localhost:5432. Does it even exist? — drum, Jan 27 '23 at 16:21
Oh, Yep. My app db is running on localhost:5432. Settings exactly the same except 'test_' prefix in the db name. — Nikita Kurabtsev, Jan 27 '23 at 16:30

score 2 · Accepted Answer · answered Jan 27 '23 at 16:29

With engine = create_engine("postgresql://...") you define a connection to an existing PostgreSql database. And with Base.metadata.create_all(bind=engine) you create the tables - according to your models - in the existing database.

So the code that you written does not create a database, it expects that you give it an already existing database.

And that has to do with PostgreSQL itself. PostgreSQL runs as a server, and a PostgreSQL server can run multiple databases. And each database has to be created explicitly. Just telling SQLAlchemy the connection string is not enough.

It's possible to create a new database from Python itself by connecting to the PostgreSQL server (see https://www.tutorialspoint.com/python_data_access/python_postgresql_create_database.htm), or alternatively you can create it manually before you run your script. E.g. by running CREATE DATABASE databasename; inside psql (or any other database tool).

However if you want to test using a running database, I would suggest using testcontainers. They will spawn a new PostgreSQL server with an empty database everytime you run the tests.

Notice, that the example from the FastAPI documentation works differently. They just use

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
Base.metadata.create_all(bind=engine)

which creates the database. This works, because SQLite doesn't run as a server. It's just one file that represents the full database, and if the file doesn't exist, the sqlite database adapter will assume that the database is just empty, and create a new file for you. PostgreSQL doesn't work like this though.

I created db on my own in the postgesql and now it works. I understod the flow, thank you. — Nikita Kurabtsev, Jan 27 '23 at 16:40

FASTAPI testing database not creating database

1 Answers1

Linked