Drift Database: A Step-by-Step Guide for Beginners
Published: 2023-06-28 18:28:23

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](https://drift.simonbinder.eu/docs/getting-started/expressions/).

```
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/](https://drift.simonbinder.eu/docs/getting-started/)