Flutter Databases: SQLite with SQFLite (How to)

Flutter How To: Simple | To the Point| Short

Flutter Dev Level: Intermediate

Objective: Learn CRUD operations on SQLite using SQFLite Library.

Photo by Resource Database™ on Unsplash

Step -1: Add Packages

1.1 Add SQFLite — This is to use SQLite Database

flutter pub add sqflite

1.2 Add path_provider — This is to find path to the directory where application may store data that is user generated

flutter pub add path_provider

Step 2: Create relevant Model class which we want to save to the SQLite DB

2.1 For this ‘how to’, I’ll generate a simple item class with fromMap and toMap.

class Item {
  final int? id;
  final String name;

  Item({this.id, required this.name});

  factory Item.fromMap(Map<String, dynamic> json) => Item(
        id: json['id'],
        name: json['name'],
      );

  Map<String, dynamic> toMap() {
    return {'id': id, 'name': name};
  }
}

Step 3: Database Helper class — Initial Setup

3.1 Create a file called database_helper.dart

3.2 Add the following imports to the top

import 'dart:async';
import 'dart:io';
import 'package:flutter_sqflite/item.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

3.3 Create the class and add a late private field _database

 late Database _database;

3.4 Implement initDatabase method

  Future initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, 'example.db');
    _database = await openDatabase(path, version: 1, onCreate: _onCreate);
  }

• First we get the path to the directory where application stores data that is user generated
• Then we join it with the name of our database (which is ‘example.db’) in our case
• Then we initialise our private _database field by calling openDatabase where we specify path, version and onCreate method
• _onCreate method is only called in case the db does not exist (This will happen like you’ve guessed when the user first launches the application).

3.5 Implement _onCreate method

  Future _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE Item(
        id INTEGER PRIMARY KEY,
        name TEXT
    )
    ''');
  }

• Creates our item table with the specified column with their types.

Step 4: Implement Create Operation

 Future<int> insert(Item item) async {
    return await _database.insert('Item', item.toMap());
  }

• 4.1 we call the insert method and specify the table name (‘Item’) and convert the instance of Item object to Map<string,dynamic> before passing it to the method.

Step -5: Implement Read operation

  Future<List<Item>> getItems() async {
    final items = await _database.query('Item', orderBy: 'name');

    final itemList = items.isNotEmpty
        ? items.map((e) => Item.fromMap(e)).toList()
        : <Item>[];
    return itemList;
  }

• 5.1 First we call the query method on our database object and provide the table name as ‘Item’ and we’d like to order that by name
• 5.2 Then we check if the list has any items
• 5.3 If there are items, we map the ‘Map<string, dynamic>’ to Item type and assign it to the item list.
• 5.4 If there are no items, we assign an empty array to the item list.
• 5.5 Then we return the result.

Step -6: Implement Update Operation

  Future<int> update(Item item) async {
    return await _database.update('Item', item.toMap(),
        where: 'id = ?', whereArgs: [item.id]);
  }

• 6.1 First Param: Table name: ‘Item’
• 6.2 Second Param: Map<String, dynamic>.
• 6.3 Third Param: Where takes a filter which can be used to find the exact item we want to update
• 6.4 Fourth Param: Provide the arguments for the filter. In this case, it is going to use the id.

Step -7: Implement Delete Operation

  Future<int> delete(int id) async {
    return await _database.delete('Item', where: 'id = ?', whereArgs: [id]);
  }

• 7.1 First Param: Table name: ‘Item’
• 7.2 Second Param: Where takes a filter which can be used to find the exact item we want to update.
• 7.3 Third Param: Where takes a filter which can be used to find the exact item we want to update

Complete Implementation of Database Helper

import 'dart:async';
import 'dart:io';
import 'package:flutter_sqflite/item.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class DatabaseHelper {
  late Database _database;

  Future initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, 'example.db');
    _database = await openDatabase(path, version: 1, onCreate: _onCreate);
  }

  Future _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE Item(
        id INTEGER PRIMARY KEY,
        name TEXT
    )
    ''');
  }

  Future<List<Item>> getItems() async {
    final items = await _database.query('Item', orderBy: 'name');

    final itemList = items.isNotEmpty
        ? items.map((e) => Item.fromMap(e)).toList()
        : <Item>[];
    return itemList;
  }

  Future<int> insert(Item item) async {
    return await _database.insert('Item', item.toMap());
  }

  Future<int> delete(int id) async {
    return await _database.delete('Item', where: 'id = ?', whereArgs: [id]);
  }

  Future<int> update(Item item) async {
    return await _database
        .update('Item', item.toMap(), where: 'id = ?', whereArgs: [item.id]);
  }
}

That’s all for this one. Hope it helped :-)

Please find more flutter articles here: https://medium.com/@rafayk66/abdurs-flutter-articles-c201eff334b8

You can find me on
LinkedIn — https://www.linkedin.com/in/knowabdur
Twitter: — https://twitter.com/AbdurDeveloper

#flutter #flutterdeveloper #AbdurDeveloper #fluttercommunity