How to set up a database for your Flutter projects using SQLite.
By Piotr Moskała
Some time ago, when we started working on the Flutter project, the configuration was pretty straightforward, and we 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. Still, 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 - the SQFLite library - Dart port for SQFLite. Unfortunately, there was no ORM at the moment. Also, the Realm didn’t have plans to release the database for Dart, but we managed to handle it. To make it more clear we’re going to show it on 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 an excellent way to decouple data sources from view and controller/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 are using Futures because database requests are asynchronous. We provided a standard CRUD interface, but instead of getting one note, we are 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 was 0.13.0.
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 a table creation query for a given type of object and method for mapping queries to object and object to map, which is being used by a database for querying data.
You may have noticed DatabaseProvider object within NotesRepository, no worries, I am going to talk about it right now. DatabaseProvider is a singleton class that 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 have got one public method “db()”, which returns database asynchronously, and if the database doesn’t get initialized yet, it does the initialization. The initialization process is nothing more than an 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 a 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 the Dao interface that we’ve created above. After writing this piece of code, we need to mix it all within our repository to provide consistent implementation ready to use by our presentation layer. The importance of 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 the implementation of the offline repository, which uses a 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.
Originally published at https://applandeo.com/blog/sqflite-flutter-database/
Follow us on Twitter.
