SQLite Test Infrastructure#

Overview#

Dify's unit test suite uses temporary file-backed SQLite databases with pytest session fixtures to give each test an isolated, schema-complete database — no Docker, no mocks for the session layer. The infrastructure lives in api/tests/unit_tests/conftest.py and replaces a prior approach of patching MagicMock sessions throughout individual test files.

This is part of a broader session-refactoring effort (#37403) that makes DB session access explicit across ~150+ production files so that tests can control transaction lifetime without relying on Flask-SQLAlchemy's global db.session.

Fixture Architecture#

The fixtures form a two-level hierarchy to minimize schema creation cost while keeping per-test data isolated.

_sqlite_database_template (scope=session)
        │
        │ file-copied per test
        ▼
_sqlite_engine (scope=function)
        │
        ├──► _sqlite_session_factory (autouse, scope=function)
        │ │ monkeypatches session_factory_module._session_maker
        │ │
        │ ├──► sqlite_session (yields a Session)
        │ └──► sqlite_session_factory (exposes the sessionmaker)
        │
        └──► sqlite_engine (exposes the Engine)

Key fixtures (conftest.py)#

The autouse _sqlite_session_factory fixture means every unit test automatically runs against a fresh SQLite database — no explicit fixture request is needed unless the test wants to write seed data.

Selective Table Creation#

Not every test needs the full schema. When only specific tables are needed, sqlite_session supports indirect parametrization:

TABLES = (DocumentSegment,)

@pytest.mark.parametrize("sqlite_session", [TABLES], indirect=True)
def test_clean_handles_summary_deletion_and_vector_cleanup(
    self, sqlite_session: Session, ...
): ...

Note: This indirect parametrization pattern is being phased out in favor of the _UsesSQLiteSession base class mixin pattern (see below).

Seeding Data in Tests#

Tests use _persist_* helper functions that call session.add(...) + session.commit() to create real ORM records. This pattern appears throughout the test suite:

• test_app_generator_extra.py defines _persist_app, _persist_workflow, _persist_end_user, and _persist_snippet to build a realistic object graph in SQLite before exercising the workflow generator.
• Assertions then use session.refresh(), ORM queries, or attribute inspection on committed objects — verifying actual DB state rather than mock call counts.

Global Session Monkeypatching#

_sqlite_session_factory patches core.db.session_factory._session_maker 1, which is the module-level singleton used by create_session() 2. This means production code that calls session_factory.create_session() automatically gets a SQLite-backed session during tests — without any per-test mock setup.

For tests that need to patch the Flask-SQLAlchemy db object (e.g., for scoped sessions), test files construct a local scoped_session adapter and monkeypatch db on the relevant module, as shown in sqlite_generator_scoped_session.

Helper Functions#

conftest.py also exports named helpers for common seeding patterns:

• persist_service_api_tenant_owner(session, tenant, owner) — seeds Tenant, Account, and TenantAccountJoin with OWNER role; replaces the legacy setup_mock_tenant_owner_execute_result mock.
• persist_service_api_dataset_owner(session, tenant, tenant_account_join) — seeds the dataset-owner mapping; replaces setup_mock_dataset_owner_execute_result.

The legacy mock helpers (setup_mock_tenant_owner_execute_result, setup_mock_dataset_owner_execute_result) were removed in #40547.

Migration Context#

The migration from MagicMock sessions to SQLite sessions is tracked under umbrella PR #38784 (77 sub-PRs). A parallel migration replaces Mock(spec=...) ORM test doubles with real SQLAlchemy model constructors, tracked under #35872.

Session Migration Examples#

• #38721 — test_metadata_bug_complete.py: adds assert not sqlite_session.in_transaction() to verify early validation exits before DB access.
• #38761 — test_sqlalchemy_workflow_execution_repository.py: tests JSON round-trips, timestamp handling, and cross-tenant isolation against real DB state.
• #39073 — test_human_input_form_repository_impl.py: removed _FakeSession, _patch_repo_session_factory, and dataclass stubs; replaced with real ORM models and a rollback test using SQLAlchemy event listeners.
• #39803 — test_app_dsl_service.py: uses unbound_session for validation-only paths and sqlite_session for persistence paths.
• #40080 — RAG docstore and index processor tests: migrated to _UsesSQLiteSession pattern; replaced mocked sessions with real SQLite sessions that persist and query ORM records.
• #40087 — Model and agent service tests: migrated test_agent_dsl_service.py, test_home_snapshot_service.py, test_workflow_publish_service.py, and test_workspace_service.py from full SQLAlchemy Session mocks to SQLite fixtures; persisted real Account, Tenant, App, Agent, Workflow, Conversation, AgentConfigDraft, AgentConfigSnapshot, AgentConfigRevision, and WorkflowAgentNodeBinding records; verified real ownership queries, writes, commits, and rollback behavior; kept external repositories, storage, model providers, and workflow collaborators mocked.
• #40509 — Application generation service and runner tests: migrated test_app_generator.py, test_agent_chat_app_runner.py, test_app_generator_and_runner.py, and test_app_generate_service.py from mocked sessions and stand-ins to real SQLite-backed sessions; replaced MagicMock(), DummyAccount, and SimpleNamespace stand-ins with real App, Account, AppModelConfig, Conversation, Message, MessageAnnotation, Agent, AgentConfigSnapshot, Workflow, and WorkflowRun constructors; exercises real app lookups, missing-row behavior, commits, session lifecycle boundaries, and annotation short-circuits; seeded records for conversation and message tests, used sqlalchemy.event.listen to track transaction commits instead of mocking session.commit() and session.close(); retained mocks for DTOs and external collaborators.
• #40594 — trigger and webhook tests: migrated test_webhook.py, test_debug_event_selectors.py, test_webhook_service.py, test_webhook_service_additional.py, and test_trigger_processing_tasks.py to use SQLite sessions; replaced MagicMock() and SimpleNamespace stand-ins with real WorkflowWebhookTrigger, WorkflowPluginTrigger, Workflow, App, EndUser, TriggerSubscription, and ToolFile constructors; exercises webhook lookup against SQLite with persisted trigger/app/workflow ownership chains and decoy rows to verify debug workflow selection remains scoped to the webhook trigger tenant and app despite cross-tenant decoys.
• #40517 — Service API dataset segment tests: migrated test_dataset_segment.py from fake session factories to the shared SQLite-backed service session factory; passed unbound_session to bind-free mocked service interface checks; persisted real Dataset, Document, Account, Tenant, DocumentSegment, ChildChunk, DocumentSegmentSummary, and updated segment ORM instances instead of mock stand-ins; used the real CachedApiToken Pydantic DTO while retaining mocks for billing feature/vector/rate-limit responses and pagination containers.
• #40521 — Console dataset controller tests: migrated test_datasets.py to use the _UsesSQLiteSession mixin pattern; replaced all SQLAlchemy session doubles with the shared SQLite session and replaced mock Account, UploadFile, AppDatasetJoin, Document, DocumentSegment, DatasetPermission, and ApiToken instances with real ORM models; persisted permission, upload, segment, and API-token rows so filtering, counts, key limits, and deletion execute through SQLAlchemy; kept mocks only for provider configuration, RBAC response DTOs, services, and cache boundaries.
• #40548 — Dataset retrieval and retrieval-service tests: migrated test_dataset_retrieval.py from mocked SQLAlchemy sessions to the shared SQLite engine and session fixtures; replaced Dataset, Document, Segment, ChildChunk, attachment-binding, metadata, upload-file, query-log, and rate-limit stand-ins with real mapped rows; exercised tenant scoping, unavailable-dataset filtering, committed audit rows, metadata JSON filters, attachment joins, and hit-count updates through real SQL.
• #40508 — Core agent runner tests: migrated test_base_agent_runner.py from mocked global/caller sessions to real SQLite-backed sessions; replaced mocked Message, MessageFile, MessageAgentThought, Conversation, and AppModelConfig stand-ins with real ORM mapped rows; verified thought inserts and updates through ORM reads, reconstructed persisted conversation history and tool calls through real queries, exercised message-file/config lookups through real queries; seeded decoy thoughts to verify count queries remain scoped to current message; retained mocks only for runtime tools, app/config DTOs, model providers, queues, and file conversion boundaries.

The _UsesSQLiteSession Mixin Pattern#

Test classes now inherit from a _UsesSQLiteSession base class mixin to inject the SQLite session into self.session:

class _UsesSQLiteSession:
    session: Session

    @pytest.fixture(autouse=True)
    def _inject_sqlite_session(self, sqlite_session: Session) -> None:
        self.session = sqlite_session

class TestDatasetDocumentStore(_UsesSQLiteSession):
    def test_document_exists_returns_true(self):
        # self.session is available here
        store = DatasetDocumentStore(dataset=_dataset(), user_id=USER_ID)
        assert store.document_exists("doc-1", session=self.session) is True

This pattern eliminates the need for @pytest.mark.parametrize("sqlite_session", ..., indirect=True) on each test class. Tests access the session via self.session directly instead of passing session as a parameter.

When a test needs both a session and the session factory (e.g., for worker threads), the mixin stores both:

class TestParagraphIndexProcessor:
    session: Session
    session_factory: sessionmaker[Session]

    @pytest.fixture(autouse=True)
    def _inject_sqlite_sessions(self, sqlite_session: Session, sqlite_session_factory: sessionmaker[Session]) -> None:
        self.session = sqlite_session
        self.session_factory = sqlite_session_factory

Persisting and Asserting Database State#

Tests now persist real records using session.add, session.flush, and session.commit instead of mocking return values, and assert database state using session.get and select queries instead of asserting mock calls:

Before (mocked session):

mock_session = MagicMock()
mock_session.scalar.return_value = segment

store.delete_document("doc-1", session=mock_session)

mock_session.delete.assert_called_with(segment)
mock_session.flush.assert_called()

After (real session):

segment = _persist_segment(self.session)
store = DatasetDocumentStore(dataset=_dataset(), user_id=USER_ID)

store.delete_document("doc-1", session=self.session)

assert self.session.get(DocumentSegment, segment.id) is None

Tests track transaction boundaries using SQLAlchemy event listeners instead of mocking commit:

Before:

session = MagicMock()
phase_events: list[str] = []
session.commit.side_effect = lambda: phase_events.append("commit")

After:

session = self.session
phase_events: list[str] = []
event.listen(session, "after_commit", lambda _session: phase_events.append("commit"))

Files Migrated in #40080#

• api/tests/unit_tests/core/rag/docstore/test_dataset_docstore.py
• api/tests/unit_tests/core/rag/indexing/processor/test_paragraph_index_processor.py
• api/tests/unit_tests/core/rag/indexing/processor/test_parent_child_index_processor.py
• api/tests/unit_tests/core/rag/indexing/processor/test_qa_index_processor.py (migrated from indirect parametrization)
• api/tests/unit_tests/core/rag/indexing/test_index_processor.py

Files Migrated in #40547#

• api/tests/unit_tests/core/app/apps/advanced_chat/test_generate_task_pipeline.py
• api/tests/unit_tests/core/app/apps/advanced_chat/test_generate_task_pipeline_core.py
• api/tests/unit_tests/core/mcp/test_mcp_client.py
• api/tests/unit_tests/core/ops/test_ops_trace_manager.py
• api/tests/unit_tests/core/rag/indexing/test_index_processor_base.py
• api/tests/unit_tests/core/repositories/test_celery_workflow_execution_repository.py
• api/tests/unit_tests/core/repositories/test_celery_workflow_node_execution_repository.py
• api/tests/unit_tests/services/workflow/test_workflow_draft_variable_service.py
• api/tests/unit_tests/services/workflow/test_workflow_restore.py

ORM Mock Replacement Examples#

The pattern of using real model constructors instead of Mock(spec=...) initializes mapped fields through constructors and patches behavior at class boundaries:

• #40019 — workflow, draft-variable, scheduling, RAG pipeline, and MCP server tests: replaces Mock(spec=WorkflowNodeExecutionModel) with WorkflowNodeExecutionModel(id=..., node_id=...), Mock(spec=Account) with Account(name=..., email=...), Mock(spec=Workflow) with Workflow(tenant_id=..., graph=json.dumps(...)).
• #40020 — app, account, service API, authentication, and tool tests: uses constructors for EndUser, App, Dataset, Document, DocumentSegment, ChildChunk, Tenant, Account, and MCPToolProvider; keeps service_api/conftest.py with changed consumers to avoid cross-PR fixture dependencies.
• #40018 — related workflow repository conversions following the same constructor-based pattern.
• #40576 — Migrated test_workflow_execute_task.py, test_workflow_run_service.py, test_pipeline_generate_service.py, test_credential_permission_service.py, and test_logstore_workflow_node_execution_repository.py from SimpleNamespace/cast stand-ins to real ORM instances for App, Workflow, WorkflowRun, Account, EndUser, Message, and Conversation models. Consolidated local sessionmaker fixtures by replacing sqlalchemy_session_factory with the shared sqlite_session_factory fixture.
• #40639 — web controller identity tests: migrates SimpleNamespace test doubles to real App and EndUser constructors with proper enums (AppMode.CHAT, AppMode.COMPLETION, AppMode.WORKFLOW, EndUserType.BROWSER, EndUserType.SERVICE_API) across service and web controller test files.
• #40626 — controller identity and MCP tests: replaces MagicMock() Account and Tenant stand-ins with real ORM instances in authentication, app-import, plugin, and OAuth tests; removes DummyServer, DummyApp, DummyWorkflow, and DummyConfig test double classes from MCP tests and replaces them with factory functions (_server, _app, _end_user) that create real AppMCPServer, App, Workflow, AppModelConfig, and EndUser ORM instances; persists MCP workflow and model-config rows in SQLite test fixtures and exercises derived user-input forms through real model behavior.
• #40587 — agent runtime, advanced-chat runner, and tool-invocation tests: replaces MagicMock() Message and Conversation stand-ins with factory functions (_make_message(), _make_conversation()) that return real ORM instances; replaces SimpleNamespace Agent and Snapshot test doubles in test_resolve_agent.py with real Agent, AgentConfigSnapshot, Conversation, App, Account, and AgentWorkspaceBinding constructors, using persisted rows to cover exact-ID lookups, missing-entity error paths, published vs. debugger-draft resolution, and generation-mismatch scenarios; replaces mocker.patch.object(session, "commit") with sqlalchemy.event.listen(session, "after_commit", ...) and mocker.patch.object(session, "close") with event.listen(session, "persistent_to_detached", ...) to track session lifecycle events through SQLAlchemy event listeners; ensures proper foreign key relationships by persisting related entities (e.g., conversation before message) and committing before event tracking begins; validates tool-invocation commits using a second session from sqlite_session_factory as an observer session to confirm committed ToolModelInvoke records.
• #40594 — trigger and webhook tests: replaces MagicMock(), SimpleNamespace, and DummyWebhookTrigger stand-ins with real WorkflowWebhookTrigger, WorkflowPluginTrigger, Workflow, App, EndUser, TriggerSubscription, and ToolFile constructors; persists webhook lookup rows (workflow, app, app-trigger) in SQLite to verify debug workflow selection remains scoped to the webhook trigger tenant and app despite cross-tenant decoy drafts, published lookup loads the persisted app through the full ownership chain and rejects cross-tenant app decoys, and real SQL queries handle disabled, unauthorized, and rate-limited AppTrigger states.
• #40547 — advanced-chat pipeline, MCP auth, Ops tracing, indexing, Celery repository, workflow draft-variable, restore, and Agent retirement tests: migrated to shared SQLite-backed SQLAlchemy sessions; replaced Message, MessageAgentThought, Account, Workflow, UploadFile, HumanInputContent, Dataset, and Document stand-ins with real mapped instances; exercised committed persistence, duplicate detection, scoped-session behavior, and transaction failures through real SQLAlchemy execution; used real Session objects injected into MCP auth and raised database failures through SQLAlchemy engine events; migrated Celery repositories to the repository-wide SQLite session factory; seeded real model ownership and mapped entities for workflow restoration, indexing uploads, and Agent retirement.

Key Source Files#