SQFlite to manage a database with SQLite in Flutter

A database is one of the main elements shared by applications today, in practically all applications today, there is always data that is persistently recorded; when you want to store structured data persistently in an application, the first thing that comes to mind is to use a database.

On Android, the default database used is SQLite; which is nothing more than a file where all our structured records are stored.

SQLite is a small, fast, self-contained, highly reliable, fully featured SQL database engine. SQLite is the most used database engine in the world. SQLite is built into every mobile phone and most computers and comes bundled with countless other applications that people use every day.

In Flutter, we have several options to store data persistently, and we can even use SQLite for this purpose; although, unlike native Android, Flutter does not natively support SQLite, otherwise we have to use a plugin with which we can take advantage of this feature; it is called as SQFlite which enables the use of an SQLite database in Flutter:

https://pub.dev/packages/sqflite

To install it, we do it through a pub:

pubspec.yaml

dependencies:
 flutter:
   sdk: flutter
 sqflite:

Basic operation

SQFlite is very easy to use, as you can suppose, when using a database in SQLite, which is nothing more than a file, a file that is stored in a folder of our application, all the operations that you want to perform on it , which would be, create or read a database, create, modify, get or delete records, all these operations are asynchronous, therefore, we will have to use the Future for each of these operations.

Create and open a database

Para poder crear o abrir una base de datos, usamos la función de:

openDatabase()

Which receives a path in which we must specify the path to the database; therefore, we have two factors here:

The path where the database is saved.
The name of the database.

To get the database path, we also have a helper function which returns the default path used to register the database:

getDatabasesPath()

Finally, the code to create the database is:

lib\helpers\db_helper.dart

import 'package:place/models/place.dart';
import 'package:sqflite/sqflite.dart';

import 'package:path/path.dart' as path;

class DBHelper {
 static Future<Database> _openDB() async {
   return openDatabase(path.join(await getDatabasesPath(), 'sites.db'),
       onCreate: (db, version) {
     return db.execute(
         "CREATE TABLE places (id INTEGER PRIMARY KEY, name TEXT, image TEXT)");
   }, version: 1);
 }
}

We are going to know the CRUD type operations that we can perform on a SQFlite database.

Insert records

To insert records, in the database, the insert() function is used, which receives two parameters:

The table where you want to insert the records.
The map, with the data that you want to insert.

As for the map, as you can guess, the function called toMap() defined in the Place model is going to be used.

Remember that, to do any operation on the database, it is necessary to open it, and for that, the _openDB() function created previously is used.

Finally, the code for the insert function:

lib\helpers\db_helper.dart

import 'package:place/models/place.dart';
import 'package:sqflite/sqflite.dart';

import 'package:path/path.dart' as path;

class DBHelper {
 static Future<Database> _openDB() async {
  // ***
 }

 static Future<int> insert(Place place) async {
   Database database = await _openDB();
   return database.insert("places", place.toMap());
 }
}

Get the list of all records

To obtain a list of all the records, the function called query() is used, in which the name of the table is indicated; this function returns a list of maps, which is converted to a list of objects, using the List.generate() function, in which each of the records returned in a map is iterated and converted to an object:

lib\helpers\db_helper.dart

import 'package:place/models/place.dart';
import 'package:sqflite/sqflite.dart';

import 'package:path/path.dart' as path;

class DBHelper {
 static Future<Database> _openDB() async {
  // ***
 }

 static Future<int> insert(Place place) async {
  // ***
 }

 static Future<List<Place>> places() async {
   Database database = await _openDB();

   final List<Map<String, dynamic>> placesMap = await database.query("places");

   for (var p in placesMap) {
     print("${p['id']}  ${p['name']} ");
   }

   return List.generate(
       placesMap.length,
       (i) => Place(
           id: placesMap[i]['id'],
           name: placesMap[i]['name'],
           image: placesMap[i]['image']));
 }
}

Update records

The update function is similar to the insert function; but, the where clause is defined to filter the records that you want to update; in this particular case, it would be a single record looking for the site id:
where, specify the conditions or condition by which you want to filter, indicating the column, operator and value.
whereArgs, specifies the values or value to pass in the where parameter, these are typical schemes, to separate the condition from the data, to help prevent "SQL attack".

import 'package:place/models/place.dart';
import 'package:sqflite/sqflite.dart';

import 'package:path/path.dart' as path;

class DBHelper {
 static Future<Database> _openDB() async {
  // ***
 }

 static Future<int> insert(Place place) async {
  // ***
 }

 static Future<List<Place>> places() async {
   // ***
 }

 static Future<int> update(Place place) async {
   Database database = await _openDB();
   return database.update("places", place.toMap(),
       where: 'id = ?', whereArgs: [place.id]);
 } 
}

Delete records

To delete records, the same logic is applied as to update, indicating the where query, but, in this case, the data to create or update is not specified:

import 'package:place/models/place.dart';
import 'package:sqflite/sqflite.dart';

import 'package:path/path.dart' as path;

class DBHelper {
 static Future<Database> _openDB() async {
  // ***
 }

 static Future<int> insert(Place place) async {
  // ***
 }

 static Future<List<Place>> places() async {
   // ***
 }

 static Future<int> update(Place place) async {
   // ***
 }

 static Future<int> delete(Place place) async {
   Database database = await _openDB();
   return database.delete("places", where: 'id = ?', whereArgs: [place.id]);
 }
}

The use of all the functions with which operations are going to be carried out in the database, are of the asynchronous type, with which there is no need to create instances of this class.

Remember that this material is part of my complete course on Flutter.

Develop with Laravel, Django, Flask, CodeIgniter, HTML5, CSS3, MySQL, JavaScript, Vue, Android, iOS, Flutter

Andrés Cruz In Udemy