[Flutter] SQLite

2021-06-09 hit count image

Let's see how to use SQLite in Flutter.

Outline

In this blog post, I will show you how to use SQLite to store the data on the user device in Flutter. In Flutter, we’ll use the sqflite package to use SQLite.

You can see the details about how to use sqflite in the Flutter official document.

Shared preferences

Normally, we use SQLite to store the complicated data on the user device. To save the simple data on the user device as form of key-value, we use the shared_preferences package.

If you want to know details about how to use shared_preferences package, see the link below.

Install sqflite

To use SQLite in Flutter, we need to install the sqflite package. Execute the command below to install the sqflite package.

flutter pub add sqflite

Prepare DB

When we use the sqflite package to use SQLite DB in Flutter, we need to prepare DB. Let’s see how to prepare SQLite DB.

Open DB

To use SQLite, we need to open the SQLite DB. You can open the SQLite DB by the code below.

import 'package:sqflite/sqflite.dart';
...
var db = await openDatabase('my_db.db');
...

If the DB file exists on the directory which is set on openDatabase, the DB file is opened. If the file doens’t exist, The new DB file is created and opend. The DB file is basically stored in default database directory on Android, and stored in the document directory on iOS.

Close DB

When we open the SQLite DB by openDatabase of the sqflite package, the DB access is closed automatically when the app is closed. If you want to close the DB at a specific timing instead of when the App is closed, you can use the code below to close the DB access.

...
await db.close();
...

Use existing DB

We can use pre-made SQLite DB with the sqflite package. First, copy the existing SQLite DB to the assets/ folder. And then, open the pubspec.yaml file and modify it like below.

assets:
  - assets/data.db

And then, If the SQLite DB doesn’t exist, you can copy the pre-made DB by using the code below.

import 'dart:io';
import 'package:flutter/services.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

Future<Database> getDB() async {
  var databasesPath = await getDatabasesPath();
  var path = join(databasesPath, '~www/data.db');
  var exists = await databaseExists(path);

  if (!exists) {
    try {
      await Directory(dirname(path)).create(recursive: true);
    } catch (_) {}

    var data = await rootBundle.load(join('assets', 'data.db'));
    List<int> bytes = data.buffer.asUint8List(
      data.offsetInBytes,
      data.lengthInBytes,
    );

    await File(path).writeAsBytes(bytes, flush: true);
  }

  return await openDatabase(path);
}

How to use

Let’s see how to use the sqflite package to do CRUD(Create, Read, Update, Delete) on SQLite DB.

Model class

You can define a model class to store or use the data with SQLite in Flutter. Although this is not a prerequisite for using SQLite, it makes more clear to get or add the data to SQLite.

You can write the modal class like the below.

class Dog {
  final int id;
  final String name;
  final int age;

  Dog({
    required this.id,
    required this.name,
    required this.age,
  });

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

  @override
  String toString() {
    return 'Dog{id: $id, name: $name, age: $age}';
  }
}

Select

You can get the data from SQLite DB with the modal class and the sqflite package like below.

final List<Map<String, dynamic>> maps = await db.query('dogs');
// final List<Map<String, dynamic>> maps = await db.rawQuery(
//   'SELECT id, name, age FROM dogs',
// );

return List.generate(maps.length, (i) {
  return Dog(
    id: maps[i]['id'],
    name: maps[i]['name'],
    age: maps[i]['age'],
  );
});

Insert

You can add the data to SQLite DB with the modal class and the sqflite package like below.

var dog = Dog(
  id: 0,
  name: 'Fido',
  age: 35,
);

await db.insert('dogs', dog.toMap());
// await db.rawInsert('INSERT INTO dogs(id, name, age) VALUES (${dog.id}, "${dog.name}", ${dog.age})');

Update

You can update the data on SQLite DB with the modal class and the sqflite package like below.

await db.update('dogs', dog.toMap(), where: 'id = ?', whereArgs: [dog.id]);
// await db.rawUpdate('UPDATE dogs SET age = ${dog.age} WHERE id = ${dog.id}');

Delete

You can delete the data from SQLite DB with the sqflite package like below.

await db.delete('dogs', where: 'id = ?', whereArgs: [id]);
// await database.rawDelete('DELETE FROM dogs WHERE id = ?', [id]);

Test

SQLite DB is basically created on the user device, and the sqflite package is designed on the detice, so we can’t do the Unit Test with it.

However, if we use sqflite_ffi, we can open the DB and test the CRUD queries in the test code.

Prepare

Copy the SQLite DB that is used for the service like the below, and initialize sqflite_ffi to prepare for testing.

import 'dart:io';

import 'package:flutter_test/flutter_test.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:path/path.dart';

void copyFile(String path, String newPath) {
  File(path).copySync(newPath);
}

void main() {
  sqfliteFfiInit();
  setUp(() {
    File(join('assets', 'my_db.db')).copySync(join('assets', 'test.db'));
  });

  ...
}

Select test

You can test the Select query with sqflite_ffi like the below.

...
void main() {
  ...
  test('Select', () async {
    var db = await databaseFactoryFfi.openDatabase('../../../assets/test.db');
    var dataProvider = DataProvider(db: db);

    var maps = await db.query('dogs');
    var list = List.generate(maps.length, (i) {
      return Dog(
        id: maps[i]['id'],
        name: maps[i]['name'],
        age: maps[i]['age'],
      );
    });

    expect(list[0].toMap(), {'id': 0, 'name': 'Fido', 'age': 35});
  });
  ...
}

Insert test

You can test the Insert query with sqflite_ffi like the below.

...
void main() {
  ...
  test('Insert', () async {
    var db = await databaseFactoryFfi.openDatabase('../../../assets/test.db');
    var dataProvider = DataProvider(db: db);

    var dog = Dog(
      id: 1,
      name: 'Fido',
      age: 35,
    );
    await db.insert('dogs', dog.toMap());

    var maps = await db.rawQuery(
      'SELECT name FROM dogs WHERE id=${dog.id}',
    );
    expect(maps[0].name, dog.name);
  });
  ...
}

Update test

You can test the Update query with sqflite_ffi like the below.

...
void main() {
  ...
  test('Update', () async {
    var db = await databaseFactoryFfi.openDatabase('../../../assets/test.db');
    var dataProvider = DataProvider(db: db);

    var dog = Dog(
      id: 0,
      name: 'Fido',
      age: 10,
    );
    await db.update('dogs', dog.toMap(), where: 'id = ?', whereArgs: [dog.id]);

    var maps = await db.rawQuery(
      'SELECT age FROM dogs WHERE id=$dog.id',
    );
    expect(maps[0].age, 10);
  });
  ...
}

Delete test

You can test the Delete query with sqflite_ffi like the below.

...
void main() {
  ...
  test('Delete', () async {
    var db = await databaseFactoryFfi.openDatabase('../../../assets/test.db');
    var dataProvider = DataProvider(db: db);

    var dog = Dog(
      id: 0,
      name: 'Fido',
      age: 35,
    );
    await db.delete('dogs', where: 'id = ?', whereArgs: [id]);

    var maps = await db.rawQuery(
      'SELECT * FROM dogs WHERE id=$dog.id',
    );
    expect(maps.length, 0);
  });
}

Completed

Done! we’ve seen how to use the sqflite package to use SQLite in Flutter. We encourage you to use SQLite to store and use data on the user device.

Was my blog helpful? Please leave a comment at the bottom. it will be a great help to me!

Posts