Database

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.

Exercise 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').

Show example 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');
}

Test yourself

Match the sqflite parts

sqflite

Database
Database

Connection
query

Query
await

Wait
What does db.query return?
- Row list
- Memory
- HTTP response
- Time
Show answer

Row list — db.query returns a row list (Future).

The db.query call

await ____.query('tasks');

Show answer

await db.query('tasks');

Next lesson: Concurrency

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies