Batch Operations
Batch operations allow you to execute multiple SQL statements as a single transaction, which can significantly improve the performance of database operations. To perform a batch operation in sqflite
, you can use the batch()
method provided by the Database
class. Here’s an example:
final db = await database;
final batch = db.batch();
batch.insert('books', {'title': 'The Great Gatsby', 'author': 'F. Scott Fitzgerald'});
batch.insert('books', {'title': 'To Kill a Mockingbird', 'author': 'Harper Lee'});
batch.insert('books', {'title': '1984', 'author': 'George Orwell'});
await batch.commit();
In the code above, we’re creating a new Batch
object and adding three INSERT
statements to it, which will insert three new rows into the books
table. Finally, we’re calling the commit()
method to execute the batch operation as a single transaction.
Transactions
Transactions allow you to perform a series of database operations as a single, atomic unit. If any of the operations fail, the entire transaction is rolled back, ensuring that the database remains in a consistent state. To perform a transaction in sqflite
, you can use the transaction()
method provided by the Database
class. Here’s an example:
await db.transaction((txn) async {
await txn.insert('books', {'title': 'Pride and Prejudice', 'author': 'Jane Austen'});
await txn.insert('books', {'title': 'One Hundred Years of Solitude', 'author': 'Gabriel Garcia Marquez'});
await txn.insert('books', {'title': 'The Catcher in the Rye', 'author': 'J.D. Salinger'});
});
In the code above, we’re using the transaction()
method to execute three INSERT
statements as a single transaction. If any of the statements fail, the entire transaction will be rolled back.
Handling Database Migrations
As your app evolves and changes, you may need to make changes to your database schema. sqflite
provides a mechanism for handling database migrations, which allows you to update your database schema without losing any existing data. To handle database migrations in sqflite
, you can use the onUpgrade
callback provided by the openDatabase()
function. Here’s an example:
final database = openDatabase(
'my_database.db',
version: 2,
onCreate: (db, version) async {
await db.execute('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT)');
},
onUpgrade: (db, oldVersion, newVersion) async {
if (oldVersion < 2) {
await db.execute('ALTER TABLE books ADD COLUMN year INTEGER');
}
},
);
In the code above, we’re creating a new database with a version number of 2. We’re also defining an onCreate
callback that will be called when the database is first created, and an onUpgrade
callback that will be called when the database version is upgraded. In the onUpgrade
callback, we’re checking the old version of the database and adding a new column to the books
table if the old version is less than 2.
Logging
Logging is an essential tool for debugging and error handling. You can use logging to capture errors and other events during database operations. sqflite
provides a built-in logging mechanism that you can enable by setting the debug
flag to true.
await Sqflite.setDebugModeOn(true);
In the code above, we are enabling the debug mode for sqflite
logging. Once enabled, sqflite
will log all SQL statements and other events to the console. This can be very helpful in identifying errors and other issues during database operations.
By using these advanced features of sqflite
in Flutter, you can build more complex and powerful database-driven apps that can handle large data sets and complex data relationships.