SQLite in Flutter

Setting Up SQLite in Flutter

To use features of SQLite in your Flutter application and make use of local storage for your application you must include the sqflite package in your Flutter project.

To include the package into your application you can follow any of the two given methods.

Method - 1

Head over to the folder of your Flutter project in your terminal (right-click on the folder and select open in terminal) and run the following command.

This will add a new line to your package's pubspec.yaml file of your Flutter project.

The command will also run the flutter pub get command implicitly. From here on all you need to do is import the package into your Dart file using the following line,

Method - 2

Alternatively, you could manually add the package to your project. To do so head over to the pubspec.yaml file of your project and head over to the dependencies section.

Add the following line in the dependencies section,

The pubspec.yaml file is indented specifically, so be sure to follow proper indentation in the file.

The dependency section should look something like,

With this, we have successfully included the sqflite package in our Flutter project and can proceed ahead to make use of SQLite to persist data in our Flutter application.

CRUD Operations in SQLite

The abbreviation CRUD stands for

• Create
• Read
• Update
• Delete

These four are the fundamental operations that take place in a Database. Before we begin understanding how to use the CRUD operations in SQLite with Flutter, let's first take a look at how we create a database and tables in it.

Making a Database and Table

database_helper.dart:

The first step is to create a file called databasehelper.dart. Inside this file, we shall then create our database and tables. You could use the following code for the file,

Opening a Database

Sometimes it may happen that you may already have a SQLite database on your system that you want to access in your application. You could in that case refer to the following code to bundle and open a pre-existing SQLite database inside your Flutter app,

Now that we have understood how to make a Database or open and use an existing database of SQLite in Flutter, Let's move onwards to the CRUD operations on the same.

Create Operation

The Create operation in SQLite also refers to adding a new entry to the table. This operation is majorly used for the latter reason ie, to add a new entry to a table.

The way to do it is,

Read Operation

The Read operation retrieves items from the SQLite database. It gets data from the table based on the conditions passed.

Update Operation

The update operation changes the value of an entry in the database or edits a specified field in the SQLite database.

Delete Operation

The Delete operation is used to delete an entry from the table of a SQLite database. You could refer to the following code to understand how the delete operation works,

Using SQLite in Flutter Application

With SQLite in Flutter, developers can easily implement data-driven features, such as user profiles, caching mechanisms, and more. The simplicity of integrating SQLite in Flutter allows developers to focus on building innovative features while ensuring efficient data management behind the scenes. Once we understand how to run SQL queries for SQLite in Flutter, let's move on to see how to use this SQLite database in your Flutter application.

Querying from a database is an asynchronous action and thus, as stated in the documentation, to perform an asynchronous computation, you use an async function which always produces a Future.

Hence, to retrieve and display data from a database in Flutter, we shall be using the FutureBuilder Widget in Flutter.

For example, while you are retrieving data from the database, display a circular progress indicator on the screen to let the user know that data is being loaded or fetched, this drastically improves the user experience and is not even complex to develop.

The retrieved data can be then displayed on the screen using various Widgets. If the entire data is to be displayed then the preferred Widget is the ListView due to its builder function.

Advanced SQLite Topics

Data Migration

Data migration or database migration is the process of transferring data from one storage system or computing system to another. Database migration is a very complex topic, more so when you need to perform it in embedded applications such as mobile phones and tablets. Thanks to the sqflite_migration package, the database migration becomes very easy and simplifies the entire process as we do not have to deal with index manipulation and script execution ourselves. You just have to use the openDatabaseWithMigration method and pass the migration configuration.

Transactions

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether manually by a user or automatically by some sort of database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating, updating, or deleting a record from the table, then you are performing a transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

Practically, you will club many SQLite queries into a group and you will execute all of them together as part of a transaction.

Backups

While using the sqflite package in Flutter for the local database, there are scenarios where we may need to back up and restore the database. As an example, suppose the user wants to uninstall your app (which is functioning in the sqflite package) and install it on a newer device. In doing so he even wants to somehow use the database stored in the previous device. Simply saying he wants an export/import DB feature in the app which makes his work quite very easy. This result can be achieved by using the permission_handler package in Flutter. Make sure to properly include the package in your project since it has a few installation steps in it. You can read those steps and how to set up over here.

Now to import and export our database we can use the following code,

Copy:

Delete:

Restore:

If you have diligently followed everything written till this point in the article, you have already understood and learned how to persist data in your Flutter application using the sqflite package.

Example App

Various apps require the use of local storage to persist their data. Whether you're building a simple note-taking app or a sophisticated data-driven application, leveraging SQLite in Flutter provides a reliable foundation for managing and querying data. The seamless integration of SQLite in Flutter enables developers to create apps that can seamlessly synchronize data between local storage and remote servers. Let's have a look at one such application.

We shall be considering a simple To-Do application in this scenario.

You can refer to the complete project over here link

Let's hop onto the code with our main.dart file,

This is the database_helper.dart file that we have used in the application which includes functions to

• Create a Database and table to store tasks
• Insert Tasks into the Database
• Update a given task
• Insert a To-Do in the given task
• Retrieve Tasks and To-Do's from the database
• Delete Tasks

Let's make a model file for our Task. The model file helps with structuring and working on our tasks. These model files help us visualize and align our databse structure to that of our app's components.

Similarly, let's make a model file for our To-Do.

This is how our Homescreen shall look

And this is the code for our HomePage Screen.

And this is how the Task Screen will be.

And this is the code for the TaskPage screen