SQLite Database with sqflite
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
| Operation | SQL | sqflite method |
|---|---|---|
| Create | INSERT | db.insert(table, map) |
| Read | SELECT | db.query(table, where: ...) |
| Update | UPDATE | db.update(table, map, where: ...) |
| Delete | DELETE | db.delete(table, where: ...) |
| Custom SQL | any | db.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.

