Ramazan Posts - Answer Overflow

Ramazan

Explore posts from servers

TtRPC TTCTheo's Typesafe Cult DTDrizzle Team

•Created by Ramazan on 9/20/2023 in #help

Prepend raw fields with table/subquery name in rendered SQL

Currently, it doesn't seem like raw fields in a select statement hold any information on the table/subquery they're used in. This results in subsequent usage of these fields elsewhere rendering to SQL as "field_name" instead of "alias"."field_name". This actually causes issues when you have overlapping field names in sql in your joins. Example:

const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
    amount: balances.amount,
  })
  .from(balances)
  .as("sq");

const result = await db
  .select({
    date: transactions.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sq.date, transactions.date));

const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
    amount: balances.amount,
  })
  .from(balances)
  .as("sq");

const result = await db
  .select({
    date: transactions.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sq.date, transactions.date));

will result in an error

error: column reference "date" is ambiguous

error: column reference "date" is ambiguous

with the following workaround

const result = await db
  .select({
    date: transactions.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sql`"sq".${sq.date}`, sql`"transactions".${transactions.date}`));

const result = await db
  .select({
    date: transactions.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sql`"sq".${sq.date}`, sql`"transactions".${transactions.date}`));

This behavior persists through subsequent subqueries where

const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
    amount: balances.amount,
  })
  .from(balances)
  .as("sq");

const sq2 = db
  .select({
    date: transactions.date,
    balanceDate: balances.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sql`"sq".${sq.date}`, sql`"transactions".${transactions.date}`))
  .as("sq2");

const result = await db.select({
  balanceDate: sq2.balanceDate,
  anotherDate: someTable.date // also using sql`...`.as("date")
}).from(sq2).innerJoin(someTable, ...);

const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
    amount: balances.amount,
  })
  .from(balances)
  .as("sq");

const sq2 = db
  .select({
    date: transactions.date,
    balanceDate: balances.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sql`"sq".${sq.date}`, sql`"transactions".${transactions.date}`))
  .as("sq2");

const result = await db.select({
  balanceDate: sq2.balanceDate,
  anotherDate: someTable.date // also using sql`...`.as("date")
}).from(sq2).innerJoin(someTable, ...);

will result in a similar error due to the ambiguity between someTable.date and sq2.balanceDate.

6 replies

DTDrizzle Team

•Created by Ramazan on 8/7/2023 in #help

Cast columns in relations

A great feature of drizzle's relational queries is that they don't actually require a foreign key, which provides a great amount of flexibility in querying your data. However, it doesn't seem like there is currently a way to cast the fields so that their types match. Something along the lines of

export const actionRelations = relations(actions, ({ many, one }) => ({
  user: one(users, {
    fields: [sql<string>`${actions.ownerId}::uuid`.as("ownerId")],
    references: [users.id],
  })
  service: one(services, {
    fields: [actions.ownerId],
    references: [sql<string>`${services.id}::text`.as("id")]
  }
}));

export const actionRelations = relations(actions, ({ many, one }) => ({
  user: one(users, {
    fields: [sql<string>`${actions.ownerId}::uuid`.as("ownerId")],
    references: [users.id],
  })
  service: one(services, {
    fields: [actions.ownerId],
    references: [sql<string>`${services.id}::text`.as("id")]
  }
}));

where an action's ownerId is text so that it can refer to multiple different owner types regardless of their primary key type.

2 replies

DTDrizzle Team

•Created by Ramazan on 7/19/2023 in #help

Update existing schema when introspecting db

Is it possible to update an existing schema using introspection? Currently, drizzle-kit just wipes any changes I make to the schema (relations, renaming, etc.).

7 replies

DTDrizzle Team

•Created by Ramazan on 5/31/2023 in #help

Prepared Stements

I was looking into using AWS RDS Proxy with Prisma and ran into this https://www.prisma.io/docs/guides/deployment/deployment-guides/caveats-when-deploying-to-aws-platforms#aws-rds-proxy I was wondering does DrizzleORM use prepared statements for everything like Prisma does? Would I run into the same issue if I switched to Drizzle?

2 replies

Gaming

Programming