Create Database with Flutter Drift
Drift is the relational persistence library for your Dart and Flutter apps. Drift lets you write queries in both SQL and Dart, providing fluent apis for both languages. You can filter and order results or use joins to run queries on multiple tables. Before start coding I want to thank Simon Binder and other Contributors for such a great library
Let’s start
We’re in the process of establishing a basic Task (Todo) database. We will develop methods for task getting, deletion, and insertion. The main goal of that article is how to deal with multiple tables and how to join them. To set up our local database, we have to add some packages to pubspec.yaml . Ensure you include these packages in the dependencies section of your pubspec.yaml file:
dependencies:
flutter:
sdk: flutter
drift: ^2.14.1
path: ^1.8.3
path_provider: ^2.1.1
sqlite3: ^2.3.0
sqlite3_flutter_libs: ^0.5.18
equatable: ^2.0.5These packages to dev_dependencies section:
dev_dependencies:
build_runner: ^2.4.7
drift_dev: ^2.14.1Then run flutter pub get to download and install the package. Now we are ready to use drift database
Create our tables
Each task is categorized into a specific type. To implement this, we will create a TypeTable that includes various task types, such as Subtask, Bug, and Feature. Create tables.dartfile and put these codes inside it
import 'package:drift/drift.dart';
class TypeTable extends Table {
IntColumn get id => integer().autoIncrement()();
TextColumn get typeName => text()();
}Let's create our main table TaskTable . You might be curious about the field: IntColumn get type => integer().references(TypeTable, #id)();. This field allows us to reference the TypeTable and retrieve the specific type associated with each task. Every task is linked to only one type, so we will create a one-to-one relationship between TaskTable and TypeTable. This will be achieved using reference keys, where each task retains the associated type ID. Put these codes inside tables.dart file as well
class TaskTable extends Table {
IntColumn get id => integer().autoIncrement()();
TextColumn get title => text()();
IntColumn get type => integer().references(TypeTable, #id)();
}In our database, each task involves multiple attendants. To establish a many-to-one relationship between the AttendantTable and TaskTable, we will use the reference key again. This allows each attendant to hold the associated task ID. Put these codes inside tables.dart file as well
class AttendantTable extends Table {
IntColumn get id => integer().autoIncrement()();
TextColumn get name => text()();
IntColumn get taskId => integer().references(TaskTable, #id)();
}Okay, our tables are ready. After defining our tables we should associate these tables with the database. Create app_database.dart file and put these codes inside it:
import 'dart:io';
import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'package:sqlite3/sqlite3.dart';
import 'package:sqlite3_flutter_libs/sqlite3_flutter_libs.dart';
import 'tables.dart'; //your tables locations
part 'app_database.g.dart';
@DriftDatabase(tables: [TaskTable, TypeTable, AttendantTable])
/// Put your tables inside [tables] list
class AppDatabase extends _$AppDatabase {
AppDatabase() : super(_openConnection());
/// This is our db version. We need this for migration
@override
int get schemaVersion => 1;
/// We will handle migration proccess here
@override
MigrationStrategy get migration {
return MigrationStrategy(
onCreate: (m) async {
/// Create all tables
m.createAll();
},
beforeOpen: (details) async {
/// Enable foreign_keys
await customStatement('PRAGMA foreign_keys = ON');
},
);
}
}
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();
/// You can replace [db.sqlite] with anything you want
/// Ex: cat.sqlite, darthVader.sqlite, todoDB.sqlite
final file = File(p.join(dbFolder.path, 'db.sqlite'));
/// Also work around limitations on old Android versions
if (Platform.isAndroid) {
await applyWorkaroundToOpenSqlite3OnOldAndroidVersions();
}
/// Make sqlite3 pick a more suitable location for temporary files - the
/// one from the system may be inaccessible due to sandboxing.
final cachebase = (await getTemporaryDirectory()).path;
/// We can't access /tmp on Android, which sqlite3 would try by default.
/// Explicitly tell it about the correct temporary directory.
sqlite3.tempDirectory = cachebase;
return NativeDatabase.createInBackground(file);
});
}To complete the setup, execute the code generation command. This will automate the creation of the necessary database structures based on the defined relationships and tables
flutter packages pub run build_runner build — delete-conflicting-outputs
After running this command you will see app_database.g.dart generated. So everything is done. We can use our database just like this
AppDatabase database = AppDatabase();Write the first query
Firstly let's start with a simple Delete query. Put this code inside the AppDatabase class
Future<void> deleteTask(int taskId) async {
/// Before deleting a specific task, we need to first delete the related attendants.
await (delete(attendantTable)..where((tbl) => tbl.taskId.equals(taskId)))
.go();
await (delete(taskTable)..where((tbl) => tbl.id.equals(taskId))).go();
}Let's explain this query. In this query, we have specified the table name (taskTable) and the unique taskId associated with the record we want to delete. It’s crucial to note the where key in this context. The where clause is essential because, without it, the delete statement would impact all rows in the table, as it would lack a specific condition to target a particular record for deletion.
The next step is retrieving (Select) data from the database. The main question is how can we join all the tables. We should define data classes for retrieving data from TaskTable. The defined TaskModel class will encompass not only the task ID and title but also the associated type and attendants list. Here are our models. Create the models.dart file and ensure you add these codes inside it
import 'package:equatable/equatable.dart';
class TaskModel extends Equatable {
final int id;
final String title;
final String type;
final List<AttendantModel> attendant;
const TaskModel({
required this.id,
required this.title,
required this.type,
required this.attendant,
});
TaskModel copyWith({
int? id,
String? title,
String? type,
List<AttendantModel>? attendant,
}) {
return TaskModel(
id: id ?? this.id,
title: title ?? this.title,
type: type ?? this.type,
attendant: attendant ?? this.attendant,
);
}
@override
List<Object> get props => [id, title, type, attendant];
}
class AttendantModel extends Equatable {
final int id;
final String name;
const AttendantModel({
required this.id,
required this.name,
});
@override
List<Object> get props => [id, name];
@override
String toString() => name;
}
class TypeModel extends Equatable {
final int id;
final String typeName;
const TypeModel({
required this.id,
required this.typeName,
});
@override
List<Object> get props => [id, typeName];
}After that, we can write our retrieving query. The query for this is quite straightforward — simply call the select method and map it to our TypeModel.
Future<List<TypeModel>> getAllTypes() async {
return (await select(typeTable).get())
.map((e) => TypeModel(id: e.id, typeName: e.typeName))
.toList();
}Now that our type retrieval method is in place, let’s turn our attention to fetching all tasks. Please incorporate this code within the AppDatabase class, as explained through comments:
Future<List<TaskModel>> getAllTasks() async {
final query = select(taskTable).join([
/// Join taskTable with typeTable with typeTable id
innerJoin(
typeTable,
taskTable.type.equalsExp(typeTable.id),
),
/// Join taskTable with attendantTable with taskTable id
innerJoin(
attendantTable,
attendantTable.taskId.equalsExp(taskTable.id),
),
]);
/// Order our result depending on id
query.orderBy([OrderingTerm.desc(taskTable.id)]);
/// Select statement with join returns return Future of List<TypedResult>
/// 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.
final queryResult = await query.get();
final groupedData = <TaskModel, List<AttendantModel>>{};
/// Parsing result
for (var row in queryResult) {
/// [readTable] method can be used to read a data class from a table.
final task = row.readTable(taskTable);
final type = row.readTable(typeTable);
final attendant = row.readTable(attendantTable);
final taskModel = TaskModel(
id: task.id,
title: task.title,
type: TypeModel(id: type.id, typeName: type.typeName),
attendant: const [],
);
/// For each entry (row) that is included in a task, put it in the map of items.
final attendantList = groupedData.putIfAbsent(taskModel, () => []);
/// Include an attendant in the [groupedData] associated with the respective task.
attendantList.add(AttendantModel(id: attendant.id, name: attendant.name));
}
/// Merge the map of tasks with the map of entries
return groupedData.entries
.map((e) => e.key.copyWith(attendant: e.value))
.toList();
}The final step involves data insertion, which is as simple as the type retrieval method. Initially, we invoke the into method and specify the table name. Subsequently, we call the insert method and invoke the specific class generated for us by drift . What is the transaction? Drift has support for transactions and allows multiple statements to run atomically so that none of their changes is visible to the main database until the transaction is finished.
Future<int> insertTask(String taskTitle, int typeId) async {
return transaction<int>(() async {
/// Insert new task to db
int newTaskId = await into(taskTable).insert(
TaskTableCompanion.insert(title: taskTitle, type: typeId),
);
/// Insert new mock attendants
/// Assign [newTaskId] to these new attendants
await into(attendantTable).insert(
AttendantTableCompanion.insert(
name: 'John',
taskId: newTaskId,
),
);
await into(attendantTable).insert(
AttendantTableCompanion.insert(
name: 'Doe',
taskId: newTaskId,
),
);
return newTaskId;
});
}It is our last query. In the next part, we will handle the migration process. Hope you like it… Let me know your thoughts! You can find a working example (source code) below.
