SQLite in Flutter

Versioned Schema and Migrations

SQFlite supports basic schema migration on open. When opening the database, you can specify several callbacks to customize behavior. The openDatabase method looks like this:

Future<Database> openDatabase(String path,
    {int version,
    OnDatabaseConfigureFn onConfigure,
    OnDatabaseCreateFn onCreate,
    OnDatabaseVersionChangeFn onUpgrade,
    OnDatabaseVersionChangeFn onDowngrade,
    OnDatabaseOpenFn onOpen,
    bool readOnly = false,
    bool singleInstance = true})

When the schema version is upgraded, onUpgrade is called. Inside this callback, you can perform a raw SQL query to alter the database.

Asynchronous Reads and Writes

The operations are asynchronous, as marked by the Future return types, so you don’t have to block the main thread with your operations.

Now that you’ve committed a batch of information to memory, you’re ready to implement it. ;]

Setting Up SQFlite

SQFlite is already in pubspec.yaml, so start setting up the database. First, open the SqlitePersistence.dart file and add the path import at the top of the file:

import 'package:path/path.dart';

Next, you’ll create the table with the proper schema and then open it. In SqlitePersistence.dart, replace the existing create method with:

 // 1
static Future<SqlitePersistence> create() async => SqlitePersistence._(await database());

// 2
static Future<Database> database() async {
  return openDatabase(join(await getDatabasesPath(), DatabaseName),
    // 4
    onCreate: (db, version) {
      // 5
      return db.execute(
        '''CREATE TABLE $MoviesWatchedTableName(
           id INTEGER PRIMARY KEY, 
           imdbId INTEGER, 
           name STRING, 
           imageUrl STRING, 
           year STRING, 
           watchedOn INTEGER
        )''',
      );
    },
    // 6
    version: 1,
  );
}

There’s quite some code here, so here’s a breakdown:

• First, you change the create method to return a SqlitePersistence from an async method called database.
• Second, you create a new static method called database. Inside it, you call openDatabase while providing a path for the database.
• Third, you declare an onCreate callback.
• Fourth, you perform a create table SQL query. You also specify the schema of the entries. The function execute runs a raw SQL query asynchronously without any return value.
• Finally, you provide a version, which can be used for schema migration later on.

Build and run the app and see that it builds successfully. There won’t be any visual changes, but make sure there aren’t any errors.

Inserting Data

Next, you’ll add entries to the database. Still in SqlitePersistence.dart, replace the contents of createOrUpdateObject with:

void createOrUpdateObject(int key, Map<String, dynamic> object) async {
  await db.insert(MoviesWatchedTableName, object, 
      conflictAlgorithm: ConflictAlgorithm.replace);
}

Here, you insert the movie object as a map, and provide MoviesWatchedTableName as the table name. You use the conflict algorithm replace, but any of them would work for this use case.

Again, build and run the app. See that it builds successfully. Try adding some movies or shows. You still won’t see a visual changes, but you know deep in your heart that it’s there.

Reading Data

At this point, it’s good to see the inserted data in the interface.

To show all the movies or shows you’ve watched, add the following to getUniqueObjects in SqlitePersistence.dart:

Future<List<Map<String, dynamic>>> getUniqueObjects() async {
  final ret = await db.rawQuery('SELECT *, COUNT(*) as watchedTimes FROM $MoviesWatchedTableName group by imdbId');
  return ret;
}

Here you perform a raw SQL query. First, you get all entries from the MoviesWatchedTableName table, then add a column named watchedTimes. You compute that column by counting entries with the same imdbId value.

Build and run the app. Or, because Flutter is awesome, you could use hot restart. Notice that the entries you added before show up now:

Now, try to search the list by tapping the search bar and typing some words. Notice that the search doesn’t work. This is because it uses a different method to get the entries.

To implement search, add the following to findObjects:

Future<List<Map<String, dynamic>>> findObjects(String query) async {
  final ret = await db.rawQuery(
      'SELECT *, COUNT(*) as watchedTimes FROM $MoviesWatchedTableName where name like ? group by imdbId',
      ['%$query%']);
  return ret;
}

Here, you still use a raw SQL query on the same table. Similar to the previous query, you select all columns and add the watchedTimes column. However, now you provide a where clause to match the name column. You also provide a where argument, which is the search term surrounded by '%', to allow matching to any string before and after the word.

Build and run the app, or use hot reload. Try searching now and you’ll see this:

Cool, right? However, say you accidentally add a movie that you didn’t watch. You’d want a function to delete existing entries. You’ll add that next.

Deleting Data

To delete entries, add the following to removeObject in SqlitePersistence.dart:

Future<void> removeObject(int key) async {
  await db.delete(
    MoviesWatchedTableName,
    where: 'id = ?',
    whereArgs: [key],
  );
}

The delete method you’re using takes a table name and a where clause with some arguments. removeObject will take in a key and insert it into the where clause, allowing you to delete entries in the database.

Save and do a hot reload. That’s better. Click one of the movies or shows and you’ll see an interface like this:

If you scroll to the bottom of the page, you’ll see a button to remove your most recent entry. After clicking the I did not watch this button, the app will redirect you to the main page. Then, you’ll see the list is now updated.

Time to celebrate! Watch a new movie or tv show or keep binge-watching. :]

Where to Go From Here?

You can download the completed project by clicking the Download Materials button at the top or bottom of the tutorial.

To learn more about SQLite, checkout the cookbook Flutter created. The SQFlite plugin github repository is also a great resource.

Another interesting way to use SQLite is via dart ffi which allows calling C libraries. You can check out the dart ffi and a plugin that makes an dart interface from ffi here.

I hope you enjoyed this tutorial on SQLite in Flutter! If you have any questions or comments, please join the forum discussion below.