quickeydb

Description:

quickeydb

Quickey Database
FAST Object-Relational Mapping SQLite wrapper.
QuickeyDB is a simple ORM inspired from ActiveRecord, built on-top of Sqflite.
QuickeyDB Object-Relational Mapping (ORM) uses a coding technique with function descriptors connected to a relational database.
Apart from 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 allow data caching /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.

Platforms #



Platform
Supported?




Web
❎ Coming Soon


MacOS
✅ Tried & Tested


Windows
✅ Tried & Tested


Linux
✅ Tried & Tested


Android
✅ Tried & Tested


iOS
✅ Tried & Tested




Introduction to QuickeyDB
Getting Started with QuickeyDB

Add QuickeyDB dependency
Create Models
Create a Schema Dart File
Initialize database
Simple Example


Data Access Objects

Building Queries
Finder Queries
Data Persistence
Calculations Methods
Helper Methods
Custom SQL Queries


Data Tables Relations

Belongs To
Has One
Has Many


Database Migration
Transaction
Batch support
Import Local Database
Persist Data Storage
Cool Color Logger
Platform setup
Taskan Crud Example
Features Request & Bug Reports
Contributing
Articles and videos

Introduction to QuickeyDB: #
QuickeyDB is an ORM inspired form ActiveRecord and depends on CREATE TABLE command which uses Regular Expression (RegExp) to analysis table defentions:

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

Note: QuickeyDB is a runtime library so it dosen't depend on heavily generate code.
Getting Started with QuickeyDB #
1. Add QuickeyDB dependency #
dependencies:
quickeydb: ^x.x.x
copied to clipboard
2. Create User Model and Task 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;

}


copied to clipboard
// 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,
};

}


copied to clipboard
3. Create a 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(),
),
);
}


copied to clipboard
4. Initialize database #

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

copied to clipboard
5. Simple Example #

await QuickeyDB.getInstance!<UserTable>()?.create(
User(
name: 'Kenzy Limon',
email: '[email protected]',
phone: '+254 712345678',
task: Task(name: 'Create Package', body: 'Create a Flutter DB Package')
),
);
copied to clipboard
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();
copied to clipboard
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')])]
copied to clipboard
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',...))]
copied to clipboard
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);
copied to clipboard
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); // | createAll

// Also you can use `insert` which accepts map
QuickeyDB.getInstance!<UserSchema>()!.insert(user.toMap()); // insertAll

// 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)
copied to clipboard
Database Relationships #
QuickeyDB has out of box support for relationships, we treasure them and they only
work when you define the relationships on your models.
After defining the relationships. QuickeyDB will do all the heavy lifting of constructing the underlying SQL queries.
One to one #
One to One creates a one-to-one relationship between two models.
For example, A user has a profile. The has one relationship needs a foreign key in the related table.
Defining relationship on the model #
Once you have created the schema with the required columns,
you will also have to define the relationship on your schema.



// 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
),
),
)
copied to clipboard
One to many #
HasMany creates a one-to-many relationship between two models. For example, A user has many posts.
The relationship needs a foreign key in the related table.
Following is an example table structure for the one-to-many relationship.
The tasks.user_id is the foreign key and forms the relationship with the user.id column.
Defining relationship on the model #
Once you have created the tables with the required columns, you will also have to define the relationship on your schema.



// 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'),...
],
),
)
copied to clipboard
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
copied to clipboard
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
copied to clipboard
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 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(),
),
);
}
copied to clipboard
Has One #
// Database/schema.dart
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 HasOne<UserSchema>(),
],
converter: Converter(
encode: (task) => Task.fromMap(task),
decode: (task) => task!.toMap(),
decodeTable: (task) => task!.toTableMap(),
),
);
}
copied to clipboard
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(),
),
);
}
copied to clipboard
Custom SQL Queries #
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();
}

...

}
copied to clipboard
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();
}

...

}
copied to clipboard
Persist Data Storage #
To use persist database set persist property to true
final quickeyDB = QuickeyDB(
persist: true',
)
copied to clipboard
Import Local Database #
To import exists database:

Copy exists database to assets/database.db
Add path to assets in pubspec.yaml

+ flutter:
+ assets:
+ - assets/database.db
copied to clipboard

Set importDB property to true

final quickeyDB = QuickeyDB(
importDB: true,
)
copied to clipboard

Run

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(),
),
);
}
copied to clipboard

Dont forget to change the database version.


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

copied to clipboard
Please Note That :: Never add NOT NULL columns while migrating unless you pass DEFAULT value.
Transaction #
Calls in Transaction must only be done using the transaction object (txn), avoid using the database or QuickeyDB.getInstance inside transaction as this will trigger a databased dead-lock.
Keep in mind that the callbacks onCreate onUpgrade onDowngrade are already internally wrapped in a transaction, so there is no need to wrap your statements within those callbacks.

await QuickeyDB.getInstance!.database!.transaction((txn) async {

txn.insert('users', { mapped data }, conflictAlgorithm: ConflictAlgorithm.replace);
txn.delete('users', where: 'id = ?', whereArgs: [id]);
txn.update('users', { mapped data });

txn.rawDelete('DELETE FROM users WHERE name = ?', ['Kenzy Limon']);
txn.rawDelete('DELETE FROM users WHERE name = ?', ['Kenzy Limon']);
txn.rawDelete('DELETE FROM users WHERE name = ?', ['Kenzy Limon']);
txn.rawQuery('SELECT COUNT(*) FROM users');

await txn.execute('CREATE TABLE task_types (id INTEGER PRIMARY KEY)');

});

copied to clipboard
Batch support #

var batch = QuickeyDB.getInstance!.database!.batch();
batch.insert('users', {'name': 'Kenzy'});
batch.update('users', {'name': 'Kenzy Limon'}, where: 'name = ?', whereArgs: ['Kenzy']);
batch.delete('users', where: 'name = ?', whereArgs: ['Kenzy']);
var results = await batch.commit();

copied to clipboard
Getting the result for each operation has a cost (id for insertion and number of changes for update and delete).
On Android where an extra SQL request is executed. If you don't care about the result and worry about performance in big batches, you can use
await batch.commit(noResult: true);
copied to clipboard
Note during a transaction, the batch won't be committed until the transaction is committed

await database.transaction((txn) async {

var batch = txn.batch();

// ...

await batch.commit();

});

copied to clipboard
The actual commit will happen when the transaction is committed.
By default, a batch stops as soon as it encounters an error (which typically reverts the uncommitted changes)
to change this action, you have to set continueOnError to false
await batch.commit(continueOnError: true);
copied to clipboard
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, // any version
);

copied to clipboard
Supported Data types #
DateTime is not a supported SQLite type. Personally I store them as
int (millisSinceEpoch) or string (iso8601)
bool is not a supported SQLite type. Use INTEGER and 0 and 1 values.
INTEGER #

Dart type: int
Supported values: from -2^63 to 2^63 - 1

REAL #

Dart type: num

TEXT #

Dart type: String

BLOB #

Dart type: Uint8List

Platform setup #
Linux #
libsqlite3 and libsqlite3-dev linux packages are required.
copied to clipboard
One time setup for Ubuntu (to run as root):
dart tool
/

linux_setup.dart

or

sudo apt
-
get -

y install
libsqlite3-0
libsqlite3-dev
copied to clipboard
MacOS #
Should work as is.
Web #
Copy sqflite_sw.js and sqlite3.wasm from the example/web folder to your root/web folder.
import the database as follows.
import 'package:quickeydb/quickeydb.dart' if (dart.library.html)
'package:quickeydb/quickeywebdb.dart';
copied to clipboard
Windows #
Should work as is in debug mode (sqlite3.dll is bundled).
In release mode, add sqlite3.dll in same folder as your executable.
Taskan Crud Example #

Taskan Crud Example

Features Request & Bug Reports #
Feature requests and bugs at the issue tracker.
Contributing #
Quickey Database ORM is an open source project, and thus contributions to this project are welcome - please feel free to create a new issue if you encounter any problems, or submit a pull request. For community contribution guidelines, please review the Code of Conduct.
If submitting a pull request, please ensure the following standards are met:


Code files must be well formatted (run flutter format .).


Tests must pass (run flutter test). New test cases to validate your changes are highly recommended.


Implementations must not add any project dependencies.


Project must contain zero warnings. Running flutter analyze must return zero issues.


Ensure docstrings are kept up-to-date. New feature additions must include docstrings.


Additional information #
This package has THREE CORE dependencies including core SQLite Packages.
- sqflite_common_ffi // for desktop apps
- sqflite
- collection
copied to clipboard
Developed by:
© 2022 Kenzy Limon
Articles and videos #
Flutter Quickey Database ORM - Medium Article
Found this project useful? ❤️ #
If you found this project useful, then please consider giving it a ⭐️ on Github and sharing it with your friends via social media.