Tables

The Table class in Weasel.Sqlite.Tables provides full DDL generation, delta detection, and schema migration for SQLite tables.

Creating a Table

var table = new Table("users");
table.AddColumn<int>("id").AsPrimaryKey().AutoIncrement();
table.AddColumn<string>("name").NotNull();
table.AddColumn<string>("email").NotNull();
table.AddColumn("settings", "TEXT"); // raw type

^{snippet source | anchor}

Primary Keys and AUTOINCREMENT

Use AsPrimaryKey() and AutoIncrement() for INTEGER PRIMARY KEY AUTOINCREMENT columns:

var table = new Table("users");
table.AddColumn<int>("id").AsPrimaryKey().AutoIncrement();

^{snippet source | anchor}

Generated Columns

SQLite 3.31+ supports GENERATED ALWAYS AS columns with Stored or Virtual types:

var table = new Table("users");
table.AddColumn("email_domain", "TEXT")
    .GeneratedAs("substr(email, instr(email, '@') + 1)", GeneratedColumnType.Stored);

^{snippet source | anchor}

Foreign Keys

Foreign keys must be defined inline at table creation. SQLite does not support ALTER TABLE ADD CONSTRAINT:

var orders = new Table("orders");
orders.AddColumn<int>("id").AsPrimaryKey().AutoIncrement();
orders.AddColumn<int>("user_id").NotNull();

// Define foreign key referencing users table
orders.ForeignKeys.Add(new ForeignKey("fk_orders_user")
{
    ColumnNames = new[] { "user_id" },
    LinkedTable = new SqliteObjectName("users"),
    LinkedNames = new[] { "id" }
});

^{snippet source | anchor}

WARNING

Foreign key enforcement is disabled by default in SQLite. Enable it via PRAGMA settings using SqliteHelper.

Indexes

Add indexes using IndexDefinition, including expression and partial indexes:

var table = new Table("users");

// Unique index
var emailIdx = new IndexDefinition("idx_email") { IsUnique = true };
emailIdx.AgainstColumns("email");
table.Indexes.Add(emailIdx);

// Expression index on JSON path
var jsonIdx = new IndexDefinition("idx_settings_theme");
jsonIdx.ForJsonPath("settings", "$.theme");
table.Indexes.Add(jsonIdx);

// Partial index with WHERE clause
var activeIdx = new IndexDefinition("idx_active_users");
activeIdx.AgainstColumns("name");
activeIdx.Predicate = "active = 1";
table.Indexes.Add(activeIdx);

^{snippet source | anchor}

STRICT Mode and WITHOUT ROWID

Enable strict type checking (SQLite 3.37+) or the WITHOUT ROWID optimization:

var table = new Table("users");
table.StrictTypes = true;   // CREATE TABLE ... (...) STRICT
table.WithoutRowId = true;  // CREATE TABLE ... (...) WITHOUT ROWID

^{snippet source | anchor}

Schema Support

Tables default to the main schema. Use SqliteObjectName or MoveToSchema for temporary tables:

// Temporary table
var temp = new Table(new SqliteObjectName("temp", "session_data"));
// DDL: CREATE TABLE IF NOT EXISTS "temp"."session_data" (...)

// Move existing table definition to temp schema
var table = new Table("users");
table.MoveToSchema("temp");

^{snippet source | anchor}

Delta Detection and Table Recreation

Weasel detects differences between expected and actual schema states. When a change cannot be applied with ALTER TABLE (such as adding a constraint or changing a column type), Weasel automatically recreates the table:

1. Create new table with the desired schema
2. Copy data with INSERT INTO new_table SELECT * FROM old_table
3. Drop the old table
4. Rename the new table

Supported incremental changes include ADD COLUMN, DROP COLUMN (3.35+), and RENAME COLUMN (3.25+).