Sqflite – playing around with Flutter and a simple database

We’ve started working on the Flutter project a couple of weeks ago, the configuration was pretty straightforward and we’ve jumped into the project right away. There are quite a lot of articles on the web about getting started or creating your first Flutter widget, but after creating architecture, which is also a kinda long story, the first big challenge for us was database setup. There was only one reliable choice at the moment – sqflite library – Dart port for sqlite. Unfortunately, there is no ORM at the moment, also Realm doesn’t have plans to release the database for Dart, but we managed to handle it. To make it more clear we’re gonna show it on the classic TODO list example.

In the beginning, let’s make it as abstract as possible and after that, we can proceed to concrete implementation.

Repository pattern

In our project, we’re using a repository pattern which is a good way to decouple data sources from view and controler/ViewModel related code. At first, we created an abstract repository class that provides a simple interface.

abstract class NotesRepository {
  DatabaseProvider databaseProvider;

  Future<Note> insert(Note note);
  
  Future<Note> update(Note note);

  Future<Note> delete(Note note);

  Future<List<Note>> getNotes();
}

As you can see we’re using Futures because database requests are asynchronous. We’re provided a standard CRUD interface but instead of getting one note, we’re getting all of them once.

Database provider and DAO’s

You got to remember to add sqflite to your pubspec.yaml file. While writing this article the most recent stable sqflite version is 0.13.0, if we can say that anything is stable in Flutter 😉

sqflite: ^0.13.0

Let me show you an abstract generic DAO object which is going to be used by DatabaseProvider and also you to transform your models into database records.

abstract class Dao<T> {
  //queries
  String get createTableQuery;

  //abstract mapping methods
  T fromMap(Map<String, dynamic> query);
  List<T> fromList(List<Map<String,dynamic>> query);
  Map<String, dynamic> toMap(T object);
}

It contains table creation query for a given type of object and methods for mapping queries to object and object to map which is being used by the database for querying data.

You may have noticed the DatabaseProvider object within NotesRepository, don’t worry, I’m gonna talk about it right now. DatabaseProvider is a singleton class that keeps database instances and is responsible for initialization. Uses DAO’s to call table creation queries.

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class DatabaseProvider {
  static final _instance = DatabaseProvider._internal();
  static DatabaseProvider get = _instance;
  bool isInitialized = false;
  Database _db;

  DatabaseProvider._internal();

  Future<Database> db() async {
    if (!isInitialized) await _init();
    return _db;
  }

  Future _init() async {
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, ‘todo_app.db’);

    _db = await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await db.execute(NoteDao().createTableQuery);
    });
  }

Besides singleton initialization, we’ve got one public method “db()” which returns the database asynchronously and if the database is not initialized yet, it does the initialization. The initialization process is nothing more than opening a database or creating it when it doesn’t exist. During the creation process, table creation queries are being called. That’s it! We’ve got DatabaseProvider fully implemented and ready to use.

Let’s make it happen!

Okay, we’ve got our abstraction layer done, so now we have to create NoteDao class which will be the DAO representation of our Note model class. We also need NotesRepository implementation, let’s call it NotesDatabaseRepository.

Our Note class is pretty simple :

class Note {
  int id;
  String title;
  String description;

  Note(this.title, this.description);
}

and now NoteDao:

class NoteDao implements Dao<Note> {
  final tableName = 'notes';
  final columnId = 'id';
  final _columnTitle = 'title';
  final _columnDescription = 'description';
  

  @override
  String get createTableQuery =>
      "CREATE TABLE $tableName($columnId INTEGER PRIMARY KEY,"
      " $_columnTitle TEXT,"
      " $_columnDescription TEXT)";

  @override
  Note fromMap(Map<String, dynamic> query) {
    Note note = Note();
    note.id = query[columnId];
    note.title = query[_columnTitle];
    note.description = query[_columnDescription];
    return note;
  }

  @override
  Map<String, dynamic> toMap(Note object) {
    return <String, dynamic>{
      _columnTitle: object.title,
      _columnDescription: object.description
    };
  }

  @override
  List<Note> fromList(List<Map<String,dynamic>> query) {
    List<Note> notes = List<Note>();
    for (Map map in query) {
      notes.add(fromMap(map));
    }
    return notes;
  }
}

NoteDao overrides methods from the Dao interface that we’ve created above. After writing this piece of code we need to mix it all together within our repository to provide consistent implementation ready to use by our presentation layer. The importance of the repository is that no one knows about our database and dao implementation so we can easily switch the repository to another one that implements the same interface e.g NoteNetworkRepository. We can mix them up together regarding internet connection quality, it all depends on what we want to achieve in our application.

class NotesDatabaseRepository implements NotesRepository {
  final dao = NoteDao();

  @override
  DatabaseProvider databaseProvider;

  NotesDatabaseRepository(this.databaseProvider);

  @override
  Future<Note> insert(Note note) async {
    final db = await databaseProvider.db();
    note.id = await db.insert(dao.tableName, dao.toMap(note));
    return note;
  }

  @override
  Future<Note> delete(Note note) async {
    final db = await databaseProvider.db();
    await db.delete(dao.tableName,
        where: dao.columnId + " = ?", whereArgs: [note.id]);
    return note;
  }

  @override
  Future<Note> update(Note note) async {
    final db = await databaseProvider.db();
    await db.update(dao.tableName, dao.toMap(note),
        where: dao.columnId + " = ?", whereArgs: [note.id]);
    return note;
  }

  @override
  Future<List<Note>> getNotes() async {
    final db = await databaseProvider.db();
    List<Map> maps = await db.query(dao.tableName);
    return dao.fromList(maps);
  }
}

Whether you’re using some middle layer in between (like interactors) or not, you’re good to go now. In the end, all you need is to instantiate NotesDatabaseRepository with DatabaseProvider – you can use a dependency injection framework to do that, or you can satisfy dependencies by yourself.

To sum up the article, we just provided an implementation of an offline repository that uses the database as storage for our data, which means no internet connection is needed. Hopefully, you find it useful in your projects. We’ve been pretty excited to take you on a little journey with Flutter.

Do you have any questions about Flutter development or any potential future Flutter projects? Let’s get in touch.