Database
- Dart course
- Advanced
- Lesson 81/83
- 6 min
Add data persistence with sqflite.
Database with sqflite
sqflite is the embedded SQLite package for Dart and Flutter. It is imported with import 'package:sqflite/sqflite.dart';. The Database class represents the connection; it is opened with openDatabase. db.query('tasks') lists the rows of a table.
Example: final db = await openDatabase('tasks.db');. All database operations are awaited because they return a Future. db.insert adds, db.update updates, db.delete deletes. In this lesson we will write the SqliteTaskService class; it takes a Database and returns tasks with the all() method. This adds persistent storage to the same interface.
Reading rows with db.query
db.query('tasks') returns a Future<List<Map<String, Object?>>>. Each row is a map; column names are keys. The result is awaited with await.
Example: final rows = await db.query('tasks'); return rows.map((r) => Task(id: r['id'] as int, title: r['title'] as String)).toList();. Here r['id'] reads a value from the row; as int casts the type. map turns each row into a Task object. This pattern is the standard way to map relational rows to objects. The operation is done inside an async method so that await can be used.
Practice The SqliteTaskService class
Add import 'package:sqflite/sqflite.dart'; at the top. Write class SqliteTaskService with a final Database db field and a SqliteTaskService(this.db) constructor. Add a Future<List<Task>> all() async method with final rows = await db.query('tasks'); and return rows.map((r) => Task(id: r['id'] as int, title: r['title'] as String)).toList(); inside. In main add print('Database: SqliteTaskService').
Starter code
import 'package:shelf/shelf.dart';
import 'package:shelf_router/shelf_router.dart';
class Task {
final int id;
final String title;
Task({required this.id, required this.title});
}
abstract class TaskService {
List<Task> list();
}
class InMemoryTaskService implements TaskService {
@override
List<Task> list() => [Task(id: 1, title: 'task 1')];
}
Response taskHandler(Request req, TaskService svc) {
return Response.ok('tasks: ${svc.list().length}');
}
// import the sqflite package
// write the SqliteTaskService class (Database, query)
void main() {
// print Database info
}Sample solution
import 'package:shelf/shelf.dart';
import 'package:shelf_router/shelf_router.dart';
import 'package:sqflite/sqflite.dart';
class Task {
final int id;
final String title;
Task({required this.id, required this.title});
}
abstract class TaskService {
List<Task> list();
}
class InMemoryTaskService implements TaskService {
@override
List<Task> list() => [Task(id: 1, title: 'task 1')];
}
Response taskHandler(Request req, TaskService svc) {
return Response.ok('tasks: ${svc.list().length}');
}
class SqliteTaskService {
final Database db;
SqliteTaskService(this.db);
Future<List<Task>> all() async {
final rows = await db.query('tasks');
return rows.map((r) => Task(id: r['id'] as int, title: r['title'] as String)).toList();
}
}
void main() {
print('Database: SqliteTaskService');
}Expected output
Database: SqliteTaskService
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Match the sqflite parts
| Term | Meaning |
|---|---|
sqflite |
Database |
Database |
Connection |
query |
Query |
await |
Wait |
2 What does db.query return?
- Row list Correct answer
- Memory
- HTTP response
- Time
Explanation: db.query returns a row list (Future).
3 The db.query call
await ____.query('tasks');await db.query('tasks');Explanation: await db.query('tasks') reads the rows.