AstroBear Posts - Answer Overflow

AstroBear

•Created by AstroBear on 1/30/2024 in #help

Do transaction automatically rollback on error?

I was reading the docs about transactions and it is not clear whether the transaction is rolled back when an error is thrown. Which is:

const db = drizzle(...)

await db.transaction(async (tx) => {
  const [account] = await tx.select({ balance: accounts.balance }).from(accounts).where(eq(users.name, 'Dan'));
  if (account.balance < 100) {
    await tx.rollback()
    // Can I use throw MyCustomError(); here instead of tx.rollback()?
    // Will it also rollback the transaction?
    return
  }

  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew'));
});

const db = drizzle(...)

await db.transaction(async (tx) => {
  const [account] = await tx.select({ balance: accounts.balance }).from(accounts).where(eq(users.name, 'Dan'));
  if (account.balance < 100) {
    await tx.rollback()
    // Can I use throw MyCustomError(); here instead of tx.rollback()?
    // Will it also rollback the transaction?
    return
  }

  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew'));
});

5 replies

DTDrizzle Team

•Created by AstroBear on 1/26/2024 in #help

Why does `sum` return `SQL<string | null>`?

I was trying to use the sum aggregate function and I noticed that it returns a string. Why is that?

/**
 * Returns the sum of all non-null values in `expression`.
 *
 * ## Examples
 *
 * \`\`\`ts
 * // Sum of every employee's salary
 * db.select({ value: sum(employees.salary) }).from(employees)
 * \`\`\`
 *
 * @see sumDistinct to get the sum of all non-null and non-duplicate values in `expression`
 */
export declare function sum(expression: SQLWrapper): SQL<string | null>;

/**
 * Returns the sum of all non-null values in `expression`.
 *
 * ## Examples
 *
 * \`\`\`ts
 * // Sum of every employee's salary
 * db.select({ value: sum(employees.salary) }).from(employees)
 * \`\`\`
 *
 * @see sumDistinct to get the sum of all non-null and non-duplicate values in `expression`
 */
export declare function sum(expression: SQLWrapper): SQL<string | null>;

10 replies

DTDrizzle Team

•Created by AstroBear on 1/11/2024 in #help

Using `count` in CTE has type `never`

I got the following tables: users: id, ... followers: followingId, followerId I'm trying to write a with common table expression to reuse in other queries:

const withUser = db.$with('user').as(
    db
      .select({
        id: users.id,
        // ...
        following: countDistinct(following.followingId),
        followers: countDistinct(followers.followerId)
      })
      .from(users)
      .leftJoin(following, eq(following.followerId, users.id))
      .leftJoin(followers, eq(followers.followingId, users.id))

const withUser = db.$with('user').as(
    db
      .select({
        id: users.id,
        // ...
        following: countDistinct(following.followingId),
        followers: countDistinct(followers.followerId)
      })
      .from(users)
      .leftJoin(following, eq(following.followerId, users.id))
      .leftJoin(followers, eq(followers.followingId, users.id))

The problem is that now, if I try to use the results of that CTE, sq.following and sq.followers are not valid columns:

const result = db
  .with(withUser)
  .select({ 
    following: withUser.following //< sq.following has type never, result[0].following is never
   })
  .from(withUser);

const result = db
  .with(withUser)
  .select({ 
    following: withUser.following //< sq.following has type never, result[0].following is never
   })
  .from(withUser);

2 replies

DTDrizzle Team

•Created by AstroBear on 1/8/2024 in #help

Include count of relations

How can I achieve the same this Prisma query does in Drizzle?

const result = await prisma.user.findUnique({
  where: { id },
  include: {
    posts: {
      select: {
        _count: true
      }
    }
  }
});

const result = await prisma.user.findUnique({
  where: { id },
  include: {
    posts: {
      select: {
        _count: true
      }
    }
  }
});

I'd like to include the number of posts the user has created in the result

3 replies

Gaming

Programming