"""Database connection utilities — shared across models, migrations, and services."""
import os
import sqlite3
import threading
DB_PATH = os.environ.get("RELAY_DB_PATH", "/app/data/relay.db")
# Write lock for schema migrations — prevents concurrent ALTER/CREATE from
# gunicorn workers + RQ worker. WAL handles concurrent reads fine, but
# schema changes must be serialized.
_db_write_lock = threading.Lock()
def get_db():
"""Return a new SQLite connection with row_factory and WAL mode."""
conn = sqlite3.connect(DB_PATH, timeout=30, check_same_thread=False)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA foreign_keys = ON")
conn.execute("PRAGMA busy_timeout = 5000") # Wait up to 5s instead of failing on lock
return conn
def write_lock():
"""Context manager for serializing write operations across threads.
Use for schema migrations (ALTER TABLE, CREATE TABLE) to prevent
concurrent schema changes from gunicorn workers + RQ worker.
Example:
with write_lock():
conn.execute("ALTER TABLE sites ADD COLUMN foo TEXT")
"""
return _db_write_lock
def _table_exists(conn, name):
"""Check if a table exists in the database."""
row = conn.execute(
"SELECT name FROM sqlite_master WHERE type='table' AND name=?",
(name,),
).fetchone()
return row is not None