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.
Re:Flyweight: An ORM for SQLite
by plugin-baby on Thursday September 29, 2022 @04:19PM
(#33026560)
This sounds very neat. Do you have an example handy?
Re:Flyweight: An ORM for SQLite
by fragile_frogs on Friday September 30, 2022 @03:58AM
(#33032222)
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
)
Re:Flyweight: An ORM for SQLite
by MichaelCollins on Thursday September 29, 2022 @11:04AM
(#33021713)
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.
- 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