Extract type from SubQuery

If I have a query like this I intend to use as a subquery:

const foo = db.select({
    id: fooTable.id,
    name: fooTable.name,
    enabled: fooTable.enabled,
}).from(fooTable).as('foo');

const foo = db.select({
    id: fooTable.id,
    name: fooTable.name,
    enabled: fooTable.enabled,
}).from(fooTable).as('foo');

Is there any way I can extract a type from this, such like:

type Foo = {
    id: number;
    name: string;
    enabled: boolean;
}

type Foo = {
    id: number;
    name: string;
    enabled: boolean;
}

18 Replies

Angelelz•11mo ago

Can you show an example of how you would use such a type?

JamesOP•11mo ago

Sure! This is incomplete and non-functional (and this specific example could be done with better queries), missing where and joins, but something like this:

const articles = db.select({
    id: articlesTable.id,
    name: articlesTable.name,
    content: articlesTable.content,
}).from(articlesTable).as('articles');

const authors = await db.select({
    id: authorsTable.id,
    name: authorsTable.name,
    articles: sql`json_agg(${articles})`,
}).from(authorsTable);

const articles = db.select({
    id: articlesTable.id,
    name: articlesTable.name,
    content: articlesTable.content,
}).from(articlesTable).as('articles');

const authors = await db.select({
    id: authorsTable.id,
    name: authorsTable.name,
    articles: sql`json_agg(${articles})`,
}).from(authorsTable);

Essentially, using the subquery in an aggregate function like json_agg to return multiple points of data per single point. I want to have a good type to pass into sql without manually defining it, being able to infer from the articles query, especially since in practice my queries are much larger than this. That way, articles doesn't come back as unknown, but as an actual typed result from the query prior.

Angelelz•11mo ago

Got you, thanks for the clarification. This is a legit use case, let me see if I can help you find a workatound. We'll inevitably have to use drizzle internal types cause I don't think we have a helper for this

JamesOP•11mo ago

Amazing, thank you! I poked around a little bit but I'm definitely not familiar enough with Drizzle's internal types for this 😅

iolyd•11mo ago

You could also build sql helpers with generic types for the various sql functions you use to avoid you the trouble of manually typing things every time you want to do things like json_agg, row_to_json, etc. I'm using something like this in a project of mine, maybe this could help?

export function jsonAgg<T extends AnyTable<TableConfig> | AnyColumn>(
  selection: T,
  { notNull = true }: { notNull?: boolean } = {}
) {
  type R = T extends AnyTable<TableConfig>
    ? InferSelectModel<T>
    : T extends AnyColumn
      ? InferColumnDataType<T>
      : T;
  if (notNull) {
    return sql<R[] | null>`json_agg(${selection}) filter (where ${selection} is not null)`;
  }
  return sql<R[] | null>`json_agg(${selection})`;
}

export function jsonAgg<T extends AnyTable<TableConfig> | AnyColumn>(
  selection: T,
  { notNull = true }: { notNull?: boolean } = {}
) {
  type R = T extends AnyTable<TableConfig>
    ? InferSelectModel<T>
    : T extends AnyColumn
      ? InferColumnDataType<T>
      : T;
  if (notNull) {
    return sql<R[] | null>`json_agg(${selection}) filter (where ${selection} is not null)`;
  }
  return sql<R[] | null>`json_agg(${selection})`;
}

Small warning if you go this route: generic types can get a bit messy when you want helpers to handle not only table arguments but also subqueries (Subquery | SubqueryWithSelection | WithSubqueryWithSelection and etc.)

Angelelz•11mo ago

Cool, it feels like this solution should work

iolyd•11mo ago

@Angelelz do you know if there's a plan for drizzle to provide such helpers of their own?

warflash•11mo ago

Perfect coincidence as that's just what we're looking for as well. Trying to define different subqueries and merge them into a final output using json_agg. Native support would be very nice though I do have to say 😅

Angelelz•11mo ago

Yeah, there is a gist running around with this type of helpers that will eventually end up in drizzle codebase

warflash•11mo ago

Quick question, like I said we're looking to do a similar pattern. However I'm curious how you would pass data to that subselect. Like if you wanted to filter for a specific author in your example then adding the where clause to authors is easy. However for articles by that author that seems impossible to do? We've been banging our heads against the wall for days now and can't find a nice solution unfortunately @iolyd Have you by chance ever run into this usecase as well? The pattern looks so close to what we're looking for and where doing without an ORM but the final piece of the puzzle seems to be missing

SELECT authorsTable.id, authorsTable.name,
    (
        SELECT json_agg(sub_articles)
        FROM (
            SELECT id, name, content
            FROM articlesTable
            -- Looking for a way to add this clause, referencing the outer table in the subquery we inject like in your example
            WHERE articlesTable.author_id = authorsTable.id
        ) AS sub_articles
    ) AS articles
FROM authorsTable;

SELECT authorsTable.id, authorsTable.name,
    (
        SELECT json_agg(sub_articles)
        FROM (
            SELECT id, name, content
            FROM articlesTable
            -- Looking for a way to add this clause, referencing the outer table in the subquery we inject like in your example
            WHERE articlesTable.author_id = authorsTable.id
        ) AS sub_articles
    ) AS articles
FROM authorsTable;

iolyd•11mo ago

Hard to test without a db I can hit, but I feel something like this should work:

const subArticles = db
  .select({
    id: articlesTable.id,
    name: articlesTable.name,
    content: articlesTable.content,
  })
  .from(articlesTable)
  .where(eq(articlesTable.authorId, authorsTable.id))
  .as('sub_articles');

const articlesByAuthors = await db
  .select({ id: authorsTable.id, name: authorsTable.name, articles: jsonAgg(subArticles) })
  .from(authorsTable);

const subArticles = db
  .select({
    id: articlesTable.id,
    name: articlesTable.name,
    content: articlesTable.content,
  })
  .from(articlesTable)
  .where(eq(articlesTable.authorId, authorsTable.id))
  .as('sub_articles');

const articlesByAuthors = await db
  .select({ id: authorsTable.id, name: authorsTable.name, articles: jsonAgg(subArticles) })
  .from(authorsTable);

In this case, the generic type of jsonAgg would need to be expanded a bit to account for Subquery With modified types for the jsonAgg helper I can confirm passing subqueries should work as expected:

export function jsonAgg<T extends Table | Column | Subquery | AnyPgSelect>(
  selection: T,
  { notNull = true }: { notNull?: boolean } = {}
): SQL<
  T extends Table
    ? InferSelectModel<T>
    : T extends Column
      ? InferColumnDataType<T>[]
      : T extends Subquery
        ? InferRecordDataTypes<T['_']['selectedFields']>[]
        : T extends AnyPgSelect
          ? Awaited<T>
          : never
> {
  if (notNull) {
    return sql`json_agg(${selection}) filter (where ${selection} is not null)`;
  }
  return sql`json_agg(${selection})`;
}

export function jsonAgg<T extends Table | Column | Subquery | AnyPgSelect>(
  selection: T,
  { notNull = true }: { notNull?: boolean } = {}
): SQL<
  T extends Table
    ? InferSelectModel<T>
    : T extends Column
      ? InferColumnDataType<T>[]
      : T extends Subquery
        ? InferRecordDataTypes<T['_']['selectedFields']>[]
        : T extends AnyPgSelect
          ? Awaited<T>
          : never
> {
  if (notNull) {
    return sql`json_agg(${selection}) filter (where ${selection} is not null)`;
  }
  return sql`json_agg(${selection})`;
}

Note that I haven't tested queries with aggregated non-aliased subqueries (Any[dialect]Select) through drizzle, so I don't know if it's valid that I also handle these. But db.select().from().as() subqueries work without problem!

Gary, el Pingüino Artefacto•11mo ago

hi, i was casually trying to steal this code and noticed some unknown types

Gary, el Pingüino Artefacto•11mo ago

Gary, el Pingüino Artefacto•11mo ago

which version of drizzle are you using?

iolyd•11mo ago

@Gary, el Pingüino Artefacto Those are some types I made, sorry I forgot to include them!

type InferSQLDataType<T extends SQL | SQL.Aliased> =
  T extends SQL<infer U> ? U : T extends SQL.Aliased<infer U> ? U : never;

type InferColumnDataType<T extends Column> = T['_']['notNull'] extends true
  ? T['_']['data']
  : T['_']['data'] | null;

type InferRecordDataTypes<T extends Record<string, Column | SQL | SQL.Aliased>> = {
  [K in keyof T]: T[K] extends SQL | SQL.Aliased
    ? InferSQLDataType<T[K]>
    : T[K] extends Column
      ? InferColumnDataType<T[K]>
      : never;
};

type InferSQLDataType<T extends SQL | SQL.Aliased> =
  T extends SQL<infer U> ? U : T extends SQL.Aliased<infer U> ? U : never;

type InferColumnDataType<T extends Column> = T['_']['notNull'] extends true
  ? T['_']['data']
  : T['_']['data'] | null;

type InferRecordDataTypes<T extends Record<string, Column | SQL | SQL.Aliased>> = {
  [K in keyof T]: T[K] extends SQL | SQL.Aliased
    ? InferSQLDataType<T[K]>
    : T[K] extends Column
      ? InferColumnDataType<T[K]>
      : never;
};

Gary, el Pingüino Artefacto•11mo ago

😄

warflash•11mo ago

Thanks for sharing and I very much appreciate the update! I really hope improvements in that regard land in the repo itself sometime down the line as at the moment the DX with anything complex really is not there yet. I feel like and you spend more time fighting types and working around things than actually writing queries. But we'll put that to great use in the meantime

iolyd•11mo ago

Couldn't agree more, imho the typing system currently is excessively hermetic, inconsistent across query types, and poorly documented. With earlier versions I even faced situations where accessing certain types required using Symbols that drizzle didn't even expose. But I have to say that now things are getting better, and the fact you can achieve almost anything with sql tagged template literals is super appreciated on my part.

Gaming

Programming

Extract type from SubQuery