Eddy Vinck Posts - Answer Overflow

Eddy Vinck

•Created by Eddy Vinck on 6/13/2024 in #help

How to do an UPDATE with a JOIN in Drizzle ORM?

Something like this https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql I have this schema where I want to do an update on the "user_usage" table, but only if users.subscriptionId is a certain string or in a set of string values . I think what I want here is an UPDATE with a JOIN. Could not quite figure out how to do this with Drizzle ORM. Something like this from StackOverflow:

UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
    JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
    t2.id = t1.t2_id
    AND t3.bar = True;

UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
    JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
    t2.id = t1.t2_id
    AND t3.bar = True;

But maybe a common table expression (CTE) could work like this example from StackOverflow?

with t as (
    select v.id as rowid, s.price_per_vehicle as calculatedvalue
    from vehicles_vehicle v 
    join shipments_shipment s on v.shipment_id = s.id 
)
update vehicles_vehicle
set price = t.calculatedvalue
from t
where id = t.rowid

with t as (
    select v.id as rowid, s.price_per_vehicle as calculatedvalue
    from vehicles_vehicle v 
    join shipments_shipment s on v.shipment_id = s.id 
)
update vehicles_vehicle
set price = t.calculatedvalue
from t
where id = t.rowid

Schema in next post

5 replies

DTDrizzle Team

•Created by Eddy Vinck on 4/18/2024 in #help

Drizzle schema vs SQL when migrating serial id to uuid?

I have a Postgres DB and I want to migrate serial primary key IDs to UUIDs, and I'm running into some potential issues here. I've already added the uuid columns and user_uuid, thing_uuid relation columns to my tables via my Drizzle schema. Using PgAdmin I verified that the various uuid columns are not empty, and I've filled the relational UUIDs with queries like this one:

UPDATE user_usage usage SET user_uuid = u.uuid FROM users u WHERE usage.user_id = u.id;

UPDATE user_usage usage SET user_uuid = u.uuid FROM users u WHERE usage.user_id = u.id;

Problem 1: To prevent having to update my whole application code, I want to basically swap the ID and UUID columns and let them keep the id and user_id column names, but with uuid primary keys of course. Is this something I can solve by editing my schema, or would this be something I need to do by running SQL queries? Problem 2: If I need to do this via SQL queries, how do I then reflect this new state in my Drizzle schema? Maybe introspecting would be a good option here? Or maybe I should just change my schema, generate a migration and then edit the migration by hand before running it? Note: this is a development database migration, so this is essentially practice for whenever I might need to do something similar in prod

50 replies

DTDrizzle Team

•Created by Eddy Vinck on 3/1/2024 in #help

feedback wanted: subquery in update

This is what I'm trying to do. I still need to make a few other adjustments to my app before I can run it, but are there any things I should be doing differently here? I want to use a subquery to avoid the network roundtrip of just a bunch of user IDs I'm using MySQL if that makes a difference.

const allSubscribedUsersIds = db
  .select({
    id: users.id,
  })
  .from(users)
  .where(
    and(
      isNotNull(users.subscriptionId),
      ne(users.subscriptionId, SubscriptionPlan.unsubscribed),
    ),
  )
  .getSQL();

const update = await db
  .update(userUsages)
  .set({
    lastResetAt: sql`CURRENT_TIMESTAMP`,
    audioRecordingsMetered: 0,
  })
  .where(
    and(
      inArray(userUsages.userId, allSubscribedUsersIds),
      sql`${userUsages.lastResetAt} <= CURDATE() - INTERVAL 1 MONTH`,
    ),
  );

const allSubscribedUsersIds = db
  .select({
    id: users.id,
  })
  .from(users)
  .where(
    and(
      isNotNull(users.subscriptionId),
      ne(users.subscriptionId, SubscriptionPlan.unsubscribed),
    ),
  )
  .getSQL();

const update = await db
  .update(userUsages)
  .set({
    lastResetAt: sql`CURRENT_TIMESTAMP`,
    audioRecordingsMetered: 0,
  })
  .where(
    and(
      inArray(userUsages.userId, allSubscribedUsersIds),
      sql`${userUsages.lastResetAt} <= CURDATE() - INTERVAL 1 MONTH`,
    ),
  );

1 replies

Gaming

Programming