How to do a database table update with SQFlite in Flutter

Question

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.

Answer 1

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