"UPDATE table_name SET w = $1, x = $2, z = $4 WHERE y = $3 RETURNING *",
does not do the same as
"UPDATE table_name SET w = $1, x = $2, y = $3, z = $4 RETURNING *",
It’s 2 am and my mind blanked out the WHERE, and just wanted the numbers neatly in order of 1234.
idiot.
FML.
Pro tip: transactions are your friend
This is a hard lesson to learn. From now on, my guess is you will have dozens of backups.
And a development environment. And not touch production without running the exact code at least once and being well slept.
Replied hastily, but the way to run db statements in prod while dealing with sleep deprivation and drinking too much is to run it a bunch in several test env scenarios so you’re just copy pasting to prod and it CAN confidently be done. Also enable transactions and determine several, valid smoke tests.
Edit: a -> several
And always use a transaction so you’re required to commit to make it permanent. See an unexpected result? Rollback.
Transactions aren’t backups. You can just as easily commit before fully realizing it. Backups, backups, backups.
Yes, but
- Begin transaction
- Update table set x=‘oopsie’
- Sees 42096 rows affected
- Rollback
Can prevent a restore, whereas doing the update with auto commit guarantees a restore on (mostly) every error you make
Postgres has a useful extension, pg_safeupdate
https://github.com/eradman/pg-safeupdate
It helps reduce these possibilities by requiring a where clause for updates or deletes.
I guess if you get into a habit of adding where 1=1 to the end of your SQL, it kind of defeats the purpose.
MySQL (and by extension, MariaDB) has an even better option:
mysql --i-am-a-dummy
Oof. Been there, done that, 0 stars; would not recommend.
This is about the one thing where SQL is a badly designed language, and you should use a frontend that forces you to write your queries in the order (table, filter, columns) for consistency.
UPDATE table_name WHERE y = $3 SET w = $1, x = $2, z = $4 RETURNING *
FROM table_name SELECT w, x, y, z
I get mildly mad all the time when writing SQL because I feel like it’s upside down
Instead of
select u.id. u.email, p.name
from user u
join persona p on p.user_id = u.id
where u.active = true
where the columns are referenced before they’re defined (like what is u.id? Keep reading to find out!)
it should instead be
from user u
join persona p on u.id = p.user_id
where u.active = true
select u.id, u.email, p.name
Now nothing is defined before it’s used, and you’re less likely to miss your where clause. I usually write the joins first anyway because I know what tables I care about, but don’t know which specific things I’ll want.
I can’t think of any other languages that use things before they’re defined except extremely dysfunctional JavaScript.
You might enjoy https://github.com/max-sixty/prql
