Flutter must know sqflite package for local storage all the interview questions asked

Overview

sqflite is the most popular SQLite plugin for Flutter, providing local database storage on Android and iOS. It's essential for offline data persistence and complex queries.

Installation

yamlCopy
# pubspec.yaml
dependencies:
  sqflite: ^2.3.0
  path: ^1.8.3  # For database path

Common Interview Questions & Answers

Q1: How to create and open a database?

dartCopy
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;

  DatabaseHelper._init();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('my_database.db');
    return _database!;
  }

  Future<Database> _initDB(String fileName) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, fileName);

    return await openDatabase(
      path,
      version: 1,
      onCreate: _createDB,
    );
  }

  Future _createDB(Database db, int version) async {
    await db.execute('''
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT NOT NULL,
        age INTEGER
      )
    ''');
  }
}

Q2: How to insert data?

dartCopy
class User {
  final int? id;
  final String name;
  final String email;
  final int age;

  User({this.id, required this.name, required this.email, required 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'],
    );
  }
}

class UserRepository {
  final DatabaseHelper _dbHelper = DatabaseHelper.instance;

  // Insert
  Future<int> insertUser(User user) async {
    final db = await _dbHelper.database;
    return await db.insert(
      'users',
      user.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // Insert multiple
  Future<void> insertUsers(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(noResult: true);
  }
}

Q3: How to query/read data?

dartCopy
class UserRepository {
  final DatabaseHelper _dbHelper = DatabaseHelper.instance;

  // Get all users
  Future<List<User>> getAllUsers() async {
    final db = await _dbHelper.database;
    final List<Map<String, dynamic>> maps = await db.query('users');

    return List.generate(maps.length, (i) => User.fromMap(maps[i]));
  }

  // Get user by ID
  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.isEmpty) return null;
    return User.fromMap(maps.first);
  }

  // Get users with condition
  Future<List<User>> getUsersByAge(int minAge) async {
    final db = await _dbHelper.database;
    final List<Map<String, dynamic>> maps = await db.query(
      'users',
      where: 'age >= ?',
      whereArgs: [minAge],
      orderBy: 'age DESC',
    );

    return List.generate(maps.length, (i) => User.fromMap(maps[i]));
  }

  // Raw query
  Future<List<User>> searchUsers(String keyword) async {
    final db = await _dbHelper.database;
    final List<Map<String, dynamic>> maps = await db.rawQuery(
      'SELECT * FROM users WHERE name LIKE ? OR email LIKE ?',
      ['%$keyword%', '%$keyword%'],
    );

    return List.generate(maps.length, (i) => User.fromMap(maps[i]));
  }
}

Q4: How to update data?

dartCopy
class UserRepository {
  final DatabaseHelper _dbHelper = DatabaseHelper.instance;

  // Update user
  Future<int> updateUser(User user) async {
    final db = await _dbHelper.database;
    return await db.update(
      'users',
      user.toMap(),
      where: 'id = ?',
      whereArgs: [user.id],
    );
  }

  // Update specific field
  Future<int> updateUserEmail(int id, String newEmail) async {
    final db = await _dbHelper.database;
    return await db.update(
      'users',
      {'email': newEmail},
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // Raw update
  Future<int> incrementAge(int userId) async {
    final db = await _dbHelper.database;
    return await db.rawUpdate(
      'UPDATE users SET age = age + 1 WHERE id = ?',
      [userId],
    );
  }
}

Q5: How to delete data?

dartCopy
class UserRepository {
  final DatabaseHelper _dbHelper = DatabaseHelper.instance;

  // Delete user by ID
  Future<int> deleteUser(int id) async {
    final db = await _dbHelper.database;
    return await db.delete(
      'users',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // Delete all users
  Future<int> deleteAllUsers() async {
    final db = await _dbHelper.database;
    return await db.delete('users');
  }

  // Delete with condition
  Future<int> deleteUsersUnderAge(int age) async {
    final db = await _dbHelper.database;
    return await db.delete(
      'users',
      where: 'age < ?',
      whereArgs: [age],
    );
  }
}

Q6: How to handle database migrations?

dartCopy
class DatabaseHelper {
  Future<Database> _initDB(String fileName) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, fileName);

    return await openDatabase(
      path,
      version: 2, // Increment version
      onCreate: _createDB,
      onUpgrade: _onUpgrade,
    );
  }

  Future _createDB(Database db, int version) async {
    // Initial schema
    await db.execute('''
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT NOT NULL,
        age INTEGER
      )
    ''');
  }

  Future _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      // Migration from v1 to v2: Add phone column
      await db.execute('ALTER TABLE users ADD COLUMN phone TEXT');
    }
  }
}

Q7: How to use transactions?

dartCopy
class UserRepository {
  Future<void> transferPoints(int fromUserId, int toUserId, int points) async {
    final db = await _dbHelper.database;

    await db.transaction((txn) async {
      // Deduct from sender
      await txn.rawUpdate(
        'UPDATE users SET points = points - ? WHERE id = ?',
        [points, fromUserId],
      );

      // Add to receiver
      await txn.rawUpdate(
        'UPDATE users SET points = points + ? WHERE id = ?',
        [points, toUserId],
      );
    });
  }
}

Q8: How to implement batch operations?

dartCopy
Future<void> batchOperations() async {
  final db = await _dbHelper.database;
  Batch batch = db.batch();

  // Insert
  batch.insert('users', {'name': 'Alice', 'email': 'alice@example.com', 'age': 25});

  // Update
  batch.update('users', {'age': 30}, where: 'id = ?', whereArgs: [1]);

  // Delete
  batch.delete('users', where: 'id = ?', whereArgs: [5]);

  // Commit all operations
  await batch.commit(noResult: true);
}

Q9: How to close database?

dartCopy
class DatabaseHelper {
  Future close() async {
    final db = await instance.database;
    db.close();
  }
}

// Call when app closes
void main() {
  runApp(MyApp());

  // Close on app termination
  WidgetsBinding.instance.addObserver(
    LifecycleEventHandler(
      detachedCallBack: () async => DatabaseHelper.instance.close(),
    ),
  );
}

Q10: How to implement full CRUD with UI?

dartCopy
class UsersPage extends StatefulWidget {
  @override
  _UsersPageState createState() => _UsersPageState();
}

class _UsersPageState extends State<UsersPage> {
  final UserRepository _repository = UserRepository();
  List<User> _users = [];

  @override
  void initState() {
    super.initState();
    _loadUsers();
  }

  Future<void> _loadUsers() async {
    final users = await _repository.getAllUsers();
    setState(() => _users = users);
  }

  Future<void> _addUser() async {
    final user = User(name: 'New User', email: 'new@example.com', age: 25);
    await _repository.insertUser(user);
    _loadUsers();
  }

  Future<void> _updateUser(User user) async {
    final updated = User(
      id: user.id,
      name: user.name,
      email: 'updated@example.com',
      age: user.age + 1,
    );
    await _repository.updateUser(updated);
    _loadUsers();
  }

  Future<void> _deleteUser(int id) async {
    await _repository.deleteUser(id);
    _loadUsers();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Users')),
      body: ListView.builder(
        itemCount: _users.length,
        itemBuilder: (context, index) {
          final user = _users[index];
          return ListTile(
            title: Text(user.name),
            subtitle: Text('${user.email} - Age: ${user.age}'),
            trailing: Row(
              mainAxisSize: MainAxisSize.min,
              children: [
                IconButton(
                  icon: Icon(Icons.edit),
                  onPressed: () => _updateUser(user),
                ),
                IconButton(
                  icon: Icon(Icons.delete),
                  onPressed: () => _deleteUser(user.id!),
                ),
              ],
            ),
          );
        },
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: _addUser,
        child: Icon(Icons.add),
      ),
    );
  }
}

Key Concepts Summary

openDatabase(path, version: 1)

db.execute('CREATE TABLE ...')

db.insert('table', data)

db.query('table', where: '...', whereArgs: [])

db.update('table', data, where: '...', whereArgs: [])

db.delete('table', where: '...', whereArgs: [])

db.rawQuery('SELECT * FROM ...')

db.transaction((txn) async { ... })

db.batch()..insert()..commit()

onUpgrade(db, oldVersion, newVersion)

Best Practices

toMap()

fromMap()

Resources

• sqflite Package
• SQLite Documentation
• Flutter Persistence Guide