Road to Production-Ready Application — II (Database in Flutter using Sqflite)

This is the second blog in the series of Road to Production-Ready Application.
You can check out the first blog — Road to Production-Ready Application — I. In this blog, we will talk about the database in Flutter using sqflite library.

By the end of this blog, you’ll know:

• The basics of how the database works.
• Setting up the database in your code using sqflite.
• Implementation of CRUD (Create, Read, Update, Delete) database operations in your code.

Sqflite library provides an efficient SQLite database for our app. It helps us to take control of your database, queries, and relationships between the tables.

Getting started

Open pubspec.yaml file and add the following lines in the dependencies section. This will basically let you use the dependency — Sqflite.

dependencies:
  flutter:     
    sdk: flutter   
  sqflite: ^2.0.0+3  
  path_provider: ^2.0.2

Change the version number to the most recent version for sqflite and path_provider. The path_provider plugin allows us to access user directories on iOS and Android, where our SQLite database file will get stored.

Update your packages by running the below command in your project’s root directory.

flutter packages get

Overview

We will create two tables: User table and Blog table. We will establish a one-to-many relationship between these two tables where a single user in the User table can be associated with multiple blogs in the Blog table. We will design this by using user_id as the primary key in the User table and the foreign key in the Blog table to allow multiple instances of the same value. We’ll perform CRUD operations on these tables.

Now before creating tables, let’s first create our own data abstraction layer. This class will help us to access the SQLite database. It will act as our data access object as well by providing functions to query for User and Blog data models.

Data Abstraction Layer

The above code creates a database connection. Now let’s create User table and Blog table in the above class. Observe how the FOREIGN KEY attribute is used to establish a one-to-many relationship between the two tables.

Next, let’s create our User and Blog model classes that will present these tables in Dart.

Data Models

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 an expected format 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 our models and tables are defined, let’s add functions for CRUD operations in our DatabaseHelper.dart class.

DAO methods

I prefer upsert methods as opposed to individual insert and update methods, but feel free to split them if you want. Both methods return a Future since sqflite accesses the database in an asynchronous nature.

Let’s add some more methods.

Let’s see how our DatabaseHelper.dart class will look after all the above additions.

DatabaseHelper.dart (Complete Code)

Now let’s see how to use these DAO methods in the dart file where you want to access the database.

I hope you would have understood the basics of how to use sqflite in your flutter project. This was a basic example for the sake of explanation. You can create more tables and establish complex relationships between them and also you can have more DAO functions according to your requirements.

Let me know if you have any doubts in the comments below or you can reach out to me on LinkedIn.

Happy Coding!!