Some ORM thoughts

[alpharder]

...continuing discussion @ Discord:

Manually defining what will be selected

I think we should explicitly differentiate and give names for two approaches:

Array-based return

which you originally demonstrated. It doesn't populate any relations and query result set contains non-nested objects.

example with implicit join:

const result = query((user: Select<User>) => [user.id, user.age, as(user.group.id, 'group')}]);

example with explicit join:

const result = query((user: Select<User>) => {
     const group = join(user.group);
     return [user.id, group.id];
 });
// result type -> {id: User['id'], group_id: Group['id']}
// "group_id" key name is autogenerated using some common sense logic

example with explicit join and renaming a field:

const result = query((user: Select<User>) => {
     const group = join(user.group);
     return [user.id, as(group.id, 'gid')];
 });
// result type -> {id: User['id'], gid: Group['id']}

Potential problems with array-based return:

• It's convenient when you get it, but the initial impression could be weird, because selected fields are described inside of an array, but the result set would contain objects. Probably returning select([x, y, z]) instead of just [x, y, z] would make it more intuitive to use, because ppl are used to this concept: describe what you select in an array (or in pure SQL just comma-separated values which have similar syntax to arrays) and get an object as a result.

(TBD)-based return

Populates relations and thus query result set may contain nested objects of populated relations (including partially populated with inferred types)

Option A

    const result = query((user: Select<User>) => {
        const group = join(user.group);
        return {id: user.id, group: {id: group.id}};
    });

 // inferred type Pick<User, 'id'> & {group: Pick<Group, 'id'>}
    

Problems:

• You have to manually construct the shape of the resulting object
• When populated relation is an array of objects (e.g. groups) it could be confusing because you still have to define its shape as an object (e.g. groups: {id: group.id})

Option B

  const result = query((user: Select<User>) => {
        const group = join(user.group);
        return pick(user, ['id', 'age', pick(group, 'id')}]);
  });
 // inferred type Pick<User, 'id'> & {group: Pick<Group, 'id'>}

Option B could be just a helper for Option A.

Declaring what will be selected using relations

// returns "valid" NoReferences<User>, just an alias for return populate(user)
const user = query((user: Select<User>) => {
   return user; 
});

// returns "valid" NoReferences<User>
const user = query((user: Select<User>) => {
   return populate(user); 
});

// returns "valid" `NoReferences<User> & {group: Group}`, so user.group.name works
const user = query((user: Select<User>) => {
   return populate(user).with(user.group);
});

// returns "valid" `NoReferences<User> & {group: PksOnly<Group>}`, so user.group.name throws actual typescript type error
const user = query((user: Select<User>) => {
   return populate(user).withPks(user.group);
});

// if you want to have invalid types, you have to manually explicitly do that

// returns "invalid" full User type. Throws runtime errors when accessing non-primary key fields of references
const user = query((user: Select<User>) => {
   return nonstrict(populate(user).withPks(user.group)); // naming is questionable but the main idea is explicitness 
});

// returns "invalid" full User type. Throws runtime errors when accessing references
const user = query((user: Select<User>) => {
   return nonstrict(populate(user));
});

// syntax sugar for "invalid" full User type
const user = queryUnsafe((user: Select<User>) => where(equal(user.id, 15)));