[FAQ]

Course

ai-dev-tools-zoomcamp

Question

Why does swapping SQLite for PostgreSQL in Agent Relay (Homework 3, Question 4) break every authenticated request with a syntax error?

Answer

The starter's database.py has a context manager, immediate_transaction(), that opens every transaction with a raw connection.exec_driver_sql("BEGIN IMMEDIATE"). BEGIN IMMEDIATE is SQLite-specific syntax — it reserves SQLite's writer lock up front — and it's invalid on PostgreSQL.

This isn't a minor edge case: immediate_transaction() wraps authenticate(), create_task(), claim_one(), heartbeat(), and commit_terminal(). Once RELAY_DATABASE_URL points at Postgres, essentially every authenticated endpoint fails immediately with a syntax error, even though the rest of the codebase (SQLAlchemy models, psycopg as a dependency, no other raw SQL) is already database-agnostic by design, per SPEC.md.

Fix: only issue BEGIN IMMEDIATE when the backend is actually SQLite; let PostgreSQL use SQLAlchemy's normal autobegin transaction instead.

def _is_sqlite(database_url: str) -> bool:
return database_url.startswith("sqlite")

@contextmanager
def immediate_transaction():
connection = engine.connect()
session = Session(bind=connection, expire_on_commit=False, autoflush=True)
try:
if _is_sqlite(DATABASE_URL):
connection.exec_driver_sql("BEGIN IMMEDIATE")
yield session
session.commit()
finally:
session.close()
connection.close()

SQLite behavior is unchanged — it still gets the writer-lock reservation. One thing to know going in: on SQLite, BEGIN IMMEDIATE's writer lock was doing double duty — it also happens to be why concurrent task claims never overlap. Postgres's default autobegin has no equivalent, so this fix alone doesn't restore that guarantee; it needs SELECT ... FOR UPDATE SKIP LOCKED on the claim query for full parity, which is exactly the seam SPEC.md itself flags for a future PostgreSQL port. Worth knowing if your test suite includes a concurrent-claims test — it may need to be skipped against Postgres until that follow-up hardening is in place.

Checklist

• I have searched existing FAQs and this question is not already answered
• The answer provides accurate, helpful information
• I have included any relevant code examples or links

Review the proposed FAQ against the repository's existing FAQ entries, then verify the technical explanation against the referenced database.py and SPEC.md from ai-dev-tools-zoomcamp. Done means the question and answer are accurate, readable, non-duplicative, and ready to publish as an FAQ entry.