---
title: The migrator API
description: How to run `ALTER` statements and complex table migrations.
---
You can write migrations manually by using `customStatement()` in a migration
callback. However, the callbacks also give you an instance of `Migrator` as a
parameter. This class knows about the target schema of the database and can be
used to create, drop and alter most elements in your schema.
## General tips
To ensure your schema stays consistent during a migration, you can wrap it in a `transaction` block.
However, be aware that some pragmas (including `foreign_keys`) can't be changed inside transactions.
Still, it can be useful to:
- always re-enable foreign keys before using the database, by enabling them in `beforeOpen`.
- disable foreign-keys before migrations.
- run migrations inside a transaction.
- make sure your migrations didn't introduce any inconsistencies with `PRAGMA foreign_key_check`.
With all of this combined, a migration callback can look like this:
## Migrating views, triggers and indices
When changing the definition of a view, a trigger or an index, the easiest way
to update the database schema is to drop and re-create the element.
With the `Migrator` API, this is just a matter of calling `await drop(element)`
followed by `await create(element)`, where `element` is the trigger, view or index
to update.
Note that the definition of a Dart-defined view might change without modifications
to the view class itself. This is because columns from a table are referenced with
a getter. When renaming a column through `.named('name')` in a table definition
without renaming the getter, the view definition in Dart stays the same but the
`CREATE VIEW` statement changes.
A headache-free solution to this problem is to just re-create all views in a
migration, for which the `Migrator` provides the `recreateAllViews` method.
## Complex migrations
Sqlite has builtin statements for simple changes, like adding columns or dropping entire tables.
More complex migrations require a [12-step procedure](https://www.sqlite.org/lang_altertable.html#otheralter) that
involves creating a copy of the table and copying over data from the old table.
Drift 2.4 introduced the `TableMigration` API to automate most of this procedure, making it easier and safer to use.
To start the migration, drift will create a new instance of the table with the current schema. Next, it will copy over
rows from the old table.
In most cases, for instance when changing column types, we can't just copy over each row without changing its content.
Here, you can use a `columnTransformer` to apply a per-row transformation.
The `columnTransformer` is a map from columns to the sql expression that will be used to copy the column from the
old table.
For instance, if we wanted to cast a column before copying it, we could use:
```dart
columnTransformer: {
todos.category: todos.category.cast(),
}
```
Internally, drift will use a `INSERT INTO SELECT` statement to copy old data. In this case, it would look like
`INSERT INTO temporary_todos_copy SELECT id, title, content, CAST(category AS INT) FROM todos`.
As you can see, drift will use the expression from the `columnTransformer` map and fall back to just copying the column
otherwise.
If you're introducing new columns in a table migration, be sure to include them in the `newColumns` parameter of
`TableMigration`. Drift will ensure that those columns have a default value or a transformation in `columnTransformer`.
Of course, drift won't attempt to copy `newColumns` from the old table either.
Regardless of whether you're implementing complex migrations with `TableMigration` or by running a custom sequence
of statements, we strongly recommend to write integration tests covering your migrations. This helps to avoid data
loss caused by errors in a migration.
Here are some examples demonstrating common usages of the table migration API. These examples
rely on [the generated step-by-step migrations](step_by_step.md), since that is the recommended
way to write migrations.
However, these APIs work without `stepByStep` as well.
### Changing the type of a column
Let's say the `category` column in `Todos` used to be a non-nullable `text()` column that we're now changing to a
nullable int. For simplicity, we assume that `category` always contained integers, they were just stored in a text
column that we now want to adapt.
```patch
class Todos extends Table {
IntColumn get id => integer().autoIncrement()();
TextColumn get title => text().withLength(min: 6, max: 10)();
TextColumn get content => text().named('body')();
- TextColumn get category => text()();
+ IntColumn get category => integer().nullable()();
}
```
After re-running your build and incrementing the schema version, you can write a migration:
The important part here is the `columnTransformer` - a map from columns to expressions that will
be used to copy the old data. The values in that map refer to the old table, so we can use
`todos.category.cast()` to copy old rows and transform their `category`.
All columns that aren't present in `columnTransformer` will be copied from the old table without
any transformation.
### Changing column constraints
When you're changing columns constraints in a way that's compatible to existing data (e.g. changing
non-nullable columns to nullable columns), you can just copy over data without applying any
transformation:
```dart
from1To2: (m, schema) async {
await m.alterTable(TableMigration(schema.todos));
}
```
### Deleting columns
Deleting a column that's not referenced by a foreign key constraint is easy too:
```dart
from1To2: (m, schema) async {
await m.alterTable(TableMigration(schema.yourTable));
}
```
To delete a column referenced by a foreign key, you'd have to migrate the referencing
tables first.
### Renaming columns
If you're renaming a column in Dart, note that the easiest way is to just rename the getter and use
`named`: `TextColumn newName => text().named('old_name')()`. That is fully backwards compatible and
doesn't require a migration.
If you know your app runs on sqlite 3.25.0 or later (it does if you're using `sqlite3_flutter_libs`),
you can also use the `renameColumn` api in `Migrator`:
```dart
from1To2: (m, schema) async {
await m.renameColumn(schema.yourTable, 'old_column_name', schema.yourTable.newColumn);
}
```
If you do want to change the actual column name in a table, you can write a `columnTransformer` to
use an old column with a different name:
```dart
from1To2: (m, schema) async {
await m.alterTable(
TableMigration(
schema.yourTable,
columnTransformer: {
schema.yourTable.newColumn: const CustomExpression('old_column_name')
},
)
);
}
```
### Combining columns
To add a column that should have a default value computed from multiple existing columns, you can also
express that with the `TableMigration` API:
```dart
await m.alterTable(
TableMigration(
schema.yourTable,
columnTransformer: {
schema.yourTable.newColumn: Variable.withString('from previous row: ') +
schema.yourTable.oldColumn1 +
schema.yourTable.oldColumn2.upper()
},
)
)
```
### Adding new columns
The easiest way to add new columns is to simply use the `addColumn` method on the migrator:
```dart
from1To2: (m, schema) async {
await m.addColumn(schema.users, schema.users.middleName);
}
```
In some cases, that won't be enough though. In particular, if you're adding a column that:
1. Is non-nullable, _and_
2. Does not have a default value (`clientDefault` doesn't count as a default value here), _and_
3. Is not an auto-incrementing primary key.
Then this column can't be added to existing tables safely because the database won't know which
value to use in existing rows.
By using the `alterTable` API, you can specify a default value that will only be applied to existing
rows (new insertions will either get the `clientDefault` or are required to specify their own values
for the new column):
```dart
await m.alterTable(
TableMigration(
schema.yourTable,
columnTransformer: {
schema.yourTable.yourNewColumn: Constant('value for existing rows'),
},
newColumns: [schema.yourTable.yourNewColumn],
)
)
```