InferModelFromColumns with columns defined with sql``

Let's say I have a select list that looks like this

const defaultBookFields = {
  id: books.id,
  tags: sql<number[]>`(SELECT JSON_ARRAYAGG(tag) from books_tags WHERE book = \`books\`.id)`.as("tags"),
  subjects: sql<number[]>`(SELECT JSON_ARRAYAGG(subject) from books_subjects WHERE book = \`books\`.id)`.as("subjects"),
  title: books.title
};

const defaultBookFields = {
  id: books.id,
  tags: sql<number[]>`(SELECT JSON_ARRAYAGG(tag) from books_tags WHERE book = \`books\`.id)`.as("tags"),
  subjects: sql<number[]>`(SELECT JSON_ARRAYAGG(subject) from books_subjects WHERE book = \`books\`.id)`.as("subjects"),
  title: books.title
};

This works fine, but if I try to use it with InferModelFromColumns, like so export type FullBook = InferModelFromColumns<typeof defaultBookFields>; I get a TS error - the tags and subjects fields are not Columns, but rather ad hoc SQL results. I do know how to work around this. I could easily Omit<> those two columns, and then manually append a tabs and subjects property of the right type. But is there any way to make this work as is? Absolutely love this library by the way - thank you for the amazing work!

8 Replies

Angelelz•14mo ago

I believe InferModelFromColumns is an internal type used for db.select() when the user doesn't pass anything to it. It's not meant to be used in this way. I would suggest you do:

type FullBook = typeof Awaited<typeof db.select(defaultBookFields).from(...).execute()>

type FullBook = typeof Awaited<typeof db.select(defaultBookFields).from(...).execute()>

I'm not sure that'll work. I'm don't have access to something to test.

AdamRackisOP•14mo ago

I don't think (afaik) you can evaluate expressions like that, in type space. I got as far as this

type X = ReturnType<typeof db.select<typeof defaultBookFields>>;
type Y = ReturnType<X["from"]>;
type FulBook = ArrayOf<Awaited<ReturnType<Y["execute"]>>>;

type X = ReturnType<typeof db.select<typeof defaultBookFields>>;
type Y = ReturnType<X["from"]>;
type FulBook = ArrayOf<Awaited<ReturnType<Y["execute"]>>>;

but the resulting type has a bunch of never types on it. In fact, everything is never except for my tags and subjects, presumably because I never passed a generic type to from but I'm not sure how to do that, and

type Z = ReturnType<Y<typeof books>

type Z = ReturnType<Y<typeof books>

doesn't work (even if I remove the ReturnType from Y)

Angelelz•14mo ago

You're right. What about this:

const query = db.select(defaultBookFields).from(...)  // <-- notice without the await it won't execute the query
type FullBook = Awaited<ReturnType<typeof query["execute"]>>

const query = db.select(defaultBookFields).from(...)  // <-- notice without the await it won't execute the query
type FullBook = Awaited<ReturnType<typeof query["execute"]>>

Just pass to from whatever you're passing in your query query IS a runtime value

AdamRackisOP•14mo ago

Ohhhhh I think that would work. Very clever! Incidentally, any idea why this doesn't work. This feels like it should

type InferSelection<T> = T extends MySqlColumn<infer U, object> ? U["data"] : T extends SQL.Aliased<infer V> ? V : never;

type InferSelection<T> = T extends MySqlColumn<infer U, object> ? U["data"] : T extends SQL.Aliased<infer V> ? V : never;

But I get bizarre ts errors like

Type 'MySqlColumn<{ name: "id"; tableName: "books"; dataType: "number"; columnType: "MySqlInt"; data: number; driverParam: string | number; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, object>' does not satisfy the constraint 'Table<TableConfig<Column<any, object, object>>>'. Type 'MySqlColumn<{ name: "id"; tableName: "books"; dataType: "number"; columnType: "MySqlInt"; data: number; driverParam: string | number; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, object>' is missing the following properties from type 'Table<TableConfig<Column<any, object, object>>>': $inferSelect, $inferInsert, [IsDrizzleTable]

Angelelz•14mo ago

What's the purpose of InferSelection? Just infer the time of a column?

AdamRackisOP•14mo ago

That's a the type helper I'm trying to write to do what's described in the question. Ie, that's my attempted solution of this This seems to work. It's not pretty, but it seems to work. Unfortunately the resulting type is a union, the first member of which has a bunch of nevers. But it looks like the subsequent unions (which intellisense does not reveal) make the end result correct

type This = ArrayOf<Awaited<ReturnType<MySqlSelectBuilder<typeof defaultBookFields, any>["from"]>>>;

type This = ArrayOf<Awaited<ReturnType<MySqlSelectBuilder<typeof defaultBookFields, any>["from"]>>>;

Got it! I was close with my InferSelection<T>. Here's how you do it

type ExtractTypeFromMySqlColumn<T extends MySqlColumn> = T extends MySqlColumn<infer U>
  ? U extends { notNull: true }
    ? U["data"]
    : U["data"] | null
  : never;

type ExtractSqlType<T> = T extends MySqlColumn<infer U, object> ? ExtractTypeFromMySqlColumn<T> : T extends SQL.Aliased<infer V> ? V : never;
type InferSelection<T> = {
  [K in keyof T]: ExtractSqlType<T[K]>;
};

type ExtractTypeFromMySqlColumn<T extends MySqlColumn> = T extends MySqlColumn<infer U>
  ? U extends { notNull: true }
    ? U["data"]
    : U["data"] | null
  : never;

type ExtractSqlType<T> = T extends MySqlColumn<infer U, object> ? ExtractTypeFromMySqlColumn<T> : T extends SQL.Aliased<infer V> ? V : never;
type InferSelection<T> = {
  [K in keyof T]: ExtractSqlType<T[K]>;
};

Which yields this, copied from the type's intellisense

type Test = {
    id: number;
    tags: number[];
    subjects: number[];
    title: string;
    pages: number | null;
    userId: string;
    authors: string[] | null;
    isbn: string | null;
    publisher: string | null;
    publicationDate: string | null;
    isRead: number;
    dateAdded: Date;
    mobileImage: string | null;
    mobileImagePreview: unknown;
    smallImage: string | null;
    smallImagePreview: unknown;
    mediumImage: string | null;
    mediumImagePreview: unknown;
}

type Test = {
    id: number;
    tags: number[];
    subjects: number[];
    title: string;
    pages: number | null;
    userId: string;
    authors: string[] | null;
    isbn: string | null;
    publisher: string | null;
    publicationDate: string | null;
    isRead: number;
    dateAdded: Date;
    mobileImage: string | null;
    mobileImagePreview: unknown;
    smallImage: string | null;
    smallImagePreview: unknown;
    mediumImage: string | null;
    mediumImagePreview: unknown;
}

Angelelz•14mo ago

Nice work Mr. typescript wizard!

AdamRackisOP•14mo ago

😄

Gaming

Programming

InferModelFromColumns with columns defined with sql``