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.
