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

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.

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.dartimport '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.dartimport '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.dartimport '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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store