Columns

Column methods of the schema builder add a column to the table a blueprint describes, and return a builder for its modifiers:

schema
    .create("deals", |t| {
        t.uuid_primary();
        t.foreign_uuid("company_id").constrained();
        t.string("title");
        t.big_integer("amount_cents").default(0);
        t.char("currency", 3).default("USD");
        t.string("stage").length(16).default("lead").index();
        t.date("expected_close_date").nullable();
        t.json("meta").nullable();
        t.timestamps_tz();
    })
    .await?;

Columns are NOT NULL unless you say .nullable(). A type a database doesn't have fails before any SQL runs, with a message saying what to use instead: "PostgreSQL does not support set columns (column tags): set is a MySQL type; use a jsonb array or a join table".

Column types

Time columns keep six fractional digits (microseconds) by default: set .precision(p) per column, .default_precision() for the database's own default, or schema.default_time_precision for all. On PostgreSQL six is the default precision, so the SQL doesn't repeat it. .subtype("point") and .srid(4326) refine geometry and geography columns.

Matching model fields

When an ORM model maps the table, pick the column for the field's type:

UUID columns on MySQL

A model writes a Uuid field as a native UUID on PostgreSQL and as its 16 bytes on MySQL, MariaDB and SQLite. SQLite stores those bytes in any column, but on MySQL uuid_primary(), uuid and foreign_uuid are CHAR(36), which refuses them: the insert fails with error 1366, "Incorrect string value". Declare the UUID columns of models as 16-byte binaries there, BINARY(16), and on MariaDB, which models reach through the same driver. A migration that runs on several databases asks the schema which one it is talking to:

use navree::schema::Dialect;

let mysql = schema.target().await?.dialect() == Dialect::MySql; // MariaDB too
schema
    .create("deals", |t| {
        if mysql {
            t.binary("id").length(16).fixed().primary();
            t.binary("company_id").length(16).fixed();
            t.foreign("company_id").references("id").on("companies").cascade_on_delete();
        } else {
            t.uuid_primary();
            t.foreign_uuid("company_id").constrained().cascade_on_delete();
        }
        t.string("title");
    })
    .await?;

On PostgreSQL a binary column would be BYTEA, which a model's Uuid doesn't fit, so keep uuid_primary() there.

Helpers

Some methods add the conventional columns of a pattern at once:

Modifiers

Chain modifiers on the builder a column method returns:

A modifier that doesn't apply to its column, such as .length(10) on an integer or .auto_increment() on a string, fails before any SQL runs.

Defaults

Defaults are typed: integers and floats are written bare, strings quoted (with backslashes escaped on MySQL), booleans as TRUE/FALSE on PostgreSQL and 1/0 elsewhere, and a serde_json::Value as JSON text. raw("..") is SQL written as is:

t.string("stage").default("lead");
t.boolean("active").default(true);
t.integer("rank").default(0);
t.json("settings").default(serde_json::json!({ "theme": "light" }));
t.timestamp_tz("seen_at").default(raw("CURRENT_TIMESTAMP"));
t.timestamp_tz("checked_at").use_current();

On MySQL, defaults of TEXT, BLOB, JSON and geometry columns are written in parentheses, which needs MySQL 8.0.13.

Generated columns

t.decimal("total", 12, 2).stored_as("price * quantity");    // computed when written
t.string("search_name").virtual_as("lower(name)");           // computed when read

A stored column is computed when the row is written and takes space; a virtual one is computed when it's read (PostgreSQL 18 and later, MySQL, MariaDB, SQLite). Generated columns can be indexed.

Changing columns

Inside schema.table, .change() alters an existing column to the definition you give:

schema
    .table("deals", |t| {
        t.decimal("amount", 14, 2).change();
        t.string("title").length(500).nullable().change();
        t.big_integer("views").using("views::bigint").change();
    })
    .await?;

The definition is complete: a default, nullable or comment you leave out is removed, so restate what the column should keep. On PostgreSQL, .using(..) converts existing values when the type changes. .without_index(), .without_unique() and .without_primary() drop a column's conventional index as it changes.

rename_column(from, to) renames a column, and drop_column("name") or drop_column(["a", "b"]) drops columns. drop_timestamps(), drop_timestamps_tz(), drop_soft_deletes(), drop_soft_deletes_tz(), drop_remember_token() and drop_morphs("subject") drop the helpers' columns.

On SQLite, changing a column rebuilds the table, keeping its rows (SQLite). Adding a NOT NULL column without a default to a table that has rows fails with how to fix it: give it a default, or add it nullable, fill it, then change it.