DT
Drizzle TeamAdamRackis

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!
Angelelz
Angelelz•246d 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.
AdamRackis
AdamRackis•246d 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
Angelelz•246d 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
AdamRackis
AdamRackis•246d 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
Angelelz•246d ago
What's the purpose of InferSelection? Just infer the time of a column?
AdamRackis
AdamRackis•246d 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
Angelelz•246d ago
Nice work Mr. typescript wizard!
AdamRackis
AdamRackis•246d ago
😄
Want results from more Discord servers?
Add your server
More Posts
Issue with drizzle-zod?Schema ``` export const testinArray = pgTable("testing_array", { id: varchar("id", { length: 14 Optional One-to-One RelationCurrently it's possible to define only a One-to-One relation (https://orm.drizzle.team/docs/rqb#one-Having Issues Pushing Database SchemaHi I am getting this issue whenever I am trying to push the database schema. Any help would be greatmigration failedcode: ```js const migrationClient = postgres("postgresql://jer:admin@localhost:5432/test", { max: 1TypeError: Cannot read properties of undefined (reading 'compositePrimaryKeys')I am attempting to run db push, but I am getting this error above^^ does anyone know what might be Nesting sqlite drizzle into a deeper location in my ts app, difficulties with src/ vs dist/Hey! I'm very new to drizzle and trying to set up a very small drizzle instance for a service that rsyntax highlighting not workingI reinstalled the node_modules multiple times and it didnt fix my problem. This isnt suposed to lookPrepare raw sql query (Postgres)Hi, I need to do a recursive query, so I think I need to use the magic sql operator to build it. Is packing migration files with the libraryhey there, I'm trying to use `migrate` inside from a library consumer (i.e I have a `@mycompany/db-lAny way to invoke a SQL function?For ex: lets say I declare a SQL function `CREATE OR REPLACE FUNCTION do_something()` how do i invoHow to include array literals in raw sql?I have an input array used for an order by using the array_position function - rather than passing tMySQL Table SchemasI need to generate the following structure of tables: `1. Sales 2.Sales ....` Currently if I use wany way to automatically cast numeric types on retrieval?I have a numeric field in a video table: ``` export const VideoTable = pgTable('videos', { id: uuiQuery in drizzle doesn't work## Error: ```console DrizzleError: No fields selected for table "usersToGroups" ("users_groups") ``Cannot read properties of undefined (reading 'referencedTable')Seemingly getting this error out of nowhere. I believe it's related to the relations I defined, but Relational query builder in mysql?I cant find anything about how to implement this. Am I blind or is this impossible?TransactionRollbackErrorNeed some help understanding TransactionRollbackError... Is this error saying the `tx.rollback()` fPostgresError: null value in column "id" of relation "pairs" violates not-null constraintHello, I am fairly new to Drizzle and working with databases, so I am running into a problem that I OnConflictDoNothing with mysqlHi everyone, I want to use the on conflict do nothing method described here: https://orm.drizzle.teadrizzle-kit introspect reorder tablesHere is the case: - I have a remote database. I made a backup and restore it locally - I ran: drizz