Drift – Using an SQLite Database with Flutter

This article is part of the Data Storage in Flutter series.

In some previous tutorials, I showed you how to use sembast and isar as a local data storage for your Flutter apps. This time, we are going to use the drift package to store our data in an SQLite database.

I will use the same app structure as in the other two tutorials to keep them comparable and only talk about the differences here.

SQLite is a very commonly used database for mobile apps, this makes it a very safe choice. Since it’s a relational SQL database this means that it requires the user to specify the data structure in the form of database tables. That is a different approach to databases like sembast which stores any type of JSON data.

The drift package helps us to take away some of the heavy lifting. That way, we don’t need to write any SQL statements – but we can if we wish.

You can find the source code for this tutorial on GitHub: https://github.com/bettercoding-dev/drift-sqlite

Prerequisites

There four packages are needed for adding drift to your project: drift, drift_flutter, drift_dev and build_runner. Together with the packages for riverpod and freezed this is what our dependencies should look like:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

dependencies:
  drift: ^2.20.3
  drift_flutter: ^0.2.0
  flutter:
    sdk: flutter
  flutter_riverpod: ^2.5.1
  freezed_annotation: ^2.4.4
  json_annotation: ^4.9.0
  riverpod_annotation: ^2.3.5

dev_dependencies:
  flutter_test:
    sdk: flutter
  flutter_lints: ^4.0.0
  build_runner: ^2.4.13
  riverpod_generator: ^2.4.3
  freezed: ^2.5.7
  json_serializable: ^6.8.0
  drift_dev: ^2.20.3

To add them all at once, you can use this command:

flutter pub add drift drift_flutter flutter_riverpod freezed_annotation json_annotation riverpod_annotation dev:build_runner dev:riverpod_generator dev:freezed dev:json_serializable dev:drift_dev

The drift package also relies on code generation. To watch for changes and rebuild the generated classes, use:

dart run build_runner watch --delete-conflicting-outputs    

The CakeTable

In contrast to sembast, where we had no schema definition at all, and isar where the schema could be derived from a data class, when using drift we need to manually specify our database table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import 'package:drift/drift.dart';
import 'package:local_storage_sqlite_drift/cake/model/cake.dart';

@UseRowClass(Cake)
class CakeTable extends Table {
  IntColumn get id => integer().autoIncrement()();

  TextColumn get name => text()();

  IntColumn get yummyness => integer()();
}

This is relatively simple, but we need to be careful to have our CakeModel and the CakeTable in sync. Notice the @UseRowClass annotation. This binds the Cake class to this table and helps deserialize the data when reading the database.

Creating a Database

With drift we need to create a class to specify our database.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

import 'package:drift/drift.dart';
import 'package:drift_flutter/drift_flutter.dart';
import 'package:local_storage_sqlite_drift/cake/database/cake_table.dart';
import 'package:local_storage_sqlite_drift/cake/model/cake.dart';

part 'cake_database.g.dart';

@DriftDatabase(tables: [CakeTable])
class CakeDatabase extends _$CakeDatabase {
  CakeDatabase() : super(_openConnection());

  @override
  int get schemaVersion => 1;

  static QueryExecutor _openConnection() {
    return driftDatabase(name: 'cake_database');
  }
}

Using the @DriftDatabase (line 7) annotation we mark our database class and specify the tables that should exist in the database.

The super constructor (line 9) requires us to pass in a QueryExecutor object that opens the database. We can use the driftDatabase function for this case.

In line 12 we need to specify a schema version. This becomes relevant when migrating between different versions of the database.

As with the other tutorials, we create a provider for the database. With drift this can be done synchronously.

1
2
3
4
5
6
7

import 'package:local_storage_sqlite_drift/common/database/cake_database.dart';
import 'package:riverpod_annotation/riverpod_annotation.dart';

part 'global_providers.g.dart';

@riverpod
CakeDatabase database(DatabaseRef ref) => CakeDatabase();

Implementing CakeRepsoitory with drift

Finally, let’s implement the CakeRepository. For simple database operations as we use in this case, drift offers us so-called managers. We can access the functions for the CakeTablelike this:

database.managers.cakeTable

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

import 'package:drift/drift.dart';
import 'package:local_storage_sqlite_drift/cake/model/cake.dart';
import 'package:local_storage_sqlite_drift/cake/repository/cake_repository.dart';
import 'package:local_storage_sqlite_drift/common/database/cake_database.dart';

class DriftCakeRepository implements CakeRepository {
  final CakeDatabase database;

  const DriftCakeRepository(this.database);

  @override
  Future deleteCake(int cakeId) =>
      database.managers.cakeTable.filter((cake) => cake.id(cakeId)).delete();

  @override
  Stream<List<Cake>> getAllCakesStream() => database.managers.cakeTable.watch();

  @override
  Future<int> insertCake(Cake cake) =>
      database.managers.cakeTable.create(
            (o) =>
            o(
              name: cake.name,
              yummyness: cake.yummyness,
            ),
      );

  @override
  Future updateCake(Cake cake) =>
      database.managers.cakeTable
          .filter((row) => row.id(cake.id))
          .update((o) =>
          o(
              name: Value(cake.name),
              yummyness: Value(cake.yummyness)
          )
      );
}

• Lines 11–13: To delete a single instance, we need to first create a filter on a Cake with the given id, and can then call the delete function.

• Lines 15–16: The watch function allows us to listen to changes to the table’s content. Since we specified Cake as the “row class” deserialization happens automatically.

• Lines 19–26: Inserting takes a few more lines of code. Here we have to use the create function and manually map the Cake attributes.

• Lines 29–37: Updating works similarly to inserting data. The only difference is that we first need to filter which rows should be affected, and then use the update function.

Summary

In this tutorial you learned how to use drift to use SQLite as your app’s data storage.

We specified a table that matches our data model, initialized a database and implemented the CakeRepository to create, update, delete and read data from the database.

Of course, drift comes with a ton more of features. If you plan to use this package for your next app, I’d recommend you checking out the documentation: https://drift.simonbinder.eu.