Drift files
.drift
files, a powerful tool to define your database in SQL.Drift files are a new feature that lets you write all your database code in SQL. But unlike raw SQL strings you might pass to simple database clients, everything in a drift file is verified by drift's powerful SQL analyzer. This allows you to write SQL queries safer: Drift will find mistakes in them during builds, and it will generate typesafe Dart APIs for them so that you don't have to read back results manually.
Getting started
To use this feature, lets create two files: database.dart
and tables.drift
. The Dart file only contains the minimum code to setup the database:
import 'package:drift/drift.dart';
import 'package:drift/native.dart';
part 'database.g.dart';
@DriftDatabase(
include: {'tables.drift'},
)
class MyDb extends _$MyDb {
// This example creates a simple in-memory database (without actual
// persistence).
// To store data, see the database setups from other "Getting started" guides.
MyDb() : super(NativeDatabase.memory());
@override
int get schemaVersion => 1;
}
We can now declare tables and queries in the drift file:
CREATE TABLE todos (
id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
content TEXT NOT NULL,
category INTEGER REFERENCES categories(id)
);
CREATE TABLE categories (
id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
description TEXT NOT NULL
) AS Category; -- the AS xyz after the table defines the data class name
-- You can also create an index or triggers with drift files
CREATE INDEX categories_description ON categories(description);
-- we can put named sql queries in here as well:
createEntry: INSERT INTO todos (title, content) VALUES (:title, :content);
deleteById: DELETE FROM todos WHERE id = :id;
allTodos: SELECT * FROM todos;
After running the build runner with dart run build_runner build
, drift will write the database.g.dart
file which contains the _$MyDb
superclass. Let's take a look at what we got:
- Generated data classes (
Todo
andCategory
), and companion versions for inserts (see Dart Interop for info). By default, drift strips a trailing "s" from the table name for the class. That's why we usedAS Category
on the second table - it would have been calledCategorie
otherwise. - Methods to run the queries:
- a
Future<int> createEntry(String title, String content)
method. It creates a new todo entry with the provided data and returns the id of the entry created. Future<int> deleteById(int id)
: Deletes a todo entry by its id, and returns the amount of rows affected.Selectable<AllTodosResult> allTodos()
. It can be used to get, or watch, all todo entries. It can be used withallTodos().get()
andallTodos().watch()
.
- a
- Classes for select statements that don't match a table. In the example above, thats the
AllTodosResult
class, which contains all fields fromtodos
and the description of the associated category.
Variables
Inside of named queries, you can use variables just like you would expect with sql. We support regular variables (?
), explicitly indexed variables (?123
) and colon-named variables (:id
). We don't support variables declared with @ or $. The compiler will attempt to infer the variable's type by looking at its context. This lets drift generate typesafe apis for your queries, the variables will be written as parameters to your method.
When it's ambiguous, the analyzer might be unable to resolve the type of a variable. For those scenarios, you can also denote the explicit type of a variable:
myQuery(:variable AS TEXT): SELECT :variable;
In addition to the base type, you can also declare that the type is nullable:
myNullableQuery(:variable AS TEXT OR NULL): SELECT :variable;
Finally, you can declare that a variable should be required in Dart when using named parameters. To do so, add a REQUIRED
keyword:
myRequiredQuery(REQUIRED :variable AS TEXT OR NULL): SELECT :variable;
Note that this only has an effect when the named_parameters
build option is enabled. Further, non-nullable variables are required by default.
Arrays
If you want to check whether a value is in an array of values, you can use IN ?
. That's not valid sql, but drift will desugar that at runtime. So, for this query:
entriesWithId: SELECT * FROM todos WHERE id IN ?;
Drift will generate a Selectable<Todo> entriesWithId(List<int> ids)
method. Running entriesWithId([1,2])
would generate SELECT * ... id IN (?1, ?2)
and bind the arguments accordingly. To make sure this works as expected, drift imposes two small restrictions:
- No explicit variables:
WHERE id IN ?2
will be rejected at build time. As the variable is expanded, giving it a single index is invalid. - No higher explicit index after a variable: Running
WHERE id IN ? OR title = ?2
will also be rejected. Expanding the variable can clash with the explicit index, which is why drift forbids it. Of course,id IN ? OR title = ?
will work as expected.
Defining tables
In .drift
files, you can define table with a CREATE TABLE
statement, just like you would write it in SQL.
Supported column types
Just like sqlite itself, we use this algorithm to determine the column type based on the declared type name.
Additionally, columns that have the type name BOOLEAN
or DATETIME
will have bool
or DateTime
as their Dart counterpart. Booleans are stored as INTEGER
(either 0
or 1
). Datetimes are stored as unix timestamps (INTEGER
) or ISO-8601 (TEXT
) depending on a configurable build option. Dart enums can automatically be stored by their index by using an ENUM()
type referencing the Dart enum class:
enum Status {
none,
running,
stopped,
paused
}
import 'status.dart';
CREATE TABLE tasks (
id INTEGER NOT NULL PRIMARY KEY,
status ENUM(Status)
);
More information on storing enums is available in the page on type converters. Instead of using an integer mapping enums by their index, you can also store them by their name. For this, use ENUMNAME(...)
instead of ENUM(...)
.
For details on all supported types, and information on how to switch between the datetime modes, see this section.
The additional drift-specific types (BOOLEAN
, DATETIME
, ENUM
and ENUMNAME
) are also supported in CAST
expressions, which is helpful for views:
CREATE VIEW with_next_status AS
SELECT id, CAST(status + 1 AS ENUM(Status)) AS status
FROM tasks
WHERE status < 3;
Drift-specific features
To help support drift's Dart API, CREATE TABLE
statements in drift files can use special syntax for Dart-specific features. Of course, drift will strip this special syntax from the CREATE TABLE
statement before it runs it.
- You can define a custom row class for a table or a defined query by appending
WITH YourDartClass
to aCREATE TABLE
statement. - Alternatively, you may use
AS DesiredRowClassName
to change the name of the row class generated by drift. - In a column definition,
MAPPED BY
can be used to apply a converter to that column. - Similarly, a
JSON KEY
constraint can be used to define the key drift will use when serializing a row of that table to JSON. - Finally,
AS getterName
can be used as a column constraint to override the generated name of that column in Dart. This can be useful when the default column name, inspired by the name of the column in SQL, conflicts with another member of the generated table class.
Imports
You can put import statements at the top of a drift
file:
import 'tables.drift'; -- single quotes are required for imports
All tables reachable from the other file will then also be visible in the current file and to the database that includes
it. If you want to declare queries on tables that were defined in another drift file, you also need to import that file for the tables to be visible. Note that imports in drift file are always transitive, so in the above example you would have all imports declared in other.drift
available as well. There is no export
mechanism for drift files.
Importing Dart files into a drift file will also work - then, all the tables declared via Dart tables can be used inside queries. We support both relative imports and the package:
imports you know from Dart.
Nested results
Many queries fetch all columns from some table, typically by using the SELECT table.*
syntax. That approach can become a bit tedious when applied over multiple tables from a join, as shown in this example:
CREATE TABLE coordinates (
id INTEGER NOT NULL PRIMARY KEY,
lat REAL NOT NULL,
long REAL NOT NULL
);
CREATE TABLE saved_routes (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
"from" INTEGER NOT NULL REFERENCES coordinates (id),
"to" INTEGER NOT NULL REFERENCES coordinates (id)
);
routesWithPoints: SELECT r.id, r.name, f.*, t.* FROM saved_routes r
INNER JOIN coordinates f ON f.id = r."from"
INNER JOIN coordinates t ON t.id = r."to";
To match the returned column names while avoiding name clashes in Dart, drift will generate a class having an id
, name
, id1
, lat
, long
, lat1
and a long1
field. Of course, that's not helpful at all - was lat1
coming from from
or to
again? Let's rewrite the query, this time using nested results:
routesWithNestedPoints: SELECT r.id, r.name, f.** AS "from", t.** AS "to" FROM saved_routes r
INNER JOIN coordinates f ON f.id = r."from"
INNER JOIN coordinates t ON t.id = r."to";
As you can see, we can nest a result simply by using the drift-specific table.**
syntax. For this query, drift will generate the following class:
class RoutesWithNestedPointsResult {
final int id;
final String name;
final Point from;
final Point to;
// ...
}
Great! This class matches our intent much better than the flat result class from before.
These nested result columns (**
) can appear in top-level select statements only, they're not supported in compound select statements or subqueries yet. However, they can refer to any result set in SQL that has been joined to the select statement - including subqueries table-valued functions.
You might be wondering how **
works under the hood, since it's not valid sql. At build time, drift's generator will transform **
into a list of all columns from the referred table. For instance, if we had a table foo
with an id INT
and a bar TEXT
column. Then, SELECT foo.** FROM foo
might be desugared to SELECT foo.id AS "nested_0.id", foo.bar AS "nested_0".bar FROM foo
.
LIST
subqueries
Starting from Drift version 1.4.0
, subqueries can also be selected as a full list. Simply put the subquery in a LIST()
function to include all rows of the subquery in the result set.
Re-using the coordinates
and saved_routes
tables introduced in the example for nested results, we add a new table storing coordinates along a route:
CREATE TABLE route_points (
route INTEGER NOT NULL REFERENCES saved_routes (id),
point INTEGER NOT NULL REFERENCES coordinates (id),
index_on_route INTEGER,
PRIMARY KEY (route, point)
);
Now, assume we wanted to query a route with information about all points along the way. While this requires two SQL statements, we can write this as a single drift query that is then split into the two statements automatically:
routeWithPoints: SELECT
route.**,
LIST(SELECT coordinates.* FROM route_points
INNER JOIN coordinates ON id = point
WHERE route = route.id
ORDER BY index_on_route
) AS points
FROM saved_routes route;
This will generate a result set containing a SavedRoute route
field along with a List<Point> points
list of all points along the route.
Internally, drift will split this query into two separate queries:
- The outer
SELECT route.** FROM saved_routes route
SQL queries - A separate
SELECT coordinates.* FROM route_points ... ORDER BY index_on_route
query that is run for each row in the outer query. Theroute.id
reference in the inner query is replaced with a variable that drift binds to the actual value in the outer query.
While LIST()
subqueries are a very powerful feature, they can be costly when the outer query has lots of rows (as the inner query is executed for each outer row).
Dart interop
Drift files work perfectly together with drift's existing Dart API:
- you can write Dart queries for tables declared in a drift file:
Future<void> insert(TodosCompanion companion) async {
await into(todos).insert(companion);
}
- by importing Dart files into a drift file, you can write sql queries for tables declared in Dart.
- generated methods for queries can be used in transactions, they work together with auto-updating queries, etc.
If you're using the fromJson
and toJson
methods in the generated Dart classes and need to change the name of a column in json, you can do that with the JSON KEY
column constraints, so id INT NOT NULL JSON KEY userId
would generate a column serialized as "userId" in json.
Dart components in SQL
You can make most of both SQL and Dart with "Dart Templates", which is a Dart expression that gets inlined to a query at runtime. To use them, declare a $-variable in a query:
filterTodos: SELECT * FROM todos WHERE $predicate;
Drift will generate a Selectable<Todo>
method with a predicate
parameter that can be used to construct dynamic filters at runtime:
Stream<List<Todo>> watchInCategory(int category) {
return filterTodos((todos) => todos.category.equals(category)).watch();
}
This lets you write a single SQL query and dynamically apply a predicate at runtime! This feature works for
- expressions, as you've seen in the example above
- single ordering terms:
SELECT * FROM todos ORDER BY $term, id ASC
will generate a method taking anOrderingTerm
. - whole order-by clauses:
SELECT * FROM todos ORDER BY $order
- limit clauses:
SELECT * FROM todos LIMIT $limit
- insertables for insert statements:
INSERT INTO todos $row
generates anInsertable<TodoEntry> row
parameter
When used as expression, you can also supply a default value in your query:
getTodos ($predicate = TRUE): SELECT * FROM todos WHERE $predicate;
This will make the predicate
parameter optional in Dart. It will use the default SQL value (here, TRUE
) when not explicitly set.
Type converters
You can import and use type converters written in Dart in a drift file. Importing a Dart file works with a regular import
statement. To apply a type converter on a column definition, you can use the MAPPED BY
column constraints:
CREATE TABLE users (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT,
preferences TEXT MAPPED BY `const PreferenceConverter()`
);
Queries or views that reference a table-column with a type converter will also inherit that converter. In addition, both queries and views can specify a type converter to use for a specific column as well:
CREATE VIEW my_view AS SELECT 'foo' MAPPED BY `const PreferenceConverter()`
SELECT
id,
json_extract(preferences, '$.settings') MAPPED BY `const PreferenceConverter()`
FROM users;
More details on type converts in drift files are available here.
When using type converters, we recommend the apply_converters_on_variables
build option. This will also apply the converter from Dart to SQL, for instance if used on variables: SELECT * FROM users WHERE preferences = ?
. With that option, the variable will be inferred to Preferences
instead of String
.
Existing row classes
You can use custom row classes instead of having drift generate one for you. For instance, let's say you had a Dart class defined as
class User {
final int id;
final String name;
User(this.id, this.name);
}
Then, you can instruct drift to use that class as a row class as follows:
import 'row_class.dart'; --import for where the row class is defined
CREATE TABLE users (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
) WITH User; -- This tells drift to use the existing Dart class
When using custom row classes defined in another Dart file, you also need to import that file into the file where you define the database. For more general information on this feature, please check this page.
Custom row classes can be applied to SELECT
queries defined a .drift
file. To use a custom row class, the WITH
syntax can be added after the name of the query.
For instance, let's say we expand the existing Dart code in row_class.dart
by adding another class:
class UserWithFriends {
final User user;
final List<User> friends;
UserWithFriends(this.user, {this.friends = const []});
}
Now, we can add a corresponding query using the new class for its rows:
-- table to demonstrate a more complex select query below.
-- also, remember to add the import for `UserWithFriends` to your drift file.
CREATE TABLE friends (
user_a INTEGER NOT NULL REFERENCES users(id),
user_b INTEGER NOT NULL REFERENCES users(id),
PRIMARY KEY (user_a, user_b)
);
allFriendsOf WITH UserWithFriends: SELECT users.**, LIST(
SELECT * FROM users a INNER JOIN friends ON user_a = a.id WHERE user_b = users.id
UNION ALL
SELECT * FROM users b INNER JOIN friends ON user_b = b.id WHERE user_a = users.id
) AS friends FROM users WHERE id = :id;
The WITH UserWithFriends
syntax will make drift consider the UserWithFriends
class. For every field in the constructor, drift will check the column from the query and verify that it has a compatible type. Internally, drift will then generate query code to map the row to an instance of the UserWithFriends
class.
For a more complete overview of using custom row classes for queries, see the section for queries.
Dart documentation comments
Comments added before columns in a drift file are added as Dart documentation comments in the generated row class:
CREATE TABLE friends (
-- The user original sending the friendship request
user_a INTEGER NOT NULL REFERENCES users(id),
-- The user accepting the friendship request from [userA].
user_b INTEGER NOT NULL REFERENCES users(id),
PRIMARY KEY (user_a, user_b)
);
The generated userA
and userB
field in the Friend
class generated by drift will have these comments as documentation comments.
Result class names
For most queries, drift generates a new class to hold the result. This class is named after the query with a Result
suffix, e.g. a myQuery
query would get a MyQueryResult
class.
You can change the name of a result class like this:
routesWithNestedPoints AS FullRoute: SELECT r.id, -- ...
This way, multiple queries can also share a single result class. As long as they have an identical result set, you can assign the same custom name to them and drift will only generate one class.
For queries that select all columns from a table and nothing more, drift won't generate a new class and instead re-use the dataclass that it generates either way. Similarly, for queries with only one column, drift will just return that column directly instead of wrapping it in a result class. It's not possible to override this behavior at the moment, so you can't customize the result class name of a query if it has a matching table or only has one column.
Supported statements
At the moment, the following statements can appear in a .drift
file.
import 'other.drift'
: Import all tables and queries declared in the other file into the current file.- DDL statements: You can put
CREATE TABLE
,CREATE VIEW
,CREATE INDEX
andCREATE TRIGGER
statements into drift files. - Query statements: We support
INSERT
,SELECT
,UPDATE
andDELETE
statements.
All imports must come before DDL statements, and those must come before named queries.
If you need support for another statement, or if drift rejects a query you think is valid, please create an issue!