How to use Flutter with SQLite

How to store app data in Flutter using the sqflite plugin

Flutter / SQLite

Introduction

In this article we’ll check out how to use SQLite in Flutter with the sqflite package for storing application data locally. SQLite has been around since 2000 and is a popular choice for embedding databases within local apps. For the example project, we’ll build a very simple “TODO” app that can create, update, and delete TODO items from a basic interface.

If you don’t already have Flutter, you can obtain a copy from the installation page. The source code used in this article is available here on GitHub.

Project Configuration

To use SQLite within a Flutter application, the first step is to include the sqflite package within the project’s pubspec.yaml like so:

Here we have specified sqflite version 1.2.0 or higher and path_provider at 1.6.0 or higher. Aside from this, the project is kept lean to make it easy to work with and understand.

Creating a Simple Model

To store data, a simple data model class will provide the necessary methods for converting between a SQLite-friendly data format and an object that can be used within the application. First, an abstract class called Model will serve as the base class for data models. This file is in lib/models/model.dart:

The Model class is very simple and is created for convenience, to define properties / methods that can be expected from data models, such as the id as shown above. This enables the creation of one or more specific data models that will conform to this basic design pattern. For this example app, a TODO item model class is created in lib/models/todo-item.dart:

The TodoItem class contains properties for task and complete and has a simple constructor for creating a new TODO item. For converting between instances of TodoItem and Map objects used by the database, the methods toMap and fromMap have been defined. Note that id is only added to the map when it isn’t null.

Database Class

Rather than having database logic mixed in randomly throughout the app, the main database handling methods are placed in lib/services/db.dart for convenience and ease of maintenance:

This class is abstract since it isn’t meant to be instantiated and only one copy of it in memory is required. Internally, it holds a reference to the SQLite database in the _db property. The database version number has been hard-coded to 1 but in more complex applications the database version can be used to migrate database schemas up or down in version to allow rollout of new features without having to wipe the database and start from scratch.

An instance of the SQLite database is created within the init method, using the example database name for this project. If the example database doesn’t exist yet, onCreate is automatically called. This is where queries for creating table structure are placed. In this case, we have a todo_items table with a primary key for id along with fields that match properties on the TodoItem class above.

The query method along with insert, update, and delete are defined for performing standard CRUD operations on the database. These provide simple abstractions and allow for containing database logic within this class, which can be extremely helpful when refactoring or performing other maintenance on the app, instead of for example having to search and replace strings across multiple files or fix strange bugs that appear when making simple changes.

Main Application File

Last but not least, we have the main app logic and UX in lib/main.dart:

This file is fairly standard for any Flutter app and defines the basic look and feel of the app along with it’s interactions. During initialization, the line WidgetsFlutterBinding.ensureInitialized() will ensure that the Flutter app initializes properly while initializing the database with await DB.init().

When the app starts and the MyHomePage widget is rendered, a call to refresh() pulls the todo list from the todo_items table and maps it to a List of TodoItem objects. These will be rendered within the app’s main ListView through the _items accessor, which takes the List of TodoItem objects and formats it as a list of widgets containing the todo item text and an indicator showing whether or not the item has been completed.

Tasks can be added by pressing the floating action button and entering a task name. When Save is pressed, the newly-created todo list item is added to the database with DB.insert. Clicking on a task in the list will toggle it between complete / incomplete states by flipping the complete boolean and storing the modified object back to the database with DB.update, and swiping a task horizontally will delete the TODO item by providing it to the DB.delete method. Whenever changes are made to the list, a call to refresh() with a subsequent setState() will ensure that the list is refreshed properly.

Conclusion

SQLite provides a convenient industry-standard way of persisting data locally within an application. This example shows how to implement basic CRUD operations for creating and manipulating simple records in a SQLite database. For more information about the sqflite plugin and the various features it supports, see this resource.

Thanks for reading and good luck with your next Flutter project!

Kenneth Reilly (8_bit_hacker) is CTO of LevelUP