Flutter Local Databases : SQLite

There are two schools of thought about how to pronounce SQLite: "Ess-Cue-El- Ight" or "See-Quel-Light". The creator of SQLite, Richard Hipp, generally uses the first one, but he also says that you can pronounce it however you want, and adds that there's no "official" pronunciation.

According to the official site (SQLite.org), SQLite is a “small, fast, self-contained, highreliability, full-featured, SQL database engine”.

There is no doubt that flutter will provide us with a plugin to make use of it , it’s called sqflite with more than 1300 likes in pubdev this package will provide us with the basics of using sqflite to insert, read, update, and remove data .

The database that we will create has two tables: Workshops and Trainings .. This can be seen in the following screenshot:

• The Workshops table has three fields: id (integer), name (text), and priority (integer).
• The items table has an id (integer), a name (text), a trainer (text), a note (integer) and an idWorkshop (integer) that will be a foreign key constraint that points to the id of the list. As you can see, the schema is very simple, but it will allow us to experiment with many of the features that are needed in order to build a database app.

Project Overview :

Flutter_Workshop_v6

Using sqflite databases :

In this section, we’ll create a new project, add the sqflite dependencies, and create our database through a SQL raw query. Then, we will test the database we’ve created by adding some mock data and printing it in the debug console. This will require a few methods to insert and retrieve data from the database.

Creating an sqflite database :

Let’s create a new Flutter project from your editor. We can call it workshops. Follow the steps given here:

1. As sqflite is a package, the first step to perform in order to be able to use it in our project is adding the dependency in the pubspec.yaml file. In order to find the latest version of the dependency, please visit https:/ / pub. dev/ packages/ sqflite. The dependencies that we are going to use in this project are shown in the following code block:

dependencies:

flutter:

sdk: flutter

sqflite: ^1.3.2+3

path: ^1.7.0

1. In the lib folder, create a subfolder called util. Here, we’ll create a new file: dbhelper.dart. This file will contain the methods to create the database, and to retrieve and write data.
2. At the top of the file, we’ll import sqflite.dart and path.dart. path.dart is a library that allows you to manipulate file paths. This is useful here, as each platform (iOS or Android) saves the file in different paths. By using the path.dart library, we don’t need to know how files are saved in the current operating system, and we can still access the database using the same code. Import the path and sqflite libraries at the top of the dbhelper.dart file, as follows: import ‘package:path/path.dart’; import ‘package:sqflite/sqflite.dart’;
3. Create a class that can be called from other parts of our code: quite predictably, we can call it DbHelper .. Inside the class, create two variables: an integer called version and a database called db. the version contains a number that represents the version of the database, which at the beginning is 1. This will make it easier to update the database when you need to change something in its structure. db will contain the SQLite database itself. Place the two declarations at the top of the DbHelper class, it will look like this:

With every change for the database you must increase the version so your changes will be perfectly working !

4. Now we have to create our first method that will open the database if it exists , or create it if it doesn’t , we will call it openDb .. The operations that we will perform will take some time to execute especially when dealing with a large amount of data , that’s why we will make it asynchronous .. for the openDb it will return Future of type Database ..

Inside the function, first, we need to check whether the db object is null. This is because we want to avoid opening a new instance of the database unnecessarily. if db is null, we need to open the database. The sqflite library has an open Database method. We’ll set three parameters in our call: the path of the database to be opened, the version of the database, and the onCreate parameter. The onCreate parameter will only be called if the database at the path specified is not found, or the version is different. The code for this is shown in the following block:

The function inside the onCreate parameter takes two values: a database and a version. In the function, we call the execute() method, which performs raw SQL queries in a database. Here, we are calling it twice: the first time to create the workshops table, and the second time for the trainings table. You may notice that we are using only two data types: INTEGER and TEXT.

In SQLite, there are only five data types: NULL, INTEGER, REAL, TEXT, and BLOB. Note that there are no Boolean or Date data types.

Creating the model classes

Let’s take a few moments to define the data that needs to be stored. For this example, we’ll just have to create two model classes, containing the same fields that are now in the tables, and a map method to simplify the process of inserting and editing data into the database. Follow these next steps to create a model class:

• We’ll start by creating a models folders inside the lib folder
• Next we will create two files : workshops_list.dart and trainings.dart
• For each one we’ll define its properties , create a constructor and finally a toMap() method that will return a Map of type String, dynamic. A map is a collection of key/value pairs: the first type we specify is for the key, which in this case will always be a string. The second type is for the value: as we have different types in the table, this will be dynamic.

Now, in the DbHelper class, we need to create two methods that will make use of the model classes to insert data into the database.

The insert() method allows you to specify the following parameters: * The name of the table where we want to insert data — lists, in this case. *A Map of the data that we want to insert: in order to get that, we’ll call our toMap() function of the list parameter. *Optionally, the conflictAlgorithm specifies the behavior that should be followed when you try to insert a record with the same ID twice. In this case, if the same list is inserted multiple times, it will replace the previous data with the new list that was passed to the function .

Adding data to the database

Now from our main.dart file we will test the two methods that we recently created , We create a function called showData that will open the database

the code will look like :

Showing database data to the user

After adding the data to our database , now it’s time to use that data and show it to our user , The workflow will be the next : We will show a list of Workshops (ListView) , and if the user taps on any item , we’ll navigate to a second screen where we’ll get all the trainings of that workshop , Hope it’s clear ..

So first things first we will add a get method in the DbHelper class :

Note that the query() helper method returns a List of Map items. In order to use them easily, we need to convert the List<Map<String, dynamic> into a List<WorkshopsList>. We can do that by calling the List.generate() method, which you can use to generate a list of values. The first parameter specifies the size of the list, and the second is a function that generates the values of the list.

Inserting and editing data

Now we will add a new file called workshops_list_dialog.dart in which we are going to show our user a dialog window that allows them to insert or edit a Workshopslist

Now let’s update our main.dart file by adding a new Fab (Floating action button) and by clicking it we show the dialog that we created :

Deleting Data

One of the touch gestures that has won a wide adoption over time in mobile apps is the “swipe-to-delete” gesture, in which you simply drag a finger across an item, and swipe it left — or in some cases, also right. This was introduced by Apple in the Mail app, and today it’s widely spread in both iOS and Android systems.

So our first step as we did in the other CRUD operations is to create a method in the DBHelper class :

Now that the deleteList() method is complete, we can call it when the user swipes an item on the main screen. There’s a very useful widget in Flutter that’s perfect when you want to use this pattern to delete an item: it’s called Dismissible.

So we’ll wrap the InkWell with the following code :

Take some time to go over the code . Remember, it’s an opportunity to pull your thoughts together . It’s the cream cheese frosting to that red velvet .

Since we talk about some packages in this article and their powerful contribution in our app , the next writing will tackel some of the most useful flutter packages .cupcake you just baked.

Written by Ahmed Klai (vice president of Insat Android Club)