quickeydb 1.0.0+1 copy "quickeydb: ^1.0.0+1" to clipboard
quickeydb: ^1.0.0+1 copied to clipboard

Published 2 years ago • Latest: 1.2.0+8
outdated
→

Metadata

A new Flutter SQLite Wrapper.

More...

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.
  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. Import Local Database
  7. Persist Data Storage
  8. Cool Color Logger
  9. Taskan Crud Example
  10. Features Request & Bug Reports

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)

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

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

Taskan Crud Example #

Features Request & Bug Reports #

Feature requests and bugs at the issue tracker.

Articles and videos #

Flutter Quickey Database ORM - Medium Article

← Metadata

9
likes
0
pub points
64%
popularity

Publisher

unverified uploader

Metadata

A new Flutter SQLite Wrapper.

Repository (GitHub)
View/report issues

License

unknown (license)

Dependencies

collection, cupertino_icons, flutter, sqflite, sqflite_common

More

Packages that depend on quickeydb

Back