Schema builder

The schema builder (navree::schema) describes tables in Rust and writes the SQL each database needs: PostgreSQL (with TimescaleDB), MySQL, MariaDB and SQLite. Migrations use it through the Schema they receive; anywhere else, build one on a connection with Schema::new(db.conn()).

use navree::schema::prelude::*;

schema
    .create("companies", |t| {
        t.uuid_primary();
        t.string("name").unique();
        t.string("domain").nullable();
        t.foreign_uuid("owner_id")
            .nullable()
            .constrained_on("users", "id")
            .null_on_delete();
        t.timestamps_tz();
        t.soft_deletes_tz();
    })
    .await?;

create takes a closure that describes the table on a Blueprint (t); table alters one the same way. Before any SQL runs, the blueprint is checked: duplicate columns, two primary keys, a decimal scale larger than its precision, a modifier that doesn't apply to its column's type, a type the database doesn't have. Each mistake fails with a message saying how to fix it, so a migration never half-runs on a typo. Then every statement runs in order on the schema's connection, inside a migration its transaction.

navree::schema::prelude brings Schema, SchemaResult, Blueprint, raw, ReferentialAction and the TimescaleDB types (Hypertable, Compression, ContinuousAggregate, RefreshPolicy, KeyStrategy).

Tables

The columns and indexes and foreign keys pages list what goes in the closure. Inside create, these set options of the table itself:

MySQL tables get DEFAULT CHARACTER SET utf8mb4 unless the configuration says otherwise (below).

Altering tables

schema
    .table("deals", |t| {
        t.string("stage").default("lead").index();        // add
        t.text("notes").nullable().after("title");        // add, placed (MySQL)
        t.decimal("amount", 14, 2).change();              // change
        t.rename_column("value", "amount_cents");
        t.drop_column(["legacy", "imported_at"]);
        t.drop_index(["stage", "created_at"]);
        t.drop_unique("deals_code_unique");
        t.drop_foreign(["company_id"]);
        t.rename_index("deals_title_index", "deals_by_title");
    })
    .await?;

In table, every call is a command, run in the order you wrote them: t.string("slug") followed by t.unique(["slug"]) adds the column, then the index. Changing columns and dropping indexes have the details. t.drop_table(), t.drop_table_if_exists() and t.rename(to) act on the table itself.

SQLite

SQLite's ALTER TABLE adds, renames and drops plain columns. For everything else (changing a column, adding or dropping a primary key or a foreign key, adding a column it can't add in place, dropping one that a key, a constraint or an index uses), the builder rebuilds the table, as SQLite documents: it reads the table as it is, creates the new one as __temp__<table>, copies the rows, drops the old table, renames the new one, and recreates the indexes and triggers. Collations, checks, generated columns, partial indexes and AUTOINCREMENT survive. Several changes in one table call share one rebuild.

Dropping the old table must not run the ON DELETE actions of the tables that reference it, so the migrator runs SQLite migrations on a connection of their own with foreign keys off, and checks them (PRAGMA foreign_key_check) before each commit. Outside a migration, rebuilding a table other tables reference, on a connection with foreign keys on, fails with that explanation instead of deleting rows by cascade. Adding a NOT NULL column without a default to a table that has rows fails with how to fix it.

Reading the schema

A schema reads the database's catalogs:

if !schema.has_column("deals", "currency").await? {
    schema.table("deals", |t| { t.char("currency", 3).default("USD"); }).await?;
}

schema
    .when_table_doesnt_have_column("deals", "currency", |t| {
        t.char("currency", 3).default("USD");
    })
    .await?;

when_table_has_column and when_table_doesnt_have_column alter a table only when it has (or lacks) a column. On TimescaleDB, is_hypertable(table) and time_dimension(table) read how a table is partitioned.

Emptying a database

drop_all_tables(), drop_all_views() and drop_all_types() (PostgreSQL) empty a database, as migrate:fresh does. disable_foreign_key_constraints() and enable_foreign_key_constraints() switch foreign-key checks off and on for the connection (on PostgreSQL, disable defers them to the end of the transaction), and ensure_extension(name) creates a PostgreSQL extension when it is missing.

Previews

Schema::offline(target) records the SQL for a database without connecting to one, for tests and for checking what a migration writes for another database:

use navree::schema::{Schema, Target};

let schema = Schema::offline(Target::postgres(17));
schema
    .create("deals", |t| {
        t.id();
        t.string("title");
        t.timestamps_tz();
    })
    .await?;
assert_eq!(
    schema.statements(),
    ["CREATE TABLE \"deals\" (\"id\" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \
      \"title\" VARCHAR(255) NOT NULL, \"created_at\" TIMESTAMP WITH TIME ZONE NULL, \
      \"updated_at\" TIMESTAMP WITH TIME ZONE NULL)"]
);

Targets name the database and its version, which the builder checks features against: Target::postgres(17).with_timescaledb((2, 17)), Target::mysql((8, 4)), Target::mariadb((11, 4)), Target::sqlite((3, 46)). Virtual generated columns need PostgreSQL 18, NULLS NOT DISTINCT PostgreSQL 15, RENAME COLUMN MySQL 8.0.3 or MariaDB 10.5.2, expression defaults MySQL 8.0.13, and vector columns MySQL 9 or MariaDB 11.7: an older target fails with that requirement instead of failing SQL. A connected schema detects its target from the server once (version, installed and available extensions).

An offline schema can't plan what needs the live table: a SQLite rebuild, or converting a table into a hypertable, fails with "needs a database connection". Schema::new(conn).pretending() reads the real database and records what it would change instead of changing it, which is what migrate --pretend does. Both kinds give the statements with schema.statements() and what the database couldn't do as asked with schema.notices().

Configuration

The schema section configures the builder, and every migration's schema:

[schema]
default_string_length = 255   # `string` columns without `.length(n)`
default_time_precision = 6    # fractional seconds of time columns
morph_key = "int"             # int, uuid or ulid: the id column of `morphs`
timescaledb = "auto"          # auto, require or off
create_extensions = true      # may run CREATE EXTENSION timescaledb
hypertable_keys = "extend"    # extend or reject primary keys missing the time column
mysql_charset = "utf8mb4"     # MySQL tables' default character set
# mysql_collation = "utf8mb4_0900_ai_ci"
# mysql_engine = "InnoDB"
dump_path = "database/schema" # where `schema:dump` writes

Like every section, these come from the environment too: APP_SCHEMA__TIMESCALEDB=require in production makes a migration stop on a server without TimescaleDB rather than create regular tables.