How To Persist and Query Data With SQLite in Flutter App Development

Persisting and querying a large amount of data on the local device is important to make a highly performant Flutter application. This data must remain intact in each app session. Hence, you need to have an advanced database management system to locally store your more complex and interdependent data. SQLite should be your pick in this scenario as it emphasizes concurrency, scalability, reliability, centralization, simplicity, and control.

When it comes to Flutter app development, you can use the SQLite database the right way via the sqflite package available on pub.dev. You can have complete control over your queries, database, relationships, or everything you desire to improve the performance of your Flutter apps. 

In today’s tutorial, I will walk you through how to effectively use the SQLite database in Flutter app development using the sqflite plugin. To demonstrate the use of Flutter SQLite, I am going to create a sample application where I will store employee information like ID, name, and designation.

I will be covering the points mentioned below in this blog:

1. Adding Dependencies

2. Setting up Database

3. Creating tables and data models

4. Performing CRUD (Create, Read, Update, Delete) operations

5. Handling migrations 

1.  Adding Dependencies

In your Flutter project, go to pubspec.yaml file and search for dependencies. Under dependencies, we need to add sqflite and path_provider packages.

• The sqflite package provides necessary classes as well as functions that enable interaction with an SQLite database.

• The path_provider package is used for getting commonly used locations on host platform file systems, such as the temp and app data directories.

dependencies:

  flutter:

    sdk: flutter

  sqflite: any

  path_provider: any

To start the app, you can open a simulator device or else can connect with a real device by following the command:

flutter run

2.  Setting Up Database

Next, we will create a database object. 

How To Create a DatabaseHelper Class

To access or work with the database, you need to create a DatabaseHelper class. It is a singleton class responsible for creating a new database and tables, establishing database connections, etc. You will have one instance of the database connection with global access to the database.

Now, we need to create a database instance that will be initialized by calling the initializeDB function. This function will create a database called employee in your application’s document directory. If it does not exist, then open it using the openDatabase function.

3.  Creating Tables and Data Models

Since we need to add multiple fields of employees in the database in our Flutter app, first, we will create a table here. 

How To Create the Employee Table

Create an employee table for storing employee information. Each employee will have an ID, name, and designation. Hence, this information will be represented as three columns in the employee table.

• The id is a “Dart String” and is stored as a TEXT SQLite Datatype. Using an id as the primary key for this table is considered good practice to improve update times as well as queries. 

• The name over here is “Dart String” and stored as TEXT in SQLite Datatype.

• The designation is a “Dart String”, stored as TEXT in SQLite Datatype.

The employee table is defined in the migrationScripts Map, where a key is a version number for the database, and the list of Strings are the tables that should be added for the version. We will be using this later to handle database migration.

4.  Performing CRUD (Create, Read, Update, Delete) Operations

To perform the CRUD operations on employees, we are going to create an EmployeeDAO class. This class will access the database via a shared database instance from DatabaseHelper class.

Insert an Employee Into the Database

Use the insertRaw method and write the insert SQL statement. 

Get a List of All Employees From the Database

• Run a query against the employee table. This returns a List<Map>.

• Iterate through the List<Map> and convert each item to Employee using the fromMap function add it into a List<Employee>.

Update an Employee in the Database:

• Use the upateRaw method and write the update SQL statement. 

• Use a where clause to ensure that you update the correct employee.

Delete an Employee From the Database:

• Use the delete method. 

• Use a where clause to ensure that you delete the correct employee.

5.  Handling Migrations

For handling migrations, I will be using the onUpgrade function and version parameter of the database.

Let’s assume you have one version of the app currently live and it is used by your customers. You need to add a new table in your upcoming release; let's say, employee1. You can create a new table without losing the data of your existing users/customers using the migrationScripts Map. 

I will set the database version as the length of migrationScripts and add one more entry in the Map with 2 as the key and the value as the list, which has a ‘create a query’ for the employee1 table. 

For your future app versions, if your database schema gets updated, you can add entries to migrationScripts and increment the key to 3, 4, etc. against those added values (SQL queries for updating your database schema).

The database will use OnCreate or onUpgrade function accordingly based on the user’s current database version.

When the app starts, the initializeDB function will execute and check the current database version. You can apply any changes that are needed to bring the schema up to date by updating the database version.

Summing It Up

You can download the above-discussed sample from here.

I hope you enjoyed this tutorial on persisting data with SQLite in Flutter app development. If you have any questions or feedback, feel free to get in touch.