SQLite Database with sqflite

Google Flutter 14 min min read Updated: Nov 16, 2026 Intermediate
SQLite Database with sqflite
Intermediate Topic 6 of 8

When Do You Need a Database?

When you have many records with a structure, such as notes, expenses or contacts, and want to search and sort them, a local database is the right tool. SQLite is a small database that lives in a file on the device. The sqflite package gives you access to it on Android and iOS.

bash flutter pub add sqflite path
Other options: Drift (typed SQL), Isar and ObjectBox are popular alternatives. SQLite is the best way to learn the basics, because SQL is useful everywhere. On desktop and web you need extra packages such as sqflite_common_ffi.

The Model

dart class Note { final int? id; final String title; final String body; final DateTime createdAt; const Note({this.id, required this.title, required this.body, required this.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), ); }

The Database Helper

dart import 'package:path/path.dart'; import 'package:sqflite/sqflite.dart'; class NotesDatabase { NotesDatabase._(); static final NotesDatabase instance = NotesDatabase._(); Database? _db; Future<Database> get database async { return _db ??= await _open(); } Future<Database> _open() async { final dir = await getDatabasesPath(); final path = join(dir, '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 ) '''); }, ); } // CREATE Future<int> insert(Note note) async { final db = await database; return db.insert('notes', note.toMap()..remove('id')); } // READ Future<List<Note>> getAll() async { final db = await database; final rows = await db.query('notes', orderBy: 'created_at DESC'); return rows.map(Note.fromMap).toList(); } 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%'], ); return rows.map(Note.fromMap).toList(); } // UPDATE Future<int> update(Note note) async { final db = await database; return db.update('notes', note.toMap(), where: 'id = ?', whereArgs: [note.id]); } // DELETE Future<int> delete(int id) async { final db = await database; return db.delete('notes', where: 'id = ?', whereArgs: [id]); } }
Always use whereArgs: Never build SQL by joining strings with user input. Using ? placeholders and whereArgs protects you from SQL injection.

Using It

dart final db = NotesDatabase.instance; await db.insert(Note(title: 'Shopping', body: 'Milk, eggs', createdAt: DateTime.now())); final notes = await db.getAll(); print(notes.length);

CRUD Cheat Sheet

OperationSQLsqflite method
CreateINSERTdb.insert(table, map)
ReadSELECTdb.query(table, where: ...)
UpdateUPDATEdb.update(table, map, where: ...)
DeleteDELETEdb.delete(table, where: ...)
Custom SQLanydb.rawQuery(sql, args), db.execute(sql)

Changing the Table Later: Migrations

dart openDatabase( path, version: 2, // increase the version onCreate: _createV2, onUpgrade: (db, oldVersion, newVersion) async { if (oldVersion < 2) { await db.execute('ALTER TABLE notes ADD COLUMN pinned INTEGER DEFAULT 0'); } }, )

Transactions

dart await db.transaction((txn) async { await txn.insert('notes', {'title': 'A', 'body': '1', 'created_at': 0}); await txn.insert('notes', {'title': 'B', 'body': '2', 'created_at': 0}); }); // both succeed or both fail
Tip: Always keep database code in a helper or repository class. Your widgets should call getAll() and never write SQL.

Conclusion

SQLite gives your app a fast, offline, structured storage. Learn CRUD and migrations, and always use parameters in queries.

Quick Summary: Open a database, create tables in onCreate, use insert, query, update and delete with whereArgs and handle upgrades with versions.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators