Dart tables
Prefer sql? If you prefer, you can also declare tables via CREATE TABLE
statements. Drift's sql analyzer will generate matching Dart code. Details.
As shown in the getting started guide, sql tables can be written in Dart:
class Todos extends Table {
IntColumn get id => integer().autoIncrement()();
TextColumn get title => text().withLength(min: 6, max: 32)();
TextColumn get content => text().named('body')();
IntColumn get category => integer().nullable()();
}
In this article, we'll cover some advanced features of this syntax.
Names
By default, drift uses the snake_case
name of the Dart getter in the database. For instance, the table
class EnabledCategories extends Table {
IntColumn get parentCategory => integer()();
// ..
}
Would be generated as CREATE TABLE enabled_categories (parent_category INTEGER NOT NULL)
.
To override the table name, simply override the tableName
getter. An explicit name for columns can be provided with the named
method:
class EnabledCategories extends Table {
String get tableName => 'categories';
IntColumn get parentCategory => integer().named('parent')();
}
The updated class would be generated as CREATE TABLE categories (parent INTEGER NOT NULL)
.
To update the name of a column when serializing data to json, annotate the getter with @JsonKey
.
You can change the name of the generated data class too. By default, drift will stip a trailing s
from the table name (so a Users
table would have a User
data class). That doesn't work in all cases though. With the EnabledCategories
class from above, we'd get a EnabledCategorie
data class. In those cases, you can use the @DataClassName
annotation to set the desired name.
Nullability
By default, columns may not contain null values. When you forgot to set a value in an insert, an exception will be thrown. When using sql, drift also warns about that at compile time.
If you do want to make a column nullable, just use nullable()
:
class Items {
IntColumn get category => integer().nullable()();
// ...
}
Checks
If you know that a column (or a row) may only contain certain values, you can use a CHECK
constraint in SQL to enforce custom constraints on data.
In Dart, the check
method on the column builder adds a check constraint to the generated column:
// sqlite3 will enforce that this column only contains timestamps happening after (the beginning of) 1950.
DateTimeColumn get creationTime => dateTime()
.check(creationTime.isBiggerThan(Constant(DateTime(1950))))
.withDefault(currentDateAndTime)();
Note that these CHECK
constraints are part of the CREATE TABLE
statement. If you want to change or remove a check
constraint, write a schema migration to re-create the table without the constraint.
Default values
You can set a default value for a column. When not explicitly set, the default value will be used when inserting a new row. To set a constant default value, use withDefault
:
class Preferences extends Table {
TextColumn get name => text()();
BoolColumn get enabled => boolean().withDefault(const Constant(false))();
}
When you later use into(preferences).insert(PreferencesCompanion.forInsert(name: 'foo'));
, the new row will have its enabled
column set to false (and not to null, as it normally would). Note that columns with a default value (either through autoIncrement
or by using a default), are still marked as @required
in generated data classes. This is because they are meant to represent a full row, and every row will have those values. Use companions when representing partial rows, like for inserts or updates.
Of course, constants can only be used for static values. But what if you want to generate a dynamic default value for each column? For that, you can use clientDefault
. It takes a function returning the desired default value. The function will be called for each insert. For instance, here's an example generating a random Uuid using the uuid
package:
final _uuid = Uuid();
class Users extends Table {
TextColumn get id => text().clientDefault(() => _uuid.v4())();
// ...
}
Don't know when to use which? Prefer to use withDefault
when the default value is constant, or something simple like currentDate
. For more complicated values, like a randomly generated id, you need to use clientDefault
. Internally, withDefault
writes the default value into the CREATE TABLE
statement. This can be more efficient, but doesn't support dynamic values.
Primary keys
If your table has an IntColumn
with an autoIncrement()
constraint, drift recognizes that as the default primary key. If you want to specify a custom primary key for your table, you can override the primaryKey
getter in your table:
class GroupMemberships extends Table {
IntColumn get group => integer()();
IntColumn get user => integer()();
@override
Set<Column> get primaryKey => {group, user};
}
Note that the primary key must essentially be constant so that the generator can recognize it. That means:
- it must be defined with the
=>
syntax, function bodies aren't supported - it must return a set literal without collection elements like
if
,for
or spread operators
Unique Constraints
Starting from version 1.6.0, UNIQUE
SQL constraints can be defined on Dart tables too. A unique constraint contains one or more columns. The combination of all columns in a constraint must be unique in the table, or the database will report an error on inserts.
With drift, a unique constraint can be added to a single column by marking it as .unique()
in the column builder. A unique set spanning multiple columns can be added by overriding the uniqueKeys
getter in the Table
class:
class WithUniqueConstraints extends Table {
IntColumn get a => integer().unique()();
IntColumn get b => integer()();
IntColumn get c => integer()();
@override
List<Set<Column>> get uniqueKeys => [
{b, c}
];
// Effectively, this table has two unique key sets: (a) and (b, c).
}
Supported column types
Drift supports a variety of column types out of the box. You can store custom classes in columns by using type converters.
Dart type | Column | Corresponding SQLite type |
---|---|---|
int | integer() | INTEGER |
BigInt | int64() | INTEGER (useful for large values on the web) |
double | real() | REAL |
boolean | boolean() | INTEGER , which a CHECK to only allow 0 or 1 |
String | text() | TEXT |
DateTime | dateTime() | INTEGER (default) or TEXT depending on options |
Uint8List | blob() | BLOB |
Enum | intEnum() | INTEGER (more information available here). |
Enum | textEnum() | TEXT (more information available here). |
Note that the mapping for boolean
, dateTime
and type converters only applies when storing records in the database. They don't affect JSON serialization at all. For instance, boolean
values are expected as true
or false
in the fromJson
factory, even though they would be saved as 0
or 1
in the database. If you want a custom mapping for JSON, you need to provide your own ValueSerializer
.
BigInt
support
Drift supports the int64()
column builder to indicate that a column stores large integers and should be mapped to Dart as a BigInt
.
This is mainly useful for Dart apps compiled to JavaScript, where an int
really is a double
that can't store large integers without loosing information. Here, representing integers as BigInt
(and passing those to the underlying database implementation) ensures that you can store large intergers without any loss of precision. Be aware that BigInt
s have a higher overhead than int
s, so we recommend using int64()
only for columns where this is necessary:
You might not need this!
In sqlite3, an INTEGER
column is stored as a 64-bit integer. For apps running in the Dart VM (e.g. on everything except for the web), the int
type in Dart is the perfect match for that since it's also a 64-bit int. For those apps, we recommend using the regular integer()
column builder.
Essentially, you should use int64()
if both of these are true:
- you're building an app that needs to work on the web, and
- the column in question may store values larger than 252.
In all other cases, using a regular integer()
column is more efficient.
Here are some more pointers on using BigInt
s in drift:
- Since an
integer()
and aint64()
is the same column in sqlite3, you can switch between the two without writing a schema migration. - In addition to large columns, it may also be that you have a complex expression in a select query that would be better represented as a
BigInt
. You can usedartCast()
for this: For an expression(table.columnA * table.columnB).dartCast<BigInt>()
, drift will report the resulting value as aBigInt
even ifcolumnA
andcolumnB
were defined as regular integers. BigInt
s are not currently supported bymoor_flutter
anddrift_sqflite
.- To use
BigInt
support on aWebDatabase
, set thereadIntsAsBigInt: true
flag when instantiating it. - Both
NativeDatabase
andWasmDatabase
have builtin support for bigints.
DateTime
options
Drift supports two approaches of storing DateTime
values in SQL:
As unix timestamp (the default): In this mode, drift stores date time values as an SQL
INTEGER
containing the unix timestamp (in seconds). When date times are mapped from SQL back to Dart, drift always returns a non-UTC value. So even when UTC date times are stored, this information is lost when retrieving rows.As ISO 8601 string: In this mode, datetime values are stored in a textual format based on
DateTime.toIso8601String()
: UTC values are stored unchanged (e.g.2022-07-25 09:28:42.015Z
), while local values have their UTC offset appended (e.g.2022-07-25T11:28:42.015 +02:00
). Most of sqlite3's date and time functions operate on UTC values, but parsing datetimes in SQL respects the UTC offset added to the value.When reading values back from the database, drift will use
DateTime.parse
as following:- If the textual value ends with
Z
, drift will useDateTime.parse
directly. TheZ
suffix will be recognized and a UTC value is returned. - If the textual value ends with a UTC offset (e.g.
+02:00
), drift first usesDateTime.parse
which respects the modifier but returns a UTC datetime. Drift then callstoLocal()
on this intermediate result to return a local value. - If the textual value neither has a
Z
suffix nor a UTC offset, drift will parse it as if it had aZ
modifier, returning a UTC datetime. The motivation for this is that thedatetime
function in sqlite3 returns values in this format and uses UTC by default.
This behavior works well with the date functions in sqlite3 while also preserving "UTC-ness" for stored values.
- If the textual value ends with
The mode can be changed with the store_date_time_values_as_text
build option.
Regardless of the option used, drift's builtin support for date and time functions return an equivalent values. Drift internally inserts the unixepoch
modifier when unix timestamps are used to make the date functions work. When comparing dates stored as text, drift will compare their julianday
values behind the scenes.
Migrating between the two modes
While making drift change the date time modes is as simple as changing a build option, toggling this behavior is not compatible with existing database schemas:
Depending on the build option, drift expects strings or integers for datetime values. So you need to migrate stored columns to the new format when changing the option.
If you are using SQL statements defined in
.drift
files, use custom SQL at runtime or manually invoke datetime expressions with a directFunctionCallExpression
instead of using the higher-level date time APIs, you may have to adapt those usages.For instance, comparison operators like
<
work on unix timestamps, but they will compare textual datetime values lexicographically. So depending on the mode used, you will have to wrap the value inunixepoch
orjulianday
to make them comparable.
As the second point is specific to usages in your app, this documentation only describes how to migrate stored columns between the format:
Note that the JSON serialization generated by default is not affected by the datetime mode chosen. By default, drift will serialize DateTime
values to a unix timestamp in milliseconds. You can change this by creating a ValueSerializer.defaults(serializeDateTimeValuesAsString: true)
and assigning it to driftRuntimeOptions.defaultSerializer
.
Migrating from unix timestamps to text
To migrate from using timestamps (the default option) to storing datetimes as text, follow these steps:
- Enable the
store_date_time_values_as_text
build option. - Add the following method (or an adaption of it suiting your needs) to your database class.
- Increment the
schemaVersion
in your database class. - Write a migration step in
onUpgrade
that callsmigrateFromUnixTimestampsToText
for this schema version increase. Remember that triggers, views or other custom SQL entries in your database will require a custom migration that is not covered by this guide.
Future<void> migrateFromUnixTimestampsToText(Migrator m) async {
for (final table in allTables) {
final dateTimeColumns =
table.$columns.where((c) => c.type == DriftSqlType.dateTime);
if (dateTimeColumns.isNotEmpty) {
// This table has dateTime columns which need to be migrated.
await m.alterTable(TableMigration(
table,
columnTransformer: {
for (final column in dateTimeColumns)
// We assume that the column in the database is an int (unix
// timestamp), use `fromUnixEpoch` to convert it to a date time.
// Note that the resulting value in the database is in UTC.
column: DateTimeExpressions.fromUnixEpoch(column.dartCast<int>()),
},
));
}
}
}
Migrating from text to unix timestamps
To migrate from datetimes stored as text back to unix timestamps, follow these steps:
- Disable the
store_date_time_values_as_text
build option. - Add the following method (or an adaption of it suiting your needs) to your database class.
- Increment the
schemaVersion
in your database class. - Write a migration step in
onUpgrade
that callsmigrateFromTextDateTimesToUnixTimestamps
for this schema version increase. Remember that triggers, views or other custom SQL entries in your database will require a custom migration that is not covered by this guide.
Future<void> migrateFromTextDateTimesToUnixTimestamps(Migrator m) async {
for (final table in allTables) {
final dateTimeColumns =
table.$columns.where((c) => c.type == DriftSqlType.dateTime);
if (dateTimeColumns.isNotEmpty) {
// This table has dateTime columns which need to be migrated.
await m.alterTable(TableMigration(
table,
columnTransformer: {
for (final column in dateTimeColumns)
// We assume that the column in the database is a string. We want
// to parse it to a date in SQL and then get the unix timestamp of
// it.
// Note that this requires sqlite version 3.38 or above.
column: FunctionCallExpression('unixepoch', [column]),
},
));
}
}
}
Note that this snippet uses the unixepoch
sqlite3 function, which has been added in sqlite 3.38. To support older sqlite3 versions, you can use strftime
and cast to an integer instead:
columnTransformer: {
for (final column in dateTimeColumns)
// Use this as an alternative to `unixepoch`:
column: FunctionCallExpression(
'strftime', [const Constant('%s'), column]).cast<int>(),
},
When using a NativeDatabase
with a recent dependency on the sqlite3_flutter_libs
package, you can safely assume that you are on a recent sqlite3 version with support for unixepoch
.
Custom constraints
Some column and table constraints aren't supported through drift's Dart api. This includes REFERENCES
clauses on columns, which you can set through customConstraint
:
class GroupMemberships extends Table {
IntColumn get group => integer().customConstraint('NOT NULL REFERENCES groups (id)')();
IntColumn get user => integer().customConstraint('NOT NULL REFERENCES users (id)')();
@override
Set<Column> get primaryKey => {group, user};
}
Applying a customConstraint
will override all other constraints that would be included by default. In particular, that means that we need to also include the NOT NULL
constraint again.
You can also add table-wide constraints by overriding the customConstraints
getter in your table class.
References
Foreign key references can be expressed in Dart tables with the references()
method when building a column:
class Todos extends Table {
// ...
IntColumn get category => integer().nullable().references(Categories, #id)();
}
@DataClassName("Category")
class Categories extends Table {
IntColumn get id => integer().autoIncrement()();
// and more columns...
}
The first parameter to references
points to the table on which a reference should be created. The second parameter is a symbol of the column to use for the reference.
Optionally, the onUpdate
and onDelete
parameters can be used to describe what should happen when the target row gets updated or deleted.
Be aware that, in sqlite3, foreign key references aren't enabled by default. They need to be enabled with PRAGMA foreign_keys = ON
. A suitable place to issue that pragma with drift is in a post-migration callback.
Views
It is also possible to define SQL views as Dart classes. To do so, write an abstract class extending View
. This example declares a view reading the amount of todo-items added to a category in the schema from the example:
abstract class CategoryTodoCount extends View {
// Getters define the tables that this view is reading from.
Todos get todos;
Categories get categories;
// Custom expressions can be given a name by defining them as a getter:.
Expression<int> get itemCount => todos.id.count();
@override
Query as() =>
// Views can select columns defined as expression getters on the class, or
// they can reference columns from other tables.
select([categories.description, itemCount])
.from(categories)
.join([innerJoin(todos, todos.category.equalsExp(categories.id))]);
}
Inside a Dart view, use
abstract getters to declare tables that you'll read from (e.g.
TodosTable get todos
).Expression
getters to add columns: (e.g.itemCount => todos.id.count()
).the overridden
as
method to define the select statement backing the view. The columns referenced inselect
may refer to two kinds of columns:- Columns defined on the view itself (like
itemCount
in the example above). - Columns defined on referenced tables (like
categories.description
in the example). For these references, advanced drift features like type converters used in the column's definition from the table are also applied to the view's column.
Both kind of columns will be added to the data class for the view when selected.
- Columns defined on the view itself (like
Finally, a view needs to be added to a database or accessor by including it in the views
parameter:
@DriftDatabase(tables: [Todos, Categories], views: [CategoryTodoCount])
class MyDatabase extends _$MyDatabase {
Nullability of columns in a view
For a Dart-defined views, expressions defined as an Expression
getter are always nullable. This behavior matches TypedResult.read
, the method used to read results from a complex select statement with custom columns.
Columns that reference another table's column are nullable if the referenced column is nullable, or if the selected table does not come from an inner join (because the whole table could be null
in that case).
Considering the view from the example above,
- the
itemCount
column is nullable because it is defined as a complexExpression
- the
description
column, referencingcategories.description
, is non-nullable. This is because it referencescategories
, the primary table of the view's select statement.