3 ms·
I think the issue the parent is referring to is when you have optional clauses in your statement. Like if you have a table with 5 update-able columns (where so
by kelnos 7y ago
I think the issue the parent is referring to is when you have optional clauses in your statement. Like if you have a table with 5 update-able columns (where someone might want to update any 1 to 5 columns that you don't know in advance), and you want some generic code that updates a row, if you're writing raw SQL then you have to do concatenations to build up the "set clause":
UPDATE sometable SET {set clause} WHERE id = ?
The "set clause" might just be "foo = ?", but it might be "foo = ?, bar = ?", or "foo = ?, baz = ?", etc.
- squiggleblaz 7y agoSo you mean I'm writing code like this, in a language somewhat like Typescript but simplified to my end. function update(id: ID_TYPE, value: Partial<sometable>) { const set_clause = value.keys.map(k => k + " = ?").join(", ") const params = value.values.concat([id]) db.query("UPDATE sometable SET " + set_clause + " WHERE = ?", params) } How is code not terrible? It shouldn't pass code review. Once you concatenate strings to generate your sql, all bets are off. There will be a day when your object doesn't match your assumptions. Either you have a series of different functions, each of which will change under different circumstances; or you should just be generating this function like this directly from the schema in which case you might as well just generate code you want at build time so there's string concatenations anywhere. Is there some other circumstance when you want that?
- kelnos 7y agoIt is terrible. That's the problem. No one wants to write code like that, so they turn to an ORM to do it for them, in a hopefully safer way.
- yellowapple 7y agoSQL's got you covered here: UPDATE sometable SET foo = COALESCE(?, foo), bar = COALESCE(?, bar), baz = COALESCE(?, baz), bam = COALESCE(?, bam), bat = COALESCE(?, bat) WHERE id = ? And then you'd pass in NULLs for any columns not being updated at the moment. Confirmed that this works exactly as expected in SQLite: $ sqlite3 optional_column_test.db sqlite> CREATE TABLE sometable (id INTEGER PRIMARY KEY, foo, bar, baz, bam, bat); sqlite> INSERT INTO sometable (foo,bar,baz,bam,bat) VALUES ('You', 'are', 'a', 'bold', 'one'); sqlite> SELECT * FROM sometable; 1|You|are|a|bold|one sqlite> UPDATE sometable SET ...> foo = COALESCE(NULL, foo), ...> bar = COALESCE('ain''t', bar), ...> baz = COALESCE(NULL, baz), ...> bam = COALESCE(NULL, bam), ...> bat = COALESCE(NULL, bat) ...> WHERE id = 1; sqlite> SELECT * FROM sometable; 1|You|ain't|a|bold|one
- kelnos 7y agoI'd never heard of COALESCE before! I'm very very far from a SQL expert, but I'm surprised that's never popped up for me before. Are there any bad performance implications here? Like, will it still rewrite columns that don't need to be updated?
- yellowapple 7y agoGood question. The answer to that is almost certainly "it depends on your DB". Most query planners should be smart enough to not try to write anything, but some are smarter than others. For example, SQL Server (if I'm understanding right) does perform a log write even if it should be obvious that the value won't change: https://www.sql.kiwi/2010/08/the-impact-of-non-updating-updates.html https://www.sql.kiwi/2010/08/the-impact-of-non-updating-upda... That being said, I'd skeptical of the performance impact (if any) being all that significant; even in a worst-case scenario of "yeah, it's going to stupidly write every field every time", the columns for each row should be pretty close together both in-memory and on-disk, so unless you're writing a whole bunch of giant strings or something at once (and even then) there shouldn't be a whole lot of seeking happening. tl;dr: assuming there's a minor performance impact is reasonable, but it should probably be measured before trying to optimize it away.