SQLite in Flutter

You’ll often find that you need to save data in your Flutter applications between app launches. If the data is simple enough, you might be able to get away with using a key value storage system. But as your data grows more complex and more interdependent, you’ll need to use a more formal data storage system, like SQLite.

SQLite is a variation of the SQL database language that allows you to save your data in a relational database and persist that data between app sessions. It allows you to to do everything you know and love with SQL – perform joins on tables, migrations, updates and much more.

Say you wanted to watch one of your favorite movies and ask your friend to join you. They say yes, and you’re excited until they want to know when you last watched it and how many times you’ve watched it. Now, you’re facing the dilemma of answering these questions accurately. In this tutorial you’ll write an application called Cinephile to track movies you’ve watched. You’ll learn how to:

Getting Started

Download the starter project by clicking the Download Materials button at the top or bottom of this tutorial. Then, open it in Android Studio 3.5 or later. You can also use VS Code, but the instructions in this tutorial will be using Android Studio.

Use Flutter 1.12 or above. If you get a ‘Packages get’ has not been run message, open the pubspec.yaml file and run the get dependencies for Flutter command.

The starter project provides the interface and some logic for the movie tracking app.

Build and run the starter project. If you encounter issues running the project on iOS, run pod install && pod update inside a Terminal window at the project root. You’ll see the movie tracking app’s main screen, like this:

Click the add floating button to see a new screen where you can add one of your watched movies. There’s a search bar where you can find movies using an online API. Add a movie you’ve recently watched and you’ll see a screen like this:

Next, add the movie by clicking the row. You’ll see it closes the movie page, but your list is still empty.

The app hasn’t persisted your data. Your SQLite in Flutter journey begins here.

The Flutter SQFlite Plugin

The sqflite plugin is one way to use SQLite in Flutter. While it’s not a first-party plugin, it’s recommended in the official Flutter cookbook.

Create, Read, Update and Delete or CRUD

sqflite lets you perform the basic CRUD operations. Here’s an overview of the operations you’ll use in this tutorial.

Create

You can create entries using this function:

Future<int> insert(String table, Map<String, dynamic> values,
      {String nullColumnHack, ConflictAlgorithm conflictAlgorithm})

Here’s a code breakdown:

• table: This is where you insert an entry.
• values: This field contains the list of column names and and their respective values.
• nullColumnHack: An optional field used when a column is null.
• conflictAlgorithm: This is the conflict handling algorithm. You’ll see the list of options in the following code. It returns the last inserted record ID.

enum ConflictAlgorithm {
  rollback,
  abort,
  fail,
  ignore,
  replace,
} 

These conflict algorithm options are well documented. Here’s a quick breakdown:

• rollback: Back to initial state of transaction, on the next section, if there is one. Otherwise, it’s the same as abort.
• abort: Simply stops the operation.
• fail: Similar to abort, but it returns a failure.
• ignore: Doesn’t stop transaction, but stops the current operation.
• replace: Replaces the conflicted entries.

For more detail, check the documentation in the sql_builder.dart file inside the plugin sources.

Read

You can call reading entries with the function below.

Future<List<Map<String, dynamic>>> query(String table,
      {bool distinct,
      List<String> columns,
      String where,
      List<dynamic> whereArgs,
      String groupBy,
      String having,
      String orderBy,
      int limit,
      int offset});

This function is also very well documented, so you can check specifics for each parameter. Two parameters, where and whereArgs, are used together to substitute values in the where clause while preventing SQL injection. It returns the matching entries as a list.

Update

You can also update entries with this function:

  Future<int> update(String table, Map<String, dynamic> values,
      {String where,
      List<dynamic> whereArgs,
      ConflictAlgorithm conflictAlgorithm});

The update function has a similar structure to insert but allows you to specify a where clause, like the query method above. It also has whereArgs, used in the same way to prevent SQL injection.

The last parameter, conflictAlgorithm, is the same as in insert. It returns the number of changes made.

Delete

Finally, you can delete entries with the function below:

  Future<int> delete(String table, {String where, List<dynamic> whereArgs});

You use where and whereArgs to provide a where clause that’s safe. It returns the number of entries deleted.

These are the basic CRUD methods you can call in the plugin. However, for more advanced uses, you can also use raw SQL statements.

Raw SQL CRUD Operations

Earlier, you learned how to use the basic CRUD methods. If they aren’t enough for your purposes, you can use raw SQL statements with these methods:

  Future<int> rawInsert(String sql, [List<dynamic> arguments]);
  Future<List<Map<String, dynamic>>> rawQuery(String sql, [List<dynamic> arguments]);
  Future<int> rawUpdate(String sql, [List<dynamic> arguments]);
  Future<int> rawDelete(String sql, [List<dynamic> arguments]);

These return the same things that the previous non-raw functions did. However, you can provide a raw SQL statement for each of them. To supply arguments, you can insert ? placeholder strings in your sql statement. You then use the arguments parameter to substitute all ?s in the SQL statement. You’ll see an example later on.

Transactions and Batch Support

Transactions allow rolling back operations if one of the operations encounters an unexpected error. This ensures either the whole transaction or nothing is applied. You can call the transaction method to start a transaction:

  Future<T> transaction<T>(Future<T> action(Transaction txn), {bool exclusive});

  // For example
  await database.transaction((txn) async {
    await txn.insert('Little Bobby Tables', { 'name': 'bobby', 'lastname': 'tables' });
    await txn.insert('Little Bobby Tables', { 'name': 'Robert");', 'lastname': 'DROP TABLE STUDENTS; --' });
  });

Make sure not to use the database instance inside of a transaction, because it will cause your app to hang. Instead, use the transaction object supplied in the action callback parameter.

If the operations inside action throws an error, the transaction won’t be committed and the whole transaction will be rolled back for you.

Notice that the operations are asynchronous. This is a side effect of calling native code via Platform Channels. It means data is going from dart to native code, multiple times.

To prevent the multiple back and forth trips and to improve performance, you can use batch support:

batch = db.batch();
batch.insert('Little Bobby Tables', { 'name': 'bobby', 'lastname': 'tables' });
batch.insert('Little Bobby Tables', { 'name': 'Robert");', 'lastname': 'DROP TABLE STUDENTS; --' });
results = await batch.commit();

You can also call the batch inside a transaction so it can rollback if the batch operation throws an error.