Verified SQL

Define your database and queries in SQL without giving up on type-safety.

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);

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.


Drift files

Learn everything about the .drift files, a powerful tool to define your database in SQL.

Experimental IDE

Get real-time feedback as you type sql

Supported sqlite extensions

Information on json1 and fts5 support in drift files

Custom SQL types

Use custom SQL types in Drift files and Dart code.

Custom queries

Write SQL for advanced queries that drift can't express in Dart yet.