Schema builder

The schema builder creates, changes and drops tables with Dart code that works on Postgres, MySQL, MariaDB and SQLite.

You mostly use it inside a migration.

await schema.create('posts', (Table table) {
  table.id();
  table.foreignId('user_id').constrained().cascadeOnDelete();
  table.string('title');
  table.text('body').nullable();
  table.enumeration('status', <String>['draft', 'published']).defaultTo('draft');
  table.timestamps();
});

Get a schema

In a migration, up and down get a Schema. It runs on the migration connection.

Outside a migration, Schema() uses the query builder connection. You can also give a driver: Schema(driver).

Schema schema = Schema();
await schema.dropIfExists('posts');

Tables

Method	What it does
`create('t', (Table table) {...})`	Create table `t`. It fails when the table exists.
`table('t', (Table table) {...})`	Change table `t`: add columns, change columns, keys.
`drop('t')`	Drop table `t`. It fails when the table is missing.
`dropIfExists('t')`	Drop table `t` when it exists.
`rename('from', 'to')`	Rename a table.
`hasTable('t')`	`true` when the table exists.
`hasColumn('t', 'c')`	`true` when the column exists.
`getColumnListing('t')`	The column names, in order.

Column types

Method	Postgres	MySQL / MariaDB	SQLite
`id()` / `bigIncrements('id')`	`BIGSERIAL PRIMARY KEY`	`BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY`	`INTEGER PRIMARY KEY AUTOINCREMENT`
`increments('id')`	`SERIAL PRIMARY KEY`	`INT UNSIGNED AUTO_INCREMENT PRIMARY KEY`	`INTEGER PRIMARY KEY AUTOINCREMENT`
`string('c', [255])`	`VARCHAR(n)`	`VARCHAR(n)`	`VARCHAR(n)`
`char('c', [255])`	`CHAR(n)`	`CHAR(n)`	`CHAR(n)`
`text('c')`	`TEXT`	`TEXT`	`TEXT`
`mediumText('c')`	`TEXT`	`MEDIUMTEXT`	`TEXT`
`longText('c')`	`TEXT`	`LONGTEXT`	`TEXT`
`integer('c')`	`INTEGER`	`INT`	`INTEGER`
`bigInteger('c')`	`BIGINT`	`BIGINT`	`INTEGER`
`smallInteger('c')`	`SMALLINT`	`SMALLINT`	`INTEGER`
`tinyInteger('c')`	`SMALLINT`	`TINYINT`	`INTEGER`
`unsignedInteger('c')` (and `unsignedBigInteger`, `unsignedSmallInteger`, `unsignedTinyInteger`)	signed type	`... UNSIGNED`	`INTEGER`
`float('c')`	`REAL`	`FLOAT`	`FLOAT`
`double('c')`	`DOUBLE PRECISION`	`DOUBLE`	`DOUBLE`
`decimal('c', [8], [2])`	`DECIMAL(p, s)`	`DECIMAL(p, s)`	`DECIMAL(p, s)`
`boolean('c')`	`BOOLEAN`	`TINYINT(1)`	`BOOLEAN`
`date('c')`	`DATE`	`DATE`	`DATE`
`dateTime('c')`	`TIMESTAMP`	`DATETIME`	`DATETIME`
`dateTimeTz('c')`	`TIMESTAMPTZ`	`DATETIME`	`DATETIME`
`time('c')`	`TIME`	`TIME`	`TIME`
`timestamp('c')`	`TIMESTAMP`	`TIMESTAMP`	`TIMESTAMP`
`timestampTz('c')`	`TIMESTAMPTZ`	`TIMESTAMP`	`TIMESTAMP`
`json('c')` / `jsonb('c')`	`JSONB`	`JSON`	`JSON`
`uuid(['uuid'])`	`UUID`	`CHAR(36)`	`TEXT`
`binary('c')`	`BYTEA`	`BLOB`	`BLOB`
`enumeration('c', ['a', 'b'])`	`VARCHAR(255)` + `CHECK`	`ENUM('a', 'b')`	`VARCHAR(255)` + `CHECK`
`foreignId('user_id')`	`BIGINT`	`BIGINT UNSIGNED`	`INTEGER`
`foreignUuid('user_id')`	`UUID`	`CHAR(36)`	`TEXT`
`foreignIdFor(User())`	`BIGINT`	`BIGINT UNSIGNED`	`INTEGER`
`ulid(['ulid'])` / `foreignUlid('user_id')`	`CHAR(26)`	`CHAR(26)`	`CHAR(26)`
`year('c')`	`INTEGER`	`YEAR`	`INTEGER`
`ipAddress(['ip_address'])`	`VARCHAR(45)`	`VARCHAR(45)`	`VARCHAR(45)`

Helpers:

timestamps(): nullable created_at and updated_at (timestampTz).
softDeletes(['deleted_at']): nullable deleted_at (timestampTz), for models with softDelete: true.
rememberToken(): nullable remember_token VARCHAR(100).
softDeletesTz(['deleted_at']): the same as softDeletes. It is already a time with a zone.
morphs('owner'): owner_id (unsigned big integer), owner_type (string) and one index on both columns.
nullableMorphs('owner'): the same as morphs, but both columns can be NULL.
uuidMorphs('owner'): the same as morphs, with a uuid owner_id.

foreignIdFor(User()) makes the foreign key column of the model, like user_id. Give a name as the second argument to change it. .constrained() then points to the table and primary key of the model:

table.foreignIdFor(User()).constrained().cascadeOnDelete();

It is always a big integer. For a model with a text key, use foreignUuid or foreignUlid.

JSON on SQLite

SQLite has no JSON type. The column is declared as JSON, so the SQLite driver knows to decode it when you read it.

Modifiers

Modifiers come after the column type. They can be chained: table.string('email').nullable().unique().

Modifier	What it does	Notes
`nullable()`	Allow `NULL`. Columns are `NOT NULL` by default.	`nullable(false)` makes it `NOT NULL` again.
`defaultTo(value)`	Default value. Strings are escaped, so any text is safe.	`bool`, numbers, `null`, `Map`/`List` (saved as JSON) and `DateTime` work too. `withDefault` does the same.
`defaultRaw('sql')`	Default as raw SQL. It is not escaped.	Put expressions in `( )`: `defaultRaw('(1 + 1)')`. MySQL and SQLite need it.
`useCurrent()`	Default to the current time.	Same as `defaultRaw('CURRENT_TIMESTAMP')`.
`useCurrentOnUpdate()`	Set the current time on each update.	MySQL only. Postgres and SQLite ignore it. Set the time in your code there.
`unique()`	Add a unique index `{table}_{column}_unique`.
`index()`	Add an index `{table}_{column}_index`.
`primary()`	Make the column the primary key.
`unsigned()`	`UNSIGNED`.	MySQL only. Others ignore it.
`comment('text')`	Column comment.	Postgres and MySQL. SQLite ignores it.
`after('column')`	Put the new column after another one.	MySQL only, only in `schema.table`. Others add it at the end.
`change()`	Change an existing column (in `schema.table`).	See Change columns.

MySQL defaults on TEXT and JSON

MySQL only allows a default on TEXT, BLOB and JSON as an expression. The schema builder writes it as DEFAULT ('value') for you.

Change a table

await schema.table('users', (Table table) {
  table.string('avatar').nullable().after('email'); // add
  table.string('name', 100).nullable().change();    // change
  table.renameColumn('bio', 'about');
  table.dropColumn('age');
  table.dropColumns(<String>['a', 'b']);
});

Drop helpers:

dropTimestamps(): drops created_at and updated_at.
dropSoftDeletes(['deleted_at']): drops deleted_at.
dropConstrainedForeignId('user_id'): drops the foreign key of the column, then the column.

A column in schema.table is added. Add .change() to change a column that exists.

Change columns

change() sets the full new column: type, null, default and comment. Give every modifier you want to keep.

// before: string('title') NOT NULL DEFAULT 'x'
table.string('title', 500).nullable().defaultTo('x').change();

How it runs:

Driver	SQL
Postgres	One `ALTER TABLE`: `ALTER COLUMN ... TYPE ... USING col::type`, `SET/DROP NOT NULL`, `SET/DROP DEFAULT`, and the enum `CHECK` again.
MySQL	`ALTER TABLE ... MODIFY COLUMN <full column>`.
SQLite	SQLite can not change a column. Dox makes the table again (see below).

change() with unique() or index() adds the index. It fails when the index already exists.

Indexes and keys

columns is a column name or a list of names.

table.index(<String>['state', 'created_at']);  // posts_state_created_at_index
table.unique('slug');                          // posts_slug_unique
table.primary(<String>['post_id', 'tag_id']);  // composite primary key

table.dropIndex(<String>['state', 'created_at']); // by columns
table.dropUnique('posts_slug_unique');            // or by name

table.fullText('body');                           // posts_body_fulltext
table.renameIndex('posts_slug_unique', 'posts_url_unique');
table.dropPrimary();                              // posts_pkey on Postgres

fullText makes a FULLTEXT index on MySQL. On Postgres it makes a gin index on to_tsvector('english', ...).
dropPrimary([name]) takes the name for Postgres. The default is {table}_pkey. MySQL needs no name.

Index names are {table}_{columns}_{index|unique|foreign}. You can give your own name as the last argument: table.unique('slug', 'my_name').

Foreign keys

table.foreignId('user_id').constrained().cascadeOnDelete();
// same as:
table.foreignId('user_id');
table.foreign('user_id').references('id').on('users').onDelete('cascade');

constrained([table], [column = 'id']) guesses the table from the column: user_id becomes users, category_id becomes categories. Dox models use singular table names by default (User is user). If your table is singular, give it: constrained('user').
Actions: onDelete('cascade' | 'set null' | 'restrict' | 'no action'), onUpdate(...), and the short ones cascadeOnDelete(), nullOnDelete(), restrictOnDelete(), cascadeOnUpdate(), nullOnUpdate() and restrictOnUpdate().
Put nullable() before constrained(): table.foreignId('editor_id').nullable().constrained('users').
Drop it: table.dropForeign(<String>['user_id']) or table.dropForeign('posts_user_id_foreign').

Same type on both sides

id() is a big integer. foreignId() is a big integer too, so they match. If the parent uses increments() (a normal integer), use unsignedInteger() for the child on MySQL.

SQLite notes

SQLite ALTER TABLE can only add, rename and drop columns. For these changes, Dox makes the table again:

change() of a column,
foreign(...) on an existing table,
dropForeign(...).

It makes a new table with the change, copies the rows, drops the old table and renames the new one. It keeps the columns, primary key, AUTOINCREMENT, foreign keys, indexes and enum checks. All of it runs in one transaction. It fails on tables with other CHECK constraints or with generated columns.

Other SQLite limits:

primary(...) on an existing table is not supported.
fullText and dropPrimary throw an UnsupportedError. SQLite has no full text index, and it can not drop a primary key.
renameIndex drops the index and makes it again with the new name. So it only runs on a database, not in See the SQL.
A new column that is NOT NULL needs a default. A new column can not default to CURRENT_TIMESTAMP.
dropColumn fails when the column is in an index or a foreign key. Drop those first.

Per driver notes

Feature	Postgres	MySQL / MariaDB	SQLite
`comment`	`COMMENT ON COLUMN`	inline `COMMENT`	ignored
add/drop foreign key on a table	`ALTER TABLE`	`ALTER TABLE`	table rebuild
schema changes in a transaction	yes	no, saved right away	yes
index name length	63	64	no limit

See the SQL

Every change is a Table blueprint. A grammar turns it into SQL, without a database:

Table table = Table('users', creating: true)
  ..id()
  ..string('email').unique();
print(table.toSql(PostgresGrammar()));
// [CREATE TABLE "users" ("id" BIGSERIAL NOT NULL PRIMARY KEY, "email" VARCHAR(255) NOT NULL),
//  CREATE UNIQUE INDEX "users_email_unique" ON "users" ("email")]

Grammars: PostgresGrammar, MysqlGrammar and SqliteGrammar. On SQLite, the changes that need a table rebuild only run on a database.