sqfly

Description:

sqfly

Sqfly is a simple ORM inspired from ActiveRecord, built ontop of Sqflite and uses DAO architecture.

How does it work
Getting Started
Data Access Objects

Queries
Finders
Persistence
Calculations
Helpers
Relations

Belongs To
Has One
Has Many


Custom Queries


In-Memory
Migration
Import
Logger
Examples

How does it work: #
Sqlfy 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: Sqfly is a runtime library so it dosen't depend on generate code using source_gen.
Getting Started #

Add to your dependencies:

dependencies:
sqfly: ^x.x.x
copied to clipboard

Create a Model

// models/person.dart
class Person {
final int id;
final String name;
final int age;

const Person({this.id, this.name});

Person.fromMap(Map<String, dynamic> map)
: id = map['id'],
name = map['name'],
age = map['age'];

Map<String, dynamic> toMap() => {
'id': id,
'name': name,
'age': age,
};
}
copied to clipboard

Create a Data Access Object (DAO)

// daos/person_dao.dart
class PersonDao extends Dao<Person> {
PersonDao()
: super(
'''
CREATE TABLE persons (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER NOT NULL
)
''',
// use to decode and encode person
converter: Converter(
encode: (person) => Person.fromMap(person),
decode: (person) => person.toMap(),
),
);
}
copied to clipboard

Initilize your database

final sqfly = await Sqfly.initialize(
/// database name
name: 'example',
// database version
version: 1,
/// pass all your daos
daos: [
PersonDao(),
],
);

// That's it (:
copied to clipboard

Usage


Sqfly.instance<UserDao>().foo().bar();
copied to clipboard
Data Access Objects #
Queries #
/// SELECT * FROM persons
Sqfly.instance<PersonDao>().all; // | toList()

/// SELECT id FROM persons
Sqfly.instance<PersonDao>().select(['id']).toList(); // [Person, ...]

/// SELECT * FROM persons WHERE name = 'Sam' OR name = 'Mike'
Sqfly.instance<PersonDao>().where({'name': 'Sam'}).or({'name': 'Mike'}).toList();

/// To use any other operation just pass it after attribute
// SELECT * FROM persons where age >= 5
Sqfly.instance<PersonDao>().where({'age >= ?': 5}).toList();

// SELECT * FROM persons ORDER BY name DESC
Sqfly.instance<PersonDao>().order('name DESC').toList();

// SELECT * FROM persons GROUP BY name HAVING LENGTH(name) > 3
Sqfly.instance<PersonDao>().group(['name']).having('LENGTH(name) > 3').toList();

// SELECT * FROM persons LIMIT 50 OFFSET 100
Sqfly.instance<PersonDao>().limit(1).offset(10).toList();

// SELECT DISTINCT * FROM persons
Sqfly.instance<PersonDao>().distinct().toList();
copied to clipboard
Includes
// SELECT * FROM persons
// SELECT * FROM dogs WHERE id IN (1)
Sqfly.instance<PersonDao>().includes([DogDao]).toList();
// [Person(id: 1, name: 'Sam', dogs: [Dog(id: 1, title: 'Roze')])]
copied to clipboard
Joins
// SELECT
// dogs.*,
// persons.id AS person_id,
// persons.name AS person_name,
// FROM dogs
// INNER JOIN persons ON persons.id = dogs.person_id
Sqfly.instance<DogDao>().joins([PersonDao]).toList();
// [Dog(id: 1, title: 'Roze', person: Person(id: 1, name: 'Sam'))]
copied to clipboard
Finders #
// SELECT * FROM persons WHERE name = 'Mike' LIMIT 1
Sqfly.instance<PersonDao>().isExists({'name': 'Mike'}); // true

// SELECT * FROM persons WHERE id = 1 LIMIT 1
Sqfly.instance<PersonDao>().find(1); // Person

// SELECT * FROM persons WHERE name = 'Mike' LIMIT 1
Sqfly.instance<PersonDao>().findBy({'name': 'Mike'}); // Person

// SELECT * FROM persons WHERE id = 1 LIMIT 1
Sqfly.instance<PersonDao>().find(1); // Person

// SELECT * FROM persons
Sqfly.instance<PersonDao>().first; // first item from select

// SELECT * FROM persons
Sqfly.instance<PersonDao>().last; // last item from select

// SELECT * FROM persons LIMIT 1
Sqfly.instance<PersonDao>().take();
// SELECT * FROM persons LIMIT 3
Sqfly.instance<PersonDao>().take(3);
copied to clipboard
Persistence #
final person = Person(id: 1, name: 'Sam', age: 33);

// INSERT INTO persons (id, name) VALUES (1, 'Sam')
Sqfly.instance<PersonDao>().create(person); // | createAll
// Also you can use `insert` which accepts map
Sqfly.instance<PersonDao>().insert(person.toMap()); // insertAll

// UPDATE persons SET name = 'Steve', age = 33 WHERE id = 1
Sqfly.instance<PersonDao>().update(person..name = 'Steve'); // | updateAll

// DELETE FROM persons WHERE id = 1
Sqfly.instance<PersonDao>().delete(person);

// DELETE FROM persons WHERE id = 1
Sqfly.instance<PersonDao>().destroy(1); // destroyAll (truncate)
copied to clipboard
One to one
// INSERT INTO persons (id, name, age) VALUES (NULL, 'Sam', 16);
// INSERT INTO dogs (id, title, person_id) VALUES (NULL, 'Roze', 1);
Sqfly.instance<DogDao>().create(
Dog(
title: 'Roze',
person: Person(name: 'Sam', age: 16),
),
)
copied to clipboard
One to many
// INSERT INTO persons (id, name, age) VALUES (NULL, 'Mike', 21);
// INSERT INTO dogs (id, title, person_id) VALUES (NULL, 'Roze', 1);
Sqfly.instance<PersonDao>().create(
Person(
name: 'Mike',
age: 21,
dogs: [
Dog(title: 'Roze'),
// ...
],
),
)
copied to clipboard
Calculations #
/// SELECT COUNT(*) FROM persons
Sqfly.instance<PersonDao>().count(); // 3

/// SELECT COUNT(name) FROM persons
Sqfly.instance<PersonDao>().count('name'); // 3

/// SELECT AVG(age) FROM persons
Sqfly.instance<PersonDao>().average('age'); // 7.4

/// SELECT id FROM persons
Sqfly.instance<PersonDao>().ids; // [1, 2, 3, ..]

/// SELECT MAX(age) FROM persons
Sqfly.instance<PersonDao>().maximum('age'); // 10

/// SELECT MIN(age) FROM persons
Sqfly.instance<PersonDao>().minimum('age'); // 1

/// SELECT name, age FROM persons LIMIT 1
Sqfly.instance<PersonDao>().pick(['name', 'age']); // ['Mike', 10]

/// SELECT name FROM persons
Sqfly.instance<PersonDao>().pluck(['name', 'age']); // [['Mike', 'Sam'], ...]

/// SELECT SUM(age) FROM persons
Sqfly.instance<PersonDao>().sum('age'); // 10.1
copied to clipboard
Helpers #
/// convert query to list
Sqfly.instance<PersonDao>().foo().bar().toList(); // [Person, ...]
/// convert query to map
Sqfly.instance<PersonDao>().foo().bar().toMap(); // [{id: 1, name: 'Mike', age: 10}, ...]

/// alias [count] > 0
Sqfly.instance<PersonDao>().foo().bar().isEmpty; // | isNotEmpty
copied to clipboard
Relations #
Make sure to add FOREIGN KEY between tables.
Belongs To #
// daos/todo_dao.dart
class DogDao extends Dao<Dog> {
DogDao()
: super(
+ relations: [
+ /// Make sure to add forign_key in sql defention
+ BelongsTo<PersonDao>(),
+ ],
);
}
copied to clipboard
Has One #
// daos/todo_dao.dart
class DogDao extends Dao<Dog> {
DogDao()
: super(
+ relations: [
+ HasOne<PersonDao>(),
+ ],
);
}
copied to clipboard
Has Many #
// daos/person_dao.dart
class PersonDao extends Dao<Person> {
PersonDao()
: super(
+ relations: [
+ HasMany<DogDao>(),
+ ],
);
}
copied to clipboard
Custom Queries #
Sqlfy is scalable with custom and complex queries so for example let's say we want to filter adult people we can add:
class PersonDao extends Dao<Person> {
...
+ Future<List<Person>> get adults {
+ return where({'age >= ?': 18}).toList();
+ }
...
}
copied to clipboard
You can also use more complex queries by accessing database object
class PersonDao extends Dao<Person> {
...
+ Future<List<Person>> get custom async {
+ // Use your custom query
+ final results = await database.rawQuery('SELECT * FROM people');
+
+ // when returning result use converter
+ return results.map((result) => converter.encode(result) as Person).toList();
+ }
...
}
copied to clipboard
In-Memory #
To use in-memory database set inMemory property to true
final sqfly = Sqfly(
+ inMemory: true',
)
copied to clipboard
Import #
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 import property to true

final sqfly = Sqfly(
+ import: true,
)
copied to clipboard

Run

Migration #
Because of depends on CREATE TABLE command as explaned above so that prevent using migrations because all new migrations will not be avalible in SQL command that located inside Dao class, so one of the best soultions was to use force migration by creating new table and move all data:

Modify your sql command by adding or removing some defention for example:

class PersonDao extends Dao<Person> {
PersonDao()
: super(
'''
CREATE TABLE persons (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER NOT NULL,
+ phone INTEGER NOT NULL
)
''',
);
}
copied to clipboard

Change database version.

final sqfly = Sqfly(
- version: 1,
+ version: 2, // or any version your want
).init();
copied to clipboard

Have fun

Note: Don't add NOT NULL columns while migrating unless you pass DEFAULT value.
To know more visit github.
Logger #
Sqfly delivers the same ActiveRecord logger as shown below
VSCode

Note: By default logger is enabled while you're in debug mode, if you want to disable it just set logger property to false.
final sqfly = Sqfly(
+ logger: false,
)
copied to clipboard
Examples #

Todos Example

Features & Bugs #
Feature requests and bugs at the issue tracker.