Getting Started

Drift provides a dart_api to define tables and to write SQL queries. Especially when you are already familiar with SQL, it might be easier to define your tables directly in SQL, with CREATE TABLE statements. Thanks to a powerful SQL parser and analyzer built into drift, you can still run type-safe SQL queries with support for auto-updating streams and all the other drift features. The validity of your SQL is checked at build time, with drift generating matching methods for each table and SQL statement.

Setup

The basic setup of adding the drift dependencies matches the setup for the dart_apis. It is described in the setup page.

What's different is how tables and queries are declared. For SQL to be recognized by drift, it needs to be put into a .drift file. In this example, we use a .drift file next to the database class named tables.drift:

-- this is the tables.drift file
CREATE TABLE todos (
    id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    body TEXT,
    category INT REFERENCES categories (id)
);

CREATE TABLE categories (
    id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
    description TEXT
) AS Category; -- see the explanation on "AS Category" below

/* after declaring your tables, you can put queries in here. Just
   write the name of the query, a colon (:) and the SQL: */
todosInCategory: SELECT * FROM todos WHERE category = ?;

/* Here's a more complex query: It counts the amount of entries per
category, including those entries which aren't in any category at all. */
countEntries:
  SELECT
    c.description,
    (SELECT COUNT(*) FROM todos WHERE category = c.id) AS amount
  FROM categories c
  UNION ALL
  SELECT null, (SELECT COUNT(*) FROM todos WHERE category IS NULL);

On that AS Category

Drift will generate Dart classes for your tables, and the name of those classes is based on the table name. By default, drift just strips away the trailing s from your table. That works for most cases, but in some (like the categories table above), it doesn't. We'd like to have a Category class (and not Categorie) generated, so we tell drift to generate a different name with the AS <name> declaration at the end.

Integrating drift files into the database simple, they just need to be added to the include parameter of the @DriftDatabase annotation. The tables parameter can be omitted here, since there are no Dart-defined tables to be added to the database.

import 'dart:io';

import 'package:drift/drift.dart';
// These imports are used to open the database
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;

part 'database.g.dart';

@DriftDatabase(
  // relative import for the drift file. Drift also supports `package:`
  // imports
  include: {'tables.drift'},
)
class AppDb extends _$AppDb {
  AppDb() : super(_openConnection());

  @override
  int get schemaVersion => 1;
}

LazyDatabase _openConnection() {
  // the LazyDatabase util lets us find the right location for the file async.
  return LazyDatabase(() async {
    // put the database file, called db.sqlite here, into the documents folder
    // for your app.
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));

    return NativeDatabase.createInBackground(file);
  });
}

To generate the database.g.dart file which contains the _$AppDb superclass, run dart run build_runner build on the command line.