Category: Database

Accuracy is Everything…

How to save and access app database using Flutter? Hmm……

This article sneak peeks into the world of app databases and how to access them….

Begin…

---

This article is inspired by the Boring Show, where they explain how to access your app database using a package, moor….

What is Moor…?

This is actually a mirror image of the word Room, (which is used in Android for accessing Database).

As per the documentation about Room :

The Room persistence library provides an abstraction layer over SQLite to allow database access. It also helps you create a cache of your app’s data.

Moor is a similar concept, but for Flutter……

---

Setting Moor….

1. Add moor_flutter in dependencies….

dependencies:  moor_flutter: 

2. Add the below in dev_dependencies…

dev_dependencies:
  moor_generator: ^1.6.0
  build_runner: ^1.6.0

3. Create a dart file which would contain the description of your database…

import 'package:moor_flutter/moor_flutter.dart';

part 'tables.g.dart';

class Modes extends Table {
  TextColumn get userName =>
      text().named('user_name').customConstraint('UNIQUE')();

 TextColumn get watchMode => text().named('watch_mode')();

 @override
 Set<Column> get primaryKey => {userName};

 DateTimeColumn get modifiedDate => dateTime().nullable()();
}

This is a description of tables.dart file

> TextColumn represents the Text in the database….

> DateTimeColumn and IntColumn, date and integer types in the database…

> Named parameter (named('NAME OF THE FIELD')) defines the name of the field in the database….

> You can define the primary key in your table, overriding the primaryKey 

> Field which can have null value, can be specified using nullable() 

> Any custom constraint, say unique, can be specified using customConstraint('YOUR CONSTRAINT')

NOTE : Don’t forget the line part 'tables.g.dart'; This will show an error as does not exist, but will be created in Step 5

4. Create a class annotated with @UseMoor(tables: [Modes])

// this annotation tells moor to prepare a database class that uses the tables we just defined. (Modes in our case)

@UseMoor(tables: [Modes])class MyDatabase {  }

5. Run the below command, 

flutter packages pub run build_runner build

This generates tables.g.dart…

6. We need to use this generated file in our tables.dart as

earlier…

@UseMoor(tables: [Modes])class MyDatabase {  }

now……

@UseMoor(tables: [Modes])
class MyDatabase extends _$MyDatabase {
  
  MyDatabase()
      : super(FlutterQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
        ));

  @override
  int get schemaVersion => 1;
}

Phew…..:p

7. Accessing the data from the database,

@UseMoor(tables: [Modes])
class MyDatabase extends _$MyDatabase {
  
  MyDatabase()
      : super(FlutterQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
        ));

  @override
  int get schemaVersion => 1;

  Future<List<Mode>> get allWatchingModes => select(modes).get();

  Stream<List<Mode>> get watchAllModes => select(modes).watch();

}

> allWatchingModes : Basically a select * query which gets all the modes and returns a future…

> watchAllModes : Also a select * query but with watch capability and returns a stream….

What does watch do : 

// watches all entries. The stream will automatically// emit new items whenever the underlying data changes.

Using Database Class in App…

---

We have done one final tweak in the tables.dart. 

Instead of having all the queries inside the class MyDatabase, we have created DAO (Data Access Objects)

1. Create a class like below, taking the 2 queries from the MyDatabase class

@UseDao(tables: [Modes]) 
class ModesDao extends DatabaseAccessor<MyDatabase> with _$ModesDaoMixin {

ModesDao(MyDatabase db) : super(db);

Future<List<Mode>> get allWatchingModes => select(modes).get();

Stream<List<Mode>> get watchAllModes => select(modes).watch();
}

Your MyDatabase class should look like this :

@UseMoor(tables: [Modes], daos: [ModesDao])
class MyDatabase extends _$MyDatabase {
  
  MyDatabase()
      : super(FlutterQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
        ));

  @override
  int get schemaVersion => 1;

}

Notice the @UseMoor(tables: [Modes], daos: [ModesDao])

This tells MyDatabase class about the DAOs (Data Access Objects)

2. Run the following command again

flutter packages pub run build_runner build

NOTE : You can delete the old tables.g.dart before running this command

---

Access database class in app….

Time to use the Database class in app…

1. We have used Providers for accessing database as 

@override
  Widget build(BuildContext context) {
    
    return MultiProvider(
      providers: [
        Provider<ModesDao>(
          builder: (_) => MyDatabase().modesDao,
        ),
      ],
      child: 'YOUR CHILD',
    );

)

2. In the child widget,

final _dbProvider = Provider.of<ModesDao>(context);

return StreamBuilder<List<Mode>>(
   stream: _dbProvider.watchAllModes,
   builder: (context, snapshot) {

   if (snapshot.hasData) {
      final _data = snapshot.data;

       if (_data.length == 0) {
          _dbProvider.addUserName(
            userName: 'Aseem',
            watchMode: 'Admin Mode',
          );

          return Center(child: CircularProgressIndicator());
       }
    }

  return MainWidget();
);

> We added 2 more queries in our ModesDAO for inserting and updating the table…

@UseDao(tables: [Modes])
class ModesDao extends DatabaseAccessor<MyDatabase> with _$ModesDaoMixin {
  ModesDao(MyDatabase db) : super(db);

Future<List<Mode>> get allWatchingModes => select(modes).get();

Stream<List<Mode>> get watchAllModes => select(modes).watch();

  ///Adds a user name...
  void addUserName({String userName, String watchMode}) {
    final _entry = ModesCompanion(
      userName: Value(userName),
      watchMode: Value(watchMode),
    );

into(modes).insert(_entry);
  }

  ///Updates a user name...
  void updateUserName({String userName, String watchMode}) {
    final _entry = ModesCompanion(
      userName: Value(userName),
      watchMode: Value(watchMode),
    );

update(modes).write(_entry);
  }
}

> _dbProvider.watchAllModes : Stream listening to the modes table

> As the app starts (for the first time), there is no data, hence we are adding default value 

_dbProvider.addUserName(
     userName: 'Aseem',
     watchMode: 'Admin Mode',
);

> After adding this, it triggers the streambuilder again and we see the data now….. 

Who’s Watching (from the above screenshot)….

3. Clicking on either Viewers or Aseem, triggers the update query,

onTap: () => _dbProvider.updateUserName(
       userName: 'Viewers',
       watchMode: 'View Mode',
),