Mendy Posts - Answer Overflow

Mendy

Explore posts from servers

•Created by Mendy on 10/31/2023 in #help

Smart or Idiotic? The Great BigInt-to-Hash Adventure!

Alright, fellow code-wranglers, here's a spicy tech taco for you: we're dancing with regular bigint IDs for all our database entities, thanks to our waltz with planetscale row-reads pricing. Now, I don't know about you, but I’d rather our users not play detective and guess how many entities are populating our database by spotting the latest ID. Feels a bit like showing your poker hand, right? Enter the possible hero of our story: a nifty method that takes these IDs and morphs them into encrypted random strings using a sprinkle of secret salt. In this magical world, our database is blissfully unaware, munching on regular int-type IDs, while our users get served fancy hashed strings that look like UUIDs. Fancy, huh? But... plot twist! What if our secret salt takes a vacation and ends up in the wrong hands? We'd be stuck with a bunch of links that might as well lead to Narnia. Picture a "https://tinyurl.com/WhereDidTheMagicGo" that's lost its sparkle. Given that I’ve been known to occasionally doubt the genius-level of my brainstorm sessions (hey, humility, right?), what say you? Smart or Idiotic? Bring on the verdict!

58 replies

DTDrizzle Team

•Created by Mendy on 7/15/2023 in #help

How to update multiple rows with one query?

Take this array as an example:

const data = [
  {id: 1, col1: "col1 data where id = 1"}
  {id: 2, col1: "col1 data where id = 2"}
  …(potentially tens of rows)
]

const data = [
  {id: 1, col1: "col1 data where id = 1"}
  {id: 2, col1: "col1 data where id = 2"}
  …(potentially tens of rows)
]

Is it possible to update all the rows with the new data in one query? Using plain sql, something like this will be possible:

WITH data(id, col1) AS (
  VALUES
    (1, 'col1 data where id = 1'),
    (2, 'col1 data where id = 2'),
)
UPDATE my_table
SET col1 = data.col1
FROM data
WHERE my_table.id = data.id;

WITH data(id, col1) AS (
  VALUES
    (1, 'col1 data where id = 1'),
    (2, 'col1 data where id = 2'),
)
UPDATE my_table
SET col1 = data.col1
FROM data
WHERE my_table.id = data.id;

Thanks for your help!

6 replies

DTDrizzle Team

•Created by Mendy on 6/20/2023 in #help

IS IT POSSIBLE: to use a relation field in a where clause?

11 replies

Gaming

Programming