Module 3
Topic 3
SQLite
A self-contained, high-reliability, embedded SQL database engine — perfect for complex data and relationships.
What Is SQLite?
SQLite is a self-contained, high-reliability, embedded, SQL database engine. It's the most widely deployed database in the world and is perfect for Flutter apps that need:
- Complex data structures with multiple tables and relationships
- Efficient queries with filtering, sorting, and joining
- Large datasets that need to be queried quickly
- Data integrity with constraints and transactions
✅ When to Use SQLite
- Complex data with multiple related tables
- Need for complex queries (JOIN, GROUP BY, ORDER BY)
- Large datasets that need efficient searching
- Data integrity with foreign keys and constraints
- When you need transactions for data consistency
SQLite vs Hive
Hive
- Key-value NoSQL database
- Simple API, like a Map
- No SQL required
- Great for objects and simple data
- Real-time updates with listenable
- Faster for simple operations
SQLite
- Relational SQL database
- Complex queries with SQL
- Multiple tables with relationships
- Great for complex data and reports
- Transactions and data integrity
- Better for large datasets
SharedPreferences
- Simple key-value storage
- Primitive types only
- Small data only
- Best for app settings
💡 Which One to Choose?
- Use SQLite – for complex data, relationships, and complex queries
- Use Hive – for simple object storage with real-time updates
- Use SharedPreferences – for simple app settings and preferences
Setting Up SQLite
To use SQLite in Flutter, you need to add the
sqflite
and
path
packages.
pubspec.yaml – Add Dependencies
dependencies:
sqflite: ^2.4.3
path: ^1.9.0
lib/database/database_helper.dart – Database Helper
import
'dart:async'
;
import
'package:sqflite/sqflite.dart'
;
import
'package:path/path.dart'
;
class
DatabaseHelper
{
static
final
DatabaseHelper _instance = DatabaseHelper._internal();
factory
DatabaseHelper() => _instance;
DatabaseHelper._internal();
static
Database? _database;
Future
<Database>
get
database async {
if
(_database !=
null
)
return
_database!;
_database =
await
_initDatabase();
return
_database!;
}
Future
<Database>
_initDatabase
() async {
final
databasesPath = await getDatabasesPath();
final
path = join(databasesPath,
'app_database.db'
);
return
await openDatabase(
path,
version:
1
,
onCreate: _onCreate,
onUpgrade: _onUpgrade,
);
}
Future
<
void
>
_onCreate
(Database db,
int
version) async {
// Create tables here
await
db.execute(
'''
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER
)
'''
);
await
db.execute(
'''
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title TEXT NOT NULL,
content TEXT,
created_at TEXT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
)
'''
);
// Insert sample data
await
_insertSampleData(db);
}
Future
<
void
>
_onUpgrade
(Database db,
int
oldVersion,
int
newVersion) async {
// Handle database migrations
if
(oldVersion <
2
) {
await
db.execute(
'ALTER TABLE users ADD COLUMN phone TEXT'
);
}
}
Future
<
void
>
_insertSampleData
(Database db) async {
// Insert sample users
await
db.insert(
'users'
, {
'name'
:
'Alice Johnson'
,
'email'
:
'alice@example.com'
,
'age'
:
28
,
});
await
db.insert(
'users'
, {
'name'
:
'Bob Smith'
,
'email'
:
'bob@example.com'
,
'age'
:
34
,
});
}
}
CRUD Operations
Here are the basic CRUD (Create, Read, Update, Delete) operations for SQLite in Flutter.
lib/models/user.dart – User Model
class
User
{
final
int
? id;
final
String name;
final
String email;
final
int
? age;
User
({
this
.id,
required
this
.name,
required
this
.email,
this
.age});
// Convert to Map for database
Map<String, dynamic>
toMap
() {
return
{
'id'
: id,
'name'
: name,
'email'
: email,
'age'
: age,
};
}
// Create from Map
factory User.fromMap(Map<String, dynamic> map) {
return
User(
id: map[
'id'
],
name: map[
'name'
],
email: map[
'email'
],
age: map[
'age'
],
);
}
@override
String
toString
() {
return
'User{id: $id, name: $name, email: $email, age: $age}'
;
}
}
lib/database/user_dao.dart – DAO (Data Access Object)
import
'package:sqflite/sqflite.dart'
;
import
'../models/user.dart'
;
import
'database_helper.dart'
;
class
UserDao
{
final
DatabaseHelper _dbHelper = DatabaseHelper();
// ----- CREATE -----
Future
<
int
>
insertUser
(User user) async {
final
db = await _dbHelper.database;
return
await db.insert(
'users'
, user.toMap());
}
// ----- READ (All) -----
Future
<List<User>>
getAllUsers
() async {
final
db = await _dbHelper.database;
final
List<Map<String, dynamic>> maps = await db.query(
'users'
);
return
maps.map((map) => User.fromMap(map)).toList();
}
// ----- READ (Single) -----
Future
<User?>
getUserById
(
int
id) async {
final
db = await _dbHelper.database;
final
List<Map<String, dynamic>> maps =
await
db.query(
'users'
,
where:
'id = ?'
,
whereArgs: [id],
);
if
(maps.isNotEmpty) {
return
User.fromMap(maps.first);
}
return
null
;
}
// ----- READ (Filtered) -----
Future
<List<User>>
getUsersByName
(String name) async {
final
db = await _dbHelper.database;
final
List<Map<String, dynamic>> maps =
await
db.query(
'users'
,
where:
'name LIKE ?'
,
whereArgs: [
'%$name%'
],
);
return
maps.map((map) => User.fromMap(map)).toList();
}
// ----- UPDATE -----
Future
<
int
>
updateUser
(User user) async {
final
db = await _dbHelper.database;
return
await db.update(
'users'
,
user.toMap(),
where:
'id = ?'
,
whereArgs: [user.id],
);
}
// ----- DELETE -----
Future
<
int
>
deleteUser
(
int
id) async {
final
db = await _dbHelper.database;
return
await db.delete(
'users'
,
where:
'id = ?'
,
whereArgs: [id],
);
}
// ----- BATCH INSERT -----
Future
<
void
>
insertMultipleUsers
(List<User> users) async {
final
db = await _dbHelper.database;
Batch batch = db.batch();
for
(
var
user
in
users) {
batch.insert(
'users'
, user.toMap());
}
await
batch.commit();
}
}
Complex Queries
SQLite supports complex queries with JOINs, GROUP BY, ORDER BY, and more.
lib/database/query_dao.dart – Complex Queries
import
'package:sqflite/sqflite.dart'
;
import
'../models/user.dart'
;
import
'database_helper.dart'
;
class
QueryDao
{
final
DatabaseHelper _dbHelper = DatabaseHelper();
// ----- JOIN Query (Users with their posts) -----
Future
<List<Map<String, dynamic>>>
getUsersWithPosts
() async {
final
db = await _dbHelper.database;
return
await
db.rawQuery(
'''
SELECT
users.id as user_id,
users.name,
users.email,
posts.id as post_id,
posts.title,
posts.content,
posts.created_at
FROM users
LEFT JOIN posts ON users.id = posts.user_id
ORDER BY users.name, posts.created_at DESC
'''
);
}
// ----- Aggregate Query (Count posts per user) -----
Future
<List<Map<String, dynamic>>>
getPostCountPerUser
() async {
final
db = await _dbHelper.database;
return
await
db.rawQuery(
'''
SELECT
users.id,
users.name,
COUNT(posts.id) as post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id
ORDER BY post_count DESC
'''
);
}
// ----- Pagination -----
Future
<List<User>>
getUsersPaginated
(
int
page,
int
limit) async {
final
db = await _dbHelper.database;
final
offset = (page -
1
) * limit;
final
List<Map<String, dynamic>> maps =
await
db.query(
'users'
,
limit: limit,
offset: offset,
orderBy:
'name ASC'
,
);
return
maps.map((map) => User.fromMap(map)).toList();
}
// ----- Search with multiple conditions -----
Future
<List<User>>
searchUsers
({
String? name,
String? email,
int
? minAge,
int
? maxAge,
}) async {
final
db = await _dbHelper.database;
List<String> conditions = [];
List<dynamic> args = [];
if
(name !=
null
&& name.isNotEmpty) {
conditions.add(
'name LIKE ?'
);
args.add(
'%$name%'
);
}
if
(email !=
null
&& email.isNotEmpty) {
conditions.add(
'email LIKE ?'
);
args.add(
'%$email%'
);
}
if
(minAge !=
null
) {
conditions.add(
'age >= ?'
);
args.add(minAge);
}
if
(maxAge !=
null
) {
conditions.add(
'age <= ?'
);
args.add(maxAge);
}
final
whereClause = conditions.isNotEmpty ? conditions.join(
' AND '
) :
null
;
final
List<Map<String, dynamic>> maps =
await
db.query(
'users'
,
where: whereClause,
whereArgs: args.isNotEmpty ? args :
null
,
);
return
maps.map((map) => User.fromMap(map)).toList();
}
}
Transactions
Transactions ensure data consistency when performing multiple operations. If any operation fails, all changes are rolled back.
lib/database/transaction_dao.dart – Transactions
import
'package:sqflite/sqflite.dart'
;
import
'../models/user.dart'
;
import
'database_helper.dart'
;
class
TransactionDao
{
final
DatabaseHelper _dbHelper = DatabaseHelper();
// ----- Transaction: Delete user and all their posts -----
Future
<
void
>
deleteUserWithPosts
(
int
userId) async {
final
db = await _dbHelper.database;
await
db.transaction((txn) async {
// Delete posts first (due to foreign key)
await
txn.delete(
'posts'
,
where:
'user_id = ?'
,
whereArgs: [userId],
);
// Then delete the user
await
txn.delete(
'users'
,
where:
'id = ?'
,
whereArgs: [userId],
);
});
}
// ----- Transaction: Transfer user data to another user -----
Future
<
void
>
transferUserData
(
int
fromUserId,
int
toUserId) async {
final
db = await _dbHelper.database;
await
db.transaction((txn) async {
// Update posts to new user
await
txn.update(
'posts'
,
{
'user_id'
: toUserId},
where:
'user_id = ?'
,
whereArgs: [fromUserId],
);
// Delete the old user
await
txn.delete(
'users'
,
where:
'id = ?'
,
whereArgs: [fromUserId],
);
});
}
// ----- Transaction with rollback on error -----
Future
<
bool
>
updateUserWithRollback
(User user) async {
final
db = await _dbHelper.database;
try
{
await
db.transaction((txn) async {
// Update user
await
txn.update(
'users'
,
user.toMap(),
where:
'id = ?'
,
whereArgs: [user.id],
);
// If validation fails, throw an error to rollback
if
(user.name.isEmpty) {
throw
Exception(
'Name cannot be empty'
);
}
});
return
true
;
}
catch
(e) {
return
false
;
}
}
}
Complete Example: User Management App
Here's a complete Flutter app that uses SQLite for user management with CRUD operations.
lib/main.dart – User Management App
import
'package:flutter/material.dart'
;
import
'package:sqflite/sqflite.dart'
;
import
'package:path/path.dart'
;
// ==========================================
// 1. MODEL
// ==========================================
class
User
{
final
int
? id;
final
String name;
final
String email;
final
int
? age;
User
({
this
.id,
required
this
.name,
required
this
.email,
this
.age});
Map<String, dynamic>
toMap
() {
return
{
'id'
: id,
'name'
: name,
'email'
: email,
'age'
: age,
};
}
factory User.fromMap(Map<String, dynamic> map) {
return
User(
id: map[
'id'
],
name: map[
'name'
],
email: map[
'email'
],
age: map[
'age'
],
);
}
}
// ==========================================
// 2. DATABASE HELPER
// ==========================================
class
DatabaseHelper
{
static
final
DatabaseHelper _instance = DatabaseHelper._internal();
factory
DatabaseHelper() => _instance;
DatabaseHelper._internal();
static
Database? _database;
Future
<Database>
get
database async {
if
(_database !=
null
)
return
_database!;
_database =
await
_initDatabase();
return
_database!;
}
Future
<Database>
_initDatabase
() async {
final
databasesPath = await getDatabasesPath();
final
path = join(databasesPath,
'user_database.db'
);
return
await 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 UNIQUE NOT NULL,
age INTEGER
)
'''
);
},
);
}
}
// ==========================================
// 3. USER DAO
// ==========================================
class
UserDao
{
final
DatabaseHelper _dbHelper = DatabaseHelper();
Future
<
int
>
insertUser
(User user) async {
final
db = await _dbHelper.database;
return
await db.insert(
'users'
, user.toMap());
}
Future
<List<User>>
getAllUsers
() async {
final
db = await _dbHelper.database;
final
List<Map<String, dynamic>> maps =
await
db.query(
'users'
);
return
maps.map((map) => User.fromMap(map)).toList();
}
Future
<
int
>
updateUser
(User user) async {
final
db = await _dbHelper.database;
return
await db.update(
'users'
,
user.toMap(),
where:
'id = ?'
,
whereArgs: [user.id],
);
}
Future
<
int
>
deleteUser
(
int
id) async {
final
db = await _dbHelper.database;
return
await db.delete(
'users'
,
where:
'id = ?'
,
whereArgs: [id],
);
}
}
// ==========================================
// 4. FLUTTER UI
// ==========================================
void
main
() => runApp(
MyApp
());
class
MyApp
extends
StatelessWidget {
@override
Widget
build
(BuildContext context) {
return
MaterialApp(
title:
'User Management'
,
theme: ThemeData(
colorScheme: ColorScheme.fromSeed(seedColor: Colors.deepPurple),
useMaterial3:
true
,
),
home: UserListScreen(),
);
}
}
class
UserListScreen
extends
StatefulWidget {
@override
State<UserListScreen>
createState
() => _UserListScreenState();
}
class
_UserListScreenState
extends
State<UserListScreen> {
final
UserDao _userDao = UserDao();
List<User> _users = [];
bool
_isLoading =
true
;
final
TextEditingController _nameController = TextEditingController();
final
TextEditingController _emailController = TextEditingController();
final
TextEditingController _ageController = TextEditingController();
@override
void
initState
() {
super
.initState();
_loadUsers();
}
Future
<
void
>
_loadUsers
() async {
setState(() => _isLoading =
true
);
final
users = await _userDao.getAllUsers();
setState(() {
_users = users;
_isLoading =
false
;
});
}
Future
<
void
>
_addUser
() async {
final
name = _nameController.text.trim();
final
email = _emailController.text.trim();
final
age = int.tryParse(_ageController.text.trim());
if
(name.isEmpty || email.isEmpty)
return
;
final
user = User(name: name, email: email, age: age);
await
_userDao.insertUser(user);
_nameController.clear();
_emailController.clear();
_ageController.clear();
await
_loadUsers();
}
Future
<
void
>
_deleteUser
(
int
id) async {
await
_userDao.deleteUser(id);
await
_loadUsers();
}
void
_showAddDialog
() {
showDialog(
context: context,
builder: (context) => AlertDialog(
title: Text(
'Add User'
),
content: Column(
mainAxisSize: MainAxisSize.min,
children: [
TextField(
controller: _nameController,
decoration: InputDecoration(
hintText:
'Name'
,
border: OutlineInputBorder(),
),
),
SizedBox(height:
8
),
TextField(
controller: _emailController,
decoration: InputDecoration(
hintText:
'Email'
,
border: OutlineInputBorder(),
),
keyboardType: TextInputType.emailAddress,
),
SizedBox(height:
8
),
TextField(
controller: _ageController,
decoration: InputDecoration(
hintText:
'Age (optional)'
,
border: OutlineInputBorder(),
),
keyboardType: TextInputType.number,
),
],
),
actions: [
TextButton(
onPressed: () => Navigator.pop(context),
child: Text(
'Cancel'
),
),
ElevatedButton(
onPressed: () {
_addUser();
Navigator.pop(context);
},
child: Text(
'Add'
),
),
],
),
);
}
@override
Widget
build
(BuildContext context) {
return
Scaffold(
appBar: AppBar(
title: Text(
'👥 Users'
),
centerTitle:
true
,
actions: [
IconButton(
icon: Icon(Icons.refresh),
onPressed: _loadUsers,
),
],
),
body: _isLoading
? Center(child: CircularProgressIndicator())
: _users.isEmpty
? Center(
child: Column(
mainAxisAlignment: MainAxisAlignment.center,
children: [
Icon(Icons.people_outline, size:
80
, color: Colors.grey.shade400),
SizedBox(height:
16
),
Text(
'No users yet'
,
style: TextStyle(fontSize:
20
, fontWeight: FontWeight.w600),
),
Text(
'Tap the + button to add one'
,
style: TextStyle(color: Colors.grey.shade400),
),
],
),
)
: ListView.builder(
itemCount: _users.length,
itemBuilder: (context, index) {
final
user = _users[index];
return
Card(
margin: EdgeInsets.symmetric(horizontal:
16
, vertical:
4
),
child: ListTile(
leading: CircleAvatar(
child: Text(user.name[
0
].toUpperCase()),
),
title: Text(user.name),
subtitle: Column(
crossAxisAlignment: CrossAxisAlignment.start,
children: [
Text(user.email),
if
(user.age !=
null
)
Text(
'Age: ${user.age}'
,
style: TextStyle(color: Colors.grey.shade600),
),
],
),
trailing: IconButton(
icon: Icon(Icons.delete, color: Colors.red),
onPressed: () => _deleteUser(user.id!),
),
),
);
},
),
floatingActionButton: FloatingActionButton(
onPressed: _showAddDialog,
child: Icon(Icons.add),
),
);
}
}
Step-by-Step Explanation
1.
Add dependencies
– Add
sqflite
and
path
to your
pubspec.yaml
.
2.
Create Database Helper
– A singleton class that manages database initialization and migration.
3.
Define the Model
– Create a Dart class with
toMap()
and
fromMap()
methods for serialization.
4.
Create DAO
– Data Access Object with CRUD methods using SQL queries.
5.
Build the UI
– Use
FutureBuilder
or
setState
to display data from the database.
Best Practices
✅ Do's
- Use a singleton database helper to avoid opening multiple connections
- Use transactions for multiple operations
- Use parameterized queries to prevent SQL injection
- Use migrations for database schema changes
- Close the database when the app is terminated
- Use indexes for frequently queried columns
❌ Don'ts
- Don't use raw SQL with string concatenation (use parameterized queries)
- Don't forget to handle exceptions in database operations
- Don't use SQLite for very simple data (use SharedPreferences)
- Don't open the database multiple times
Common Mistakes
❌ Mistake 1: Forgetting to close the database
Not closing the database can cause memory leaks and connection issues.
✅ Correct: Close database when done
await db.close();
when the app is terminated or the database is no longer needed.
❌ Mistake 2: Not using transactions for multiple operations
If you insert multiple related records without a transaction, partial failures can corrupt your data.
✅ Correct: Use transactions
Wrap multiple operations in
db.transaction()
to ensure atomicity.
❌ Mistake 3: Not handling database upgrades
When you change the schema, users with existing databases need a migration path.
✅ Correct: Use onUpgrade
Use the
onUpgrade
callback in
openDatabase()
to handle schema migrations.
🎯 Key Takeaway
SQLite is a powerful relational database for Flutter apps that need complex data structures , relationships , and efficient queries . Use transactions for data consistency, parameterized queries for security, and migrations for schema updates. SQLite is the best choice for complex, relational data.