irelynx Posts - Answer Overflow

irelynx

•Created by irelynx on 1/15/2024 in #help

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)

24 replies

DTDrizzle Team

•Created by irelynx on 9/10/2023 in #help

How to add custom SQL queries during or after migration in drizzle schema?

Hi all. I would like to execute some SQL queries during (or after) migration. Is there a way how I can do it without calling SQL queries each time at application startup? In my use-case I am trying to make fixed partitions to improve performance, but that way can also be used to create custom and more complex indexes or event triggers.

9 replies

Gaming

Programming