Flutter Sqflite (SQLite)

Powerful local database for Flutter applications

🗄️ What is Sqflite?

Sqflite is a Flutter plugin for SQLite database operations. It provides a powerful relational database solution for storing structured data locally, supporting complex queries, transactions, and relationships between tables for robust data management. 


// Add to pubspec.yaml
dependencies:
  sqflite: ^2.3.0
  path: ^1.8.3

// Create database
final database = await openDatabase('my_database.db');

Key Features

📊

SQL Queries

Full SQL support for data operations 

await db.query('users');
🔗

Relationships

Create tables with foreign keys 

FOREIGN KEY (userId) 
REFERENCES users(id)
💪

Transactions

Batch operations safely 

await db.transaction((txn) {
  // Multiple operations
});
🔍

Complex Queries

WHERE, JOIN, ORDER BY support 

WHERE age > 18 
ORDER BY name

🔹 Installation

Add required packages to your project: 

# pubspec.yaml
dependencies:
  flutter:
    sdk: flutter
  sqflite: ^2.3.0
  path: ^1.8.3

The path package helps locate the database file

🔹 Creating a Database

Set up your database with tables: 

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

class DatabaseHelper {
  static Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await initDatabase();
    return _database!;
  }

  Future<Database> initDatabase() async {
    String path = join(await getDatabasesPath(), 'app_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,
            age INTEGER
          )
        ''');
      },
    );
  }
}

Result:

✅ Database created with 'users' table

🔹 Insert Data

Add records to your database: 

Future<int> insertUser(Map<String, dynamic> user) async {
  final db = await database;
  
  // Method 1: Using insert
  int id = await db.insert(
    'users',
    user,
    conflictAlgorithm: ConflictAlgorithm.replace,
  );
  
  return id;
}

// Usage
Map<String, dynamic> newUser = {
  'name': 'Alice Smith',
  'email': '[email protected]',
  'age': 28,
};

int userId = await insertUser(newUser);
print('Inserted user with ID: $userId');

Output:

Inserted user with ID: 1

🔹 Query Data

Retrieve data from the database: 

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

// Get user by ID
Future<Map<String, dynamic>?> getUserById(int id) async {
  final db = await database;
  List<Map<String, dynamic>> results = await db.query(
    'users',
    where: 'id = ?',
    whereArgs: [id],
  );
  return results.isNotEmpty ? results.first : null;
}

// Get users with condition
Future<List<Map<String, dynamic>>> getAdultUsers() async {
  final db = await database;
  return await db.query(
    'users',
    where: 'age >= ?',
    whereArgs: [18],
    orderBy: 'name ASC',
  );
}

Result:

[{id: 1, name: Alice Smith, email: [email protected], age: 28}]

🔹 Update Data

Modify existing records: 

Future<int> updateUser(int id, Map<String, dynamic> user) async {
  final db = await database;
  
  return await db.update(
    'users',
    user,
    where: 'id = ?',
    whereArgs: [id],
  );
}

// Usage
await updateUser(1, {
  'name': 'Alice Johnson',
  'age': 29,
});
print('User updated successfully');

Output:

User updated successfully

🔹 Delete Data

Remove records from the database: 

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

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

🔹 Complete Example

A simple user management app: 

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'],
    );
  }
}

// Usage in a widget
class UserListPage extends StatefulWidget {
  @override
  _UserListPageState createState() => _UserListPageState();
}

class _UserListPageState extends State<UserListPage> {
  final dbHelper = DatabaseHelper();
  List<User> users = [];

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

  Future<void> loadUsers() async {
    final data = await dbHelper.getAllUsers();
    setState(() {
      users = data.map((map) => User.fromMap(map)).toList();
    });
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Users')),
      body: ListView.builder(
        itemCount: users.length,
        itemBuilder: (context, index) {
          return ListTile(
            title: Text(users[index].name),
            subtitle: Text(users[index].email),
          );
        },
      ),
    );
  }
}

🔹 Best Practices

  • Singleton Pattern: Use one database instance
  • Use Transactions: For multiple related operations
  • Parameterized Queries: Prevent SQL injection
  • Close Database: When app is terminated
  • Version Control: Handle database migrations properly

🧠 Test Your Knowledge

What does SQL stand for in SQLite?