Weird Query builder behavior (extras)

Hello! I have experiencing weird situation while using Drizzle query builder. My code is the following:

// schema.ts
import * as db from "drizzle-orm/pg-core";

export const products = db.pgTable('products', {
  id: db.char('id', { length: 26 }).primaryKey().$defaultFn(() => ulid()),
   // other fields ...
});

export const pricesMinMaxView = db.pgMaterializedView('latest_prices_minmax', {
  productId: db.char('productId', { length: 26 }).notNull(),
  avg_price: db.real('avg_price'),
}).existing();

// index.ts
import * as schema from './schena.ts';
const sqlClient = postgres(env.PG_URL);
const db = drizzle(sqlClient, {
  logger: true,
  schema,
});

// actual code
console.log((db.dialect as PgDialect)?.sqlToQuery(sql<number | null>`(select ${schema.pricesMinMaxView.avg_price} from ${schema.pricesMinMaxView} where ${eq(schema.pricesMinMaxView.productId, schema.products.id)} limit 1)`.as('t').getSQL()).sql);
// prints: (select "latest_prices_minmax"."avg_price" from "latest_prices_minmax" where "latest_prices_minmax"."productId" = "products"."id" limit 1)

// BUT:
console.log(db.query.products.findMany({
  columns: {
    id: true,
  },
  extras: {
    avgPrice: sql<number | null>`(select ${schema.pricesMinMaxView.avg_price} from ${schema.pricesMinMaxView} where ${eq(schema.pricesMinMaxView.productId, schema.products.id)} limit 1)`.as('avgPrice'),
  },
  limit: 10
}).toSQL().sql())
// prints: select "id", (select "avg_price" from "latest_prices_minmax" where "products"."productId" = "products"."id" limit 1) as "avgPrice" from "products" limit $1

// schema.ts
import * as db from "drizzle-orm/pg-core";

export const products = db.pgTable('products', {
  id: db.char('id', { length: 26 }).primaryKey().$defaultFn(() => ulid()),
   // other fields ...
});

export const pricesMinMaxView = db.pgMaterializedView('latest_prices_minmax', {
  productId: db.char('productId', { length: 26 }).notNull(),
  avg_price: db.real('avg_price'),
}).existing();

// index.ts
import * as schema from './schena.ts';
const sqlClient = postgres(env.PG_URL);
const db = drizzle(sqlClient, {
  logger: true,
  schema,
});

// actual code
console.log((db.dialect as PgDialect)?.sqlToQuery(sql<number | null>`(select ${schema.pricesMinMaxView.avg_price} from ${schema.pricesMinMaxView} where ${eq(schema.pricesMinMaxView.productId, schema.products.id)} limit 1)`.as('t').getSQL()).sql);
// prints: (select "latest_prices_minmax"."avg_price" from "latest_prices_minmax" where "latest_prices_minmax"."productId" = "products"."id" limit 1)

// BUT:
console.log(db.query.products.findMany({
  columns: {
    id: true,
  },
  extras: {
    avgPrice: sql<number | null>`(select ${schema.pricesMinMaxView.avg_price} from ${schema.pricesMinMaxView} where ${eq(schema.pricesMinMaxView.productId, schema.products.id)} limit 1)`.as('avgPrice'),
  },
  limit: 10
}).toSQL().sql())
// prints: select "id", (select "avg_price" from "latest_prices_minmax" where "products"."productId" = "products"."id" limit 1) as "avgPrice" from "products" limit $1

The question is: why Drizzle generates different query, replacing table name in some cases? Maybe i am doing something wrong? Thanks in advance! (drizzle-orm: 0.29.3, node 20)

17 Replies

Angelelz•11mo ago

Can you try the extras like this?

extras: {
    avgPrice: db.select({ avgPrice: schema.pricesMinMaxView.avg_price })
      .from(schema.pricesMinMaxView)
      .where(eq(schema.pricesMinmaxView.productId, schema.products.id))
      .limit(1)
      .as("avgPrice")
      .avgPrice,
  }

extras: {
    avgPrice: db.select({ avgPrice: schema.pricesMinMaxView.avg_price })
      .from(schema.pricesMinMaxView)
      .where(eq(schema.pricesMinmaxView.productId, schema.products.id))
      .limit(1)
      .as("avgPrice")
      .avgPrice,
  }

Or better yet, define the subquery outside

irelynxOP•11mo ago

Thanks for your answer, but unfortunately it didn't help much: 1. TypeScipt linter arguing:

Type 'PgColumn<{ name: "avg_price"; tableName: "avgPrice"; dataType: "number"; columnType: "PgReal"; data: number; driverParam: string | number; notNull: false; hasDefault: false; enumValues: undefined; baseColumn: never; }, {}, {}>' is missing the following properties from type 'Aliased<unknown>': sql, fieldAlias ts(2739)

Type 'PgColumn<{ name: "avg_price"; tableName: "avgPrice"; dataType: "number"; columnType: "PgReal"; data: number; driverParam: string | number; notNull: false; hasDefault: false; enumValues: undefined; baseColumn: never; }, {}, {}>' is missing the following properties from type 'Aliased<unknown>': sql, fieldAlias ts(2739)

2. Also, there is runtime error:

TypeError: Cannot read properties of undefined (reading 'queryChunks')
    at mapColumnsInSQLToAlias (node_modules/.pnpm/[email protected][email protected]/node_modules/src/alias.ts:111:24)
    at mapColumnsInAliasedSQLToAlias (node_modules/.pnpm/[email protected][email protected]/node_modules/src/alias.ts:107:25)
    at PgDialect.buildRelationalQueryWithoutPK (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/dialect.ts:1143:14)
    at QueryPromise._getQuery (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:109:23)
    at QueryPromise._toSQL (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:126:22)
    at <anonymous> (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:85:39)
    at Object.startActiveSpan (node_modules/.pnpm/[email protected][email protected]/node_modules/src/tracing.ts:27:11)
    at QueryPromise._prepare (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:84:17)
    at <anonymous> (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:139:16)
    at Object.startActiveSpan (node_modules/.pnpm/[email protected][email protected]/node_modules/src/tracing.ts:27:11)

TypeError: Cannot read properties of undefined (reading 'queryChunks')
    at mapColumnsInSQLToAlias (node_modules/.pnpm/[email protected][email protected]/node_modules/src/alias.ts:111:24)
    at mapColumnsInAliasedSQLToAlias (node_modules/.pnpm/[email protected][email protected]/node_modules/src/alias.ts:107:25)
    at PgDialect.buildRelationalQueryWithoutPK (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/dialect.ts:1143:14)
    at QueryPromise._getQuery (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:109:23)
    at QueryPromise._toSQL (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:126:22)
    at <anonymous> (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:85:39)
    at Object.startActiveSpan (node_modules/.pnpm/[email protected][email protected]/node_modules/src/tracing.ts:27:11)
    at QueryPromise._prepare (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:84:17)
    at <anonymous> (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:139:16)
    at Object.startActiveSpan (node_modules/.pnpm/[email protected][email protected]/node_modules/src/tracing.ts:27:11)

Angelelz•11mo ago

Of all the error I thought it would show, that was not one of them import * as db from "drizzle-orm/pg-core ??

irelynxOP•11mo ago

i tried to remove .as("avgPrice").avgPrice and added .getSQL().as('avgPrice'), then query builds, but it looks like this:

select "id", select "avg_price" from "latest_prices_minmax" where "products"."productId" = "products"."id" limit $1 as "avgPrice" from "products" where "products"."id" = $2 limit $3

select "id", select "avg_price" from "latest_prices_minmax" where "products"."productId" = "products"."id" limit $1 as "avgPrice" from "products" where "products"."id" = $2 limit $3

what do you mean by this message?

Angelelz•11mo ago

I just thought it was weird to name it that, but I see you probably did that only in the schema file

irelynxOP•11mo ago

yeah, sorry for awkward naming

Angelelz•11mo ago

I guess try it like this. Typescript playground

TS Playground - An online editor for exploring TypeScript and JavaS...

The Playground lets you write TypeScript or JavaScript online in a safe and sharable way.

irelynxOP•11mo ago

now generated query looks like this:

select "id", "avg_price" as "avgPrice" from "products" where "products"."id" = $1 limit $2

select "id", "avg_price" as "avgPrice" from "products" where "products"."id" = $1 limit $2

Angelelz•11mo ago

lol I guess you'll have to use crud API

irelynxOP•11mo ago

oh, finally! i changed your code a little bit and now it produces valid SQL!

const sq = db.select({ avgPrice: pricesMinMaxView.avg_price })
      .from(pricesMinMaxView)
      .where(eq(pricesMinMaxView.productId, products.id))
      .limit(1)
//    .as("avgPrice") // remove

db.query.products.findMany({
  columns: {
    id: true,
  },
  extras: {
//  avgPrice: sql<number>`${sq.avgPrice}`.as("avgPrice")
    avgPrice: sql<number>`${sq}`.as("avgPrice")
  },
  limit: 10
})

const sq = db.select({ avgPrice: pricesMinMaxView.avg_price })
      .from(pricesMinMaxView)
      .where(eq(pricesMinMaxView.productId, products.id))
      .limit(1)
//    .as("avgPrice") // remove

db.query.products.findMany({
  columns: {
    id: true,
  },
  extras: {
//  avgPrice: sql<number>`${sq.avgPrice}`.as("avgPrice")
    avgPrice: sql<number>`${sq}`.as("avgPrice")
  },
  limit: 10
})

produced SQL:

select "id", (select "avg_price" from "latest_prices_minmax" where "latest_prices_minmax"."productId" = "products"."id" limit $1) as "avgPrice" from "products" limit $2

select "id", (select "avg_price" from "latest_prices_minmax" where "latest_prices_minmax"."productId" = "products"."id" limit $1) as "avgPrice" from "products" limit $2

I will try it out on bigger query and write down if everything is fine or not. Thanks again for your help!

Angelelz•11mo ago

Damn, I know that 🤦‍♂️

irelynxOP•11mo ago

😀 yeap, everything is fine now.

Omar Goubail•9mo ago

This example helped me alot thank you, but If you don't mind I have a question. In my case it doesn't return number it returns an an array of objects.

 const numOfDevs = await db
    .select({
      numOfDevs: count(developer.id),
    })
    .from(property)
    .where(eq(developer.developerId, String(developerId)))
    // the numOfDevs returns [ { numOfDevs: 0 } ] not the number directly

  const project = await db.query.project.findFirst({
    where: eq(project.id, String(projectId)),
    with: { categories: { with: { subCategories: {} } } },
    extras: {
      numOfDevs: sql<number>`${numOfDevs}`.as('numOfDevs'),
    },
  })

 const numOfDevs = await db
    .select({
      numOfDevs: count(developer.id),
    })
    .from(property)
    .where(eq(developer.developerId, String(developerId)))
    // the numOfDevs returns [ { numOfDevs: 0 } ] not the number directly

  const project = await db.query.project.findFirst({
    where: eq(project.id, String(projectId)),
    with: { categories: { with: { subCategories: {} } } },
    extras: {
      numOfDevs: sql<number>`${numOfDevs}`.as('numOfDevs'),
    },
  })

Did you by any chance find a way around this?

irelynxOP•9mo ago

I am not sure, but it can happens because of

const numOfDevs = await... // you are receiving result here!

const numOfDevs = await... // you are receiving result here!

Try to remove await, and check again

Omar Goubail•9mo ago

You were right, it worked thank you! But why does this happen though, is this a js Promises thing or a drizzle thing? I am not sure I understand why.

irelynxOP•9mo ago

I am not sure how exactly it works under the hood, but all "queries" that you await - will be executed and you will receive an execution result, but if you will not await - it can be used as a subquery later in your code. I am not sure though is it possible to store them separately or not (like, create special file with all subqueries and import one of them then it needed)

Omar Goubail•9mo ago

I am still very curious and will probably try to learn more about this later. Thank you for your help though. Will let you know if I learn anything.

Gaming

Programming

Weird Query builder behavior (extras)