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•8mo ago

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

James•8mo 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•8mo 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

James•8mo 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•8mo 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•8mo ago

Cool, it feels like this solution should work

iolyd•8mo ago

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

warflash•8mo 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•8mo ago

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

warflash•8mo 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•8mo 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•8mo ago

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

Gary, el Pingüino Artefacto•8mo ago

Gary, el Pingüino Artefacto•8mo ago

which version of drizzle are you using?

iolyd•8mo 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;
};

Gaming

Programming

Extract type from SubQuery