Flutter Quickey Database ORM

QuickeyDB is a fast Flutter ORM inspired by ActiveRecord and built on top of Sqflite. Made purely to be runtime only, eliminating the need to use code generators.

QuickeyDB (ORM) uses a coding technique with function descriptors connected to a relational database.

Apart from the data access technique, QuickeyDB can benefit a developer in many ways including

• Requires Simplified development and Maintenance: this is because ORMs automate the object-to-table and table-to-object conversion
• QuickeyDB allows data indexing improving database performance
• You get to write better queries in a Dart, Most developers are not the best at writing SQL statements.
• Lastly, QuickeyDB has incredibly lower code lines compared to embedded SQL Queries.
• Database Migration and Database versioning
• Memory database storage for test cases and situations that don't require data to persist.
• Relation

Introduction to QuickeyDB:

QuickeyDB is an ORM inspired form ActiveRecord and depends on CREATE TABLE command which uses Regular Expression (RegExp) to analyze table definitions:

• Table name.
• Columns definition.
• Primary key.
• Foreign keys.

Note: QuickeyDB is a runtime library that doesn’t depend on heavily generated code.

Getting Started with QuickeyDB

1. Add QuickeyDB dependency

dependencies:
  quickeydb: ^x.x.x

2. Create Model Files

User Model

// Database/Models/user.dart
import 'task.dart';
class User {
   int? id;
   String? name;
   String? email;
   String? phone;
   int? age;
   Task? task;
   User({
      this.id,
      required this.name,
      required this.email,
      required this.age,
      this.phone,
      this.task
   });
   Map<String, dynamic> toMap() => {
      'id': id,
      'name': name,
      'email': email,
      'age': age,
      'phone': phone,
      'task': task != null ? task!.toMap() : null,
   };
   Map<String, dynamic> toTableMap() => {
      'id': id,
      'name': name,
      'email': email,
      'age': age,
      'phone': phone,
   };
   User.fromMap(Map<String?, dynamic> map)
           : id = map['id'],
              name = map['name'],
              email = map['email'],
              age = map['age'],
              phone = map['phone'],
              task = map['task'] != null ? 
                      Task.fromMap(map['task']) : null;
}

Task Model

// Database/Models/task.dart
import 'user.dart';
class Task {
   int? id;
   String name;
   String body;
   int? level;
   User? user;
   Task({
      this.id,
      required this.name,
      required this.body,
      required this.level,
      this.user,
   });
   Task.fromMap(Map<String?, dynamic> map)
           : id = map['id'],
              name = map['name'],
              body = map['body'],
              level = map['level'],
              user = map['user'] != null ? 
                     User.fromMap(map['user']) : null;
   Map<String, dynamic> toMap() => {
      'id': id,
      'name': name,
      'body': body,
      'level': level,
      'user': user != null ? user?.toMap() : null,
   };
   Map<String, dynamic> toTableMap() => {
      'id': id,
      'name': name,
      'body': body,
      'level': level,
   };
}

3. Create Database Table Schema Dart File

// Database/schema.dart
import 'package:quickeydb/quickeydb.dart';
import 'Models/user.dart';
import 'Models/task.dart';
class UserSchema extends DataAccessObject<User> {
   UserSchema()
           : super(
      '''
          CREATE TABLE user (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            phone TEXT,
            age INTEGER
          )
          ''',
      relations: [
         const HasOne<TaskSchema>(),
      ],
      converter: Converter(
         encode: (user) => User.fromMap(user),
         decode: (user) => user!.toMap(),
         decodeTable: (user) => user!.toTableMap(),
      ),
   );
   Future<List<User?>> getOldUsers() {
      return where({'age >= ?': 18}).toList();
   }
   Future<List<User>> doRawQuery() async {
      // Use your custom query
      final results = await database!.rawQuery('SELECT * FROM user');
      // when returning result use converter
      return results.map((result) => converter.encode(result) as User).toList();
   }
}
class TaskSchema extends DataAccessObject<Task> {
   TaskSchema()
           : super(
      '''
          CREATE TABLE tasks (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            body TEXT,
            status TEXT,
            level INTEGER DEFAULT "1" NOT NULL,
            FOREIGN KEY (user_id) REFERENCES user (id)
          )
          ''',
      relations: [
         const BelongsTo<UserSchema>(),
      ],
      converter: Converter(
         encode: (task) => Task.fromMap(task),
         decode: (task) => task!.toMap(),
         decodeTable: (task) => task!.toTableMap(),
      ),
   );
}

4. Initialize the database

await QuickeyDB.initialize(
    persist: false,
    dbVersion: 1,
    dataAccessObjects: [
      UserSchema(),
      TaskSchema(),
    ],
    dbName: 'tascan_v1',
  );

5. Simple Example

await QuickeyDB.getInstance!<UserTable>()?.create(
    User(
      name: 'Kenzy Limon',
      email: 'itskenzylimon@gmail.com',
      phone: '+254 712345678',
      task: Task(name: 'Create Package', body: 'Create a Flutter DB Package')
    ),
  );

Data Access Objects

Building Queries

/// SELECT * FROM user
QuickeyDB.getInstance!<UserSchema>()!.all; // | returns a list<T>
/// SELECT id FROM user
QuickeyDB.getInstance!<UserSchema>()!.select(['id']).toList(); // returns a list<T>
/// SELECT * FROM user WHERE name = 'Sam' OR name = 'Mike'
QuickeyDB.getInstance!<UserSchema>()!.where({'name': 'Kenzy Limon'}).or({'age': '21'}).toList();
/// To use any other operation just pass it after attribute
// SELECT * FROM user where age >= 18
QuickeyDB.getInstance!<UserSchema>()!.where({'age >= ?': 18}).toList();
// SELECT * FROM user ORDER BY name DESC
QuickeyDB.getInstance!<UserSchema>()!.order(['age']).toList();
// SELECT * FROM user GROUP BY name HAVING LENGTH(name) > 3
QuickeyDB.getInstance!<UserSchema>()!.group(['name']).having('LENGTH(name) > 5').toList();
// SELECT * FROM user LIMIT 50 OFFSET 100
QuickeyDB.getInstance!<UserSchema>()!.limit(1).offset(10).toList();
// SELECT DISTINCT * FROM user
QuickeyDB.getInstance!<UserSchema>()!.distinct().toList();

Include Queries

// SELECT * FROM user
// SELECT * FROM task WHERE id IN (1)
QuickeyDB.getInstance!<UserSchema>()?.includes([TaskSchema]).toList()
// [User(id: 1, name: 'Kenzy Limon',... task: [Task(id: 1, name: 'Complete ORM', body: 'Do nice Documentation')])]

Join Queries

// SELECT
//   task.*,
//   user.id AS user_id,
//   user.name AS user_name,
// FROM task
//   INNER JOIN user ON user.id = task.user_id
QuickeyDB.getInstance!<TaskSchema>()!.joins([UserSchema]).toList();
// [Task(id: 1, name: 'Complete ORM', body: 'Do nice Documentation',... user: User(id: 1, name: 'Kenzy Limon',...))]

Finder Queries

// SELECT * FROM user WHERE name = 'Kenzy Limon' LIMIT 1
QuickeyDB.getInstance!<UserSchema>()!.isExists({'name': 'John Doe'}); // true
// SELECT * FROM user WHERE id = 1 LIMIT 1
QuickeyDB.getInstance!<UserSchema>()!.find(1); // User
// SELECT * FROM user WHERE name = 'Mike' LIMIT 1
QuickeyDB.getInstance!<UserSchema>()!.findBy({'name': 'Jane Doe'}); // User
// SELECT * FROM user
QuickeyDB.getInstance!<UserSchema>()!.first; // first item
// SELECT * FROM user
QuickeyDB.getInstance!<UserSchema>()!.last; // last item
//  SELECT * FROM user LIMIT 3
QuickeyDB.getInstance!<UserSchema>()!.take(10);

Data Persistence

final user = User(id: 1, name: 'Kenzy Limon', age: 21,...);
// INSERT INTO user (id, name, age,...) VALUES (1, 'Kenzy Limon', 21,...)
QuickeyDB.getInstance!<UserSchema>()!.create(user); // | create
// Also you can use `insert` which accepts map
QuickeyDB.getInstance!<UserSchema>()!.insert(user.toMap()); // insert
// UPDATE user SET name = 'Kenzy Limon', age = 21 WHERE id = 1
QuickeyDB.getInstance!<UserSchema>()!.update(user..name = 'John Doe');
// DELETE FROM user WHERE id = 1
QuickeyDB.getInstance!<UserSchema>()!.delete(user);
// DELETE FROM user WHERE id = 1
QuickeyDB.getInstance!<UserSchema>()!.destroy(1); // (truncate)

One to one

// INSERT INTO user (id, name, age,...) VALUES (NULL, 'Jane Doe', 25,...);
// INSERT INTO task (id, name, user_id,...) VALUES (NULL, 'Test Cases', 1);
QuickeyDB.getInstance!<TaskSchema>()!.create(
Task(
name: 'Test Cases',...
user: User(
            name: 'Jane Doe', age: 25
      ),
  ),
)

One to many

// INSERT INTO user (id, name, age) VALUES (NULL, 'John Doe', 10);
// INSERT INTO task (id, name, user_id) VALUES (NULL, 'Write Documentation', 1);
QuickeyDB.getInstance!<UserSchema>()!.create(
User(
name: 'Jane Doe',
age: 25,...
task: [
  Task(name: 'Test Cases'),...
    ],
  ),
)

Calculations Methods

/// SELECT COUNT(*) FROM user
QuickeyDB.getInstance!<UserSchema>()!.count(); // 1001
/// SELECT COUNT(email) FROM user
QuickeyDB.getInstance!<UserSchema>()!.count('email'); // 600
/// SELECT AVG(age) FROM user
QuickeyDB.getInstance!<UserSchema>()!.average('age'); // 35
/// SELECT id FROM user
QuickeyDB.getInstance!<UserSchema>()!.ids; // [1, 2, 3,...]
/// SELECT MAX(age) FROM user
QuickeyDB.getInstance!<UserSchema>()!.maximum('age'); // 69
/// SELECT MIN(age) FROM user
QuickeyDB.getInstance!<UserSchema>()!.minimum('age'); // 18
/// SELECT name, age FROM user LIMIT 1
QuickeyDB.getInstance!<UserSchema>()!.pick(['name', 'age']); // ['John Doe', 96]
/// SELECT name FROM user
QuickeyDB.getInstance!<UserSchema>()!.pluck(['name', 'age']); // [['John Doe', '96'],...]
/// SELECT SUM(age) FROM user
QuickeyDB.getInstance!<UserSchema>()!.sum('age'); // 404

Helper Methods

/// convert query to list
QuickeyDB.getInstance!<UserSchema>()!.foo().bar().toList(); // [User,...]
/// convert query to map
QuickeyDB.getInstance!<UserSchema>()!.foo().bar().toMap(); // [{id: 1, name: 'Mike', age: 10}, ...]
/// alias [count] > 0
QuickeyDB.getInstance!<UserSchema>()!.foo().bar().isEmpty; // | false

Data Tables Relations

Make sure to add FOREIGN KEY between tables.

Belongs To

// Database/schema.dart
class TaskSchema extends DataAccessObject<Task> {
  TaskSchema()
      : super(
    '''
          CREATE TABLE task (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            body TEXT,
            status TEXT,
            level INTEGER DEFAULT "1" NOT NULL,
            FOREIGN KEY (user_id) REFERENCES user (id)
          )
          ''',
    relations: [
      const BelongsTo<UserSchema>(),
    ],
    converter: Converter(
      encode: (task) => Task.fromMap(task),
      decode: (task) => task!.toMap(),
      decodeTable: (task) => task!.toTableMap(),
    ),
  );
}

Has One

// Database/schema.dart
class TaskSchema extends DataAccessObject<Task> {
  TaskSchema()
      : super(
    '''
          CREATE TABLE task (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            body TEXT,
            status TEXT,
            level INTEGER DEFAULT "1" NOT NULL,
            FOREIGN KEY (user_id) REFERENCES user (id)
          )
          ''',
    relations: [
      const HasOne<UserSchema>(),
    ],
    converter: Converter(
      encode: (task) => Task.fromMap(task),
      decode: (task) => task!.toMap(),
      decodeTable: (task) => task!.toTableMap(),
    ),
  );
}

Has Many

// Database/schema.dart
class UserSchema extends DataAccessObject<User> {
  UserSchema()
      : super(
    '''
          CREATE TABLE user (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            phone TEXT,
            age INTEGER
          )
          ''',
    relations: [
      const HasMany<TaskSchema>(),
    ],
    converter: Converter(
      encode: (user) => User.fromMap(user),
      decode: (user) => user!.toMap(),
      decodeTable: (user) => user!.toTableMap(),
    ),
  );
}

Custom SQL Query Builder

QuickeyDB is scalable with custom and complex queries so for example let’s say we want to filter old users we can add:

class UserSchema extends DataAccessObject<User> {
  ...
  
  Future<List<User?>> getOldUsers() {
  return where({'age >= ?': 18}).toList();
  }
  
  ...
  
}

You can also use more complex queries by accessing database object

class UserSchema extends DataAccessObject<User> {
  ...
  
  Future<List<User>> doRawQuery() async {
    // Use your custom query
    final results = await database!.rawQuery('SELECT * FROM user');
    // when returning result use converter
    return results.map((result) => converter.encode(result) as User).toList();
  }
  
  ...
  
}

Persist Data Storage

To use persist database set persist property to true

final quickeyDB = QuickeyDB(
  persist: true',
)

Import Local Database

To import exists database:

Copy existing database file to assets/database.db

Add path to assets in pubspec.yaml

+ flutter:
+   assets:
+     - assets/database.db 

Set importDB property to true

final quickeyDB = QuickeyDB(
  importDB: true,
)

Run your app smoothly.

Database Migration

Because we depend on CREATE TABLE command as explained, so one of the best solutions was to use force migration by creating a new table and moving all data:

Modify your SQL command by adding or removing some definitions like:

class UserSchema extends DataAccessObject<User> {
  UserSchema()
      : super(
    '''
          CREATE TABLE user (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            phone TEXT,
            + address TEXT,
            age INTEGER
          )
          ''',
    relations: [
      const HasOne<TaskSchema>(),
    ],
    converter: Converter(
      encode: (user) => User.fromMap(user),
      decode: (user) => user!.toMap(),
      decodeTable: (user) => user!.toTableMap(),
    ),
  );
}

Don't forget to change the database version.

final quickeyDB = QuickeyDB.initialize!(
     dbVersion: 2, // any version
  );

Please Note That:: Never add NOT NULL columns while migrating unless you pass DEFAULT value.

Cool Color Logger

Note: By default logger is enabled while you're in debugging mode, if you want to disable it just set debugging property to false.

final quickeyDB = QuickeyDB.initialize!(
     debugging: false,
  );

You can play along with an Example App: Taskan Crud Example Taskan Crud

Conclusion

We have only covered most of the features, you can find the complete documentation about QuickeyDB at Here and really get in-depth knowledge of how cool QuickeyDB is.

Want to keep in touch online? Medium | Twitter | Instagram | Behance