Using sqflite in your Flutter applicaiton effectively

When it comes to cross platform, native application development for Android and iOS, you pretty much have two options. Flutter, and React Native. Depending on your love (or hate) of Javascript, React Native may be more up your alley. Despite React Native having more libraries available, and also being used in more production apps, I've come to love Flutter for it's stricter syntax checking with Dart and excellent language integration with IntelliJ.

Flutter and Dart dont provide a built in abstraction for accessing SQLite databases, but luckily there is a nice open source module we can use.

https://github.com/tekartik/sqflite - "SQLite plugin for Flutter. Supports both iOS and Android."

Start by adding sqflite and path_provider to your pubspec.yaml dependencies.

dependencies:
  flutter:
    sdk: flutter
  sqflite: any
  path_provider: any

The path_provider plugin allows us to access the user directories on iOS and Android which is where we will have to store the SQLite database file.

Update your packages by running the following command in your project's root directory.

flutter packages get

Now lets start by creating our own database abstraction layer. This new class will allow for easy access to our SQLite database. It will act as our data access object as well by providing functions to query for specific data models.

import 'dart:async';
import 'dart:io';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';

class DatabaseClient {
  Database _db;

  Future create() async {
    Directory path = await getApplicationDocumentsDirectory();
    String dbPath = join(path.path, "database.db");

    _db = await openDatabase(dbPath, version: 1);
  }
}

Presently the class doesn't do much. You can instantiate it, and then call .create() to open the connection, but we don't have any tables defined, so we can't actually query for anything. Let's expand the class by providing an onCreate function to setup our tables.

  Future _create(Database db, int version) async {
    await db.execute("""
            CREATE TABLE story (
              id INTEGER PRIMARY KEY, 
              user_id INTEGER NOT NULL,
              title TEXT NOT NULL,
              body TEXT NOT NULL,
              FOREIGN KEY (user_id) REFERENCES user (id) 
                ON DELETE NO ACTION ON UPDATE NO ACTION
            )""");

    await db.execute("""
            CREATE TABLE user (
              id INTEGER PRIMARY KEY,
              username TEXT NOT NULL UNIQUE
            )""");
  }

This create method uses the sqflite database object to create two tables, story and user. Now we have to give sqflite access to the _create method so that it can be called when the database is initialized.

class DatabaseClient {
  Database _db;

  Future create() async {
    Directory path = await getApplicationDocumentsDirectory();
    String dbPath = join(path.path, "tradecraft.db");

    _db = await openDatabase(dbPath, version: 1,
        onCreate: this._create);
  }

...

Next let's create our model classes that will present these tables in Dart.

import 'dart:convert';

class User {
  User();

  int id;
  String username;

  static final columns = ["id", "username"];

  Map toMap() {
    Map map = {
      "username": username,
    };

    if (id != null) {
      map["id"] = id;
    }

    return map;
  }

  static fromMap(Map map) {
    User user = new User();
    user.id = map["id"];
    user.username = map["username"];

    return user;
  }
}

import 'dart:convert';

class Story {
  Story();

  int id;
  String title;
  String body;
  int user_id;
  User user;

  static final columns = ["id", "title", "body", "user_id"];

  Map toMap() {
    Map map = {
      "title": title,
      "body": body,
      "user_id": user_id,
    };

    if (id != null) {
      map["id"] = id;
    }

    return map;
  }

  static fromMap(Map map) {
    Story story = new Story();
    story.id = map["id"];
    story.title = map["title"];
    story.body = map["body"];
    story.user_id = map["user_id"];

    return story;
  }
}

Both of these model objects provide two utility functions so that they can easily be serialized and deserialized to and from the database. The toMap() function converts the model to a format expected when inserting or updating a row in the database. The fromMap() method converts the Map object provided by sqflite into a model instance.

Now that we have our models and tables defined, let's add some DAO methods to our database client to create, update, and retrieve rows.

 Future<User> upsertUser(User user) async {
    var count = Sqflite.firstIntValue(await _db.rawQuery("SELECT COUNT(*) FROM user WHERE username = ?", [user.username]));
    if (count == 0) {
      user.id = await _db.insert("user", user.toMap());
    } else {
      await _db.update("user", user.toMap(), where: "id = ?", whereArgs: [user.id]);
    }

    return user;
  }

 Future<Story> upsertStory(Story story) async {
    if (story.id == null) {
      story.id = await _db.insert("story", story.toMap());
    } else {
      await _db.update("story", story.toMap(), where: "id = ?", whereArgs: [story.id]);
    }

    return story;
  }

I prefer to write upsert methods as opposed to individual insert and update methods, but feel free to split them if your preference differs. Both methods return a future since sqflite accesses the database in an asynchronous nature.

Now lets add some methods to query for specific stories and users.

 Future<User> fetchUser(int id) async {
    List<Map> results = await _db.query("user", columns: User.columns, where: "id = ?", whereArgs: [id]);

    User user = User.fromMap(results[0]);

    return user;
  }

 Future<Story> fetchStory(int id) async {
    List<Map> results = await _db.query("story", columns: Story.columns, where: "id = ?", whereArgs: [id]);

    Story story = Story.fromMap(results[0]);

    return story;
  }

But wait, why are we fetching stories by their ID? Let's add another method to fetch the latest stories.

 Future<List<Story>> fetchLatestStories(int limit) async {
    List<Map> results = await _db.query("story", columns: Story.columns, limit: limit, orderBy: "id DESC");

    List<Story> stories = new List();
    results.forEach((result) {
      Story story = Story.fromMap(result);
      stories.add(story);
    });

    return stories;
  }

The pieces are all coming together, but we're still missing a few things. When we query for our story we also want to get the author as well. There are a few ways to go about this. The simplest way is to use our existing methods and call two separate queries. I will show an example of this below. If you want to get more advanced, you can write a single query that joins on the user table. This would require a refactor of the map methods because you will need to prefix the columns you select since there are ambiguously named fields.

  Future<Story> fetchStoryAndUser(int storyId) async {
    List<Map> results = await _db.query("story", columns: Story.columns, where: "id = ?", whereArgs: [storyId]);

    Story story = Story.fromMap(results[0]);
    story.user = await fetchUser(story.user_id);

    return story;
  }

Now lets put all the pieces together in an example that shows inserting and querying.

DatabaseClient db = new DatabaseClient();
await db.create();

User admin = new User();
admin.username = "admin";

user = await db.upsertUser(user);

Story story = new Story();
story.title = "Breaking Story!";
story.body = "Some great content...";
story.user_id = admin.id;

story = await db.upsertStory(story);

Story story = await db.fetchStoryAndUser(story.id);

List<Story> latestStories = await db.fetchLatestStories(5);