Infer relation attributes from column foreignKey (or vice versa)

[IlyaSemenov]

I stumbled upon repetitive boilerplate when defining foreign keys. Consider user-posts models:

class UserTable extends BaseTable {
  readonly table = "user"

  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    name: t.string(1, 100),
  }))

  // No relations for simplicity.
}

class PostTable extends BaseTable {
  readonly table = "post"

  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    content: t.text(1, 10000),
    user_id: t.integer().foreignKey(() => UserTable, "id"),
  }))

  relations = {
    user: this.belongsTo(() => UserTable, {
      required: true,
      primaryKey: "id",
      foreignKey: "user_id",
    }),
  }
}

In the code above, user has to couple UserTable.id with PostTable.user_id twice: first in columns, then in relations. What if this could be optionally automated/inferred?

Such as with:

relations = {
  user: this.belongsToViaColumn("user_id") // Somehow refer to the column with foreignKey
}

or:

columns = this.setColumns((t) => ({
  user_id: t.integer().foreignKeyViaRelation("user") // Somehow refer to the table relation
}))

(Ugly naming intended for demonstration purposes.)

[romeerez]

user_id column already has a foreign key pointing to UserTable, so in theory, it's possible for a belongsTo (and others) method to automatically walk through the columns, find a column with a matching foreign key, infer user_id from the name of the found column, infer required from the fact that the column is not null, infer id from UserTable primary key.

It requires some research and TS performance considerations, and I'm leaving it for the future.