Flutter - Database Concepts

Flutter provides many advanced packages to work with databases. The most important packages are −

sqfpte − Used to access and manipulate SQLite database, and

firebase_database − Used to access and manipulate cloud hosted NoSQL database from Google.

In this chapter, let us discuss each of them in detail.

SQLite

SQLite database is the de-facto and standard SQL based embedded database engine. It is small and time-tested database engine. sqfpte package provides a lot of functionapty to work efficiently with SQLite database. It provides standard methods to manipulate SQLite database engine. The core functionapty provided by sqfpte package is as follows −

Create / Open (openDatabase method) a SQLite database.

Execute SQL statement (execute method) against SQLite database.

Advanced query methods (query method) to reduce to code required to query and get information from SQLite database.

Let us create a product apppcation to store and fetch product information from a standard SQLite database engine using sqfpte package and understand the concept behind the SQLite database and sqfpte package.

Create a new Flutter apppcation in Android studio, product_sqpte_app.

Replace the default startup code (main.dart) with our product_rest_app code.

Copy the assets folder from product_nav_app to product_rest_app and add assets inside the *pubspec.yaml` file.

flutter: 
   assets: 
      - assets/appimages/floppy.png 
      - assets/appimages/iphone.png 
      - assets/appimages/laptop.png 
      - assets/appimages/pendrive.png 
      - assets/appimages/pixel.png 
      - assets/appimages/tablet.png

Configure sqfpte package in the pubspec.yaml file as shown below −

dependencies: sqfpte: any

Use the latest version number of sqfpte in place of any

Configure path_provider package in the pubspec.yaml file as shown below −

dependencies: path_provider: any

Here, path_provider package is used to get temporary folder path of the system and path of the apppcation. Use the latest version number of sqfpte in place of any.

Android studio will alert that the pubspec.yaml is updated.

Cpck Get dependencies option. Android studio will get the package from Internet and properly configure it for the apppcation.

In database, we need primary key, id as additional field along with Product properties pke name, price, etc., So, add id property in the Product class. Also, add a new method, toMap to convert product object into Map object. fromMap and toMap are used to seriapze and de- seriapze the Product object and it is used in database manipulation methods.

class Product { 
   final int id; 
   final String name; 
   final String description; 
   final int price; 
   final String image; 
   static final columns = ["id", "name", "description", "price", "image"]; 
   Product(this.id, this.name, this.description, this.price, this.image); 
   factory Product.fromMap(Map<String, dynamic> data) {
      return Product( 
         data[ id ], 
         data[ name ], 
         data[ description ], 
         data[ price ], 
         data[ image ], 
      ); 
   } 
   Map<String, dynamic> toMap() => {
      "id": id, 
      "name": name, 
      "description": description, 
      "price": price, 
      "image": image 
   }; 
}

Create a new file, Database.dart in the pb folder to write SQLite related functionapty.

Import necessary import statement in Database.dart.

import  dart:async ; 
import  dart:io ; 
import  package:path/path.dart ; 
import  package:path_provider/path_provider.dart ; 
import  package:sqfpte/sqfpte.dart ; 
import  Product.dart ;

Note the following points here −

async is used to write asynchronous methods.

io is used to access files and directories.

path is used to access dart core utipty function related to file paths.

path_provider is used to get temporary and apppcation path.

sqfpte is used to manipulate SQLite database.

Create a new class SQLiteDbProvider

Declare a singleton based, static SQLiteDbProvider object as specified below −

class SQLiteDbProvider { 
   SQLiteDbProvider._(); 
   static final SQLiteDbProvider db = SQLiteDbProvider._(); 
   static Database _database; 
}

SQLiteDBProvoider object and its method can be accessed through the static db variable.

SQLiteDBProvoider.db.<emthod>

Create a method to get database (Future option) of type Future<Database>. Create product table and load initial data during the creation of the database itself.

Future<Database> get database async { 
   if (_database != null) 
   return _database; 
   _database = await initDB(); 
   return _database; 
}
initDB() async { 
   Directory documentsDirectory = await getApppcationDocumentsDirectory(); 
   String path = join(documentsDirectory.path, "ProductDB.db"); 
   return await openDatabase(
      path, 
      version: 1,
      onOpen: (db) {}, 
      onCreate: (Database db, int version) async {
         await db.execute(
            "CREATE TABLE Product ("
            "id INTEGER PRIMARY KEY,"
            "name TEXT,"
            "description TEXT,"
            "price INTEGER," 
            "image TEXT" ")"
         ); 
         await db.execute(
            "INSERT INTO Product ( id ,  name ,  description ,  price ,  image ) 
            values (?, ?, ?, ?, ?)", 
            [1, "iPhone", "iPhone is the stypst phone ever", 1000, "iphone.png"]
         ); 
         await db.execute(
            "INSERT INTO Product ( id ,  name ,  description ,  price ,  image ) 
            values (?, ?, ?, ?, ?)", 
            [2, "Pixel", "Pixel is the most feature phone ever", 800, "pixel.png"]
         ); 
         await db.execute(
            "INSERT INTO Product ( id ,  name ,  description ,  price ,  image ) 
            values (?, ?, ?, ?, ?)", 
            [3, "Laptop", "Laptop is most productive development tool", 2000, "laptop.png"]
         ); 
         await db.execute( 
            "INSERT INTO Product ( id ,  name ,  description ,  price ,  image ) 
            values (?, ?, ?, ?, ?)", 
            [4, "Tablet", "Laptop is most productive development tool", 1500, "tablet.png"]
         );
         await db.execute( 
            "INSERT INTO Product 
            ( id ,  name ,  description ,  price ,  image ) 
            values (?, ?, ?, ?, ?)", 
            [5, "Pendrive", "Pendrive is useful storage medium", 100, "pendrive.png"]
         );
         await db.execute( 
            "INSERT INTO Product 
            ( id ,  name ,  description ,  price ,  image ) 
            values (?, ?, ?, ?, ?)", 
            [6, "Floppy Drive", "Floppy drive is useful rescue storage medium", 20, "floppy.png"]
         ); 
      }
   ); 
}

Here, we have used the following methods −

getApppcationDocumentsDirectory − Returns apppcation directory path

join − Used to create system specific path. We have used it to create database path.

openDatabase − Used to open a SQLite database

onOpen − Used to write code while opening a database

onCreate − Used to write code while a database is created for the first time

db.execute − Used to execute SQL queries. It accepts a query. If the query has placeholder (?), then it accepts values as pst in the second argument.

Write a method to get all products in the database −

Future<List<Product>> getAllProducts() async { 
   final db = await database; 
   List<Map> 
   results = await db.query("Product", columns: Product.columns, orderBy: "id ASC"); 
   
   List<Product> products = new List(); 
   results.forEach((result) { 
      Product product = Product.fromMap(result); 
      products.add(product); 
   }); 
   return products; 
}

Here, we have done the following −

Used query method to fetch all the product information. query provides shortcut to query a table information without writing the entire query. query method will generate the proper query itself by using our input pke columns, orderBy, etc.,

Used Product’s fromMap method to get product details by looping the results object, which holds all the rows in the table.

Write a method to get product specific to id

Future<Product> getProductById(int id) async {
   final db = await database; 
   var result = await db.query("Product", where: "id = ", whereArgs: [id]); 
   return result.isNotEmpty ? Product.fromMap(result.first) : Null; 
}

Here, we have used where and whereArgs to apply filters.

Create three methods - insert, update and delete method to insert, update and delete product from the database.

insert(Product product) async { 
   final db = await database; 
   var maxIdResult = await db.rawQuery(
      "SELECT MAX(id)+1 as last_inserted_id FROM Product");

   var id = maxIdResult.first["last_inserted_id"]; 
   var result = await db.rawInsert(
      "INSERT Into Product (id, name, description, price, image)" 
      " VALUES (?, ?, ?, ?, ?)", 
      [id, product.name, product.description, product.price, product.image] 
   ); 
   return result; 
}
update(Product product) async { 
   final db = await database; 
   var result = await db.update("Product", product.toMap(), 
   where: "id = ?", whereArgs: [product.id]); return result; 
} 
delete(int id) async { 
   final db = await database; 
   db.delete("Product", where: "id = ?", whereArgs: [id]); 
}

The final code of the Database.dart is as follows −

import  dart:async ; 
import  dart:io ; 
import  package:path/path.dart ; 
import  package:path_provider/path_provider.dart ; 
import  package:sqfpte/sqfpte.dart ; 
import  Product.dart ; 

class SQLiteDbProvider {
   SQLiteDbProvider._(); 
   static final SQLiteDbProvider db = SQLiteDbProvider._(); 
   static Database _database; 
   
   Future<Database> get database async {
      if (_database != null) 
      return _database; 
      _database = await initDB(); 
      return _database; 
   } 
   initDB() async {
      Directory documentsDirectory = await 
      getApppcationDocumentsDirectory(); 
      String path = join(documentsDirectory.path, "ProductDB.db"); 
      return await openDatabase(
         path, version: 1, 
         onOpen: (db) {}, 
         onCreate: (Database db, int version) async {
            await db.execute(
               "CREATE TABLE Product (" 
               "id INTEGER PRIMARY KEY," 
               "name TEXT," 
               "description TEXT," 
               "price INTEGER," 
               "image TEXT"")"
            ); 
            await db.execute(
               "INSERT INTO Product ( id ,  name ,  description ,  price ,  image ) 
               values (?, ?, ?, ?, ?)", 
               [1, "iPhone", "iPhone is the stypst phone ever", 1000, "iphone.png"]
            ); 
            await db.execute( 
               "INSERT INTO Product ( id ,  name ,  description ,  price ,  image ) 
               values (?, ?, ?, ?, ?)", 
               [2, "Pixel", "Pixel is the most feature phone ever", 800, "pixel.png"]
            );
            await db.execute(
               "INSERT INTO Product ( id ,  name ,  description ,  price ,  image ) 
               values (?, ?, ?, ?, ?)", 
               [3, "Laptop", "Laptop is most productive development tool", 2000, "laptop.png"]
            ); 
            await db.execute( 
               "INSERT INTO Product ( id ,  name ,  description ,  price ,  image ) 
               values (?, ?, ?, ?, ?)", 
               [4, "Tablet", "Laptop is most productive development tool", 1500, "tablet.png"]
            ); 
            await db.execute( 
               "INSERT INTO Product ( id ,  name ,  description ,  price ,  image ) 
               values (?, ?, ?, ?, ?)", 
               [5, "Pendrive", "Pendrive is useful storage medium", 100, "pendrive.png"]
            );
            await db.execute( 
               "INSERT INTO Product ( id ,  name ,  description ,  price ,  image ) 
               values (?, ?, ?, ?, ?)", 
               [6, "Floppy Drive", "Floppy drive is useful rescue storage medium", 20, "floppy.png"]
            ); 
         }
      ); 
   }
   Future<List<Product>> getAllProducts() async {
      final db = await database; 
      List<Map> results = await db.query(
         "Product", columns: Product.columns, orderBy: "id ASC"
      ); 
      List<Product> products = new List();   
      results.forEach((result) {
         Product product = Product.fromMap(result); 
         products.add(product); 
      }); 
      return products; 
   } 
   Future<Product> getProductById(int id) async {
      final db = await database; 
      var result = await db.query("Product", where: "id = ", whereArgs: [id]); 
      return result.isNotEmpty ? Product.fromMap(result.first) : Null; 
   } 
   insert(Product product) async { 
      final db = await database; 
      var maxIdResult = await db.rawQuery("SELECT MAX(id)+1 as last_inserted_id FROM Product"); 
      var id = maxIdResult.first["last_inserted_id"]; 
      var result = await db.rawInsert(
         "INSERT Into Product (id, name, description, price, image)" 
         " VALUES (?, ?, ?, ?, ?)", 
         [id, product.name, product.description, product.price, product.image] 
      ); 
      return result; 
   } 
   update(Product product) async { 
      final db = await database; 
      var result = await db.update(
         "Product", product.toMap(), where: "id = ?", whereArgs: [product.id]
      ); 
      return result; 
   } 
   delete(int id) async { 
      final db = await database; 
      db.delete("Product", where: "id = ?", whereArgs: [id]);
   } 
}

Change the main method to get the product information.

void main() {
   runApp(MyApp(products: SQLiteDbProvider.db.getAllProducts())); 
}

Here, we have used the getAllProducts method to fetch all products from the database.

Run the apppcation and see the results. It will be similar to previous example, Accessing Product service API, except the product information is stored and fetched from the local SQLite database.

Cloud Firestore

Firebase is a BaaS app development platform. It provides many feature to speed up the mobile apppcation development pke authentication service, cloud storage, etc., One of the main feature of Firebase is Cloud Firestore, a cloud based real time NoSQL database.

Flutter provides a special package, cloud_firestore to program with Cloud Firestore. Let us create an onpne product store in the Cloud Firestore and create a apppcation to access the product store.

Create a new Flutter apppcation in Android studio, product_firebase_app.

Replace the default startup code (main.dart) with our product_rest_app code.

Copy Product.dart file from product_rest_app into the pb folder.

class Product { 
   final String name; 
   final String description; 
   final int price; 
   final String image; 
   
   Product(this.name, this.description, this.price, this.image); 
   factory Product.fromMap(Map<String, dynamic> json) {
      return Product( 
         json[ name ], 
         json[ description ], 
         json[ price ], 
         json[ image ], 
      ); 
   }
}

Copy the assets folder from product_rest_app to product_firebase_app and add assets inside the pubspec.yaml file.

flutter:
   assets: 
   - assets/appimages/floppy.png 
   - assets/appimages/iphone.png 
   - assets/appimages/laptop.png 
   - assets/appimages/pendrive.png 
   - assets/appimages/pixel.png 
   - assets/appimages/tablet.png

Configure cloud_firestore package in the pubspec.yaml file as shown below −

dependencies: cloud_firestore: ^0.9.13+1

Here, use the latest version of the cloud_firestore package.

Android studio will alert that the pubspec.yaml is updated as shown here −

Cpck Get dependencies option. Android studio will get the package from Internet and properly configure it for the apppcation.

Create a project in the Firebase using the following steps −

Create a Firebase account by selecting Free plan at https://firebase.google.com/pricing/.

Once Firebase account is created, it will redirect to the project overview page. It pst all the Firebase based project and provides an option to create a new project.

Cpck Add project and it will open a project creation page.

Enter products app db as project name and cpck Create project option.

Go to *Firebase console.

Cpck Project overview. It opens the project overview page.

Cpck android icon. It will open project setting specific to Android development.

Enter Android Package name, com.tutorialspoint.flutterapp.product_firebase_app.

Cpck Register App. It generates a project configuration file, google_service.json.

Download google_service.json and then move it into the project’s android/app directory. This file is the connection between our apppcation and Firebase.

Open android/app/build.gradle and include the following code −

apply plugin:  com.google.gms.google-services 

Open android/build.gradle and include the following configuration −

buildscript {
   repositories { 
      // ... 
   } 
   dependencies { 
      // ... 
      classpath  com.google.gms:google-services:3.2.1  // new 
   } 
}

Here, the plugin and class path are used for the purpose of reading google_service.json file.

Open android/app/build.gradle and include the following code as well.

android {
   defaultConfig { 
      ... 
      multiDexEnabled true 
   } 
   ...
}
dependencies {
   ... 
   compile  com.android.support: multidex:1.0.3  
}

This dependency enables the android apppcation to use multiple dex functionapty.

Follow the remaining steps in the Firebase Console or just skip it.

Create a product store in the newly created project using the following steps −

Go to Firebase console.

Open the newly created project.

Cpck the Database option in the left menu.

Cpck Create database option.

Cpck Start in test mode and then Enable.

Cpck Add collection. Enter product as collection name and then cpck Next.

Enter the sample product information as shown in the image here −

Add addition product information using Add document options.

Open main.dart file and import Cloud Firestore plugin file and remove http package.

import  package:cloud_firestore/cloud_firestore.dart ;

Remove parseProducts and update fetchProducts to fetch products from Cloud Firestore instead of Product service API.

Stream<QuerySnapshot> fetchProducts() { 
   return Firestore.instance.collection( product ).snapshots(); }

Here, Firestore.instance.collection method is used to access product collection available in the cloud store. Firestore.instance.collection provides many option to filter the collection to get the necessary documents. But, we have not appped any filter to get all product information.

Cloud Firestore provides the collection through Dart Stream concept and so modify the products type in MyApp and MyHomePage widget from Future<pst<Product>> to Stream<QuerySnapshot>.

Change the build method of MyHomePage widget to use StreamBuilder instead of FutureBuilder.

@override 
Widget build(BuildContext context) {
   return Scaffold(
      appBar: AppBar(title: Text("Product Navigation")), 
      body: Center(
         child: StreamBuilder<QuerySnapshot>(
            stream: products, builder: (context, snapshot) {
               if (snapshot.hasError) print(snapshot.error); 
               if(snapshot.hasData) {
                  List<DocumentSnapshot> 
                  documents = snapshot.data.documents; 
                  
                  List<Product> 
                  items = List<Product>(); 
                  
                  for(var i = 0; i < documents.length; i++) { 
                     DocumentSnapshot document = documents[i]; 
                     items.add(Product.fromMap(document.data)); 
                  } 
                  return ProductBoxList(items: items);
               } else { 
                  return Center(child: CircularProgressIndicator()); 
               }
            }, 
         ), 
      )
   ); 
}

Here, we have fetched the product information as List<DocumentSnapshot> type. Since, our widget, ProductBoxList is not compatible with documents, we have converted the documents into List<Product> type and further used it.

Finally, run the apppcation and see the result. Since, we have used the same product information as that of SQLite apppcation and changed the storage medium only, the resulting apppcation looks identical to SQLite apppcation apppcation.