3 ms·

I mostly agree. I think sql's achilles heel in this regard is where prepared statement parameters are needed but aren't supported, or need a better representat

by chrsig 4y ago

I mostly agree. I think sql's achilles heel in this regard is where prepared statement parameters are needed but aren't supported, or need a better representation - during bulk inserts, having to generate a list of values. It'd be wonderful to be able to just supply a single `?`, or use some other symbol to note that it's a value list. Making the user generate a bunch of (?,?),(?,?)... is not at all friendly, and something everyone has to do. and the cherry on top is that there can't be a dangling comma at the end, so it's gotta be chopped off, or omitted. Not at all a hard problem. It's an annoying problem that I don't understand why it hasn't been solved at the prepared statement level. - things like database, table, or column identifiers that may be variable based on application context Basically anywhere that currently winds up getting interpolated should have a way to be parameterized. without those two, i think it's inevitable to arrive at one of: - an orm - a sql query template renderer - a bunch of functions to do very specific string interpolations

MichaelCollins 4y ago
Absolutely agree with this, particularly the (?,?,?,...) issue. SQL has a lot of little pain points, but generally ORMs feel like they throw the baby out with the bath water.
matharmin 4y ago
With SQLite, I've started using json_each on a JSON parameter for bulk inserts or updates. Other SQL databases should have something similar. It's much cleaner than generating SQL, and doesn't run into issues with exceeding the maximum number of parameters.
- plugin-baby 4y ago
  This sounds very neat. Do you have an example handy?
  - fragile_frogs 4y ago
    This is what I do in postgres: insert into your_table(id, created_at, uri, project_id) select id , created_at , endpoint as uri , project_id from jsonb_to_recordset($1) as x( id uuid , created_at timestamptz , endpoint text , project_id uuid )

by chrsig 4y ago

MichaelCollins 4y ago

Absolutely agree with this, particularly the (?,?,?,...) issue. SQL has a lot of little pain points, but generally ORMs feel like they throw the baby out with the bath water.

matharmin 4y ago

With SQLite, I've started using json_each on a JSON parameter for bulk inserts or updates. Other SQL databases should have something similar. It's much cleaner than generating SQL, and doesn't run into issues with exceeding the maximum number of parameters.

plugin-baby 4y ago
This sounds very neat. Do you have an example handy?
- fragile_frogs 4y ago
  This is what I do in postgres: insert into your_table(id, created_at, uri, project_id) select id , created_at , endpoint as uri , project_id from jsonb_to_recordset($1) as x( id uuid , created_at timestamptz , endpoint text , project_id uuid )