Working with SQLite Database

1. Using sqflite Package

Property	Description	Example
openDatabase	Opens or creates a SQLite database file	`openDatabase(path, version: 1, onCreate: onCreate)`
query	Executes a SELECT query on the database	`await db.query('users', where: 'id = ?', whereArgs: [1])`
insert	Inserts a new row into the database	`await db.insert('users', {'name': 'John', 'age': 30})`
update	Updates existing rows in the database	`await db.update('users', {'age': 31}, where: 'id = ?', whereArgs: [1])`
delete	Deletes rows from the database	`await db.delete('users', where: 'id = ?', whereArgs: [1])`

Example: Initialize and Open Database

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

Future<Database> initDatabase() async {
  final dbPath = await getDatabasesPath();
  final path = join(dbPath, 'my_database.db');

  return await openDatabase(
    path,
    version: 1,
    onCreate: (db, version) {
      db.execute('''
        CREATE TABLE users (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          age INTEGER
        )
      ''');
    },
  );
}

Example: Insert Data

Future<void> insertUser(Database db, String name, int age) async {
  await db.insert('users', {
    'name': name,
    'age': age,
  });
}

Example: Query Data

Future<List<Map<String, dynamic>>> getUsers(Database db) async {
  return await db.query('users');
}

Note: Ensure you add sqflite and path to your pubspec.yaml dependencies.

2. Opening Database

Property	Description	Example
openDatabase()	Opens or creates a SQLite database file	`openDatabase('my_database.db');`
path	File path to the database file	`'/data/user/0/com.example.myapp/databases/mydb.db'`
version	Database version for migration handling	`1`
onCreate	Callback when database is created	`(Database db, int version) async { ... }`
onUpgrade	Callback when database version is upgraded	`(Database db, int oldVersion, int newVersion) async { ... }`

Example: Opening a Database

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

Future<Database> openDatabase() async {
  final dbPath = await getDatabasesPath();
  final path = join(dbPath, 'my_database.db');
  
  return await openDatabase(
    path,
    version: 1,
    onCreate: (db, version) async {
      await db.execute('''
        CREATE TABLE users (
          id INTEGER PRIMARY KEY,
          name TEXT NOT NULL
        )
      ''');
    },
  );
}

Note: The sqflite package is the most popular choice for SQLite in Flutter.

Warning: Always close the database when done to prevent memory leaks.

3. Creating Database Tables

Example: Creating a Users Table

import &#x27;package:sqflite/sqflite.dart&#x27;;

Future<void> createUsersTable(Database db) async {
  await db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)");
}

Property	Type	Description	Notes
id	`INTEGER PRIMARY KEY`	Unique identifier for each user record	Auto-incremented if not specified
name	`TEXT`	User's full name	Required field
email	`TEXT UNIQUE`	User's email address	Must be unique across records

Note: The UNIQUE constraint ensures no two users can have the same email address.

Example: Creating a Products Table with Foreign Key

Future<void> createProductsTable(Database db) async {
  await db.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, category_id INTEGER, FOREIGN KEY(category_id) REFERENCES categories(id))");
}

Property	Type	Description	Notes
id	`INTEGER PRIMARY KEY`	Unique identifier for each product	Auto-incremented if not specified
name	`TEXT`	Name of the product	Required field
price	`REAL`	Price of the product in USD	Decimal value
category_id	`INTEGER REFERENCES categories(id)`	Foreign key to the categories table	Ensures referential integrity

4. Inserting Data

Inserting data into a SQLite database in Flutter is typically done using the insert method provided by the sqflite package. This operation adds a new row to a specified table.

Parameter	Type	Description
table	`String`	Name of the table where data will be inserted.
values	`Map<String, dynamic>`	Map containing column names as keys and their corresponding values.
conflictAlgorithm	`ConflictAlgorithm`	Specifies how to handle conflicts (e.g., `ConflictAlgorithm.replace`).

Example: Inserting a new record

import 'package:sqflite/sqflite.dart';

Future<void> insertUser(int id, String name, String email) async {
  final db = await openDatabase(
    'my_database.db',
    version: 1,
  );

  await db.insert(
    'users',
    {
      'id': id,
      'name': name,
      'email': email,
    },
  );
}

Note: The insert method returns the row ID of the newly inserted record. If you need to use this ID later, store the result in a variable.

Example: Inserting with conflict resolution

await db.insert(
  'users',
  {
    'id': 1,
    'name': 'John Doe',
    'email': 'john@example.com',
  },
  conflictAlgorithm: ConflictAlgorithm.replace,
);

5. Querying Data

Example: Basic SELECT Query

import &#x27;package:sqflite/sqflite.dart&#x27;;

Future<void> queryData() async {
  var db = await openDatabase(&#x27;my_database.db&#x27;);
  var results = await db.query(&#x27;users&#x27;);
  for (var row in results) {
    print(&#x27;ID: ${row[&#x27;id&#x27;]}, Name: ${row[&#x27;name&#x27;]}&#x27;);
  }
}

Method	Description	Parameters	Return Type
query	Executes a SELECT query on the database	`tableName`, `columns`, `where`, `whereArgs`	`Future<List<Map<String, dynamic>>>`
rawQuery	Executes a raw SQL SELECT query	`sql`, `whereArgs`	`Future<List<Map<String, dynamic>>>`

Example: Query with WHERE Clause

Future<void> queryWithCondition() async {
  var db = await openDatabase(&#x27;my_database.db&#x27;);
  var results = await db.query(
    &#x27;users&#x27;,
    where: &#x27;age > ?&#x27;,
    whereArgs: [18],
  );
  print(&#x27;Adult users: ${results.length}&#x27;);
}

Example: Query with ORDER BY and LIMIT

Future<void> queryOrdered() async {
  var db = await openDatabase(&#x27;my_database.db&#x27;);
  var results = await db.query(
    &#x27;users&#x27;,
    orderBy: &#x27;name ASC&#x27;,
    limit: 5,
  );
  for (var row in results) {
    print(&#x27;Name: ${row[&#x27;name&#x27;]}&#x27;);
  }
}

6. Updating Data

Updating data in SQLite with Flutter involves using the update method provided by the sqflite package. This operation modifies existing records in a database table based on specified conditions.

Method	Description	Parameters	Return Type
update	Updates records in a table based on a WHERE clause	`table`, `values`, `where`, `whereArgs`	`Future<int>`

Example: Updating a User's Email

import 'package:sqflite/sqflite.dart';

// Assume a table named 'users' with columns: id, name, email
Future<int> updateUserEmail(int userId, String newEmail) async {
  final db = await openDatabase(path);
  return await db.update(
    'users',
    {'email': newEmail},
    where: 'id = ?',
    whereArgs: [userId],
  );
}

Note: The update method returns the number of affected rows, which can be used to verify if the update was successful.

Example: Updating Multiple Fields

Future<int> updateUserInfo(int userId, String newName, String newEmail) async {
  final db = await openDatabase(path);
  return await db.update(
    'users',
    {
      'name': newName,
      'email': newEmail,
    },
    where: 'id = ?',
    whereArgs: [userId],
  );
}

Warning: Always use where and whereArgs to avoid updating all records in the table.

Key Points for Updating Data

The update method modifies existing records in a table.
Use where and whereArgs to target specific records.
The method returns the number of affected rows.
Updating without a WHERE clause affects all records in the table.

7. Deleting Data

Deleting data in SQLite with Flutter is performed using the delete method on a Database object. This operation removes rows that match a specified condition.

Method	Description	Parameters	Return Type
delete	Deletes rows from a table based on a WHERE condition.	`table` (string), `where` (string), `whereArgs` (list)	`int` (number of rows deleted)

Example: Delete a Single Row

import 'package:sqflite/sqflite.dart';

Future<int> deleteSingleUser(int userId) async {
  final db = await openDatabase('my_database.db');
  final deletedRows = await db.delete(
    'users',
    where: 'id = ?',
    whereArgs: [userId],
  );
  return deletedRows;
}

Example: Delete All Rows

import 'package:sqflite/sqflite.dart';

Future<int> deleteAllUsers() async {
  final db = await openDatabase('my_database.db');
  final deletedRows = await db.delete('users');
  return deletedRows;
}

Note: If no where clause is provided, all rows in the table will be deleted.

Warning: Deleting data is irreversible. Always ensure you have backups or a rollback mechanism in place.

Key Points

The delete method returns the number of affected rows.
Use where and whereArgs to safely delete specific records.
Be cautious when deleting all rows without a condition.

8. Using Transactions

Example: Basic Transaction

import &#x27;package:sqflite/sqflite.dart&#x27;;

Future<void> useTransaction() async {
  var db = await openDatabase(&#x27;my_database.db&#x27;);

  await db.transaction((txn) async {
    await txn.insert(&#x27;users&#x27;, {
      &#x27;name&#x27;: &#x27;John Doe&#x27;,
      &#x27;email&#x27;: &#x27;john@example.com&#x27;,
    });
    
    await txn.update(&#x27;users&#x27;, {
      &#x27;name&#x27;: &#x27;Jane Doe&#x27;,
    }, where: &#x27;name = ?&#x27;, whereArgs: [&#x27;John Doe&#x27;]);
  });
}

Method	Description	Notes
db.transaction()	Executes a series of operations as a single atomic unit	Rolls back if any operation fails

Note: All operations within the transaction must be completed before the transaction is committed or rolled back.

Warning: Avoid long-running transactions as they can block other database operations.

9. Using Raw SQL Queries

When working with SQLite in Flutter, you can execute raw SQL queries using the rawQuery and rawUpdate methods provided by the database object. These methods allow you to run SQL statements directly without using the ORM-style APIs.

Method	Description	Return Type
rawQuery	Executes a SELECT query and returns a list of maps.	List<Map<String, dynamic>>
rawUpdate	Executes an UPDATE, INSERT, or DELETE query and returns the number of affected rows.	int

Example: Executing a Raw SQL Query

import &#x27;package:sqflite/sqflite.dart&#x27;;

Future<List<Map<String, dynamic>>> fetchUsers() async {
  final db = await openDatabase(&#x27;my_database.db&#x27;);
  final result = await db.rawQuery(&#x27;SELECT * FROM users&#x27;);
  return result;
}

Example: Executing a Raw SQL Update

import &#x27;package:sqflite/sqflite.dart&#x27;;

Future<int> updateUserAge(int userId, int newAge) async {
  final db = await openDatabase(&#x27;my_database.db&#x27;);
  final result = await db.rawUpdate(
    &#x27;UPDATE users SET age = ? WHERE id = ?&#x27;,
    [newAge, userId]
  );
  return result;
}

Note: When using raw SQL queries, ensure that you properly escape or parameterize inputs to prevent SQL injection vulnerabilities. The rawQuery and rawUpdate methods support parameterized queries using placeholders like ?.

Warning: Be cautious when using raw SQL queries as they bypass the ORM layer and can lead to inconsistencies or errors if not handled carefully. Always validate your queries before executing them.

10. Managing Database Migrations

Migration Type	Description	Implementation	Notes
Version-Based	Database schema changes tracked by version numbers	`sqflite` with `onUpgrade` callback	Recommended for production apps
Schema Hash	Migration based on schema hash comparison	Custom logic to compute and compare schema hashes	More robust than version tracking
Manual	Developer-controlled migration steps	Direct SQL statements in `onUpgrade`	Full control but more error-prone

Example: Version-Based Migration

import 'package:sqflite/sqflite.dart';

Future<void> migrateDatabase(Database db, int oldVersion, int newVersion) async {
  if (oldVersion < 2) {
    await db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  }
  if (oldVersion < 3) {
    await db.execute("ALTER TABLE users ADD COLUMN email TEXT");
  }
}

Note: Always backup user data before performing migrations in production.

Warning: Migrations must be backward compatible to avoid breaking older app versions.

Migration Best Practices

Always test migrations on a copy of production data
Use version numbers to track schema changes
Implement onDowngrade for rollback scenarios
Avoid destructive operations in migrations

11. Implementing Repository Pattern with SQLite

Example: Basic Repository Implementation

// Define a model
class User {
  final int id;
  final String name;
  final String email;

  User({required this.id, required this.name, required this.email});
}

// Define a repository interface
abstract class UserRepository {
  Future<List<User>> getAllUsers();
  Future<User?> getUserById(int id);
  Future<void> insertUser(User user);
  Future<void> updateUser(User user);
  Future<void> deleteUser(int id);
}

// Implement the repository with SQLite
class UserRepositoryImpl implements UserRepository {
  late Database _db;

  @override
  Future<void> init() async {
    final dbPath = await getDatabasesPath();
    _db = await openDatabase(
      '$dbPath/users.db',
      version: 1,
      onCreate: (db, version) {
        db.execute('''
          CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT NOT NULL
          )
        ''');
      },
    );
  }

  @override
  Future<List<User>> getAllUsers() async {
    final List<Map<String, dynamic>> maps = await _db.query('users');
    return List.generate(maps.length, (i) {
      return User(
        id: maps[i]['id'],
        name: maps[i]['name'],
        email: maps[i]['email'],
      );
    });
  }

  @override
  Future<User?> getUserById(int id) async {
    final List<Map<String, dynamic>> maps = await _db.query(
      'users',
      where: 'id = ?',
      whereArgs: [id],
    );
    if (maps.isNotEmpty) {
      final map = maps.first;
      return User(
        id: map['id'],
        name: map['name'],
        email: map['email'],
      );
    }
    return null;
  }

  @override
  Future<void> insertUser(User user) async {
    await _db.insert('users', {
      'id': user.id,
      'name': user.name,
      'email': user.email,
    });
  }

  @override
  Future<void> updateUser(User user) async {
    await _db.update(
      'users',
      {
        'name': user.name,
        'email': user.email,
      },
      where: 'id = ?',
      whereArgs: [user.id],
    );
  }

  @override
  Future<void> deleteUser(int id) async {
    await _db.delete('users', where: 'id = ?', whereArgs: [id]);
  }
}

Method	Description	Notes
getAllUsers()	Fetches all users from the database	Returns a list of User objects
getUserById(int id)	Fetches a user by ID	Returns a User object or null if not found
insertUser(User user)	Inserts a new user into the database	Uses the provided User object's properties
updateUser(User user)	Updates an existing user in the database	Uses the provided User object's properties
deleteUser(int id)	Deletes a user by ID	Removes the user with the specified ID

Note: The init() method must be called before any database operations to ensure the database is opened.

Warning: Ensure that all database operations are performed asynchronously to avoid blocking the UI thread.

Key Concepts

The repository pattern abstracts database operations, making the code more maintainable and testable.
SQLite is used for local data persistence in Flutter applications.
Database initialization must be handled properly to ensure the database is ready for use.