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
  1. Introduction to QuickeyDB
  2. Getting Started with QuickeyDB
    1. Add QuickeyDB dependency
    2. Create Models
    3. Create a Schema Dart File
    4. Initialize database
    5. Simple Example
  3. Data Access Objects
    1. Building Queries
    2. Finder Queries
    3. Data Persistence
    4. Calculations Methods
    5. Helper Methods
    6. Custom SQL Queries
  4. Data Tables Relations
    1. Belongs To
    2. Has One
    3. Has Many
  5. Database Migration
  6. Transaction
  7. Batch support
  8. Import Local Database
  9. Persist Data Storage
  10. Cool Color Logger
  11. Platform setup
  12. Taskan Crud Example
  13. Features Request & Bug Reports
  14. Contributing
  15. 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

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;

}



// 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 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(),
      ),
   );
}

4. Initialize 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); // | 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)

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
      ),
  ),
)

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'),...
    ],
  ),
)

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

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

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 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();
  }
  
  ...
  
}

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:

  1. Copy exists database to assets/database.db
  2. Add path to assets in pubspec.yaml
+ flutter:
+   assets:
+     - assets/database.db
  1. Set importDB property to true
final quickeyDB = QuickeyDB(
  importDB: true,
)
  1. 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:

  1. 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(),
    ),
  );
}
  1. Dont 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.

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)');

});

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();

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);

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();

});

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);

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
  );

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.

One time setup for Ubuntu (to run as root):

dart tool
/

linux_setup.dart

or

sudo apt
-
get -

y install
libsqlite3-0
libsqlite3-dev

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';

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

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:

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

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

  3. Implementations must not add any project dependencies.

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

  5. 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

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.

Libraries

controls/belongs_to
extension/src/bool_extension
builder/calculation_method
types/column
configs/converter
extension/core_extension
configs/data_access_object
builder/data_method
configs/env_utils
types/foreign_key
controls/has_many
controls/has_one
configs/include
extension/src/iterable_extension
configs/logger
extension/src/map_extension
configs/migration
configs/patterns
builder/query_method
builder/quickey_builder
quickeydb
quickeywebdb
builder/read_method
types/reference
controls/relation
builder/relation_method
configs/relations
builder/return_method
types/schema
configs/sql_builder
extension/src/string_extension
configs/value_utils