# https://github.com/SQLMesh/sqlmesh Project Manual Generated at: 2026-06-19 04:59:00 UTC ## Table of Contents - [SQLMesh Overview and Core Architecture](#page-1) - [Database Engine Adapters and External Integrations](#page-2) - [LSP, VSCode Extension, and Web UI](#page-3) - [State Management, Migrations, and CI/CD](#page-4) ## SQLMesh Overview and Core Architecture ### Related Pages Related topics: [Database Engine Adapters and External Integrations](#page-2), [State Management, Migrations, and CI/CD](#page-4)
Related Source Files The following source files were used to generate this page: - [README.md](https://github.com/SQLMesh/sqlmesh/blob/main/README.md) - [web/server/api/endpoints/files.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/files.py) - [web/server/api/endpoints/meta.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/meta.py) - [web/server/api/endpoints/commands.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/commands.py) - [web/server/api/endpoints/plan.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/plan.py) - [web/server/api/endpoints/models.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/models.py) - [web/server/api/endpoints/lineage.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/lineage.py) - [web/server/api/endpoints/environments.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/environments.py) - [web/server/api/endpoints/table_diff.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/table_diff.py) - [web/server/settings.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/settings.py) - [web/server/console.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/console.py) - [web/server/watcher.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/watcher.py) - [web/server/exceptions.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/exceptions.py) - [web/common/package.json](https://github.com/SQLMesh/sqlmesh/blob/main/web/common/package.json)
# SQLMesh Overview and Core Architecture ## 1. Project Purpose and Positioning SQLMesh is a next-generation data transformation framework that lets data teams define, test, and deploy SQL or Python transformations with strong correctness guarantees. Per the project README, it is positioned as "more than just a dbt alternative," emphasizing that its value proposition is broader than incremental model materialization [README.md]. Its headline capabilities include: - **Virtual Data Environments** that let engineers preview changes against production data without mutating it [README.md]. - A **plan/apply workflow** that shows what will change before warehouse execution [README.md]. - **Cross-dialect transpilation** so models authored in any SQL dialect can be executed against a different target engine [README.md]. - **Column-level lineage** that surfaces impact before code is shipped to production [README.md]. The latest published release, v0.235.4, demonstrates the project's continued expansion of engine support and reliability fixes (e.g., DuckLake override paths, ClickHouse `secure` field, JSON cast preservation), reinforcing its positioning as a portable transformation layer [README.md release notes]. ## 2. Core Architectural Layers SQLMesh is structured into several cooperating layers. The Python core owns model parsing, planning, and snapshot management; an execution layer talks to target warehouses; and a web server exposes the same surface area over HTTP for IDE-style clients. ```mermaid flowchart LR A[SQL/Python Models
models/, audits/, seeds/] --> B[Context
sqlmesh.core.context] B --> C[Plan Builder
sqlmesh.core.plan] C --> D[Snapshots
sqlmesh.core.snapshot] D --> E[Engine Adapter
Athena, ClickHouse, StarRocks, DuckDB, ...] B --> F[Web Server API
web/server/api/endpoints] F --> G[Web UI
web/common - React + Vite] F --> H[SSE Console Stream
web/server/console.py] B --> I[Watchfiles Watcher
web/server/watcher.py] ``` - **Project source tree.** The web watcher monitors the `models/`, `audits/`, `macros/`, `metrics/`, and `seeds/` directories of a SQLMesh project for changes that should trigger context refresh [web/server/watcher.py]. - **Context.** The `Context` (referenced throughout the API endpoints, e.g. `get_loaded_context`) is the runtime facade that loads project files, resolves state, and exposes models to consumers [web/server/settings.py]. - **Plan / Apply.** Plan construction is exposed through `POST /plan` and applied through `POST /apply`, both running asynchronously via `asyncio.create_task` on the server side [web/server/api/endpoints/plan.py, web/server/api/endpoints/commands.py]. - **Engine adapters.** SQLMesh targets many warehouses. Community engagement around adapters is significant: issue #1315 requests Athena, #1300 requests ClickHouse (since implemented — v0.235.4 added a `secure` field), #5610 requests StarRocks with custom `DISTRIBUTED BY` / `PARTITION BY` DDL, and #4724 requests S3 Tables support [community context]. ## 3. Virtual Data Environments and the Plan Lifecycle Virtual Data Environments (VDEs) are first-class entities managed by SQLMesh. They are persisted, can be created from a plan, and can be invalidated or deleted via the API. | Capability | API Surface | Notes | |---|---|---| | List environments (prod + custom + default target) | `GET /environments` [web/server/api/endpoints/environments.py] | Backfills a synthetic `prod` environment if missing | | Delete / invalidate an environment | `DELETE /environments/{environment}` [web/server/api/endpoints/environments.py] | Calls `state_sync.invalidate_environment` and purges expired artifacts | | Build a plan against an environment | `POST /plan` [web/server/api/endpoints/plan.py] | Accepts `environment`, `plan_dates`, `plan_options`, `categories` | | Apply a plan | `POST /apply` [web/server/api/endpoints/commands.py] | Returns a `PlanApplyStageTracker` for streaming progress | The plan lifecycle is: a plan is built, its diff is rendered (model changes, schema changes, intervals), and only after explicit approval is it applied to a target environment. The server streams stage transitions back to the UI via `ApiConsole`, a subclass of `TerminalConsole` that pushes SSE events through an `asyncio.Queue` [web/server/console.py]. This design decouples long-running warehouse work from the HTTP request thread. ## 4. Web Server, Lineage, and Extensibility The FastAPI server is intentionally thin and delegates all business logic to the underlying `Context`. Settings resolve the project path, config, gateway, and UI mode, and map each `Mode` (IDE, CATALOG, PLAN) to a set of `Modules` the client should load [web/server/settings.py]. This lets the same server power both a full IDE experience and a read-only data catalog. Two capabilities that recur in community discussions are exposed through dedicated endpoints: - **Column-level lineage** is served by `web/server/api/endpoints/lineage.py`, which builds an adjacency list of column dependencies per model. Community issue #3565 ("SQLMesh Lineage Integration with Data Catalog") requests deeper integration with external catalogs such as Open Metadata Catalog (OMC), suggesting that the existing in-app lineage is the seed point for a broader catalog story [community context]. - **Table diff** is served by `web/server/api/endpoints/table_diff.py`, returning schema diffs, row diffs, and column stats so users can verify that a development environment matches production before promoting it. The frontend is a separate workspace (`web/common`) built with React + Vite + Tailwind, packaged as an ES module with a `dist/index.d.ts` type surface for embedding [web/common/package.json]. Build scripts include `build`, `storybook`, `lint`, and `syncpack` for dependency hygiene. Errors raised by any endpoint are wrapped in `ApiException`, which captures the origin string, timestamp, and traceback so the UI can render a structured error payload [web/server/exceptions.py]. ### Common Failure Modes to Watch - **Async task already running.** Both `/plan` and `/apply` short-circuit if `request.app.state.task` is not yet `done()`, preventing concurrent plan evaluation [web/server/api/endpoints/plan.py, web/server/api/endpoints/commands.py]. - **Project file changes during a plan.** The `watchfiles`-based watcher ignores `.env`, the `cache/` directory, and `.db`/`.db-wal` files to avoid spurious reloads during DuckDB-based local runs [web/server/watcher.py]. - **Engine DDL mismatches.** Engines that need non-standard `CREATE TABLE` syntax (StarRocks `DISTRIBUTED BY`, S3 Tables, etc.) require adapter-level work — the generic `mysql` adapter will not suffice, as called out in #5610 [community context]. ## See Also - SQLMesh Models and SQL Definition - Virtual Data Environments Deep Dive - Plan and Apply Workflow - Engine Adapters and Execution - Web UI and API Reference --- ## Database Engine Adapters and External Integrations ### Related Pages Related topics: [SQLMesh Overview and Core Architecture](#page-1), [State Management, Migrations, and CI/CD](#page-4)
Related Source Files The following source files were used to generate this page: - [README.md](https://github.com/SQLMesh/sqlmesh/blob/main/README.md) - [web/server/settings.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/settings.py) - [web/server/models.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/models.py) - [web/server/console.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/console.py) - [web/server/api/endpoints/meta.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/meta.py) - [web/server/api/endpoints/modules.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/modules.py) - [web/server/api/endpoints/models.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/models.py) - [web/server/api/endpoints/lineage.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/lineage.py) - [web/server/api/endpoints/table_diff.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/table_diff.py) - [web/server/api/endpoints/plan.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/plan.py) - [web/server/api/endpoints/commands.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/commands.py) - [web/server/api/endpoints/files.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/files.py) - [web/client/package.json](https://github.com/SQLMesh/sqlmesh/blob/main/web/client/package.json) - [web/common/package.json](https://github.com/SQLMesh/sqlmesh/blob/main/web/common/package.json) - [tooling/README.md](https://github.com/SQLMesh/sqlmesh/blob/main/tooling/README.md)
# Database Engine Adapters and External Integrations ## Overview and Purpose SQLMesh positions itself as a next-generation data transformation framework that abstracts over many execution engines through a uniform `Context` interface. As stated in the project README, SQLMesh "is more than just a dbt alternative" and emphasizes shipping data transformations "at any size" with visibility and control — capabilities that depend on a broad adapter layer to reach warehouses, lakehouses, and embedded engines. Source: [README.md:23-30]() The public web surface exposes this engine-agnostic design. The FastAPI server routes (file, model, lineage, plan, table-diff, commands) all import `sqlmesh.core.context.Context` as the single entry point, which is the contract that every engine adapter must satisfy. Source: [web/server/api/endpoints/lineage.py:9-15](), [web/server/api/endpoints/table_diff.py:14-19](), [web/server/api/endpoints/plan.py:12-18]() ## Engine-Agnostic Web API Surface The web server presents a consistent, engine-neutral API for any underlying warehouse. Each endpoint module maps a REST call onto a context method, so clients do not need to know which engine is configured. | Endpoint | Engine-Agnostic Capability | Source | | --- | --- | --- | | `/api/meta` | Reports SQLMesh version, active plan task, and per-node colors from `context.config.ui.node_colors` | [web/server/api/endpoints/meta.py:14-39]() | | `/api/modules` | Enumerates enabled feature modules per UI mode | [web/server/api/endpoints/modules.py:13-20]() | | `/api/models` | Serializes all models, normalizing names via the context's dialect | [web/server/api/endpoints/models.py:18-50]() | | `/api/lineage` | Builds a column-level adjacency list using `sqlmesh.core.lineage` over any dialect | [web/server/api/endpoints/lineage.py:30-60]() | | `/api/table_diff` | Compares source/target tables, producing schema and row diffs engine-agnostically | [web/server/api/endpoints/table_diff.py:21-66]() | | `/api/plan` | Initiates `PlanBuilder` evaluation inside the asyncio task pool | [web/server/api/endpoints/plan.py:30-55]() | | `/api/commands/apply` | Drives `run_plan_apply` via `run_in_executor`, gated by a circuit breaker | [web/server/api/endpoints/commands.py:30-60]() | | `/api/files` | Reads and writes project files relative to `settings.project_path` | [web/server/api/endpoints/files.py:27-55]() | The `Settings` class wires the `project_path`, optional `gateway`, and `ui_mode` into the context, so the same backend can serve an IDE, a catalog browser, or a plan-review UI. Source: [web/server/settings.py:35-50](). UI modes map to module sets: `IDE` enables editor, files, data-catalog, errors, and plans; `CATALOG` enables only data-catalog; `PLAN` enables plans, data-catalog, lineage, and errors. Source: [web/server/settings.py:28-34]() ```mermaid flowchart LR Client[Web Client] -->|HTTP/SSE| FastAPI[FastAPI Endpoints] FastAPI --> Context[sqlmesh.core.context.Context] Context --> AdapterA[Engine Adapter: Warehouse / Lakehouse] Context --> AdapterB[Engine Adapter: Embedded Engine] Context --> AdapterC[Engine Adapter: Cloud DW] FastAPI -->|SSE events| ApiConsole[ApiConsole / TerminalConsole] ApiConsole --> Tracker[PlanApply / PlanOverview / PlanCancel Trackers] ``` The `ApiConsole` (a `TerminalConsole` subclass) emits plan, apply, test, and cancel events to the client via Server-Sent Events, so any engine that the context supports can be observed through the same UI. Source: [web/server/console.py:18-65]() ## External Integrations and Model Types Beyond raw engine execution, SQLMesh integrates with several external systems, and the web API surfaces these distinctions through a typed `ModelType` enum: - `PYTHON` — Python-defined models - `SQL` — SQL-defined models - `SEED` — Seed/CSV-loaded models - `EXTERNAL` — Externally managed tables - `SOURCE` — Source-only declarations (no materialization) Source: [web/server/models.py:43-48]() Two `ApplyType` values (`virtual` and `backfill`) capture how a plan is realized in the target environment, and `PlanStage` plus `Status` track progress through the apply pipeline. Source: [web/server/models.py:60-78]() The web client itself is split into `@tobikodata/sqlmesh-client` (the React/Vite UI) and `@tobikodata/sqlmesh-common` (shared React components, Tailwind styling, and the Storybook-based component library), which lets the catalog and IDE be deployed independently of the API server. Source: [web/client/package.json:1-40](), [web/common/package.json:1-25]() ## Community-Driven Adapter Expansion The community issue tracker is the primary signal for which engines are next on the roadmap. Open requests visible in the repository include: - **Athena** — long-standing request (issue #1315) for an AWS Athena adapter - **ClickHouse** — engine support requested (issue #1300), with the v0.235.4 release adding a `secure` field to the ClickHouse connection config (PR #5751) - **StarRocks** — feature request #5610 noting that the existing `mysql` adapter is insufficient because StarRocks needs `DISTRIBUTED BY` / `PARTITION BY` DDL - **S3 Tables** — request #4724 to support AWS S3 table-buckets and lakehouse formats - **Data catalog lineage** — feature request #3565 to push SQLMesh lineage into an external catalog such as OpenMetadata The v0.235.4 release also shipped a DuckLake override data path (PR #5784) and Databricks fixes, indicating that existing adapters continue to receive engine-specific refinements alongside new integrations. ## See Also - Project architecture and CLI quickstart: see the [README.md](https://github.com/SQLMesh/sqlmesh/blob/main/README.md) and the `sqlmesh/core/context.py` module. - Lineage graph construction: [web/server/api/endpoints/lineage.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/lineage.py). - Plan/Apply workflow: [web/server/api/endpoints/plan.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/plan.py) and [web/server/api/endpoints/commands.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/commands.py). - Web UI configuration and module gating: [web/server/settings.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/settings.py) and [web/server/models.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/models.py). --- ## LSP, VSCode Extension, and Web UI ### Related Pages Related topics: [SQLMesh Overview and Core Architecture](#page-1)
Related Source Files The following source files were used to generate this page: - [README.md](https://github.com/SQLMesh/sqlmesh/blob/main/README.md) - [tooling/README.md](https://github.com/SQLMesh/sqlmesh/blob/main/tooling/README.md) - [pnpm-workspace.yaml](https://github.com/SQLMesh/sqlmesh/blob/main/pnpm-workspace.yaml) - [web/common/package.json](https://github.com/SQLMesh/sqlmesh/blob/main/web/common/package.json) - [web/client/package.json](https://github.com/SQLMesh/sqlmesh/blob/main/web/client/package.json) - [web/server/models.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/models.py) - [web/server/api/endpoints/modules.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/modules.py) - [web/server/api/endpoints/files.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/files.py) - [web/server/api/endpoints/meta.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/meta.py) - [web/server/api/endpoints/table_diff.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/table_diff.py) - [web/server/api/endpoints/models.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/models.py) - [web/server/api/endpoints/commands.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/commands.py) - [web/server/api/endpoints/directories.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/directories.py) - [web/server/api/endpoints/plan.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/plan.py) - [web/server/api/endpoints/lineage.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/lineage.py)
# LSP, VSCode Extension, and Web UI ## Overview SQLMesh ships with three primary developer-facing surfaces in addition to its Python and SQL API: 1. **Language Server Protocol (LSP)** — provides SQL completions, formatting, and language tooling that integrate with editors. 2. **VSCode Extension** — a TypeScript-based extension layered on top of the LSP, the message bus, and the shared React component library. 3. **Web UI** — a React-based planning and lineage UI that talks to a FastAPI server wrapping SQLMesh's core `Context`. The README advertises "instant SQL impact and context of your changes, both in the CLI and in the SQLMesh VSCode Extension" ([README.md](README.md)). The install snippet for VSCode users is `pip install 'sqlmesh[lsp]'`, which pulls the LSP extras needed for the extension to function. The broader ecosystem relies on a pnpm workspace that ties together the extension, the message bus between the extension and the LSP, the React web client, and a shared component library. ## Monorepo Layout (pnpm Workspace) The VSCode extension and the Web UI are co-located in a pnpm workspace. According to [pnpm-workspace.yaml](pnpm-workspace.yaml), the workspace contains five packages: | Package | Role | |---|---| | `vscode/bus` | Bridge between the VSCode extension and the LSP process. | | `vscode/extension` | VSCode extension entry point. | | `vscode/react` | React rendering layer used inside VSCode webviews. | | `web/client` | Standalone React web client (the Web UI). | | `web/common` | Shared design-system / component library exported as `@tobikodata/sqlmesh-common`. | The shared library `@tobikodata/sqlmesh-common` is built with `tsc` + `vite` and exposes both ESM and UMD bundles plus a Tailwind-compiled stylesheet ([web/common/package.json](web/common/package.json)). The web client (`web/client`) depends on `reactflow` for DAG rendering and `@uiw/react-codemirror` for the SQL editor ([web/client/package.json](web/client/package.json)). Editor-side tooling configuration (lint, format, VSCode workspace settings) lives under `tooling/` and is wired through the Makefile target `make vscode_settings`, which copies sample VSCode configs into the user's editor settings ([tooling/README.md](tooling/README.md)). ## Architecture: How the Surfaces Talk to SQLMesh Core The Web UI follows a standard three-tier pattern. The React web client calls a FastAPI server, which constructs a SQLMesh `Context` and proxies user actions to it. The VSCode extension uses the LSP process for language features and a separate React webview (rendered with the shared components) for richer views. ```mermaid flowchart LR User[User / Browser] VSCode[VSCode Extension] LSP[LSP Process] WebClient["web/client React App"] WebServer["FastAPI web/server"] Core["sqlmesh.core.context.Context"] User --> WebClient VSCode --> LSP VSCode --> Core WebClient -- "HTTP / REST" --> WebServer WebServer -- "get_loaded_context / get_context" --> Core LSP -- "completions, diagnostics" --> VSCode ``` The FastAPI server's endpoint layer is intentionally thin: handlers depend on `Context` via `Depends(get_loaded_context)` and delegate the heavy lifting to SQLMesh's core. For example, the models endpoint refreshes the context and serializes every loaded model ([web/server/api/endpoints/models.py](web/server/api/endpoints/models.py)). The files endpoint reads and writes project files and uses `format_model_expressions` from `sqlmesh.core.dialect` to keep SQL formatting consistent ([web/server/api/endpoints/files.py](web/server/api/endpoints/files.py)). ## Web Server Feature Modules The web server exposes features as toggleable modules so deployments can run a read-only viewer or a full editor. The canonical list is defined as a string enum in [web/server/models.py](web/server/models.py): | Module key | Capability | |---|---| | `editor` | Edit files and run queries | | `files` | Browse project files | | `data-catalog` | Data catalog browser | | `plans` | Run and apply plans | | `tests` | Run tests | | `audits` | Run audits | | `errors` | Surface errors | | `data` | Query data | | `lineage` | Render column/table lineage | The `/api/modules` endpoint returns the configured list ([web/server/api/endpoints/modules.py](web/server/api/endpoints/modules.py)), which the client uses to render only the enabled tabs. The same `Models` enum is consulted by other endpoints: for example, the meta endpoint only logs plan events when `Modules.PLANS` is enabled in the active settings ([web/server/api/endpoints/meta.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/meta.py)). ## Plan, Apply, and Table-Diff Surfaces Plan and apply are the most action-heavy endpoints and are implemented as long-running async tasks guarded by `request.app.state.task`: - `POST /api/plan` builds a `PlanBuilder` off the executor and returns a `PlanOverviewStageTracker` ([web/server/api/endpoints/plan.py](web/server/api/endpoints/plan.py)). - `POST /api/commands/apply` applies a plan and exposes `SnapshotChangeCategory` overrides per model ([web/server/api/endpoints/commands.py](web/server/api/endpoints/commands.py)). The meta endpoint reports version information, any in-flight task, and the configured UI node colors that the DAG renderer uses to color nodes in the lineage view ([web/server/api/endpoints/meta.py](web/server/api/endpoints/meta.py)): ```python node_colors: dict[str, str] = {} context = get_context(settings) if context: node_colors = context.config.ui.node_colors ``` The table-diff endpoint serializes schema and row diffs as PyArrow-friendly payloads so the web client can stream large samples back to the browser ([web/server/api/endpoints/table_diff.py](web/server/api/endpoints/table_diff.py)). Directory operations (create, rename, delete) round out the file system surface ([web/server/api/endpoints/directories.py](web/server/api/endpoints/directories.py)). ## Lineage Visualization Column-level lineage is one of the headline features of the Web UI. The endpoint [web/server/api/endpoints/lineage.py](web/server/api/endpoints/lineage.py) builds an adjacency list that includes CTE nodes, not just upstream models. Three helpers do the work: - `quote_column` — re-quotes column identifiers in the project's dialect. - `get_source_name` — returns the upstream model name for a node, falling back to a `": "` form when the source is a CTE. - `get_column_name` — extracts the alias from an `exp.Alias` or falls back to `exp.to_column(node.name).name`. The resulting graph is rendered with `reactflow` in the web client and laid out with `elkjs`, both declared as dependencies of `web/client` ([web/client/package.json](web/client/package.json)). This area is also where the community is pushing for more: [issue #3565](https://github.com/SQLMesh/sqlmesh/issues/3565) requests deeper lineage integration with data catalogs such as Open Metadata Catalog (OMC), so users can visualize origin and impact across the broader data platform rather than only inside SQLMesh. ## Common Failure Modes and Limitations - **Plan/apply conflict:** Because plan and apply share `request.app.state.task`, issuing a second request while one is in flight is silently ignored; the user sees no progress until the first task completes ([web/server/api/endpoints/plan.py](web/server/api/endpoints/plan.py), [web/server/api/endpoints/commands.py](web/server/api/endpoints/commands.py)). - **Module mismatch in the client:** If the server is configured without `Modules.LINEAGE` but the client tries to navigate to the lineage tab, the endpoint will not be available; the `Modules` enum is the single source of truth ([web/server/models.py](web/server/models.py)). - **Dialect-specific quoting:** Lineage column names must round-trip through the project's dialect, and `quote_column` is the only place that handles this — engine adapters with non-standard quoting (Athena, ClickHouse, StarRocks, S3 Tables, all of which appear as open community requests in #1315, #1300, #5610, and #4724) may surface mismatches here. ## See Also - [README.md](https://github.com/SQLMesh/sqlmesh/blob/main/README.md) — High-level overview of the VSCode extension and Web UI. - [tooling/README.md](https://github.com/SQLMesh/sqlmesh/blob/main/tooling/README.md) — Editor configuration tooling. - [pnpm-workspace.yaml](https://github.com/SQLMesh/sqlmesh/blob/main/pnpm-workspace.yaml) — Monorepo workspace declaration. - [web/server/models.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/models.py) — Module/feature flag enum used by the UI. - [web/server/api/endpoints/lineage.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/lineage.py) — Column-level lineage graph builder. - [web/client/package.json](https://github.com/SQLMesh/sqlmesh/blob/main/web/client/package.json) — Web client dependencies (React Flow, CodeMirror, ELK). - [Issue #3565 — Lineage Integration with Data Catalog](https://github.com/SQLMesh/sqlmesh/issues/3565) — Open feature request relevant to the lineage UI. --- ## State Management, Migrations, and CI/CD ### Related Pages Related topics: [SQLMesh Overview and Core Architecture](#page-1), [Database Engine Adapters and External Integrations](#page-2)
Related Source Files The following source files were used to generate this page: - [web/server/api/endpoints/plan.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/plan.py) - [web/server/api/endpoints/commands.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/commands.py) - [web/server/console.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/console.py) - [web/server/settings.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/settings.py) - [web/server/models.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/models.py) - [web/server/api/endpoints/meta.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/meta.py) - [web/server/api/endpoints/files.py](https://github.com/SQLMesh/sqlmesh/blob/main/web/server/api/endpoints/files.py)
# State Management, Migrations, and CI/CD SQLMesh is a data transformation framework whose core state engine tracks every model as a versioned *snapshot* inside named *environments*. The web server shipped in `web/server` exposes that engine as a FastAPI surface so that operators, IDEs, and CI/CD systems can drive plan evaluation, environment promotion, and schema migration through ordinary HTTP calls rather than shelling out to the SQLMesh CLI. ## State Management via the Plan Lifecycle A `Plan` is the only sanctioned way to mutate SQLMesh state. Every interaction — preview, apply, cancel — is mediated through a `PlanBuilder` that resolves the candidate snapshots and the change category (`SnapshotChangeCategory`) for each affected model. `web/server/api/endpoints/plan.py:1-12` imports these primitives directly: ```python from sqlmesh.core.context import Context from sqlmesh.core.environment import EnvironmentNamingInfo from sqlmesh.core.plan import Plan, PlanBuilder from sqlmesh.core.snapshot.definition import SnapshotChangeCategory ``` Because plan evaluation against a large graph can take minutes, the API launches each request as an `asyncio` task rather than blocking the request lifecycle. `web/server/api/endpoints/plan.py` shows the pattern in `initiate_plan`: ```python request.app.state.task = asyncio.create_task( run_in_executor( get_plan_builder, context, plan_options, environment, plan_dates, categories ) ) ``` `web/server/console.py:1-15` layers a stage-tracking console on top of `TerminalConsole`, emitting `PlanOverviewStageTracker`, `PlanApplyStageTracker`, and `PlanCancelStageTracker` objects through an `asyncio.Queue` so SSE clients can render progress without polling. ```mermaid stateDiagram-v2 [*] --> Requested Requested --> Evaluating: initiate_plan Evaluating --> Applied: commands/apply Evaluating --> Cancelled: cancel Applied --> [*] Cancelled --> [*] ``` ## Migrations: Virtual vs Backfill Applying a plan is the migration step that moves SQLMesh from one consistent state to another. `web/server/models.py` defines the two supported strategies as the `ApplyType` enum (`virtual` and `backfill`). The web API in `web/server/api/endpoints/commands.py` clears a per-app circuit breaker before launching the apply task, ensuring that a previous failed run does not poison subsequent attempts: ```python request.app.state.circuit_breaker.clear() request.app.state.task = asyncio.create_task( run_in_executor( run_plan_apply, context, environment, plan_options, plan_dates, categories, request.app.state... ``` | Apply type | Effect on production tables | Typical CI/CD stage | |------------|------------------------------|---------------------| | `virtual` | No writes to prod; new code lands in a Virtual Data Environment | Pre-merge validation, code review, QA | | `backfill` | Physically rewrites the prod tables for the requested intervals | Production promotion, breaking schema fixes | Change categories from `SnapshotChangeCategory` (referenced in both `plan.py` and `commands.py`) classify each delta so a CI pipeline can route non-breaking changes to `virtual` and breaking ones to `backfill` automatically. ## CI/CD Integration The web server is mode-aware, and the mode is the primary deployment switch for CI/CD. `web/server/settings.py` defines `MODE_TO_MODULES`, which maps each top-level mode to the set of API modules it should expose: ```python MODE_TO_MODULES = { models.Mode.IDE: {EDITOR, FILES, DATA_CATALOG, ERRORS, PLANS}, models.Mode.CATALOG: {DATA_CATALOG}, models.Mode.PLAN: {PLANS, DATA_CATALOG, LINEAGE, ERRORS}, } ``` The mode is selected at boot via the `UI_MODE` environment variable in `web/server/settings.py`: ```python ui_mode: Mode = Field( default_factory=lambda: Mode[os.getenv("UI_MODE", Mode.IDE.value).upper()] ) ``` A typical CI/CD topology therefore deploys the same image three times — once as an `IDE` for engineers, once as `CATALOG` for analysts, and once as `PLAN` for automated promotion. The `PLAN` mode intentionally omits the `EDITOR` and `FILES` modules so untrusted runners cannot rewrite project sources. Because every plan operation is a plain HTTP call, a pipeline step is just: 1. `POST /api/commands/apply` with `environment=prod` and `apply_type=virtual` against staging. 2. Run audits and model tests via the test endpoints. 3. Repeat with `apply_type=backfill` once validation passes. Community demand for richer lineage visibility (see issue #3565, "SQLMesh Lineage Integration with Data Catalog") further pushes this surface toward plug-in catalog backends, but the plan/apply endpoints remain the stable contract today. ## Operational Observability Every long-running plan is observable through the meta endpoint. `web/server/api/endpoints/meta.py` exposes `has_running_task`, letting CI scripts and dashboards block on completion: ```python has_running_task = hasattr(request.app.state, "task") and not request.app.state.task.done() ``` The same endpoint clears a `plan_cancellation` flag once the in-flight task finishes (`finish_plan_cancellation`), preventing zombie cancel state from leaking into the next run. Combined with the SSE-backed stage stream from `web/server/console.py`, operators can follow each `initiate_plan` and `initiate_apply` from start to finish. The `web/server/api/endpoints/files.py` module closes the loop by exposing validated file write endpoints (`POST /api/files/{path:path}`), so the same HTTP surface that runs a migration can also update the SQL/Python models that drive it. This lets a CI job stage a change, write the new model definition, and immediately re-evaluate the plan — all without leaving the FastAPI boundary. ## See Also - Virtual Data Environments and the `ApplyType` enum - Adapter support matrix (community requests for Athena #1315, ClickHouse #1300, StarRocks #5610, S3 Tables #4724) - VS Code Extension "Plan Mode" integration --- --- ## Pitfall Log Project: SQLMesh/sqlmesh Summary: Found 13 structured pitfall item(s), including 4 high/blocking item(s). Top priority: Installation risk - Installation risk requires verification. ## 1. Installation risk - Installation risk requires verification - Severity: high - Evidence strength: source_linked - Finding: Project evidence flags a installation risk. Review the linked source before relying on this workflow. - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: community_evidence:github | https://github.com/SQLMesh/sqlmesh/issues/5765 ## 2. Installation risk - Installation risk requires verification - Severity: high - Evidence strength: source_linked - Finding: Project evidence flags a installation risk. Review the linked source before relying on this workflow. - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: community_evidence:github | https://github.com/SQLMesh/sqlmesh/issues/5350 ## 3. Configuration risk - Configuration risk requires verification - Severity: high - Evidence strength: source_linked - Finding: Project evidence flags a configuration risk. Review the linked source before relying on this workflow. - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: community_evidence:github | https://github.com/SQLMesh/sqlmesh/issues/5841 ## 4. Capability evidence risk - Capability evidence risk requires verification - Severity: high - Evidence strength: source_linked - Finding: Project evidence flags a capability evidence risk. Review the linked source before relying on this workflow. - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: community_evidence:github | https://github.com/SQLMesh/sqlmesh/issues/5767 ## 5. Installation risk - Installation risk requires verification - Severity: medium - Evidence strength: source_linked - Finding: Project evidence flags a installation risk. Review the linked source before relying on this workflow. - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: community_evidence:github | https://github.com/SQLMesh/sqlmesh/issues/5842 ## 6. Configuration risk - Configuration risk requires verification - Severity: medium - Evidence strength: source_linked - Finding: Project evidence flags a configuration risk. Review the linked source before relying on this workflow. - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: community_evidence:github | https://github.com/SQLMesh/sqlmesh/issues/5843 ## 7. Capability evidence risk - Capability evidence risk requires verification - Severity: medium - Evidence strength: source_linked - Finding: README/documentation is current enough for a first validation pass. - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: capability.assumptions | https://github.com/SQLMesh/sqlmesh ## 8. Maintenance risk - Maintenance risk requires verification - Severity: medium - Evidence strength: source_linked - Finding: Project evidence flags a maintenance risk. Review the linked source before relying on this workflow. - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: evidence.maintainer_signals | https://github.com/SQLMesh/sqlmesh ## 9. Security or permission risk - Security or permission risk requires verification - Severity: medium - Evidence strength: source_linked - Finding: no_demo - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: downstream_validation.risk_items | https://github.com/SQLMesh/sqlmesh ## 10. Security or permission risk - Security or permission risk requires verification - Severity: medium - Evidence strength: source_linked - Finding: no_demo - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: risks.scoring_risks | https://github.com/SQLMesh/sqlmesh ## 11. Security or permission risk - Security or permission risk requires verification - Severity: medium - Evidence strength: source_linked - Finding: Project evidence flags a security or permission risk. Review the linked source before relying on this workflow. - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: community_evidence:github | https://github.com/SQLMesh/sqlmesh/issues/5840 ## 12. Maintenance risk - Maintenance risk requires verification - Severity: low - Evidence strength: source_linked - Finding: issue_or_pr_quality=unknown。 - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: evidence.maintainer_signals | https://github.com/SQLMesh/sqlmesh ## 13. Maintenance risk - Maintenance risk requires verification - Severity: low - Evidence strength: source_linked - Finding: release_recency=unknown。 - User impact: May increase setup, validation, or first-run risk for the user. - Evidence: evidence.maintainer_signals | https://github.com/SQLMesh/sqlmesh