SQFLite PLugin on Flutter is a good choice for developers when it comes to local data storage. This plugin provides a simple way to incorporate a lightweight, local relational database in your Flutter applications. It is compatible with several operating systems, including Windows, Linux, MacOS, iOS, and Android. For example, if you're building a to-do list app, a budget tracker, or a sophisticated note-taking application, SQFLite can efficiently handle your data storage needs.
Let's explore SQFLite in this blog, learn about its foundations, and go through a simple example of how it's used in Flutter.
Main Features of SQFlite in Flutter:
Cross-Platform Support:
SQFlite works seamlessly across different platforms supported by Flutter, such as iOS and Android. This enables developers to create consistent database functionality regardless of the device used.
Simplicity and Ease of Use:
The plugin's API is designed to be straightforward to use. It abstracts away the complexities of working directly with SQLite, making database operations more manageable and less error-prone.
Asynchronous Operations:
SQFlite allows performing database operations asynchronously, preventing UI freezes and ensuring a smoother user experience, especially when dealing with potentially time-consuming tasks like database queries.
Support for Transactions:
Transactions in SQFlite ensure the atomicity of database operations, allowing for safer and more reliable interactions with the database. This feature helps maintain data integrity, especially when multiple operations need to be executed together.
Getting Started with SQFlite in Flutter :
First, add the sqflite package to your pubspec.yaml file to use SQFlite in your Flutter project.
dependencies:
...
sqflite:
flutter pub get
Make sure to run ‘ flutter pub get ’ to fetch the package after adding the dependency.
Basic Operations with SQFlite:
* Import sqflite.dart
import 'package:sqflite/sqflite.dart';
* Accessing a Database:
A SQLite database is a file located in the device's file system, distinguished by its pathway. When the path is described as relative, it means it's connected to the location retrieved through getDatabasesPath(). On Android, this location represents the default database directory, while on iOS/MacOS, it corresponds to the documents directory.
var db = await openDatabase('my_db.db');There is a basic migration mechanism to manage changes to the database structure when you open it.
Lots of apps operate with just one database that stays open until the app is shut down. If there's a need to free up resources, you have the option to close the database manually.
await db.close();
* SQLite Supported Types and Dart Equivalents
- INTEGER: Corresponds to Dart type int. Supports a wide range of integer values.
- REAL: Corresponds to Dart type num. They are typically used for floating-point numbers.
- TEXT: Corresponds to Dart type String. Used for text values.
- BLOB: Corresponds to Dart type Uint8List. Useful for storing binary data, like images or files.
- DateTime: SQLite doesn't have a specific DateTime type. Storing DateTime values can be handled by converting them to int (e.g., DateTime's millisecondsSinceEpoch) or String (e.g., ISO8601 format) before storage in SQLite.
- bool: SQLite does not have a boolean type. You can represent boolean values using INTEGER columns and store 0 or 1.
For example, if you want to store binary data (like an image) in a SQLite database, you might use a column of type BLOB:
Uint8List imageBytes = ... // Your binary image data
db.rawInsert('INSERT INTO images (image_data) VALUES (?)', [imageBytes]);
* Raw SQL queries
Sample code to use Raw SQL queries.
// Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');
// Delete the database
await deleteDatabase(path);
// open the database
Database database = await openDatabase(path, version: 1,
onCreate: (Database db, int version) async {
// When creating the db, create the table
await db.execute(
'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
});
// Insert some records in a transaction
await database.transaction((txn) async {
int id1 = await txn.rawInsert(
'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
print('inserted1: $id1');
int id2 = await txn.rawInsert(
'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
['another name', 12345678, 3.1416]);
print('inserted2: $id2');
});
// Update some record
int count = await database.rawUpdate(
'UPDATE Test SET name = ?, value = ? WHERE name = ?',
['updated name', '9876', 'some name']);
print('updated: $count');
// Get the records
List<Map> list = await database.rawQuery('SELECT * FROM Test');
List<Map> expectedList = [
{'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.789},
{'name': 'another name', 'id': 2, 'value': 12345678, 'num': 3.1416}
];
print(list);
print(expectedList);
assert(const DeepCollectionEquality().equals(list, expectedList));
// Count the records
count = Sqflite
.firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
assert(count == 2);
// Delete a record
count = await database
.rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
assert(count == 1);
// Close the database
await database.close();
* SQL helpers
Sample code to use helpers
final String tableTodo = 'todo';
final String columnId = '_id';
final String columnTitle = 'title';
final String columnDone = 'done';
class Todo {
int id;
String title;
bool done;
Map<String, Object?> toMap() {
var map = <String, Object?>{
columnTitle: title,
columnDone: done == true ? 1 : 0
};
if (id != null) {
map[columnId] = id;
}
return map;
}
Todo();
Todo.fromMap(Map<String, Object?> map) {
id = map[columnId];
title = map[columnTitle];
done = map[columnDone] == 1;
}
}
class TodoProvider {
Database db;
Future open(String path) async {
db = await openDatabase(path, version: 1,
onCreate: (Database db, int version) async {
await db.execute('''
create table $tableTodo (
$columnId integer primary key autoincrement,
$columnTitle text not null,
$columnDone integer not null)
''');
});
}
Future<Todo> insert(Todo todo) async {
todo.id = await db.insert(tableTodo, todo.toMap());
return todo;
}
Future<Todo> getTodo(int id) async {
List<Map> maps = await db.query(tableTodo,
columns: [columnId, columnDone, columnTitle],
where: '$columnId = ?',
whereArgs: [id]);
if (maps.length > 0) {
return Todo.fromMap(maps.first);
}
return null;
}
Future<int> delete(int id) async {
return await db.delete(tableTodo, where: '$columnId = ?', whereArgs: [id]);
}
Future<int> update(Todo todo) async {
return await db.update(tableTodo, todo.toMap(),
where: '$columnId = ?', whereArgs: [todo.id]);
}
Future close() async => db.close();
}
This example illustrates a situation to manage a "Todo" list inside of a SQLite database.
Schema Definition:
The code initializes the schema for a 'todo' table within the SQLite database. This table contains columns for Todo's ID, title, and completion status.
Todo Class:
The 'Todo' class represents an individual item in the to-do list. It encapsulates its properties, such as ID, title, and completion status. Additionally, it provides two crucial methods:
toMap(): Converts a 'Todo' object into a Map to prepare it for database operations.
Todo.fromMap(): Constructs a 'Todo' object from a Map retrieved from the database.
TodoProvider Class:
The 'TodoProvider' class acts as a helper to interact with the SQLite database. It offers methods that abstract away complex SQL queries:
open(String path): Opens a connection to the database, creating the 'todo' table if it doesn't exist.
insert(Todo todo): Inserts a 'Todo' object into the 'todo' table and returns the inserted object.
getTodo(int id): Retrieves a 'Todo' item based on its ID from the 'todo' table.
delete(int id): Deletes a 'Todo' item from the 'todo' table based on its ID.
update(Todo todo): Updates a 'Todo' item in the 'todo' table based on its ID.
close(): Closes the database connection.
* Read Results:
Suppose you've queried the database for a list of blog posts:
List<Map<String, Object?>> records = await db.query('blog_posts');
Map<String, Object?> mapRead = records.first; // This map is read-onlyHere, records contain a list of blog post records fetched from the database. The mapRead variable holds the first record from the fetched list, but it's read-only by default, meaning you cannot directly modify its contents.
* Updating a Record:
If you try to modify mapRead directly, like this:
mapRead['title'] = 'Updated Title'; // This will throw an exception
This modification will throw an exception because mapRead is read-only. To make changes, create a new map:
Map<String, Object?> map = Map<String, Object?>.from(mapRead);
map['title'] = 'Updated Title'; // Now you can modify this 'map' in memory
* Transactions:
When performing database operations within a transaction:
await database.transaction((txn) async {
await txn.execute('INSERT INTO blog_posts (title, content) VALUES (?, ?)', ['New Post', 'Lorem ipsum...']);
// Avoid using 'database' in the transaction block to prevent deadlocks
// await database.execute('...'); // Don't do this
// Commit the transaction; if an error occurs, the transaction is rolled back
});Using the txn object inside the transaction block to execute queries, inserts, or updates is recommended instead of using the main database object to avoid potential deadlocks.
* Batch Support:
Using batch operations to handle multiple database operations efficiently:
var batch = db.batch();
batch.insert('blog_posts', {'title': 'Post A', 'content': 'Content A'});
batch.update('blog_posts', {'title': 'Updated Post A'}, where: 'id = ?', whereArgs: [1]);
batch.delete('blog_posts', where: 'id = ?', whereArgs: [2]);
await batch.commit();
This example demonstrates inserting a new blog post, updating an existing post, and deleting another post within a single batch, reducing communication overhead between Dart and the database.
* Error Handling:
You can control error handling within batches:
await batch.commit(continueOnError: true);
This statement allows the batch to continue executing other operations even if one operation fails, ensuring that successful operations are committed, while failed ones are rolled back.
When working with SQLite databases, especially when dealing with table and column names, it's crucial to avoid using SQLite keywords as entity names to prevent conflicts and ensure proper query execution.
For instance, if a table is named 'group' in SQLite, it's recommended to escape it properly in queries to avoid confusion with the SQL keyword 'GROUP'. This could be handled as follows:
// Example of escaping table and column names
db.query('my_table', columns: ['my_column'], where: '"group" = ?', whereArgs: ['my_group']);
Here, 'my_table' and 'my_column' are placeholders for actual table and column names, and 'group' is properly escaped with double quotes to prevent any conflicts with the SQL keyword.
These practices help manage database interactions efficiently in Flutter applications, ensuring proper handling of transactions, batch operations, and error scenarios to maintain data integrity and optimize performance.
By following these good practices, developers can handle SQLite databases well in Dart apps. This helps things work together smoothly, prevents problems, and keeps the data safe and correct. Understanding the differences between Dart and SQLite types and using quotes properly in queries makes sure everything runs smoothly and fast when working with the database.