Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.sqlbuild.com/llms.txt

Use this file to discover all available pages before exploring further.

Virtual environments require two things: environment_mode = "virtual" in your project config, and a state store configured for your active environment.

​
Project configuration

name = "my_project"
adapter = "duckdb"
environment_mode = "virtual"
default_environment = "dev"

[connection]
database = "warehouse.duckdb"

[environments.dev]
schema = "dev"

[environments.dev.state]
backend = "duckdb"
schema = "sqlbuild_state"

[environments.dev.state.connection]
database = "state.duckdb"
The environment_mode field switches the project from direct mode (default) to virtual mode. All state, plan, build, promote, rollback, and reconcile commands route through the virtual path when this is set.

​
State configuration

Each physical environment that uses virtual mode needs a [environments.<name>.state] block:
FieldRequiredDescription
backendYesState store engine: duckdb or postgres
schemaYesSchema name for state tables
connectionYesBackend-specific connection config
allow_resetNoWhether sqb state reset is permitted (default: false)
unsuffixed_virtual_envNoVDE name that uses the base schema without a suffix (for adopt/detach)

​
DuckDB state backend

[environments.dev.state]
backend = "duckdb"
schema = "sqlbuild_state"

[environments.dev.state.connection]
database = "state.duckdb"
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

[environments.prod.state]
backend = "postgres"
schema = "sqlbuild_state"

[environments.prod.state.connection]
host = "state-db.internal"
port = 5432
user = "sqlbuild_state"
password = "${ENV:STATE_DB_PASSWORD}"
dbname = "sqlbuild_state"
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: 
sqb state init
This creates the state tables in the configured schema. Run it once per physical environment.

​
State lifecycle

CommandDescription
sqb state initCreate state tables
sqb state migrateBack up current state and re-initialize tables
sqb state rollbackRestore from the latest backup
sqb state rollback --backup-id <id>Restore from a specific backup
sqb state reset --auto-approveDrop all state tables (requires allow_reset = true)
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: 
[environments.dev.state.connection]
database = "local-state.duckdb"
This is useful when each developer uses a local state file while the project config points to a shared state database.

​
Per-environment state

Different physical environments can use different state backends: 
[environments.dev.state]
backend = "duckdb"
schema = "sqlbuild_state"

[environments.dev.state.connection]
database = "state.duckdb"

[environments.prod.state]
backend = "postgres"
schema = "sqlbuild_state"

[environments.prod.state.connection]
host = "prod-state.internal"
dbname = "sqlbuild_state"
Each physical environment has its own state store. VDEs are scoped to their physical environment and cannot be promoted or compared across different physical environments.

​
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: 
[environments.dev.state]
backend = "duckdb"
schema = "sqlbuild_state"
unsuffixed_virtual_env = "dev"

[environments.dev.state.connection]
database = "state.duckdb"
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: 
[janitor]
enabled = true
retention_days = 30
max_checkpoints = 20
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