Function create_database

api/database.py:368–437

Create database and return engine + session maker. Uses a cache to avoid creating new engines for each request, which improves performance by reusing database connections. Args: project_dir: Directory containing the project Returns: Tuple of (engine, SessionLo

(project_dir: Path)

def create_database(project_dir: Path) -> tuple:
    """
    Create database and return engine + session maker.

    Uses a cache to avoid creating new engines for each request, which improves
    performance by reusing database connections.

    Args:
        project_dir: Directory containing the project

    Returns:
        Tuple of (engine, SessionLocal)
    """
    cache_key = project_dir.as_posix()

    if cache_key in _engine_cache:
        return _engine_cache[cache_key]

    db_url = get_database_url(project_dir)

    # Ensure parent directory exists (for .autoforge/ layout)
    db_path = get_database_path(project_dir)
    db_path.parent.mkdir(parents=True, exist_ok=True)

    # Choose journal mode based on filesystem type
    # WAL mode doesn't work reliably on network filesystems and can cause corruption
    is_network = _is_network_path(project_dir)
    journal_mode = "DELETE" if is_network else "WAL"

    engine = create_engine(db_url, connect_args={
        "check_same_thread": False,
        "timeout": 30  # Wait up to 30s for locks
    })

    # Set journal mode BEFORE configuring event hooks
    # PRAGMA journal_mode must run outside of a transaction, and our event hooks
    # start a transaction with BEGIN IMMEDIATE on every operation
    with engine.connect() as conn:
        # Get raw DBAPI connection to execute PRAGMA outside transaction
        raw_conn = conn.connection.dbapi_connection
        if raw_conn is None:
            raise RuntimeError("Failed to get raw DBAPI connection")
        cursor = raw_conn.cursor()
        try:
            cursor.execute(f"PRAGMA journal_mode={journal_mode}")
            cursor.execute("PRAGMA busy_timeout=30000")
        finally:
            cursor.close()

    # Configure IMMEDIATE transactions via event hooks AFTER setting PRAGMAs
    # This must happen before create_all() and migrations run
    _configure_sqlite_immediate_transactions(engine)

    Base.metadata.create_all(bind=engine)

    # Migrate existing databases
    _migrate_add_in_progress_column(engine)
    _migrate_fix_null_boolean_fields(engine)