Learn to Use Drift Database with Flutter: A Starter Guide

Drift Database is a powerful library for Dart and Flutter applications—or at least, that’s what the documentation says. I haven’t used Drift Database before, but as a new Flutter developer, I’m looking for a solid on-device database solution, so I thought I’d give it a try. In this article, we’ll dive into how Drift Database works and how we can use it effectively. Let’s get started!

here are the official docs, if you want to look it up yourself.

Adding Drift to your project

The documentation provides several ways to add the necessary packages to the pubspec.yaml file, depending on the type of application you’re building.

You can choose between drift_flutter for mobile Flutter applications (iOS and Android), sqlite3 for server-based Dart applications or desktop apps, and drift_postgres if you want to use PostgreSQL instead of SQLite.

drift_flutter

dependencies:
  drift: ^2.21.0
  drift_flutter: ^0.1.0

dev_dependencies:
  drift_dev: ^2.21.0
  build_runner: ^2.4.13

or as command

dart pub add drift drift_flutter dev:drift_dev dev:build_runner

sqlite3

dependencies:
  drift: ^2.21.0
  sqlite3: ^2.4.6

dev_dependencies:
  drift_dev: ^2.21.0
  build_runner: ^2.4.13

or as command

dart pub add drift sqlite3 dev:drift_dev dev:build_runner

postgres

dependencies:
  drift: ^2.21.0
  postgres: ^3.4.0
  drift_postgres: ^1.3.0

dev_dependencies:
  drift_dev: ^2.21.0
  build_runner: ^2.4.13

or as command

dart pub add drift postgres drift_postgres dev:drift_dev dev:build_runner

If you want to use PostgreSQL, you’ll also need to add a build.yaml file with the following code to your project, as Drift only generates code for SQLite by default.

targets:
  $default:
    builders:
      drift_dev:
        options:
          sql:
            dialects:
              - postgres
              # Uncomment if you need to support both
#              - sqlite

This tutorial will focus on mobile applications, so I’ll be using drift_flutter. The code that follows will also be tailored for mobile applications. If you’d like to use one of the other options, please refer to a different article or consult the official documentation.

Set Up the Database

To set up the database, create a database.dart file. Inside this file, you’ll define the AppDatabase class and start specifying your table schemas. Although, in a production app, you’d likely define them in dedicated files. Here, I’ll define a WordPairs table, where each entry represents a combination of two words (first, last).

import 'package:drift/drift.dart';

part 'database.g.dart';

class WordPairs extends Table{
  IntColumn get id => integer().autoIncrement()();
  TextColumn get first => text()();
  TextColumn get last => text()();
}

@DriftDatabase(tables: [WordPairs])
class AppDatabase extends _$AppDatabase{

}

Let’s look at the WordPairs table first. As you can see, WordPairs extends the Table class, which is provided by Drift. The properties use different types of columnbuilders to specify the data type of each property. You can find the full list of available columnbuilders here.

The AppDatabase class extends _$AppDatabase, which will likely give you an error for now, as the _$AppDatabase class doesn’t exist yet. Similarly, the part statement may also cause an error because Drift needs to generate the _$AppDatabase class in the database.g.dart file. The @DriftDatabase(tables: [WordPairs]) annotation informs the buildrunner that this is the class it needs to create and specifies which tables to track. To run the buildrunner, open a terminal in your project’s folder and execute the following command:

dart run build_runner build

Ensure that your database file name matches the name in the part statement. For example, if your database file is named myappsdatabase.dart, the part statement should be myappsdatabase.g.dart. Otherwise, the buildrunner won’t be able to generate the _$AppDatabase class.

After running this command, you should now see a new file created next to your database file. Inside this file, you’ll find the _$AppDatabase class.

In your database file, you may still see an error in your AppDatabase class. This is because _$AppDatabase requires a constructor with a QueryExecutor parameter and also needs you to override the schemaVersion property.

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

  @override
  int get schemaVersion => 1;

  static QueryExecutor _openConnection(){
    return driftDatabase(name: "wordpairdb");
  }
}

The schemaVersion property is needed to inform Drift of the current version of your app’s database. Every time you change the database structure (e.g., adding, deleting, or updating tables and columns), you’ll increment this version number.

The QueryExecutor is the API responsible for executing SQL statements on the database.

With these set up, we can start performing database operations!

Insert, Update, Delete and Select

Before we dive into actual database operations, let’s look at two classes that buildrunner created inside the database.g.dart file: WordPair and WordPairsCompanion. Remember when we specified the structure of the WordPairs table?

class WordPairs extends Table{
  IntColumn get id => integer().autoIncrement()();
  TextColumn get first => text()();
  TextColumn get last => text()();
}

buildrunner used this information to create the WordPair and WordPairsCompanion classes. Drift generates these classes for each table you specify, representing the entities within the table. The WordPair class is used to hold and display information in your app’s widgets, while WordPairsCompanion is used for creating and updating table rows.

Companion objects wrap property values in a Value object, allowing Drift to determine which properties should be inserted or updated, depending on the operation you want to perform. For properties you wish to exclude from a database operation, you can use Value.absent().

    final companion = WordPairsCompanion(
      first: Value(pair.first),
      last: Value(pair.last),
    );

By not setting the id inside the companion object, Drift will automatically use Value.absent() for this property, signaling it to ignore id in the operation. Since id is auto-incremented, we don’t need to specify it manually.

To perform any database operations, we first need to initialize our AppDatabase and store it in a variable.

 final AppDatabase database = AppDatabase();

All database operations will use this object.

For each database operation, you need to specify the operation type (e.g., select, insert, update, or delete) and provide the table you want to access. Next, you’ll execute the operation by passing in the relevant object or query.

Let’s look at examples of select, insert, update, and delete statements.

Select

Future<List<WordPair>> getWordPairs() async {
    return await database.select(database.wordPairs).get();
}

select specifies the operation we want to perform, database.wordPairs is the table we want to access (created by build_runner), and get() executes the query against the database. This operation selects each entry in the WordPair table and returns them as a list of WordPair objects. Everything before get() is simply defining the query to be used. get() then executes that query.

For example, if you want to retrieve a single entry by its id, you can separate the query definition and its execution like this:

 Future<WordPair?> getWordPairById(int id) async {
    final query = database.select(database.wordPairs)
      ..where((tbl) => tbl.id.equals(id));

    final result = await query.getSingleOrNull();
    return result;
  }

This function will only return 1 WordPair, if one is found.

Insert

Future<int> saveFavorite(WordPair pair) async {
    final companion = WordPairsCompanion(
      first: Value(pair.first),
      last: Value(pair.last),
    );
    return await database.into(database.wordPairs).insert(companion);
  }

First, we create a companion object to specify what to insert. into defines the operation as an insert statement, while insert actually performs the query, inserting the companion object into the table.

Delete

Future<bool> removeFavorite(WordPair pair) async {
    return await (database.delete(database.wordPairs)
          ..where((tbl) => tbl.id.equals(pair.id))).go() > 0;
  }

delete tells Drift that this is a delete statement, and where specifies which entity (or entities) we want to delete. go executes the operation. Because where uses the .. operator and returns void, we need to wrap the query in parentheses to avoid a syntax error.

Alternatively, we could write it like this:

  final query = database.delete(database.wordPairs)
          ..where((tbl) => tbl.id.equals(pair.id));
    return await query.go();

Update

 Future<int> updateWordpair(WordPair pair) async {
    final companion = WordPairsCompanion(
      first: Value(pair.first),
      last: Value(pair.last),
    );
    return await (database.update(database.wordPairs)
          ..where((tbl) => tbl.id.equals(pair.id)))
        .write(companion);
  }

First, we create a companion object to specify which properties to update. update tells Drift that this is an update statement, and write performs the operation. Again, we pass in the companion object that contains the properties we want to update.

Conclussion

Drift Database is a great tool for making SQLite database access much easier. I learned a lot while writing this article, and I hope you learned something new as well. This was just a getting-started guide with Drift—there’s still much more to explore with this framework, too much for a single article. If you’d like me to dive deeper into a specific topic regarding Drift, let me know in the comments. I hope you enjoyed this article. Keep learning, and see you next time!