SQLite

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

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.

SQLite

✅ When to Use SQLite

Hive

SQLite

SharedPreferences

💡 Which One to Choose?

✅ Do's

❌ Don'ts

🎯 Key Takeaway