feature: adding an orm module

[kaziwaseef]

Feature Request: Dartseid should have an orm.

Since an orm is a very integral part of any backend framework, dartseid should have one as well. Since code generation and reflection are not part of the design architecture of dartseid, let us discuss and come up with the design of how we can implement an orm.

Design Considerations:

Dartseid orm should have an adapter pattern so that any database can be used with it. We can support some common databases in the beginning and have it be extendable so that any new database can be easily added to it. There should be a way to get the data returned from the db queries into data classes. The database adapter needs to handle db connection, query optimization, connection pooling and more.

Some possible apis can be considered such as this:

// Table Schema Definiton
@dataClass
class User {
  const factory User({
    int id,
    String fullName,
    int age,
    String email,
    String? password,
    int profileId,
    Profile? profile,
  }) = _User;
}
@dataClass
class Profile {
  const factory Profile({
    int id,
    int userId,
    String? bio,
    String? avatar,
  }) = _Profile;
}
final usersSchema = Dorm.table(
    "users",
    {
      "fullName": Int.primaryKey().autoIncrement(),
      "age": Int(),
      "email": String().unique(),
      "password": String().nullable(),
      "profileId": Int.foreignKey("profiles", onDelete: OnDelete.cascade),
    },
    converters: (fromJson: User.fromJson, toJson: User.toJson),
    { options }
);

userSchema.index(( id: 1, email: 1 ), { options });

final profilesSchema = Dorm.table(
    "profiles",
    {
      "id": Int.primaryKey().autoIncrement(),
      "userId": Int.foreignKey("users", onDelete: OnDelete.cascade),
      "bio": String().nullable(),
      "avatar": String().nullable(),
    },
    converters: (fromJson: Profile.fromJson, toJson: Profile.toJson),
);

// Query
@dataClass
class SelectedUser {
  const factory SelectedUserProfile({
    int id,
    String fullName,
    SelectedUserProfile profile,
  }) = _SelectedUserProfile;
}
@dataClass
class SelectedUserProfile {
  const factory SelectedUserProfile({
    String bio,
    String? avatar,
  }) = _SelectedUserProfile;
}

final user = await usersSchema.findFirst().where({"id": isEqual(1)}).include(
  "profile",
  profilesSchema,
  on: "profileId", // Can be omitted as defined in schema
  where: {"bio": isNotEmpty()},
).select(
  {
    "id",
    "fullName",
    "profile": {"id", "bio"},
  },
).exec(
  converter: SelectedUser.fromJson, // Can be omitted if no select or aggregation operations are used
);

// init

await Dorm.init(
  schemas: [usersSchema, profilesSchema],
  adapter: DormSqliteAdapter(
    connection: {}
    { options }
  )
  { options }
);

The other most important feature would be to consider migrations, to generate, run and keep track of migrations.

We can create a package executable in the dartseid orm package to handle these things.

Let's discuss further.