[English] Local Storage in Flutter with SQFLite

In a previous blog, we discussed local storage using shared_preferences. Now we will try to discuss SQFLite. SQFLite is local storage with table schema. If we need to store data to tables, this package will be one of the best options for now.

This blog is part of blogs about handling local data on Flutter:

1. Local storage with shared_preferences in Flutter
2. [This Blog] Penyimpanan data lokal pada Flutter (sqflite)
3. [English version soon] Cara untuk melakukan migration pada SQFLite

Let’s get straight to the code, Yuk Ngoding

1. Adding package 📦

In pubspec.yaml add package and run “flutter pub get”

  sqflite: ^2.2.8+4

2. MySqflite.dart

In this blog, we will store student data. First we will create a Constant File

class Constant {
  static const studentId = 'id';
  static const studentName = 'name';
  static const studentDepartment = 'department';
  static const studentSKS = 'sks';
}

• We will use this file to convert models to strings (and backwards) and also to process SQFLite
• This is just another solution to save us from typos in the code.

After that, we continued to create the Student class

import '../commons/const.dart';

class StudentModel {
  String id;
  String name;
  String department;
  int sks;

  StudentModel({
    required this.id,
    required this.name,
    required this.department,
    required this.sks,
  });

  // Convert  into a Map. The keys must correspond to the names of the
  // columns in the database.
  Map<String, dynamic> toMap() {
    return {
      Constant.studentId: id,
      Constant.studentName: name,
      Constant.studentDepartment: department,
      Constant.studentSKS: sks,
    };
  }

}

• In this class, we will add the toMap() function to make it easier for us to use SQFLite
• Update the import code based on your folder structure

Ok, let’s continue with MySqflite.dart

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

import '../../commons/const.dart';
import '../../models/student_model.dart';

class MySQFLite {
  static const _databaseName = "MyDatabase.db";

  static const _databaseV1 = 1;
  static const tableStudent = 'Student';

  // Singleton class
  MySQFLite._privateConstructor();

  static final MySQFLite instance = MySQFLite._privateConstructor();

  static Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  _initDatabase() async {
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, _databaseName);

    return await openDatabase(path, version: _databaseV1,
        onCreate: (db, version) async {
      var batch = db.batch();
      _onCreateTableStudent(batch);

      await batch.commit();
    });
  }

  void _onCreateTableStudent(Batch batch) async {
    batch.execute('''
          CREATE TABLE $tableStudent (
            ${Constant.studentId} TEXT PRIMARY KEY,
            ${Constant.studentName} TEXT,
            ${Constant.studentDepartment} TEXT,
            ${Constant.studentSKS} INTEGER
          )
          ''');
  }
  
  // Other Function
  
}

• This class is singleton
• We created a static variable, so in the SQFLite process we can access the same variable, avoiding typos.
• The _initDatabase function will define the SQFLite path, with the name, version, and onCreate process. When we upgrade the database version, we need to add it with the onUpgrade function. (We will cover database migrations in another blog)
• Update the import code based on your folder structure

Apart from other functions. We can add a lot of this code to the MySQFLite class based on what you need.

a. Insert

  Future<int> insertStudent(StudentModel model) async {
    Database db = await instance.database;

    return await db.insert(
      tableStudent,
      model.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  Future<void> insertStudents(List<StudentModel> models) async {
    Database db = await instance.database;

    Batch batch = db.batch();
    for (var model in models) {
      batch.insert(tableStudent, model.toMap(),
          conflictAlgorithm: ConflictAlgorithm.replace);
    }
    await batch.commit(noResult: true);
  }

• This function is used to add data to SQFLite
• We use ConflictAlgorithm.replace, so if there is data with the same value, it will be replaced with a new one.
• We are using the toMap() function that we have set up in the Student class
• We use batch to enter a lot of data at once.

b. Get

  Future<List<StudentModel>> getStudents() async {
    Database db = await instance.database;

    // var allData = await db.rawQuery("SELECT * FROM $tableStudent");
    var allData = await db.query(tableStudent);

    return List.generate(allData.length, (i) {
      return StudentModel(
        id: allData[i][Constant.studentId] as String,
        name: allData[i][Constant.studentName] as String,
        department: allData[i][Constant.studentDepartment] as String,
        sks: int.parse(
          allData[i][Constant.studentSKS].toString(),
        ),
      );
    });
  }

  Future<StudentModel?> getStudentById(String id) async {
    Database db = await instance.database;

    // var allData = await db
    //     .rawQuery("SELECT * FROM $tableStudent WHERE ${Constant.studentId} = "
    //         "$id LIMIT 1");
    var allData = await db.query(
      tableStudent,
      where: '${Constant.studentId} = ?',
      whereArgs: [id],
    );

    if (allData.isNotEmpty) {
      return StudentModel(
          id: allData[0][Constant.studentId] as String,
          name: allData[0][Constant.studentName] as String,
          department: allData[0][Constant.studentDepartment] as String,
          sks: int.parse(allData[0][Constant.studentSKS] as String));
    }
    return null;
  }

• For get the data, we can use query or with rawQuery

c. Update & Delete

  Future<int> updateStudentDepartment(StudentModel model) async {
    Database db = await instance.database;

    // return await db.rawUpdate(
    //   'UPDATE $tableStudent SET ${Constant.studentName} = ${model.name}, '
    //   '${Constant.studentDepartment} = ${model.department}, '
    //   '${Constant.studentSKS} = ${model.sks} '
    //   'Where ${Constant.studentId} = ${model.id}',
    // );
    return await db.update(
      tableStudent,
      model.toMap(),
      where: '${Constant.studentId} = ?',
      whereArgs: [model.id],
    );
  }

  Future<int> deleteStudent(String id) async {
    Database db = await instance.database;

    // return await db.rawDelete(
    //   'DELETE FROM $tableStudent Where ${Constant.studentId} = $id',
    // );
    return await db.delete(
      tableStudent,
      where: '${Constant.studentId} = ?',
      whereArgs: [id],
    );
  }

  clearAllData() async {
    Database db = await instance.database;

    // await db.rawQuery("DELETE FROM $tableStudent");
    await db.delete(tableStudent);
  }

• This is more or less the same as the Get function. We can use manual queries or helper provided by SQFLite.

3. Screen

For screen, we only try to save data to SQFLite, and show it. Use this code:

import 'package:explore_storage/repository/local/sqflite.dart';
import 'package:flutter/material.dart';

import '../models/student_model.dart';

class StudentsScreen extends StatefulWidget {
  const StudentsScreen({super.key});

  @override
  State<StudentsScreen> createState() => _StudentsScreenState();
}

class _StudentsScreenState extends State<StudentsScreen> {
  final keyFormStudent = GlobalKey<FormState>();

  TextEditingController controllerId = TextEditingController();
  TextEditingController controllerName = TextEditingController();
  TextEditingController controllerDepartment = TextEditingController();
  TextEditingController controllerSks = TextEditingController();

  String id = "";
  String name = "";
  String department = "";
  int sks = 0;

  List<StudentModel> students = [];

  @override
  void initState() {
    super.initState();

    WidgetsBinding.instance.addPostFrameCallback((_) async {
      students = await MySQFLite.instance.getStudents();
      setState(() {});
    });
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text("Explore SQFLite"),
      ),
      body: Column(
        crossAxisAlignment: CrossAxisAlignment.stretch,
        children: [
          Container(
            margin: const EdgeInsets.only(top: 36, left: 24, bottom: 4),
            child: const Text(
              "Input Student",
              style: TextStyle(
                fontWeight: FontWeight.bold,
              ),
            ),
          ),
          Form(
            key: keyFormStudent,
            child: Container(
              margin: const EdgeInsets.only(left: 24, right: 24),
              child: Column(
                children: [
                  TextFormField(
                    controller: controllerId,
                    decoration: const InputDecoration(hintText: "ID"),
                    validator: (value) => _onValidateText(value),
                    keyboardType: TextInputType.number,
                    onSaved: (value) => id = value.toString(),
                  ),
                  TextFormField(
                    controller: controllerName,
                    decoration: const InputDecoration(hintText: "Name"),
                    validator: (value) => _onValidateText(value),
                    onSaved: (value) => name = value.toString(),
                  ),
                  TextFormField(
                    controller: controllerDepartment,
                    decoration: const InputDecoration(hintText: "Department"),
                    validator: (value) => _onValidateText(value),
                    onSaved: (value) => department = value.toString(),
                  ),
                  TextFormField(
                    controller: controllerSks,
                    decoration: const InputDecoration(hintText: "SKS"),
                    validator: (value) => _onValidateText(value),
                    keyboardType: TextInputType.number,
                    onSaved: (value) => sks = int.parse(value.toString()),
                  ),
                ],
              ),
            ),
          ),
          Container(
            margin: const EdgeInsets.only(left: 24, right: 24),
            child: ElevatedButton(
              onPressed: _onSaveStudent,
              child: const Text("Save"),
            ),
          ),
          Container(
            margin: const EdgeInsets.only(top: 24, left: 24, bottom: 4),
            child: const Text("Student Data",
                style: TextStyle(fontWeight: FontWeight.bold)),
          ),
          Expanded(
            child: ListView.builder(
              itemCount: students.length,
              padding: const EdgeInsets.fromLTRB(24, 0, 24, 8),
              itemBuilder: (BuildContext context, int index) {
                var value = students[index];
                return Container(
                  margin: const EdgeInsets.only(bottom: 12),
                  child: Column(
                    crossAxisAlignment: CrossAxisAlignment.stretch,
                    mainAxisSize: MainAxisSize.min,
                    children: [
                      Text("Id: ${value.id}"),
                      Text("Name: ${value.name}"),
                      Text("Department: ${value.department}"),
                      Text("SKS: ${value.sks}"),
                    ],
                  ),
                );
              },
            ),
          )
        ],
      ),
    );
  }

  String? _onValidateText(String? value) {
    if (value?.isEmpty ?? true) return 'Can\'t empty';
    return null;
  }

  _onSaveStudent() async {
    FocusScope.of(context).requestFocus(FocusNode());

    if (keyFormStudent.currentState?.validate() ?? false) {
      keyFormStudent.currentState?.save();
      controllerId.text = "";
      controllerName.text = "";
      controllerDepartment.text = "";
      controllerSks.text = "";

      await MySQFLite.instance.insertStudent(StudentModel(
        id: id,
        name: name,
        department: department,
        sks: sks,
      ));

      students = await MySQFLite.instance.getStudents();
      setState(() {});
    }
  }
}

4. Result

After that, we can call the StudentScreen class from runApp. The result will be like this:

Only that. I hope this helps everyone 😁

This blog is part of blogs about handling local data on Flutter:

1. Local storage with shared_preferences in Flutter
2. [This Blog] Penyimpanan data lokal pada Flutter (sqflite)
3. [English version soon] Cara untuk melakukan migration pada SQFLite