Moor Database

Vipul Chauhan

29 April 2023

What is the need for Moor Database?

When we begin to develop the applications, there comes a time when we find ourselves with the need to save the data within the application itself for offline support or for user-generated data.

At that point, what we do is introduce a database in the development of the application.
Moor is one of the options available for flutter.

Moor

This is a reactive persistence library for Flutter and Dart, built on top of SQLite.

It is a layer that sits on top of an SQLite database and makes it easier to use.

Looking out for some Flutter app development services

Let’s start the implementation

Add packages in your pubspec.yaml file.

dependencies:
  flutter:
    sdk: flutter
  moor: ^4.4.1
  sqlite3_flutter_libs: any. //--------------this ships the latest sqlite3 version with your Android or iOS app.

//---------this gives the path for database-------------//
  path_provider: any.  
  path: any 
//------------------------//

get_it: ^7.2.0 //------------this will help to make the database class singleton, to read more please visit: https://pub.dev/packages/moor_flutter


dev_dependencies:
  flutter_test:
    sdk: flutter
//--------------Generates query code based on your tables
  moor_generator: # use the latest version
//--------------------Common tool for code-generation, maintained by the Dart team
  build_runner:

dependencies:

flutter:

sdk: flutter

moor: ^4.4.1

sqlite3_flutter_libs: any. //--------------this ships the latest sqlite3 version with your Android or iOS app.

//---------this gives the path for database-------------//

path_provider: any.

path: any

//------------------------//

get_it: ^7.2.0 //------------this will help to make the database class singleton, to read more please visit: https://pub.dev/packages/moor_flutter

dev_dependencies:

flutter_test:

sdk: flutter

//--------------Generates query code based on your tables

moor_generator: # use the latest version

//--------------------Common tool for code-generation, maintained by the Dart team

build_runner:

Table

Create a table according to your needs.

class Users extends Table {
  IntColumn get id => integer().nullable().autoIncrement()();

  TextColumn get username => text()();

  TextColumn get mail => text()();

  TextColumn get country => text()();

  TextColumn get language => text()();
}

class Users extends Table {

IntColumn get id => integer().nullable().autoIncrement()();

TextColumn get username => text()();

TextColumn get mail => text()();

TextColumn get country => text()();

TextColumn get language => text()();

}

Database Class

In this class, you have to register all your data tables and write the methods for your database.

part 'AppDatabase.g.dart';

@UseMoor(tables: [Users]). //--------Here register all yoour data tables.
class AppDatabase extends _$AppDatabase {
  AppDatabase(QueryExecutor e) : super(e);

  @override
  int get schemaVersion => 1;

  Future<List<User>> getAllUsers() => select(users).get(); //--------It's a single shot query which will retrurn the available data in database.

  Stream<List<User>> streamAllUsers() => select(users).watch(); //---------It's a continious running query which will give you addition of new item as well as updtae of existing item.

  Future<int> insertUser(User user) => into(users).insert(user);

  Future<int> deleteUser(User user) => delete(users).delete(user);

  updateUser(int id, String name) =>
      (update(users)..where((t) => t.id.equals(id)))
          .write(UsersCompanion(username: Value(name)));
}

part 'AppDatabase.g.dart';

@UseMoor(tables: [Users]). //--------Here register all yoour data tables.

class AppDatabase extends _$AppDatabase {

AppDatabase(QueryExecutor e) : super(e);

@override

int get schemaVersion => 1;

Future<List<User>> getAllUsers() => select(users).get(); //--------It's a single shot query which will retrurn the available data in database.

Stream<List<User>> streamAllUsers() => select(users).watch(); //---------It's a continious running query which will give you addition of new item as well as updtae of existing item.

Future<int> insertUser(User user) => into(users).insert(user);

Future<int> deleteUser(User user) => delete(users).delete(user);

updateUser(int id, String name) =>

(update(users)..where((t) => t.id.equals(id)))

.write(UsersCompanion(username: Value(name)));

}

After that, run command – flutter pub run build_runner build as a result, this command will create the code for moor database.

Query Executor

A query executor is responsible for executing statements on a database and return their results in a raw form.

class AppDatabaseUtils {
  AppDatabaseUtils._();

  static LazyDatabase openConnection() {
    // the LazyDatabase util lets us find the right location for the file async.
    return LazyDatabase(() async {
      // put the database file, called db.sqlite here, into the documents folder
      // for your app.
      final dbFolder = await getApplicationDocumentsDirectory();
      final file = File(p.join(dbFolder.path, 'db.sqlite'));
      return VmDatabase(file);
    });
  }
}

class AppDatabaseUtils {

AppDatabaseUtils._();

static LazyDatabase openConnection() {

// the LazyDatabase util lets us find the right location for the file async.

return LazyDatabase(() async {

// put the database file, called db.sqlite here, into the documents folder

// for your app.

final dbFolder = await getApplicationDocumentsDirectory();

final file = File(p.join(dbFolder.path, 'db.sqlite'));

return VmDatabase(file);

});

}

Now, all things are set up and we can move forward to the main view.

Step 1: Create a singleton object of your database class.

GetIt locator = GetIt.I;

Future setupLocator() async {
  locator.registerSingleton(AppDatabase(AppDatabaseUtils.openConnection()));
}

Future<void> main() async {
  await setupLocator();
  runApp(MyApp());
}

GetIt locator = GetIt.I;

Future setupLocator() async {

locator.registerSingleton(AppDatabase(AppDatabaseUtils.openConnection()));

}

Future<void> main() async {

await setupLocator();

runApp(MyApp());

}

Step 2: Let’s Create the functions for our database.

I have used static data for demonstration you can change it according to you

void insertUser() async {
  await locator<AppDatabase>().insertUser(User(
      id: null,
      username: "Demo",
      mail: 'demo@gmail.com',
      country: 'India',
      language: 'Hindi'));
}

void deleteUser() async {
  await locator<AppDatabase>().deleteUser(User(
      id: 6,
      username: "Demo",
      mail: 'demo@gmail.com',
      country: 'India',
      language: 'Hindi'));
}

void updateUser() {
  locator<AppDatabase>().updateUser(5, "Update");
}

void getUsers() {
  locator<AppDatabase>()
      .getAllUsers()
      .then((value){});
}

void insertUser() async {

await locator<AppDatabase>().insertUser(User(

id: null,

username: "Demo",

mail: 'demo@gmail.com',

country: 'India',

language: 'Hindi'));

}

void deleteUser() async {

await locator<AppDatabase>().deleteUser(User(

id: 6,

username: "Demo",

mail: 'demo@gmail.com',

country: 'India',

language: 'Hindi'));

}

void updateUser() {

locator<AppDatabase>().updateUser(5, "Update");

}

void getUsers() {

locator<AppDatabase>()

.getAllUsers()

.then((value){});

}

Step 3: Let’s show the data on UI with the stream builder

StreamBuilder<List<User>>(
  stream: locator<AppDatabase>().streamAllUsers(),
  builder: (_, AsyncSnapshot<List<User>> snapshot) {
    print(snapshot.connectionState);
    if (snapshot.connectionState == ConnectionState.active) {
      if (snapshot.hasError) {
        return Text('Error whie fetching data');
      }
      return Expanded(
        child: ListView.builder(
          itemBuilder: (_, int index) {
            return Text(snapshot.data![index].username);
          },
          itemCount: snapshot.data!.length,
        ),
      );
    } else {
      return Text('No data found');
    }
  },
),

StreamBuilder<List<User>>(

stream: locator<AppDatabase>().streamAllUsers(),

builder: (_, AsyncSnapshot<List<User>> snapshot) {

print(snapshot.connectionState);

if (snapshot.connectionState == ConnectionState.active) {

if (snapshot.hasError) {

return Text('Error whie fetching data');

}

return Expanded(

child: ListView.builder(

itemBuilder: (_, int index) {

return Text(snapshot.data![index].username);

itemCount: snapshot.data!.length,

);

} else {

return Text('No data found');

}

For more details, pleaae check Moor Database