Flutter Lesson 52 of 83 5 min read

SQLite in Flutter with sqflite

Store structured data in Flutter with SQLite using sqflite: create tables, insert, query, update, delete, and run migrations.

On this page

When your app has many records that need searching, sorting and filtering, such as notes, expenses or cached products, use a database. SQLite is a complete SQL database in a single file on the device. The sqflite package gives Flutter access to it on Android, iOS and macOS.

flutter pub add sqflite path

A model #

class Note {
  const Note({this.id, required this.title, required this.body, required this.createdAt});

  final int? id; // null until the database assigns one
  final String title;
  final String body;
  final DateTime createdAt;

  Map<String, Object?> toMap() => {
        'id': id,
        'title': title,
        'body': body,
        'created_at': createdAt.millisecondsSinceEpoch,
      };

  factory Note.fromMap(Map<String, Object?> map) => Note(
        id: map['id'] as int,
        title: map['title'] as String,
        body: map['body'] as String,
        createdAt: DateTime.fromMillisecondsSinceEpoch(map['created_at'] as int),
      );
}

SQLite has no date or boolean type. Store dates as integers (milliseconds) or ISO text, and booleans as 0 and 1.

Opening the database #

import 'package:path/path.dart' as p;
import 'package:sqflite/sqflite.dart';

class NotesDatabase {
  Database? _db;

  Future<Database> get database async => _db ??= await _open();

  Future<Database> _open() async {
    final path = p.join(await getDatabasesPath(), 'notes.db');
    return openDatabase(
      path,
      version: 1,
      onCreate: (db, version) async {
        await db.execute('''
          CREATE TABLE notes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            body TEXT NOT NULL,
            created_at INTEGER NOT NULL
          )
        ''');
      },
    );
  }

onCreate runs once, the first time the database file is created. The class continues in the next block with the methods that read and write notes.

Create, read, update, delete #

  Future<Note> insert(Note note) async {
    final db = await database;
    final id = await db.insert('notes', note.toMap()..remove('id'));
    return Note(id: id, title: note.title, body: note.body, createdAt: note.createdAt);
  }

  Future<List<Note>> all() async {
    final db = await database;
    final rows = await db.query('notes', orderBy: 'created_at DESC');
    return [for (final row in rows) Note.fromMap(row)];
  }

  Future<List<Note>> search(String text) async {
    final db = await database;
    final rows = await db.query(
      'notes',
      where: 'title LIKE ? OR body LIKE ?',
      whereArgs: ['%$text%', '%$text%'],
      orderBy: 'created_at DESC',
    );
    return [for (final row in rows) Note.fromMap(row)];
  }

  Future<void> update(Note note) async {
    final db = await database;
    await db.update('notes', note.toMap(), where: 'id = ?', whereArgs: [note.id]);
  }

  Future<void> delete(int id) async {
    final db = await database;
    await db.delete('notes', where: 'id = ?', whereArgs: [id]);
  }
}

Method	SQL it runs
`insert`	`INSERT INTO`
`query`	`SELECT`, with `where`, `orderBy`, `limit`, `offset`
`update`	`UPDATE ... WHERE`
`delete`	`DELETE ... WHERE`
`rawQuery`, `execute`	Any SQL you write

Always use whereArgs #

Never build SQL by joining strings with user input. That allows SQL injection, and breaks as soon as someone types an apostrophe.

// Dangerous and fragile
db.query('notes', where: "title = '$userInput'");

// Safe
db.query('notes', where: 'title = ?', whereArgs: [userInput]);

Using it in a screen #

class _NotesPageState extends State<NotesPage> {
  final _db = NotesDatabase();
  late Future<List<Note>> _notes = _db.all();

  Future<void> _add() async {
    await _db.insert(Note(title: 'New note', body: '', createdAt: DateTime.now()));
    setState(() => _notes = _db.all()); // reload
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: const Text('Notes')),
      body: FutureBuilder<List<Note>>(
        future: _notes,
        builder: (context, snapshot) {
          if (!snapshot.hasData) return const Center(child: CircularProgressIndicator());
          final notes = snapshot.data!;
          if (notes.isEmpty) return const Center(child: Text('No notes yet'));
          return ListView.builder(
            itemCount: notes.length,
            itemBuilder: (context, i) => ListTile(
              title: Text(notes[i].title),
              subtitle: Text('${notes[i].createdAt}'),
            ),
          );
        },
      ),
      floatingActionButton: FloatingActionButton(onPressed: _add, child: const Icon(Icons.add)),
    );
  }
}

Several changes at once #

A transaction makes a group of changes succeed or fail together. A batch sends many statements in one go, which is much faster for bulk inserts.

await db.transaction((txn) async {
  await txn.update('accounts', {'balance': 400}, where: 'id = ?', whereArgs: [1]);
  await txn.update('accounts', {'balance': 600}, where: 'id = ?', whereArgs: [2]);
});

final batch = db.batch();
for (final note in manyNotes) {
  batch.insert('notes', note.toMap()..remove('id'));
}
await batch.commit(noResult: true);

Changing the schema: migrations #

Users already have the old database on their phones. To add a column in a new version of the app, raise version and handle the change in onUpgrade.

openDatabase(
  path,
  version: 2,
  onCreate: (db, version) async {
    // Create the latest schema for new installs.
    await db.execute('''
      CREATE TABLE notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        body TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        pinned INTEGER NOT NULL DEFAULT 0
      )
    ''');
  },
  onUpgrade: (db, oldVersion, newVersion) async {
    if (oldVersion < 2) {
      await db.execute('ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0');
    }
  },
);

Never edit an old migration after release. Add a new step for each version.

Alternatives #

Package	Style	Notes
`sqflite`	Raw SQL	Simple and direct. Mobile and macOS
`drift`	Type-safe SQL with generated code	Queries checked at compile time, streams of results, migrations. Works on every platform including web
`hive_ce`, `isar`, `objectbox`	Object stores without SQL	Very fast for simple object storage

For a serious app with a real schema, drift is worth learning. sqflite is the easiest way to start.

Try it yourself #

Build an expense tracker with a table holding an amount, a category and a date. Add a form to insert expenses, a list sorted by date, swipe to delete, and a line showing the total for the current month using rawQuery with SUM.