Open pubspec.yaml
and in the dependency section add the following lines:
sqflite: ^1.0.0
path_provider: ^0.4.1
The sqflite
is the SQFlite plugin of course and the path_provider
will help us get the user directory on Android and iPhone.
I'm keeping a global reference to the database in a singleton class. This will prevent concurrency issues and data leaks (that's what I hear, but tell me if I'm wrong). You can also add helper methods (like update) in here for accessing the database.
Create a new file called database_helper.dart and paste in the following code:
import 'dart:io' show Directory;
import 'package:path/path.dart' show join;
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart' show getApplicationDocumentsDirectory;
class DatabaseHelper {
static final _databaseName = "MyDatabase.db";
static final _databaseVersion = 1;
static final table = 'my_table';
static final columnId = '_id';
static final columnName = 'name';
static final columnAge = 'age';
// 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;
// lazily instantiate the db the first time it is accessed
_database = await _initDatabase();
return _database;
}
// this opens the database (and creates it if it doesn't exist)
_initDatabase() async {
Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, _databaseName);
return await openDatabase(path,
version: _databaseVersion,
onCreate: _onCreate);
}
// SQL code to create the database table
Future _onCreate(Database db, int version) async {
await db.execute('''
CREATE TABLE $table (
$columnId INTEGER PRIMARY KEY,
$columnName TEXT NOT NULL,
$columnAge INTEGER NOT NULL
)
''');
}
}
First lets insert a row so that we have something to update:
_insert() async {
Database db = await DatabaseHelper.instance.database;
Map<String, dynamic> row = {
DatabaseHelper.columnName : 'Bob',
DatabaseHelper.columnAge : 23
};
int id = await db.insert(DatabaseHelper.table, row);
print(await db.query(DatabaseHelper.table));
}
Then this is how to do the update:
_update() async {
// get a reference to the database
// because this is an expensive operation we use async and await
Database db = await DatabaseHelper.instance.database;
// row to update
Map<String, dynamic> row = {
DatabaseHelper.columnName : 'Mary',
DatabaseHelper.columnAge : 32
};
// We'll update the first row just as an example
int id = 1;
// do the update and get the number of affected rows
int updateCount = await db.update(
DatabaseHelper.table,
row,
where: '${DatabaseHelper.columnId} = ?',
whereArgs: [id]);
// show the results: print all rows in the db
print(await db.query(DatabaseHelper.table));
}
Notes
DatabaseHelper
class and sqflite
if you are in another file (like main.dart).Map<String, dynamic>
to map the column names to the data in each row.SQFlite also supports doing a raw update. This means that you can use a SQL string. Lets update the same row again using rawUpdate()
.
int updateCount = await db.rawUpdate('''
UPDATE my_table
SET name = ?, age = ?
WHERE _id = ?
''',
['Susan', 13, 1]);
The items in the brackets at the end are bound to the ?
question marks in the SQL string. You can use interpolation to fill in the table and column names but you shouldn't use interpolation for the values because of the danger of SQL injection attacks.
int updateCount = await db.rawUpdate('''
UPDATE ${DatabaseHelper.table}
SET ${DatabaseHelper.columnName} = ?, ${DatabaseHelper.columnAge} = ?
WHERE ${DatabaseHelper.columnId} = ?
''',
['Susan', 13, 1]);