How to generate relationship properties through .moor file?

[JCKodel]

I have a .moor file like this:

CREATE TABLE Foos (
  id TEXT NOT NULL PRIMARY KEY,
  name TEXT NOT NULL
) AS Foo;

CREATE TABLE Bars (
  id TEXT NOT NULL PRIMARY KEY,
  fooId TEXT NOT NULL REFERENCES Foos(id),
  name TEXT NOT NULL
) AS Bar;

Now I need the class Foo to be generated as:

class Foo extends DataClass implements Insertable<Foo> {
  final String id;
  final String name;
  final List<Bar> bars;
}

But it won't generate the bars property =\

I know I can always use Dart to generate the table classes and use .moor only for indexes and queries, but I wonder if some of these would be possible:

1) To automatically generate relationship properties (list of bars in foo and an instance of foo in each bar).

2) To allow we to apply one or more mixins: Let's say we have this mixin:

abstract class ListOfBarsMixin {
  final List<Bar> = List<Bar>();
}

And in the .moor file we could:

CREATE TABLE Foos (
  id TEXT NOT NULL PRIMARY KEY,
  name TEXT NOT NULL
) AS Foo WITH ListOfBarsMixin;

Notice the WITH [ListOfMixins].

That would generate the following:

class User extends DataClass with BaseUser implements Insertable<User> {
}

3) Allow us to create queries and indexes outside .moor files (i.e.: don't use .moor files at all):

@Index(name: "IX_Foo_IdAndName", fields: ["id", "name"])
@Index(name: "IX_Foo_OnlyName", fields: ["name"])
class Foo extends Table {
...
}

@UseMoor(tables: [Todos, Categories])
class MyDatabase extends _$MyDatabase {

  @Insert("INSERT INTO Foo(id, name) VALUES (:id, :name)")
  Future<int> anyName(String id, String name); // If bodyless are not allowed, an empty body will not hurt

  @Select("SELECT * FROM Foo WHERE id = :id")
  Future<Foo> getFooById(String id);  // It will use `getSingle` if the return is not a list or get otherwise
}

[simolus3]

Thanks for the ideas!

To automatically generate relationship properties (list of bars in foo and an instance of foo in each bar).

Doing this based on CREATE TABLE statements alone will probably result in inaccurate code. Just because there is some other table Bars referencing Foos, it doesn't necessarily mean that a list of Bar is a logical property of Foo. It would also break queries in moor files, since it's impossible to write a query having a List<Bar> in a column.

allow we to apply one or more mixins:

We already have #114 as an issue for this.

Allow us to create queries and indexes outside .moor files (i.e.: don't use .moor files at all):

If it helps, you can write queries in a @UseMoor and @UseDao annotation (under the queries parameter). The syntax you're suggesting (having an annotated abstract method) would be hard to implement now, since moor will generate a superclass for you. To support abstract methods, it would have to generate an implementation for the interface you provide.

In general, I don't really see moor as an ORM. To me, it's more of a convenience framework around sql, so it inherits most of its concepts. I know there is interest in adding more ORM-like features, but those would also add a lot of complexity to the project. This includes automatic joins or subqueries (which we would need when generating a List<Bar>) or customizing the generated classes. In my experience, attempting to prescind a relational model rarely works and can easily add a lot of complexity. See also What ORMs have taught me: just learn SQL and Embracing SQL without abstraction. I'm not saying that those features don't add value, but I'm reluctant to work on them if they're very complex.