Offline Database from WebService using SQFlite in Flutter

By | September 8, 2019

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,
                ),
        ],
      ),
    );
  }
}

3 thoughts on “Offline Database from WebService using SQFlite in Flutter

  1. @nyone

    When i close and open again all records gone!!
    I have to download it again!!

    Reply
    1. James Post author

      Because in the code, it’s clearing the offline database on launch. You can write your own logic to do whatever you want.

      Reply
  2. Pingback: Google's Flutter Tutorial - Offline DB Storage From Service, SQLite CRUD Operations - TutsFx

Leave a Reply

Your email address will not be published. Required fields are marked *