Drift Database: A Step-by-Step Guide for Beginners

Palwisha Baloch — Wed, 28 Jun 2023 18:28:23 +0000

Before we dive into the step-by-step guide, let us get an understanding of What is Drift database and its concept?

Drift database is a modern, reactive database for Flutter apps. It is built on top of SQLite, but it provides several features that make it easier to use and more powerful. For example, the Drift database supports auto-updating streams, which means that your app will always be up-to-date with the latest data. Drift database also supports type safety, which means that you can be sure that your queries will be executed correctly.

The basic concept of the Drift database is that it is a reactive database. This means that it automatically updates your app when the data changes. This is in contrast to traditional databases, which require you to manually poll the database for changes.

Now we know about the basic concept of the Drift database let's move forward with the following:

embed Getting started
Configuration of Drift database
Perform CURD operation
Advance queries(Joins)

Getting started

Inside your Flutter project, add the following dependencies in pubspec.yaml file.

Note: add the latest version of following packages

dependencies:
  drift: ^2.9.0
  sqlite3_flutter_libs: ^0.5.0
  path_provider: ^2.0.0
  path: ^1.8.3

dev_dependencies:
  drift_dev: ^2.9.0
  build_runner: ^2.4.5

drift: This is the core package defining most APIs
sqlite3_flutter_libs: Ships the latest sqlite3 version with your Android or iOS app. This is not required when you're not using Flutter, but then you need to take care of including sqlite3 yourself.
path_provider and path: Used to find a suitable location to store the database. Maintained by the Flutter and Dart team
drift_dev: This development-only dependency generates query code based on your tables. It will not be included in your final app.
build_runner: Common tool for code generation, maintained by the Dart team
Configuration of Drift database
Inside your root project create a database file called database.dart and create a class MyDatabase which extends _$database.

class MyDatabase extends _$MyDatabase {
  MyDatabase() : super();
}

Now create a function _openConnection which will create a database file in the defined location.

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));

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

LazyDatabase util lets us find the right location for the file async.

The db.sqlite is the name of the database file located in the application’s database folder. Add the schemaVersion in MyDatabase class.

@override
  int get schemaVersion => 1;

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

The final code will be:

import 'dart:io';

import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;

part 'database.g.dart';

@DriftDatabase()
class AppDb extends _$AppDb {
  AppDb() : super(_openConnection());

  @override
  int get schemaVersion => 1;
}

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));

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

Perform CURD operation

Create Table

Let’s create an employee table by creating a file employee_table.dart

import 'package:drift/drift.dart';

class Employees extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().nullable()();
  TeColumn get post => text().nullable()();
  IntColumn get salary => integer().nullable()();
}

After creating a table, add the Employee class to MyDatabase inside DriftDabse() function and run dart run build_runner build on the terminal. If you want to continuously rebuild the generated code where you change your code, run dart run build_runner watch instead. When you run the build runner command it will create a

@DriftDatabase(tables: [Employee])

Insert

Before inserting data into the table, create a helper file employeeTableHelper.dart where we define our all queries. You can very easily insert any valid object into tables. As some values can be absent (like default values that we don’t have to set explicitly), we again use the companion version.


// returns the generated id
Future<int> addEmployee(EmployeeCompanion entry) {
  return into(employee).insert(entry);
}

All row classes generated will have a constructor that can be used to create objects:

addEmployee(
  EmployeeCompanion(
    name: Value('John Doe'),
    post: Value('Manager'),
    salary: Value('200000'),
  ),
);

You can also insert multiple entries by using a batch. To do that, you can use the insertAll method inside a batch:

Future<void> insertMultipleEntries() async{
  await batch((batch) {
    // functions in a batch don't have to be awaited - just
    // await the whole batch afterwards.
    batch.insertAll(employee, [listOfEmployees);
  });
}

Select

You can create select statements by starting them with select(tableName), where the table name is a field generated for you by drift. Each table used in a database will have a matching field to run queries against. Any query can be run once with get() or be turned into an auto-updating stream using watch()

Future<List<Employee>> get allEmployeeData => select(employee).get();

watches of all employee entries in a given category. The stream will automatically emit new items whenever the underlying data changes.

Stream<List<Employee>> watchEntriesInCategory(Category c) {
    return (select(employee)..where((t) => t.category.equals(c.id))).watch();
  }

We can also use Where, Limit and Ordering in dart query as same as we use in SQL queries. Let's see how its works

Where

You can apply filters to a query by calling where(). The where method takes a function that should map the given table to an Expression of boolean. A common way to create such expression is by using equals on expressions. Integer columns can also be compared with isBiggerThan and isSmallerThan. You can compose expressions using a & b, a | b and a.not(). For more details on expressions, see this guide.


Future<Employee> get employeeData => select(employee).where((t) => t.name.equals("john Doe")).getSingle();

Limit

You can limit the number of results returned by calling limit on queries. The method accepts the number of rows to return and an optional offset.

Future<List<Employee>> limitEmployee(int limit, {int offset}) {
  return (select(employee)..limit(limit, offset: offset)).get();
}

Ordering

You can use the orderBy method on the select statement. It expects a list of functions that extract the individual ordering terms from the table. You can use any expression as an ordering term - for more details, see this guide.

Future<List<Employee>> sortEntriesAlphabetically() {
  return (select(employee)..orderBy([(t) => OrderingTerm(expression: t.name)])).get();
}

You can also reverse the order by setting the mode property of the OrderingTerm to OrderingMode.desc

Update

You can use the generated classes to update individual fields of any row. For updates, we use the “companion” version of a generated class. This wraps the fields in a “Value” type which can be set to be absent using “Value.absent()”. This allows us to separate between “SET category = NULL” (category: Value(null)) and not update the category at all: category: Value.absent().

Future updateEmployeePost() {
return (update(employee)
      ..where((t) => t.name.equals("John Doe")
    ).write(EmployeeCompanion(
      post: Value("Assistant Manager"),
    ),
  );
}

Using replace will update all fields from the entry that are not marked as a primary key. It will also make sure that only the entry with the same primary key will be updated.

Future updateEmployee(Employee employeeData) {
  return update(employee).replace(employeeData);
}

Delete

you can delete a single row by using Where() in the query as

Future deleteEmployeeRow() {
  return (delete(employee)..where((t) => t.id.equals(10))).go();
}

If you don’t explicitly add a where the clause on deletes, the statement will affect all rows in the table

Future deleteEmployee() {
  return delete(employee).go();
}

The above query will delete all the data in the employee table.

Advance queries(Joins)

Drift supports SQL joins to write queries that operate on more than one table. To use that feature, start a select regular select statement with select(table) and then add a list of joins using .join(). For inner and left outer joins, an ON expression needs to be specified. Here's an example, we use an employee table and create another table employee-info for join.

import 'package:drift/drift.dart';

class EmployeesInfo extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get employeeId => text().nullable()();
  IntColumn get contact => text().nullable()();
  TextColumn get adress => integer().nullable()();
}

Now let us suppose we have added employee information in employee-info table by following the above steps.

Future<List<Employee>> employeeWithInfo() async {

  final query = select(employee).join([
    // In joins, `useColumns: false` tells drift to not add columns of the
    // joined table to the result set. This is useful here, since we only join
    // the tables so that we can refer to them in the where clause.
    leftJoin(employee-info, employee-info.employeeId.equals(employee.id),
        useColumns: false)
  ])
    ..where(employee.salary.isSmallerOrEqual(80000));

  return query.map((row) => row.readTable(otherTodos)).get();
}

Calling get()on a select statement with join returns a Future of List<TypedResult>, respectively. Each TypedResult represents a row from which data can be read. It contains a rawData getter to obtain the raw columns. But more importantly, the readTable method can be used to read a data class from a table.

In the example query above, we can read the employee entry and the employee-info from each row like this:

return query.map((rows) {
  return rows.map((row) {
    return EmployeeWithInfo(
      row.readTable(employee),
      row.readTableOrNull(employee-info),
    );
  }).toList();
});

Note: readTable will throw an ArgumentError when a table is not present in the row. For instance, employeeId might not be in any empoyee-info. To account for that, we use row.readTableOrNull to load employee info.

Conclusion

By using the drift database we can write type-safe queries in Dart or SQL, and auto-updating streams that can easily manage transactions and so much more. In this article, we have covered the basic and advanced queries in dart. For further detail and so many advanced features see the drift complete guide https://drift.simonbinder.eu/docs/getting-started/

DEV Community: Palwisha Baloch