Flutter – SQLite Database

Wednesday, 18/12/2019

Tram Ho

Introduce

Perhaps people are no stranger to SQLite in mobile programming, this is a useful tool for caching data or stored locally. In Android, we are familiar with the Room library, this is a powerful library that provides a method cable to exploit the full power of SQLite most easily. On Flutter we also have a lot of plugins for working with SQLite easily such as sqlcool , sqflite , floor …. Details of the plugin you can search on pub.dev. Here I will use the floor plugin because it has many similarities with the Room library above android and it is great that this plugin supports both android and ios

Floor

Floor is a library that provides lightweight SQLite abstraction with automatic mapping between in-memory objects and database rows while providing full database control with the use of SQL.

The current version of floor is 0.9.0

Use

Add necessary dependencies to the pubspec.yaml file

 dependencies:
   flutter:
     sdk: flutter
   floor: ^0.9.0

 dev_dependencies:
   floor_generator: ^0.9.0
   build_runner: ^1.7.1

dependencies:

flutter:

sdk: flutter

floor: ^0.9.0

dev_dependencies:

floor_generator: ^0.9.0

build_runner: ^1.7.1

build_runner: a tool to generate needed files

Create a Entity Each Entity will represent a table in our database

import 'package:floor/floor.dart';

 @entity
 class Person {
   @primaryKey
   final int id;
   
   @ColumnInfo(name: 'custom_name', nullable: false)
   final String name;

   Person(this.id, this.name);
 }

import 'package:floor/floor.dart';

@entity

class Person {

@primaryKey

final int id;

@ColumnInfo(name: 'custom_name', nullable: false)

final String name;

Person(this.id, this.name);

}

@Entity keyword: This is a table worth noting
@PrimaryKey keyword: This property is worthy of being the primary key of the table. This property is required. It is possible to set multiple fields as Primarykey then using @Entity (primaryKeys: [‘id’, ‘name’])
Keyword @ColumnInfo : to change field information like name, notnull

Create a DAO

Class DAO is the component responsible for managing access to the database. The abstract class contains database query methods

import 'package:floor/floor.dart';
 @dao
 abstract class PersonDao {
   @Query('SELECT * FROM Person')
   Future&lt;List&lt;Person&gt;&gt; findAllPersons();

   @Query('SELECT * FROM Person WHERE id = :id')
   Future&lt;Person&gt; findPersonById(int id);

   @insert
   Future&lt;void&gt; insertPerson(Person person);
   
   @transaction
   Future&lt;void&gt; replaceUsers(List&lt;Person&gt; users) async {
       await deleteAllUsers();
       await insertUsers(users);
   }  
 }

import 'package:floor/floor.dart';

@dao

abstract class PersonDao {

@Query('SELECT * FROM Person')

Future<List<Person>> findAllPersons();

@Query('SELECT * FROM Person WHERE id = :id')

Future<Person> findPersonById(int id);

@insert

Future<void> insertPerson(Person person);

@transaction

Future<void> replaceUsers(List<Person> users) async {

await deleteAllUsers();

await insertUsers(users);

}

@Query : annotation marks queries (select, delete) methods
@insert : annotation marks the insert data method
@delete : annotation marks the delete data method
@transaction : mark method as a transaction set consisting of one or more different transactions

Create Database

 import 'dart:async';
 import 'package:floor/floor.dart';
 import 'package:path/path.dart';
 import 'package:sqflite/sqflite.dart' as sqflite;   

 import 'dao/person_dao.dart';
 import 'model/person.dart';   

 part 'database.g.dart'; // the generated code will be there

 @Database(version: 1, entities: [Person])
 abstract class AppDatabase extends FloorDatabase {
   PersonDao get personDao;
 }

import 'dart:async';

import 'package:floor/floor.dart';

import 'package:path/path.dart';

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

import 'dao/person_dao.dart';

import 'model/person.dart';

part 'database.g.dart'; // the generated code will be there

@Database(version: 1, entities: [Person])

abstract class AppDatabase extends FloorDatabase {

PersonDao get personDao;

}

Create an abstract class enxtend from FloorDatabase . Anotation @Database to mark the database version as well as the entities that need storage.

Make sure you have added part ‘database.g.dart’; This file will be automatically generated when we run build_runner
Run the command line flutter packages pub run build_runner build in the terminal to gen the needed files. Alternatively, you can run the command flutter packages pub run build_runner watch to automatically genize when a database or entities changes.
Use generated code. To get an instance of the database, use the automatically created $ FloorAppDatabase class

final database = await $FloorAppDatabase.databaseBuilder('app_database.db').build();

 final person = await database.findPersonById(1);
 await database.insertPerson(person);****

final database = await $FloorAppDatabase.databaseBuilder('app_database.db').build();

final person = await database.findPersonById(1);

await database.insertPerson(person);****

app_database.db: The database name you want to set

Architecture

Querying

The query methods are marked with the @Query annotation (). When using the query, you need to ensure the accuracy of the query. The query statement can return the Future, Stream type of an Entity or Void. Few examples of query queries

@Query('SELECT * FROM Person WHERE id = :id')
Future&lt;Person&gt; findPersonById(int id);

@Query('SELECT * FROM Person WHERE id = :id AND name = :name')
Future&lt;Person&gt; findPersonByIdAndName(int id, String name);

@Query('SELECT * FROM Person')
Future&lt;List&lt;Person&gt;&gt; findAllPersons(); // select multiple items

@Query('SELECT * FROM Person')
Stream&lt;List&lt;Person&gt;&gt; findAllPersonsAsStream(); // stream return

@Query('DELETE FROM Person')
Future&lt;void&gt; deleteAllPersons(); // query without returning an entity

@Query('SELECT * FROM Person WHERE id IN (:ids)')
Future&lt;List&lt;Person&gt;&gt; findPersonsWithIds(List&lt;int&gt; ids); //

@Query('SELECT * FROM Person WHERE name LIKE :name')
Future&lt;List&lt;City&gt;&gt; findPersonsWithNamesLike(String name);

@Query('SELECT * FROM Person WHERE id = :id')

Future<Person> findPersonById(int id);

@Query('SELECT * FROM Person WHERE id = :id AND name = :name')

Future<Person> findPersonByIdAndName(int id, String name);

@Query('SELECT * FROM Person')

Future<List<Person>> findAllPersons(); // select multiple items

@Query('SELECT * FROM Person')

Stream<List<Person>> findAllPersonsAsStream(); // stream return

@Query('DELETE FROM Person')

Future<void> deleteAllPersons(); // query without returning an entity

@Query('SELECT * FROM Person WHERE id IN (:ids)')

Future<List<Person>> findPersonsWithIds(List<int> ids); //

@Query('SELECT * FROM Person WHERE name LIKE :name')

Future<List<City>> findPersonsWithNamesLike(String name);

Migrations

As the app version changes, the database structure changes frequently, so it is imperative that the data migrations are required to ensure the integrity of the data.

// update entity with new 'nickname' field
@Entity(tableName: 'person')
class Person {
  @PrimaryKey(autoGenerate: true)
  final int id;

  @ColumnInfo(name: 'custom_name', nullable: false)
  final String name;
  
  final String nickname;

  Person(this.id, this.name, this.nickname);
}

// bump up database version
@Database(version: 2)
abstract class AppDatabase extends FloorDatabase {
  PersonDao get personDao;
}

// create migration
final migration1to2 = Migration(1, 2, (database) {
  database.execute('ALTER TABLE person ADD COLUMN nickname TEXT');
});

final database = await $FloorAppDatabase
    .databaseBuilder('app_database.db')
    .addMigrations([migration1to2])
    .build();

// update entity with new 'nickname' field

@Entity(tableName: 'person')

class Person {

@PrimaryKey(autoGenerate: true)

final int id;

@ColumnInfo(name: 'custom_name', nullable: false)

final String name;

final String nickname;

Person(this.id, this.name, this.nickname);

}

// bump up database version

@Database(version: 2)

abstract class AppDatabase extends FloorDatabase {

PersonDao get personDao;

}

// create migration

final migration1to2 = Migration(1, 2, (database) {

database.execute('ALTER TABLE person ADD COLUMN nickname TEXT');

});

final database = await $FloorAppDatabase

.databaseBuilder('app_database.db')

.addMigrations([migration1to2])

.build();

After a change in the database structure, the database version has been upgraded. Then define the changes using the implementation functions like migration1to2 …. Finally use addMigrations () when building the database.

Callback

final callback = Callback(
   onCreate: (database, version) { /* database has been created */ },
   onOpen: (database) { /* database has been opened */},
   onUpgrade: (database, startVersion, endVersion) { /* database has been upgraded */ },
);

final database = await $FloorAppDatabase
    .databaseBuilder('app_database.db')
    .addCallback(callback)
    .build();

final callback = Callback(

onCreate: (database, version) { /* database has been created */ },

onOpen: (database) { /* database has been opened */},

onUpgrade: (database, startVersion, endVersion) { /* database has been upgraded */ },

);

final database = await $FloorAppDatabase

.databaseBuilder('app_database.db')

.addCallback(callback)

.build();

Source: https://pub.dev/packages/floor#persisting-data-changes

Share the news now

Source : Viblo

Flutter – SQLite Database

Introduce

Floor

Use

Architecture

Querying

Migrations

Callback

TikTok becomes the second largest social platform in South Africa

The fastest depreciating after 9 months of launch, iPhone 14 Pro Max continues to break the bottom in Vietnam

Beginner's guide to R: Introduction

10 essential SublimeText plugins for JavaScript developers