search

mwild1 / luadbi

Multi-backend SQL database library for Lua
MIT License
38 stars 14 forks source link

sqlite: support binding named query parameters #70

Open nwf-msr opened 1 year ago

nwf-msr commented 1 year ago

sqlite3 supports named query parameters in addition to positional ones (see https://sqlite.org/lang_expr.html). These are particularly nice for big, complex queries likely to evolve with software maintenance and it'd be great to be able support them in luadbi. Perhaps extending statement handles' :execute method to behave differently when given a single argument of type table.

As it stands, I've had to fall back to LuaSQLite3 to better support these complex queries.

sparked435 commented 8 months ago

I think this is a great feature, and it's worth adding support in LuaDBI.

Perhaps extending statement handles' :execute method to behave differently when given a single argument of type table.

I like this idea. The problem is, I don't (currently) see anything similar in the PostgreSQL or MySQL API's. They will need a way to cleanly and consistently fail if the programmer tries to use this SQLite-specific feature on one of those databases. It is probably not a big deal to have them simply call error() in the event of a single table being provided as bind arguments; but now I'm worried about what kinds of edge cases this presents.

I'd love to hear other thoughts/opinions.

nwf-msr commented 8 months ago

Wow, I had no idea that that was so SQLite specific or that the SQLite one-at-a-time parameter binding was at all unusual. Great work, everyone.

In any case, I think I'd be OK with the drivers for backend SQLs that don't have named parameters being extended to accept array-like tables and quietly discarding any non-numeric keys, since those can't have meaning in their query languages. FWIW, it looks like Oracle might also support similar things, but MS SQL seems to not (https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlbindparameter-function?view=sql-server-ver16).