We’ve started working on Flutter project couple weeks ago, configuration was pretty straightforward and we’ve jumped into the project right away. There is quite a lot articles in the web about getting started or creating your first Flutter widget, but after creating architecture, which is also kinda long story, 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 database for Dart, but we managed to handle it. To make it more clear we’re gonna show it on classic TODO list example.

At 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 repository pattern which is good way to decouple data sources from view and controler/viewmodel related code. At first we created abstract repository class that provides 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 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 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 given type of object and methods for mapping queries to object and object to map which is being used by database for querying data.

You may have noticed DatabaseProvider object within NotesRepository, don’t worry, I’m gonna talk about it right now. DatabaseProvider is singleton class which keeps database instance 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 database asynchronously and if database is not initialized yet, it does the initialization. Initialization process is nothing more than opening database or creating it when it doesn’t exist. During 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 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 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. Importance of repository is that no one knows about our database and dao implementation so we can easily switch repository to other one which 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. At the end all you need is to instantiate NotesDatabaseRepository with DatabaseProvider – you can use dependency injection framework to do that, or you can satisfy dependencies by yourself.

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

My friends should read it! f i l g