Contents
1. Introduction
Drift is a powerful database library for Dart and Flutter applications. To support its advanced capabilities like type-safe SQL queries, verification of out database and migrations, it uses a builder and command-line tooling that runs at compile-time.
we can create the Dart class for mapping the table below
class TodoItems extends Table {
IntColumn get id => integer().autoIncrement()();
TextColumn get title => text().withLength(min: 6, max: 32)();
TextColumn get content => text().named('body')();
IntColumn get category => integer().nullable()();
}
But I like to use the SQL statement for generate the ORM coding (I will show we later). Because I will usually to create the SQL for a table first, and I just need to re-use my SQL will be ok 🙂
2. Usage
I will show we how to use the SQL statements to create a model in Drift.
2.1 Import dependencies
Drift is base on sqlite3 and flutter build runner for generating codes, so we need to import the below dependencies in pubspec.yaml file:
dependencies:
drift: ^2.16.0
sqlite3_flutter_libs: ^0.5.20
path_provider: ^2.1.2
path: ^1.9.0
dev_dependencies:
drift_dev: ^2.16.0
build_runner: ^2.4.8
2.2 Create the Database class
For example, we need to create a book db, so we can create a book_database.dart file below
import 'dart:io';
import 'package:drift/drift.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;
import 'package:drift/native.dart';
part 'book_database.g.dart'; //this is the generation file and need to use the same file name with existing db file
@DriftDatabase(
include: {'book.drift'}, //the SQL file for create book table
)
class BookDatabase extends _$BookDatabase {
BookDatabase() : super(_openConnection());
@override
int get schemaVersion => 1;
}
LazyDatabase _openConnection() {
return LazyDatabase(() async {
// put the database file, called db.sqlite here, into the documents folder
// for our app.
final dbFolder = await getApplicationSupportDirectory();
// setup the db file's path
final file = File(p.join(dbFolder.path, 'book.db'));
return NativeDatabase.createInBackground(file);
});
}
2.2 Create the SQL statement file
Create the book.drift file, just put the normal SQL statements for creating the table, and we also can create the custom methods in SQL:
CREATE TABLE IF NOT EXISTS "book" (
"id" INTEGER PRIMARY KEY NOT NULL UNIQUE,
"viewCount" INTEGER DEFAULT(NULL),
"name" VARCHAR(64) COLLATE NOCASE NOT NULL UNIQUE,
"artist" VARCHAR(64) COLLATE NOCASE NOT NULL,
"folder" VARCHAR(64) COLLATE NOCASE NOT NULL,
"coverImg" VARCHAR(64) COLLATE NOCASE NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS "book_1" ON book (id);
CREATE UNIQUE INDEX IF NOT EXISTS "book_2" ON book (name);
isBookExists: Select count(*) from book where name = :name Limit 1;
filterBooks: SELECT * FROM book WHERE $predicate;
getTop10: SELECT * FROM book ORDER BY viewCount DESC Limit 10;
As we can see, we just created a simple book table, and create the index for id and name column, in the other hand, we also create 3 custom methods, and I will let you know how to use them later.
After generate the code, drift will auto create these 3 methods in DB helper class with dart codes
2.3 Generate Database Helper Class
We can execute below command for generating DB helper class:
dart run build_runner build
we will find there is one more file will be generated book_database.g.dart, and we can also find the custom methods in it. Of course, we don’t need to care about how does it works, we just need to use it!
2.4 Using the Database Helper
We will use the GetX framework for the example.
put the BookDatabase class in main.dart file
Get.put(BookDatabase());
and we can use it in anywhere:
//get the book DB helper
final BookDatabase _bookDB = Get.find<BookDatabase>();
...
//get all book items from DB
var dbBooks = await _bookDB.book.select().get();
...
//only update the folder column in book item to DB
_bookDB.updateBookItem(
bookItem.id,
BookCompanion(
folder: drift.Value(bookItem.folder),
),
);
...
// batch insert book items
List<BookData> dbBooks = [];
dbBooks.add(BookData(
id: 1,
viewCount: 0,
name: 'book 1',
artist: 'artist 1',
folder: 'assets/books/01/',
coverImg: 'assets/books/01/cover.jpg',));
dbBooks.add(BookData(
id: 2,
viewCount: 0,
name: 'book 2',
artist: 'artist 2',
folder: 'assets/books/02/',
coverImg: 'assets/books/02/cover.jpg',));
_bookDB.book.insertAll(dbBooks);
2.5 Use the custom methods
For this example, we have defined 3 methods in SQL statements:
isBookExists: Select count(*) from book where name = :name Limit 1 ;
filterBooks: SELECT * FROM book WHERE $predicate;
getTop10: SELECT * FROM book ORDER BY viewCount DESC Limit 10;
we can use them below
//check the book whether exists by name
int bookCount = await _bookDB.isBookExists('bookName').getSingle();
//get books with custom filter
var bookItems = await _bookDB.filterBooks((book) => book.name.contains('bookName')).get();
//get top 10 books
var topTenBooks = await _bookDB.getTop10().get();
3. Conclusion
Drift is useful for helping to manage the database, you can create the custom methods to get the data, and if you don’t want to use SQL statements, you also can use the Dart API and find the detail here. There are still many powerful functions you can find in the officer website.
