Getting references from a column in types

[tharropoulos]

Having a schema that looks like this:

type SchemaTables = {
    readonly session: PgTableWithColumns<{
        name: "session";
        schema: undefined;
        columns: {
            id: PgColumn<{
                name: "id";
                tableName: "session";
                dataType: "string";
                columnType: "PgUUID";
                data: string;
                driverParam: string;
                notNull: true;
                hasDefault: true;
                ... 6 more ...;
                generated: undefined;
            }, {}, {}>;
            userId: PgColumn<...>;
            expiresAt: PgColumn<...>;
        };
        dialect: "pg";
    }>;
    readonly test: PgTableWithColumns<...>;
    readonly user: PgTableWithColumns<...>;
}

and the Session table references the User table:

export const session = pgTable("session", {
  id: uuid("id").defaultRandom().primaryKey(),
  userId: uuid("user_id")
    .notNull()
    .references(() => user.id),
  expiresAt: timestamp("expires_at", {
    withTimezone: true,
    mode: "date",
  }).notNull(),
});

Getting the referencing tables

I've built a very primitive mapped type to get the names of the tables referencing a table by its name:

export type PgTableReferences<T extends keyof SchemaTables> = {
  [K in keyof SchemaTables]: K extends string ?
    T extends string ?
      `${T}Id` extends keyof SchemaTables[K] ?
        SchemaTables[K][`${T}Id` & keyof SchemaTables[K]]["columnType" &
          keyof SchemaTables[K][`${T}Id` & keyof SchemaTables[K]]] extends "PgUUID" ?
          K
        : never
      : never
    : never
  : never;
}[keyof SchemaTables];

But its heavily reliant on the id field's type + the naming scheme always referencing in the format of <table>Id (.e.g userId).

This can also be easily spoofed by having a non-referencing column defined while meeting the criteria.

I want to use it on a more generic level:

  joinByIdWith<const Select extends PgTableReferences<"user">>(
    ref: string,
    table: Select,
  ): ResultAsync<JoinedTableResult<"user", Select>, NotFoundRepositoryError | UnsafeError> {
    return ResultAsync.fromPromise(
      this.db
        .select({ user: schema.user, [table]: schema[table] })
        .from(schema.session)
        .innerJoin(schema.user, eq(schema[table].userId, schema.user.id))
        .where(eq(schema.session.id, ref)),
      handleUnsafeError,
    ).andThen((rows) => {
      if (rows.length === 0) return errAsync(NotFoundRepositoryError);

      return okAsync(rows[0] as unknown as JoinedTableResult<"user", Select>);
    });
  }

Is it even possible with the current API or have I not found the correct types?

[tharropoulos]

I've submitted a PR at #4388. Feedback is more than welcome!