How do you update data in a table row in Flutter using the SQFlite plugin?

There are a number of problem solving questions out there (see this and this) but none that I could find to add a canonical answer to. My answer is below.

3 Answers

Add the dependencies

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.

Make a database helper class

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 ) '''); }
}

Update row

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

• You will have to import the DatabaseHelper class and sqflite if you are in another file (like main.dart).
• The SQFlite plugin uses a Map<String, dynamic> to map the column names to the data in each row.

Raw update

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]);

3

Try this please

await db.update(TABLE_NAME, data, where: 'column = ?', whereArgs: [value]); 

Just use this function for SqlFlite Update in flutter

 Future<int> update(TaskModel task, id) async {
return await db.update(tableName, task.toMap(), where: '$columnId = ?', whereArgs: [id]);}

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy