Skip to main content
Virtual environments require two things: virtual_environments = true in your project settings, and a state store configured for your active target.

​
Project configuration

The virtual_environments setting switches the project from standard mode (default) to virtual mode. All state, plan, build, promote, rollback, and reconcile commands route through the virtual path when this is enabled.

​
State configuration

Each physical target that uses virtual mode needs a [targets.<name>.state] block:

​
DuckDB state backend

DuckDB state stores are file-based. Relative paths are resolved against the project directory. Suitable for local development and single-user workflows. Not recommended when multiple processes or CI jobs need concurrent state access.

​
Postgres state backend

Postgres is recommended for production. It supports concurrent access from multiple developers or CI jobs.

​
State initialization

Before using virtual commands, initialize the state store:
This creates the state tables in the configured schema. Run it once per physical target.

​
State lifecycle

state migrate creates a backup schema (e.g. sqlbuild_state__backup_<id>) before re-initializing. This lets you roll back if a migration causes problems. state reset is destructive and requires both allow_reset = true in config and --auto-approve on the command line.

​
Local overrides

Use sqlbuild_local.toml to override state connection config per developer:
This is useful when each developer uses a local state file while the project config points to a shared state database.

​
Per-target state

Different physical targets can use different state backends:
Each physical target has its own state store. VDEs are scoped to their physical target and cannot be promoted or compared across different physical targets.

​
Unsuffixed VDE naming

By default, all VDE logical views use a suffixed schema: dev__kevin.fact_orders. For the primary VDE that consumers query directly (e.g. the production VDE), you typically want clean unsuffixed names: dev.fact_orders. Configure this with unsuffixed_virtual_env:
With this config, VDE dev uses dev.fact_orders while other VDEs like kevin use dev__kevin.fact_orders. This setting is also required for adopt and detach operations so that existing consumer queries continue to work after migrating to virtual mode.

​
Janitor configuration

When using virtual environments, configure the janitor to run periodically to clean up expired VDEs, old checkpoints, and unreferenced physical versions:
See Janitor for details on what gets cleaned up and how retention works.

​
State tables

The state store contains current-state tables and append-only history tables. You do not need to interact with these directly, but understanding what is stored helps when debugging: Current state: state_versions, model_versions, function_versions, physical_relations, physical_relation_ancestry, virtual_environments, virtual_environment_refs, virtual_environment_function_refs, virtual_environment_checkpoints, virtual_environment_checkpoint_refs, virtual_environment_checkpoint_function_refs, locks, state_operations History: plan_runs, virtual_environment_ref_events, reconcile_events, state_migration_events, state_operation_events