Sqflite is a Flutter plugin that provides a lightweight and efficient way to access SQLite databases. SQLite is a popular database engine used for storing and managing data in mobile and web applications. Sqflite provides a simple API for creating, opening, querying, and updating SQLite databases within your Flutter app.
Before we start, make sure you have the sqflite plugin installed in your Flutter project by adding the following line to your pubspec.yaml file:
dependencies:
sqflite:
Once you have installed the sqflite plugin, you can start using it in your Flutter app.
How to use sqflite in Flutter
Here’s an example of how to use sqflite to create and query a database:
Create helper to create database
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
class DatabaseHelper {
static final _databaseName = "mydatabase.db";
static final _databaseVersion = 1;
static final table = 'mytable';
static final columnId = '_id';
static final columnName = 'name';
// make this a singleton class
DatabaseHelper._privateConstructor();
static final DatabaseHelper instance = DatabaseHelper._privateConstructor();
// only have a single app-wide reference to the database
static Database? _database;
Future<Database> get database async {
if (_database != null) return _database!;
_database = await _initDatabase();
return _database!;
}
// this opens the database (and creates it if it doesn't exist)
_initDatabase() async {
String path = join(await getDatabasesPath(), _databaseName);
return await openDatabase(path,
version: _databaseVersion, onCreate: _onCreate);
}
// SQL string to create the database table
static final String _onCreate = '''
CREATE TABLE $table (
$columnId INTEGER PRIMARY KEY,
$columnName TEXT NOT NULL
)
''';
// Helper methods
// Insert a row into the database
Future<int> insert(Map<String, dynamic> row) async {
Database db = await instance.database;
return await db.insert(table, row);
}
// Query all rows in the database
Future<List<Map<String, dynamic>>> queryAllRows() async {
Database db = await instance.database;
return await db.query(table);
}
}In the above code, we have defined a DatabaseHelper class that provides helper methods for creating, opening, and querying the database. We have also defined the table schema and column names.
To create a new row in the database, we can call the insert method and pass in a map of key-value pairs:
var row = {'name': 'John Doe'};
int id = await DatabaseHelper.instance.insert(row);
print('inserted row id: $id');To query all rows in the database, we can call the queryAllRows method:
List<Map<String, dynamic>> rows = await DatabaseHelper.instance.queryAllRows();
rows.forEach((row) => print(row));Opening and Closing the Database
In the example above, we used the openDatabase method to create or open the database file. When the app starts up, it will create the database file if it does not exist, and then return a reference to the database instance. When the app is closed, the database connection is automatically closed as well.
However, in some cases, you may want to close the database connection manually before the app is closed. To do this, you can call the close method on the database instance:
await db.close();Updating the Database Schema
If you need to modify the database schema (i.e., add or remove tables, columns, or indices), you will need to update the onCreate and onUpgrade callbacks when you create or open the database:
// SQL string to create the database table
static final String _onCreate = '''
CREATE TABLE $table (
$columnId INTEGER PRIMARY KEY,
$columnName TEXT NOT NULL
)
''';
// SQL string to upgrade the database table schema
static final String _onUpgrade = '''
DROP TABLE IF EXISTS $table;
$_onCreate
''';
// this opens the database (and creates it if it doesn't exist)
_initDatabase() async {
String path = join(await getDatabasesPath(), _databaseName);
return await openDatabase(path,
version: _databaseVersion,
onCreate: _onCreate,
onUpgrade: _onUpgrade);
}In the above code, we have defined a _onUpgrade callback that drops the existing table if it exists, and then creates a new one using the _onCreate SQL string. This is just an example – in practice, you would modify the SQL string to match your specific schema changes.
Note that when you update the database schema, you will need to increment the _databaseVersion constant in your DatabaseHelper class. This will trigger the onUpgrade callback the next time the database is opened, and allow you to apply the schema changes.
Transactions
If you need to perform multiple database operations as a single atomic transaction (i.e., either all the operations succeed, or none of them do), you can use the transaction method on the database instance:
await db.transaction((txn) async {
await txn.insert(table, row1);
await txn.insert(table, row2);
});In the above code, we are inserting two rows into the database within a single transaction. If either of the inserts fails, the entire transaction will be rolled back and none of the changes will be committed to the database.
Error Handling
When working with sqflite, it’s important to handle errors that may occur during database operations. The most common errors are SQLiteExceptions, which can occur if you try to insert a row with a duplicate primary key, or if you try to query a non-existent table.
To handle these errors, you can use a try-catch block around your database operations:
try {
int id = await DatabaseHelper.instance.insert(row);
} on DatabaseException catch (e) {
if (e.isUniqueConstraintError()) {
// handle unique constraint violation error
} else if (e.isNoSuchTableError()) {
// handle table not found error
} else {
// handle other database errors
}
}
In the above code, we are catching the DatabaseException thrown by the insert method, and then checking the specific type of exception using the isUniqueConstraintError…
Querying the Database
To query the database, you can use the query method on the database instance. The query method takes a table name, optional columns to select, and optional WHERE, GROUP BY, and ORDER BY clauses.
Future<List<Map<String, dynamic>>> getAllRows() async {
final db = await database;
return await db.query(table);
}
In the above code, we are using the query method to retrieve all rows from the specified table. The method returns a list of maps, where each map represents a row in the database.
Updating and Deleting Rows
To update or delete rows in the database, you can use the update and delete methods on the database instance.
Future<int> updateRow(Row row) async {
final db = await database;
return await db.update(table, row.toMap(),
where: '$columnId = ?', whereArgs: [row.id]);
}
Future<int> deleteRow(int id) async {
final db = await database;
return await db.delete(table, where: '$columnId = ?', whereArgs: [id]);
}
In the above code, we are using the update method to update a row in the database, and the delete method to delete a row from the database. Both methods take a table name, a map of column values to update or delete, and optional WHERE clauses to specify which rows to update or delete.
Using DAOs (Data Access Objects)
To keep your database logic separate from your UI code, it’s a good practice to use DAOs (Data Access Objects) to encapsulate your database operations. A DAO is a simple class that provides methods to insert, update, delete, and query rows in the database.
class RowDao {
final _database = DatabaseHelper.instance.database;
Future<List<Row>> getAllRows() async {
final db = await _database;
final List<Map<String, dynamic>> maps = await db.query(table);
return List.generate(maps.length, (i) => Row.fromMap(maps[i]));
}
Future<void> insertRow(Row row) async {
final db = await _database;
await db.insert(table, row.toMap());
}
Future<void> updateRow(Row row) async {
final db = await _database;
await db.update(table, row.toMap(),
where: '$columnId = ?', whereArgs: [row.id]);
}
Future<void> deleteRow(int id) async {
final db = await _database;
await db.delete(table, where: '$columnId = ?', whereArgs: [id]);
}
}
In the above code, we have defined a RowDao class that provides methods to insert, update, delete, and query rows in the database. The class encapsulates all database operations, and allows you to keep your UI code separate from your database logic.
To use the DAO, you can simply create an instance of the DAO class and call its methods:
final _dao = RowDao();
void _deleteRow(int id) async {
await _dao.deleteRow(id);
}
In the above code, we are creating an instance of the RowDao class and calling its deleteRow method to delete a row from the database.
That’s it! You now have a working example of how to use sqflite in your Flutter app to create and query SQLite databases. You can build on this example to create more complex database schemas and add additional CRUD operations to your app.
