Do you need to store and query structured data locally in your Flutter app? Using plain files or key-value storage works for small amounts of data, but it quickly becomes inefficient when the app needs to handle larger datasets, multiple records, or complex queries. In such cases, you might want to use SQLite with Flutter.
SQLite is a lightweight relational database engine that runs directly on the device without a separate server. With the sqflite plugin, Flutter apps can use SQLite to create tables, store rows of data, and perform fast queries using SQL syntax.
Note: The code examples in this article are simplified for explanation and learning purposes. They are not production-ready and should be treated as a reference only.
Adding sqflite and path to Your Project
To start working with SQLite in Flutter, you need two packages:
- sqflite: The plugin that provides SQLite support in Flutter.
- path: Helper library to build correct file paths for the database on different platforms.
Run the following commands in your project root:
flutter pub add sqflite pathDoing so updates your pubspec.yaml to something like below:
dependencies:
sqflite: ^latest
path: ^latestThe sqflite package handles database operations, while the path package ensures your database file is created in the right location across iOS, Android, and macOS.
Defining a Data Model for the Database
Before creating tables, think about how you want to represent your data. You could work directly with raw maps returned by SQLite, but that quickly becomes messy. Wouldn’t it be easier to convert those maps into clean Dart objects? That’s where defining a data model helps.
In this example, let’s define a simple User class with three fields: an ID, a name, and an email.
class User {
final int id;
final String name;
final String email;
const User({required this.id, required this.name, required this.email});
// Convert a User object into a Map for database storage
Map<String, dynamic> toMap() {
return {
'id': id,
'name': name,
'email': email,
};
}
// Convert a Map from the database back into a User object
factory User.fromMap(Map<String, dynamic> map) {
return User(
id: map['id'] as int,
name: map['name'] as String,
email: map['email'] as String,
);
}
@override
String toString() => 'User(id: $id, name: $name, email: $email)';
}In this example, toMap() prepares the object so it can be stored in SQLite, while fromMap() reconstructs a User object when reading rows back. This pattern keeps your code organized and avoids spreading raw Map<String, dynamic> logic throughout your app.
Setting Up the Database
With the data model created, the next step is to open a SQLite database and create a table that matches User. Where should the file live, and how do we avoid reopening the database every time?
A small helper that opens the DB once and exposes a single Database instance keeps things tidy and fast.
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
class AppDatabase {
AppDatabase._();
static final AppDatabase instance = AppDatabase._();
Database? _db;
Future<Database> get database async {
if (_db != null) return _db!;
_db = await _open();
return _db!;
}
Future<Database> _open() async {
final dbDir = await getDatabasesPath();
final path = join(dbDir, 'app.db');
return openDatabase(
path,
version: 1,
onCreate: (db, version) async {
await db.execute('''
CREATE TABLE users(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL
)
''');
},
);
}
}In this example, getDatabasesPath() returns a platform-safe directory, and join() builds a correct file path. The openDatabase call creates the file if it does not exist and runs onCreate the first time, where we define a users table that mirrors the User model.
The singleton pattern ensures you reuse the same Database connection across your app instead of opening new ones repeatedly.
Performing CRUD Operations
With the database and table ready, the next question is obvious: how do we actually add, fetch, update, or delete records? These four operations, commonly called CRUD, form the foundation of working with SQLite in Flutter.
Create: Inserting a User
Future<void> insertUser(User user) async {
Future<void> insertUser(User user) async {
final db = await AppDatabase.instance.database;
await db.insert(
'users',
user.toMap(),
conflictAlgorithm: ConflictAlgorithm.replace,
);
}Here we pass a User object, convert it into a map with toMap(), and insert it into the users table. The conflictAlgorithm.replace option is important: if another row already has the same primary key (id), SQLite will replace it instead of throwing an exception. This makes inserts more reliable, especially when syncing or re-saving the same record.
Read: Querying Users
Future<List<User>> getUsers() async {
Future<List<User>> getUsers() async {
final db = await AppDatabase.instance.database;
final result = await db.query('users');
return result.map((map) => User.fromMap(map)).toList();
}Here, query(‘users’) fetches all rows as maps, which we then convert back into User objects using fromMap. This way, you work with typed models instead of raw maps scattered across the codebase.
Update: Modifying Data
Future<void> updateUser(User user) async {
Future<void> updateUser(User user) async {
final db = await AppDatabase.instance.database;
await db.update(
'users',
user.toMap(),
where: 'id = ?',
whereArgs: [user.id],
);
}In this example, the update method looks for a row in the users table with the same id as the object you pass in. The where: ‘id = ?‘ part defines the condition, and the whereArgs: [user.id] safely supplies the actual value.
Using placeholders (?) with whereArgs prevents SQL injection and avoids string concatenation bugs. This guarantees that only the intended record is updated, even if multiple rows exist in the table.
Delete: Removing a Record
Future<void> deleteUser(int id) async {
Future<void> deleteUser(int id) async {
final db = await AppDatabase.instance.database;
await db.delete(
'users',
where: 'id = ?',
whereArgs: [id],
);
}The delete method works the same way. You specify a condition with where and then pass the actual argument separately through whereArgs. This ensures that the database removes only the row that matches the given id.
Without whereArgs, you risk accidental deletion of multiple records or exposing the query to unsafe input. This approach keeps deletions precise and predictable.
Wrapping Up
Working with SQLite gives you flexibility, but it also comes with responsibilities. As a best practice, always use transactions when performing multiple writes so that either all changes succeed or none do.
Stick to whereArgs instead of string interpolation to avoid SQL injection risks. Keep a single instance of the database open with a singleton pattern, and only close it when the app itself is shutting down or when you’re explicitly done with it.