Skip to content

[Flutter] Sqflite Tutorial (With Example)

Last Updated on 2021-10-13 by Clay

When we use Flutter to develop an application and we need to store a large amount of data, we can use an embedded database such as SQLite (Sqflite) to store the data.

Today, I want to record how to use the sqflite package to create, insert, delete, and update data in a SQLite embedded database in Flutter.

Preparation

To use the sqflite package, you must add the following package name to the pubspec.yaml file (perhaps add the specified version).

The packages we need are sqflite and path. The former is a package that allows us to operate the SQLite database, the latter is a package that allows us to accurately read the specified path on different platforms.

Then execute the command to get packages:

flutter pub get


Use sqflite to build a database

Before starting, I explain my sample program. The goal of my database is to store SuperHero data, for which I need 3 different files:

lib/
├── DB.dart
├── hero.dart
└── main.dart

  • DB.dart: All operations of the database are written in this file
  • hero.dart: The class of the superhero template is written here
  • main.dart: The code used in the example, insert Batman, Superman and other data, and delete Batman data


hero.dart

As mentioned above, this is a template category program for superheroes.

class SuperHero {
  // Init
  final int id;
  final String name;
  final int age;
  final String ability;
  SuperHero({this.id, this.name, this.age, this.ability,});

  // toMap()
  Map<String, dynamic> toMap() {
    return {
      "id": id,
      "name": name,
      "age": age,
      "ability": ability,
    };
  }

  @override
  String toString() {
    return "SuperHero{\n  id: $id\n  name: $name\n  age: $age\n  ability: $ability\n}\n\n";
  }
}


In this code, I define the class SuperHero. Which contains:

  • id
  • name
  • age
  • ability

In addition, the toString() function is rewritten according to the official tutorial, which allows us to choose the printed format of this class.


DB.dart

This code is the basic operation of the database.

import 'dart:async';

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

import 'package:flutter_app/hero.dart';


class HeroDB {
  static Database database;

  // Initialize database
  static Future<Database> initDatabase() async {
    database = await openDatabase(
      // Ensure the path is correctly for any platform
      join(await getDatabasesPath(), "hero_database.db"),
      onCreate: (db, version) {
        return db.execute(
          "CREATE TABLE HEROS("
              "id INTEGER PRIMARY KEY,"
              "name TEXT,"
              "age INTEGER,"
              "ability TEXT"
              ")"
        );
      },

      // Version
      version: 1,
    );

    return database;
  }

  // Check database connected
  static Future<Database> getDatabaseConnect() async {
    if (database != null) {
      return database;
    }
    else {
      return await initDatabase();
    }
  }

  // Show all data
  static Future<List<SuperHero>> showAllData() async {
    final Database db = await getDatabaseConnect();
    final List<Map<String, dynamic>> maps = await db.query("HEROS");

    return List.generate(maps.length, (i) {
      return SuperHero(
        id: maps[i]["id"],
        name: maps[i]["name"],
        age: maps[i]["age"],
        ability: maps[i]["ability"],
      );
    });
  }

  // Insert
  static Future<void> insertData(SuperHero hero) async {
    final Database db = await getDatabaseConnect();
    await db.insert(
      "HEROS",
      hero.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // Update
  static Future<void> updateData(SuperHero hero) async {
    final db = await getDatabaseConnect();
    await db.update(
      "HEROS",
      hero.toMap(),
      where: "id = ?",
      whereArgs: [hero.id],
    );
  }

  // Delete
  static Future<void> deleteData(int id) async {
    final db = await getDatabaseConnect();
    await db.delete(
      "HEROS",
      where: "id = ?",
      whereArgs: [id],
    );
  }
}


It looks very long, in fact there are only 5 important functions:

  • initDatabase()
  • showAllData(): Return all data in the database
  • insertData(): Insert data
  • updateData(): Update data
  • deleteData(): Delete data

It’s not complicated, right?


main.dart

The next step is the final test program.

Don't forget to import both hero.dart and DB.dart. No matter the relative path or the absolute path when importing, it is possible.

For example, my project name is flutter_app (this is the default name), then I want to import DB.dart, and write at the beginning of the code:

import 'package:flutter_app/DB.dart';


The following is the complete code, you can see that I imported Batman, Superman and other data; then I deleted Batman's data, and I printed twice.

import 'package:flutter/widgets.dart';

import 'package:flutter_app/hero.dart';
import 'package:flutter_app/DB.dart';


void main() async {
  // Avoid errors
  WidgetsFlutterBinding.ensureInitialized();

  // Open the database
  //final Future<Database> database = HeroDB.getDatabaseConnect();

  // Main work
  // Batman
  var batman = SuperHero(
    id: 0,
    name: "Batman",
    age: 50,
    ability: "Rich",
  );

  // Superman
  var superman = SuperHero(
    id: 1,
    name: "Superman",
    age: 35,
    ability: "I can fly",
  );

  // Main work
  await HeroDB.insertData(batman);
  await HeroDB.insertData(superman);
  print(await HeroDB.showAllData());

  await HeroDB.deleteData(0);
  print(await HeroDB.showAllData());

}


Output:

I/flutter ( 9807): [SuperHero{
I/flutter ( 9807):   id: 0
I/flutter ( 9807):   name: Batman
I/flutter ( 9807):   age: 50
I/flutter ( 9807):   ability: Rich
I/flutter ( 9807): }
I/flutter ( 9807): 
I/flutter ( 9807): , SuperHero{
I/flutter ( 9807):   id: 1
I/flutter ( 9807):   name: Superman
I/flutter ( 9807):   age: 35
I/flutter ( 9807):   ability: I can fly
I/flutter ( 9807): }
I/flutter ( 9807): 
I/flutter ( 9807): ]
I/flutter ( 9807): [SuperHero{
I/flutter ( 9807):   id: 1
I/flutter ( 9807):   name: Superman
I/flutter ( 9807):   age: 35
I/flutter ( 9807):   ability: I can fly
I/flutter ( 9807): }
I/flutter ( 9807): 
I/flutter ( 9807): ]


As you see, the first time I printed, Batman and Superman in the database; but in the second time, the deleted Batman has disappeared.

Bay the way, I actually like Batman better. The sudden disappearance of Batman is not a metaphor. Thank you very much.

References

Read More

Tags:

Leave a Reply