Offline Database from WebService using SQFlite in Flutter

We are going to create an Offline Database using data from a Webservice.

Watch Video Tutorial

We are going to use the below service for that.

https://jsonplaceholder.typicode.com/photos

This service has about 5000 records, we are going to parse these records and insert all the records into the Offline SQLite database.

---

Generate JSON Models

To create json models, make sure you follow my previous post or you can watch my Youtube Video below.

---

Our model names will be ‘album’ and ‘albums’.

album will have the below record

{
    "albumId": 1,
    "id": 1,
    "title": "accusamus beatae ad facilis cum similique qui sunt",
    "url": "https://via.placeholder.com/600/92c952",
    "thumbnailUrl": "https://via.placeholder.com/150/92c952"
}

and albums will be list of albums.

Once we have the generated Json Models, we will Create the Offline Database and do the CRUD Operations.

---

CRUD Operations

Create a file named “DBHelper.dart” and initialize the database.

import 'dart:async';
import 'dart:io' as io;
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import '../../models/album.dart';
import '../../models/albums.dart';

class DBHelper {
  static Database _db;
  // Create the Table colums
  static const String TABLE = 'albums';
  static const String ALBUM_ID = 'albumId';
  static const String ID = 'id';
  static const String TITLE = 'title';
  static const String URL = 'url';
  static const String THUMBNAIL_URL = 'thumbnailUrl';
  static const String DB_NAME = 'albums.db';

  // Initialize the Database
  Future<Database> get db async {
    if (null != _db) {
      return _db;
    }
    _db = await initDb();
    return _db;
  }

  initDb() async {
    // Get the Device's Documents directory to store the Offline Database...
    io.Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, DB_NAME);
    var db = await openDatabase(path, version: 1, onCreate: _onCreate);
    return db;
  }

  _onCreate(Database db, int version) async {
    // Create the DB Table
    await db.execute(
        "CREATE TABLE $TABLE ($ID INTEGER PRIMARY KEY, $ALBUM_ID TEXT, $TITLE TEXT, $URL TEXT, $THUMBNAIL_URL TEXT)");
  }

  // Method to insert the Album record to the Database
  Future<Album> save(Album album) async {
    var dbClient = await db;
    // this will insert the Album object to the DB after converting it to a json
    album.id = await dbClient.insert(TABLE, album.toJson());
    return album;
  }

  // Method to return all Albums from the DB
  Future<Albums> getAlbums() async {
    var dbClient = await db;
    // specify the column names you want in the result set
    List<Map> maps =
        await dbClient.query(TABLE, columns: [ID, TITLE, URL, THUMBNAIL_URL]);
    Albums allAlbums = Albums();
    List<Album> albums = [];
    if (maps.length > 0) {
      for (int i = 0; i < maps.length; i++) {
        albums.add(Album.fromJson(maps[i]));
      }
    }
    allAlbums.albums = albums;
    return allAlbums;
  }

  // Method to delete an Album from the Database
  Future<int> delete(int id) async {
    var dbClient = await db;
    return await dbClient.delete(TABLE, where: '$ID = ?', whereArgs: [id]);
  }

  // Method to Update an Album in the Database
  Future<int> update(Album album) async {
    var dbClient = await db;
    return await dbClient
        .update(TABLE, album.toJson(), where: '$ID = ?', whereArgs: [album.id]);
  }

  // Method to Truncate the Table
  Future<void> truncateTable() async {
    var dbClient = await db;
    return await dbClient.delete(TABLE);
  }

  // Method to Close the Database
  Future close() async {
    var dbClient = await db;
    dbClient.close();
  }
}

---

Service

Now we will write the service to parse the data.

import 'dart:convert';
import 'package:http/http.dart' as http;
import '../../models/album.dart';
import '../../models/albums.dart';

class Services {
  //
  static List<Album> albums;
  static const String url = 'https://jsonplaceholder.typicode.com/photos';

  static Future<Albums> getPhotos() async {
    try {
      final response = await http.get(url);
      if (200 == response.statusCode) {
        Albums albums = parsePhotos(response.body);
        return albums;
      } else {
        Albums albums = new Albums();
        albums.albums = [];
        return albums; // we are returning empty album list
        // Handle these as you want...
      }
    } catch (e) {
      Albums albums = new Albums();
      albums.albums = [];
      return albums;
    }
  }

  static Albums parsePhotos(String responseBody) {
    final parsed = json.decode(responseBody).cast<Map<String, dynamic>>();
    List<Album> albums =
        parsed.map<Album>((json) => Album.fromJson(json)).toList();
    Albums a = new Albums();
    a.albums = albums;
    return a;
  }
}

---

Show Data in a GridView

First we will create the cell for the GridView.

Create a file named “GridCell.dart” and copy the below code.

import 'package:flutter/material.dart';
import '../../models/album.dart';

class AlbumCell extends StatelessWidget {
  // Pass the Update and Delete Function as Constructor Parameter
  const AlbumCell(this.album, this.updateFunction, this.deleteFunction);

  @required
  final Album album;
  final Function updateFunction;
  final Function deleteFunction;

  @override
  Widget build(BuildContext context) {
    return Card(
      shape: RoundedRectangleBorder(
        borderRadius: BorderRadius.circular(10.0),
      ),
      color: Colors.white,
      child: Padding(
        padding: EdgeInsets.all(0.0),
        child: Container(
          decoration: BoxDecoration(
            image: DecorationImage(
              image: NetworkImage(album.url),
              fit: BoxFit.cover,
            ),
          ),
          alignment: Alignment.bottomCenter,
          child: Column(
            crossAxisAlignment: CrossAxisAlignment.center,
            mainAxisAlignment: MainAxisAlignment.center,
            mainAxisSize: MainAxisSize.min,
            children: <Widget>[
              Padding(
                padding: EdgeInsets.all(10.0),
                child: Text(
                  '${album.title}',
                  maxLines: 1,
                  softWrap: true,
                  textAlign: TextAlign.center,
                  style: TextStyle(
                    fontSize: 20.0,
                    color: Colors.white,
                    fontWeight: FontWeight.w500,
                  ),
                ),
              ),
              Padding(
                padding: EdgeInsets.all(10.0),
                child: Text(
                  '${album.id}', // show the Album id
                  maxLines: 1,
                  softWrap: true,
                  textAlign: TextAlign.center,
                  style: TextStyle(
                    fontSize: 20.0,
                    color: Colors.white,
                    fontWeight: FontWeight.w500,
                  ),
                ),
              ),
              // Add two more buttons for Update and Delete
              Row(
                crossAxisAlignment: CrossAxisAlignment.end,
                mainAxisAlignment: MainAxisAlignment.center,
                children: <Widget>[
                  FlatButton(
                    color: Colors.green,
                    child: Text(
                      'UPDATE',
                      style: TextStyle(
                        fontSize: 14.0,
                        fontWeight: FontWeight.w500,
                        color: Colors.white,
                      ),
                    ),
                    onPressed: () {
                      album.title = '${album.id} Updated';
                      updateFunction(album);
                    },
                  ),
                  FlatButton(
                    color: Colors.red,
                    child: Text(
                      'DELETE',
                      style: TextStyle(
                        fontSize: 14.0,
                        fontWeight: FontWeight.w500,
                        color: Colors.white,
                      ),
                    ),
                    onPressed: () {
                      deleteFunction(album.id);
                    },
                  )
                ],
              ),
            ],
          ),
        ),
      ),
    );
  }
}

---

Code for adding the GridView.

 gridview(AsyncSnapshot<Albums> snapshot) {
    return Padding(
      padding: EdgeInsets.all(5.0),
      child: GridView.count(
        crossAxisCount: 2,
        childAspectRatio: 1.0,
        mainAxisSpacing: 4.0,
        crossAxisSpacing: 4.0,
        children: snapshot.data.albums.map((album) {
          return GridTile(
            child: AlbumCell(album, update, delete),
          );
        }).toList(),
      ),
    );
  }

  // Get the Records from the Service and insert into the database.
  getPhotos() {
    setState(() {
      counter = 0;
      albumsLoaded = false;
    });
    Services.getPhotos().then((allAlbums) {
      albums = allAlbums;
      // Now we got all the albums from the Service...
      // We will insert each album one by one into the Database...
      // On Each load, we will truncate the table
      dbHelper.truncateTable().then((val) {
        // Write a recursive function to insert all the albums
        insert(albums.albums[0]);
      });
    });
  }

  // recursive function to insert record one by one to the database.
  insert(Album album) {
    dbHelper.save(album).then((val) {
      counter = counter + 1;
      // we are calculating the percent here on insert of each record
      percent = ((counter / albums.albums.length) * 100) /
          100; // percent from 0 to 1...
      // terminate condition for this recursive function
      if (counter >= albums.albums.length) {
        // when inserting is done
        setState(() {
          albumsLoaded = true;
          percent = 0.0;
          title = '${widget.title} [$counter]';
        });
        return;
      }
      setState(() {
        title = 'Inserting...$counter';
      });
      Album a = albums.albums[counter];
      insert(a);
    });
  }


  // Update Function
  update(Album album) {
    // We are updating the album title on each update
    dbHelper.update(album).then((updtVal) {
      showSnackBar('Updated ${album.id}');
      refresh();
    });
  }

  // Delete Function
  delete(int id) {
    dbHelper.delete(id).then((delVal) {
      showSnackBar('Deleted $id');
      refresh();
    });
  }

  // Method to refresh the List after the DB Operations
  refresh() {
    dbHelper.getAlbums().then((allAlbums) {
      setState(() {
        albums = allAlbums;
        counter = albums.albums.length;
        title = '${widget.title} [$counter]'; // updating the title
      });
    });
  }

  // Show a Snackbar
  showSnackBar(String message) {
    scaffoldKey.currentState.showSnackBar(SnackBar(
      content: Text(message),
    ));
  }

---

Complete Code

import 'package:flutter/material.dart';
import 'Services.dart';
import '../../models/album.dart';
import '../../models/albums.dart';
import 'DBHelper.dart';
import 'GridCell.dart';

class GridViewDemo extends StatefulWidget {
  GridViewDemo() : super();

  final String title = "Photos";

  @override
  GridViewDemoState createState() => GridViewDemoState();
}

// Add two plugins
// One for Http and other for getting the Device Directories
// Go to pubspec.yaml file

// Let me show the service url we are going to use
// https://jsonplaceholder.typicode.com/photos
// In this service we have 5000 records or albums
// Let's create the model classes first.
// For that we need some plugins..let me show those...

// To Auto-generate Json Models, we need to create a folder named
// 'jsons' in the root of the project. You can have different name as well,
// in that case the folder name should be specified along with the command
// in the terminal...

// Let's create the folder first
// Now copy the json you want to create model for...

// We will run a command in the terminal to generate the model,
// the generated models will be inside a folder named 'models' inside the 'lib' folder.
// Let's see how to do it...
// I am already having the 'models' folder which I created for other tutorials...

// You can watch my other tutorial on generating json models by clicking the 'i' button above
// or the link is provided in the description

// Now we have the basic model
// But we have a list of Albums, so create another model

// Ok Now we have both models for this demo...

// Now we will add the Sqflite library to create an offline database
// Link to all libraries used is provided in the description below.
// Let's write the basic CRUD operations for saving the albums in the Offline Database...

// Now we will write the Service Class to get the Data from the service.
// Add the Http Plugin...

// We will use these Database functions now to do the DB operations

// Now we will add a progressbar while inserting the album records

// Seems like my system is running bit slow...Sorry for that

class GridViewDemoState extends State<GridViewDemo> {
  //
  int counter;
  static Albums albums;
  DBHelper dbHelper;
  bool albumsLoaded;
  String title; // Title for the AppBar where we will show the progress...
  double percent;
  GlobalKey<ScaffoldState> scaffoldKey;

  @override
  void initState() {
    super.initState();
    counter = 0;
    percent = 0.0;
    title = widget.title;
    albumsLoaded = false;
    scaffoldKey = GlobalKey();
    dbHelper = DBHelper();
  }

  getPhotos() {
    setState(() {
      counter = 0;
      albumsLoaded = false;
    });
    Services.getPhotos().then((allAlbums) {
      albums = allAlbums;
      // Now we got all the albums from the Service...
      // We will insert each album one by one into the Database...
      // On Each load, we will truncate the table
      dbHelper.truncateTable().then((val) {
        // Write a recursive function to insert all the albums
        insert(albums.albums[0]);
      });
    });
  }

  insert(Album album) {
    dbHelper.save(album).then((val) {
      counter = counter + 1;
      // we are calculating the percent here on insert of each record
      percent = ((counter / albums.albums.length) * 100) /
          100; // percent from 0 to 1...
      // terminate condition for this recursive function
      if (counter >= albums.albums.length) {
        // when inserting is done
        setState(() {
          albumsLoaded = true;
          percent = 0.0;
          title = '${widget.title} [$counter]';
        });
        return;
      }
      setState(() {
        title = 'Inserting...$counter';
      });
      Album a = albums.albums[counter];
      insert(a);
    });
  }

  gridview(AsyncSnapshot<Albums> snapshot) {
    return Padding(
      padding: EdgeInsets.all(5.0),
      child: GridView.count(
        crossAxisCount: 2,
        childAspectRatio: 1.0,
        mainAxisSpacing: 4.0,
        crossAxisSpacing: 4.0,
        children: snapshot.data.albums.map((album) {
          return GridTile(
            child: AlbumCell(album, update, delete),
          );
        }).toList(),
      ),
    );
  }

  // Update Function
  update(Album album) {
    // We are updating the album title on each update
    dbHelper.update(album).then((updtVal) {
      showSnackBar('Updated ${album.id}');
      refresh();
    });
  }

  // Delete Function
  delete(int id) {
    dbHelper.delete(id).then((delVal) {
      showSnackBar('Deleted $id');
      refresh();
    });
  }

  // Method to refresh the List after the DB Operations
  refresh() {
    dbHelper.getAlbums().then((allAlbums) {
      setState(() {
        albums = allAlbums;
        counter = albums.albums.length;
        title = '${widget.title} [$counter]'; // updating the title
      });
    });
  }

  // Show a Snackbar
  showSnackBar(String message) {
    scaffoldKey.currentState.showSnackBar(SnackBar(
      content: Text(message),
    ));
  }

  // We will create a GridView to show the Data...
  // Before that we will create the class from each Cell in the GridView
  // Add a Gridview to the UI

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      key: scaffoldKey,
      appBar: AppBar(
        title: Text(title),
        // Add action buttons in the AppBar
        actions: <Widget>[
          IconButton(
            icon: Icon(Icons.file_download),
            onPressed: () {
              getPhotos();
            },
          ),
        ],
      ),
      body: Column(
        mainAxisAlignment: MainAxisAlignment.start,
        crossAxisAlignment: CrossAxisAlignment.start,
        mainAxisSize: MainAxisSize.min,
        children: <Widget>[
          albumsLoaded
              ? Flexible(
                  child: FutureBuilder<Albums>(
                    future: dbHelper.getAlbums(),
                    builder: (context, snapshot) {
                      if (snapshot.hasError) {
                        return Text('Error ${snapshot.error}');
                      }
                      if (snapshot.hasData) {
                        return gridview(snapshot);
                      }
                      // if still loading return an empty container
                      return Container();
                    },
                  ),
                )
              : LinearProgressIndicator(
                  value: percent,
                ),
        ],
      ),
    );
  }
}