Flutter Floor - Room database in Flutter

Published January 23, 2022

In this Flutter tutorial example we will learn what is Floor Database and how to implement floor database in flutter. Floor database is an lightweight, abstraction layer for the SQFlite database. With this Floor flutter plugin we can perform create,update,delete and read operations on SQFlite database.

Let's implement Floor Database in flutter application. Here we are creating simple Employee Details app which will create Employee details and insert in Employee Table.

Step 1: Create Flutter Application

Read some other flutter articles about create

Migrate Flutter to Null Safety

GetX Shared Preferences in Flutter

Flutter Data Persistency with Shared Preferences

Step 2: Add required dependencies in pubspec.yaml file

floor: ^1.2.0
faker: ^2.0.0

To work with Floor we need to add Generated plugins like floor_generator inside pubspec.yaml file

We need to add it under dev_dependencies, we will use it only in development phase and since we don’t need the build_runner to be included when compiling the application

floor_generator: ^1.2.0
build_runner: ^2.1.4

Now Let move to create Database files

Before going to implement database functionality we need to know about how floor database will work. This plugin will uses the architecture pattern like Data Access object which we called as DAO.

Whenever we are working on a large project we need to separate the code blocks in different layers like UI, Business logic and Data Access layer. Here our floor will be under Data Access layer.

We have to create three different files like

Entity

DAO

Database

Entity: Entity is class which will represents as Table for the Sqflite

DAO: DAO is an interface or abstract class which we will define our database operations like add, delete...

Database: Database class is used to create instance of Sqflite database and handle the data.

Now create a Model class

Here we are working for Employee data so create an Employee model class

import 'package:floor/floor.dart';

@entity
class Employee{
   @PrimaryKey(autoGenerate: true)
   int? id;

   String? firstName, lastName, email;

   Employee({this.id, this.firstName, this.lastName, this.email});

}

We will annotate model class with @entity, which will create a table with the name Employee

Creating the Data Access Object

import 'dart:core';
import 'package:floor/floor.dart';
import 'package:floor_db_flutter/entity/Employee.dart';

@dao
abstract class EmployeeDao{
  @Query('SELECT * FROM Employee')
  Stream<List<Employee>> getAllEmployee();

  @Query('SELECT * FROM Employee WHERE id = :id')
  Stream<Employee?> getAllEmployeeBYID(int id);

  @Query('DELETE FROM Employee')
  Future<void> deleteAllEmployee();

  @insert
  Future<void> insertEmployee(Employee employee);

  @update
  Future<void> updateEmployee(Employee employee);

  @delete
  Future<void> deleteEmployee(Employee employee);
}

This DAO class contains all operations. Here we annotated with @dao

Create Floor Database class

We have create entity and DAO classes, now we need to create database class which will initiate Database object.

import 'dart:async';

import 'package:floor/floor.dart';
import 'package:floor_db_flutter/dao/EmployeeDao.dart';
import 'package:floor_db_flutter/entity/Employee.dart';
import 'package:sqflite/sqflite.dart' as sqflite;

part 'database.g.dart';

@Database(version: 1, entities: [Employee])
abstract class AppDatabase extends FloorDatabase{
  EmployeeDao get employeeDao;

}

Now to create respected tables and database classes for the Sqflite database we need to run below command

flutter packages pub run build_runner build

This will generate a file called database.g.dart

// GENERATED CODE - DO NOT MODIFY BY HAND

part of 'database.dart';

// **************************************************************************
// FloorGenerator
// **************************************************************************

// ignore: avoid_classes_with_only_static_members
class $FloorAppDatabase {
  /// Creates a database builder for a persistent database.
  /// Once a database is built, you should keep a reference to it and re-use it.
  static _$AppDatabaseBuilder databaseBuilder(String name) =>
      _$AppDatabaseBuilder(name);

  /// Creates a database builder for an in memory database.
  /// Information stored in an in memory database disappears when the process is killed.
  /// Once a database is built, you should keep a reference to it and re-use it.
  static _$AppDatabaseBuilder inMemoryDatabaseBuilder() =>
      _$AppDatabaseBuilder(null);
}

class _$AppDatabaseBuilder {
  _$AppDatabaseBuilder(this.name);

  final String? name;

  final List<Migration> _migrations = [];

  Callback? _callback;

  /// Adds migrations to the builder.
  _$AppDatabaseBuilder addMigrations(List<Migration> migrations) {
    _migrations.addAll(migrations);
    return this;
  }

  /// Adds a database [Callback] to the builder.
  _$AppDatabaseBuilder addCallback(Callback callback) {
    _callback = callback;
    return this;
  }

  /// Creates the database and initializes it.
  Future<AppDatabase> build() async {
    final path = name != null
        ? await sqfliteDatabaseFactory.getDatabasePath(name!)
        : ':memory:';
    final database = _$AppDatabase();
    database.database = await database.open(
      path,
      _migrations,
      _callback,
    );
    return database;
  }
}

class _$AppDatabase extends AppDatabase {
  _$AppDatabase([StreamController<String>? listener]) {
    changeListener = listener ?? StreamController<String>.broadcast();
  }

  EmployeeDao? _employeeDaoInstance;

  Future<sqflite.Database> open(String path, List<Migration> migrations,
      [Callback? callback]) async {
    final databaseOptions = sqflite.OpenDatabaseOptions(
      version: 1,
      onConfigure: (database) async {
        await database.execute('PRAGMA foreign_keys = ON');
        await callback?.onConfigure?.call(database);
      },
      onOpen: (database) async {
        await callback?.onOpen?.call(database);
      },
      onUpgrade: (database, startVersion, endVersion) async {
        await MigrationAdapter.runMigrations(
            database, startVersion, endVersion, migrations);

        await callback?.onUpgrade?.call(database, startVersion, endVersion);
      },
      onCreate: (database, version) async {
        await database.execute(
            'CREATE TABLE IF NOT EXISTS `Employee` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `firstName` TEXT, `lastName` TEXT, `email` TEXT)');

        await callback?.onCreate?.call(database, version);
      },
    );
    return sqfliteDatabaseFactory.openDatabase(path, options: databaseOptions);
  }

  @override
  EmployeeDao get employeeDao {
    return _employeeDaoInstance ??= _$EmployeeDao(database, changeListener);
  }
}

class _$EmployeeDao extends EmployeeDao {
  _$EmployeeDao(this.database, this.changeListener)
      : _queryAdapter = QueryAdapter(database, changeListener),
        _employeeInsertionAdapter = InsertionAdapter(
            database,
            'Employee',
            (Employee item) => <String, Object?>{
                  'id': item.id,
                  'firstName': item.firstName,
                  'lastName': item.lastName,
                  'email': item.email
                },
            changeListener),
        _employeeUpdateAdapter = UpdateAdapter(
            database,
            'Employee',
            ['id'],
            (Employee item) => <String, Object?>{
                  'id': item.id,
                  'firstName': item.firstName,
                  'lastName': item.lastName,
                  'email': item.email
                },
            changeListener),
        _employeeDeletionAdapter = DeletionAdapter(
            database,
            'Employee',
            ['id'],
            (Employee item) => <String, Object?>{
                  'id': item.id,
                  'firstName': item.firstName,
                  'lastName': item.lastName,
                  'email': item.email
                },
            changeListener);

  final sqflite.DatabaseExecutor database;

  final StreamController<String> changeListener;

  final QueryAdapter _queryAdapter;

  final InsertionAdapter<Employee> _employeeInsertionAdapter;

  final UpdateAdapter<Employee> _employeeUpdateAdapter;

  final DeletionAdapter<Employee> _employeeDeletionAdapter;

  @override
  Stream<List<Employee>> getAllEmployee() {
    return _queryAdapter.queryListStream('SELECT * FROM Employee',
        mapper: (Map<String, Object?> row) => Employee(
            id: row['id'] as int?,
            firstName: row['firstName'] as String?,
            lastName: row['lastName'] as String?,
            email: row['email'] as String?),
        queryableName: 'Employee',
        isView: false);
  }

  @override
  Stream<Employee?> getAllEmployeeBYID(int id) {
    return _queryAdapter.queryStream('SELECT * FROM Employee WHERE id = ?1',
        mapper: (Map<String, Object?> row) => Employee(
            id: row['id'] as int?,
            firstName: row['firstName'] as String?,
            lastName: row['lastName'] as String?,
            email: row['email'] as String?),
        arguments: [id],
        queryableName: 'Employee',
        isView: false);
  }

  @override
  Future<void> deleteAllEmployee()  async {
    await _queryAdapter.queryNoReturn('DELETE FROM Employee');
  }

  @override
  Future<void> insertEmployee(Employee employee) async {
    await _employeeInsertionAdapter.insert(employee, OnConflictStrategy.abort);
  }

  @override
  Future<void> updateEmployee(Employee employee) async {
    await _employeeUpdateAdapter.update(employee, OnConflictStrategy.abort);
  }

  @override
  Future<void> deleteEmployee(Employee employee) async {
    await _employeeDeletionAdapter.delete(employee);
  }
}

Now Create UI for the Employee Data

WidgetsFlutterBinding.ensureInitialized();
final database =
    await $FloorAppDatabase.databaseBuilder('nisha_database.db').build();
final dao = database.employeeDao;

import 'package:faker/faker.dart';
import 'package:floor_db_flutter/dao/EmployeeDao.dart';
import 'package:floor_db_flutter/database/database.dart';
import 'package:floor_db_flutter/entity/Employee.dart';
import 'package:flutter/cupertino.dart';
import 'package:flutter/material.dart';
import 'package:flutter_slidable/flutter_slidable.dart';

Future<void> main() async {
  WidgetsFlutterBinding.ensureInitialized();
  final database =
      await $FloorAppDatabase.databaseBuilder('nisha_database.db').build();
  final dao = database.employeeDao;
  runApp(MyApp(dao: dao));
}

class MyApp extends StatelessWidget {
  final EmployeeDao? dao;

  MyApp({this.dao});

  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      debugShowCheckedModeBanner: false,
      title: 'Flutter Demo',
      theme: ThemeData(),
      home: MyHomePage(dao: dao),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key? key, this.dao});

  final EmployeeDao? dao;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  final GlobalKey<ScaffoldState> scaffoldKey = GlobalKey<ScaffoldState>();

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      key: scaffoldKey,
      appBar: AppBar(
        title: Text('Room Database'),
        actions: [
          IconButton(
              onPressed: () async {
                final employee = Employee(
                    firstName: Faker().person.firstName(),
                    lastName: Faker().person.lastName(),
                    email: Faker().internet.email());
                await widget.dao!.insertEmployee(employee);
                showSnackBar(scaffoldKey.currentState, 'Add Success');
              },
              icon: Icon(Icons.add)),
          IconButton(
              onPressed: () async {
                widget.dao!.deleteAllEmployee();
                setState(
                    (){
                      showSnackBar(scaffoldKey.currentState, 'Clear Success');
                    }
                );
              },
              icon: Icon(Icons.clear)),
        ],
      ),
      body: StreamBuilder(
        stream: widget.dao!.getAllEmployee(),
        builder: (context, snapshot) {
          if (snapshot.hasError) {
            return Center(child: (Text('${snapshot.error}')));
          } else if (snapshot.hasData) {
            var listEmployee = snapshot.data as List<Employee>;
            return Container(
              color: Colors.black12,
              padding: EdgeInsets.all(0),
              child: ListView.builder(
                itemCount: listEmployee != null ? listEmployee.length : 0,
                  itemBuilder: (context, index) {
                return Slidable(
                    actionPane: SlidableDrawerActionPane(),
                    secondaryActions: [
                      IconSlideAction(
                        caption: 'Update',
                        color: Colors.pink,
                        icon: Icons.update,
                        onTap: () async{
                          final updateEmployee = listEmployee[index];
                          updateEmployee.firstName = Faker().person.firstName();
                          updateEmployee.lastName = Faker().person.lastName();
                          updateEmployee.email = Faker().internet.email();

                          await widget.dao!.updateEmployee(updateEmployee);

                          showSnackBar(scaffoldKey.currentState, 'Updated');

                        },
                      ),
                      IconSlideAction(
                        caption: 'Delete',
                        color: Colors.red,
                        icon: Icons.delete,
                        onTap: () async{
                          final deleteEmployee = listEmployee[index];
                          await widget.dao!.deleteEmployee(deleteEmployee);
                          showSnackBar(scaffoldKey.currentState, 'Deleted');
                        },
                      )
                    ],
                    child: ListTile(
                      contentPadding: EdgeInsets.only(left: 20),
                      tileColor: Colors.black12,
                      title: Text('${listEmployee[index].firstName} ${listEmployee[index].lastName}', style: TextStyle(color: Colors.black, fontSize: 18),),
                      subtitle: Text('${listEmployee[index].email}', style: TextStyle(color: Colors.black, fontSize: 14)),
                    ));
              }),
            );
          } else {
            return Center(
              child: CircularProgressIndicator(),
            );
          }
        },
      ),
    );
  }
}

void showSnackBar(ScaffoldState? currentState, String s) {
  final snackBar = SnackBar(
    content: Text(s),
        duration: Duration(seconds: 1),
  );
  currentState!.showSnackBar(snackBar);
}

Run application

Flutter Room database with Floor - Sqflite

Conclusion: In this flutter example we created Flutter Floor Database with DAO pattern

Article Contributed By :

3768 Views