Schema Evolution

SqlCAD employs a declarative schema management approach, similar to Terraform or Kubernetes. Instead of writing imperative migration scripts (e.g., up.sql, down.sql), you define your desired schema state in .sqlcad files, and SqlCAD calculates the necessary changes to converge the database to that state.

The Diff Engine

The core component enabling this is the Diff Engine.

1. Desired State: Parsed from your .sqlcad project files. This represents the "perfect" world.
2. Current State: Introspected directly from the target database (e.g., DuckDB). This represents the "actual" world.
3. Diff Calculation: The engine compares the two states and generates a list of differences (Diffs).
4. Plan Generation: Diffs are translated into an ordered sequence of DDL statements (e.g., CREATE TABLE, ALTER TABLE ADD COLUMN).

Supported Operations

Currently, SqlCAD supports the following schema evolution operations:

• CREATE TABLE / VIEW: When a new entity is defined in code but missing in the database.
• DROP TABLE / VIEW: When an entity exists in the database but is removed from code (Experimental).
• ADD COLUMN: When a column is added to a definition.
• DROP COLUMN: When a column is removed from a definition.
• ALTER COLUMN TYPE: When a column's data type changes (e.g., INTEGER -> TEXT). Note: This depends on the dialect's support for type coercion.

Workflow

1. Plan

Run sqlcad schema plan to preview changes. This is a read-only operation (unless --apply-on-plan is used) that connects to the database, introspects schema, and prints pending changes.

sqlcad schema plan --target duckdb --db-path warehouse.duckdb

2. Apply

Run sqlcad schema apply (or use --apply-on-plan) to execute the generated SQL.

sqlcad schema plan --target duckdb --db-path warehouse.duckdb --apply-on-plan

Introspection

SqlCAD currently uses duckdb introspection capabilities. It maps database types (e.g., VARCHAR, UBIGINT) to canonical SqlCAD types to minimize false positives.