[vlovich123]

I’m kind of shocked that there’s an extra round trip.

One would expect you can do the preparation lazily as there shouldn’t even be a hypothetical performance consideration for using prepared statements.

In other words you send an execute prepared statement request, you can provide the truly prepared statement OR the templated sql string. In the latter case the server would send you the results of preparing the statement before sending you the result of execution so that you can provide it on subsequent requests.

Or use an RPC system that supports promise pipelining so that you could send the prepare request and the execute is sent with the promise of the prepare request.

[evanelias]

I think the core mistake in a lot of these older protocols is conflating the concept of a prepared statement with the concept of a parameterized query.

With a prepared statement, there's an implication that you want to re-use it multiple times, so the DB needs to give you back some sort of handle/identifier on it. But this requires bookkeeping on both the client and server side, and that overhead is often not worthwhile on modern CPUs, especially when running fairly simple CRUD-style queries which are quick to parse. (and at least in modern versions of MySQL, the DB will track a "digest" of the query which removes params anyway, even if you used client-side param interpolation!)

So ideally DB binary protocols should offer less expensive ways of doing parameters for "fire and forget" queries, instead of having to track real prepared statements, but often they don't.

MySQL's newer X Protocol provides a way to avoid the extra round-trip, but it isn't very widely used yet AFAIK, and it still involves prepared statement bookkeeping: https://dev.mysql.com/doc/internals/en/x-protocol-use-cases-...

[vlovich123]

Yes, you should also be able to do a stand-alone parametrized query. The only DB system I’ve seriously programmed against was SQLite where this isn’t an issue.

[namibj]

PostgreSQL Extended Query Protocol won't cause the round trip issue; you can even send multiple parametrized queries back-to-back without any RTT penalties. This requires consideration in the API, though, because errors are delayed until the end of such a sequence, loosing attribution as to which individual query caused the error (at least at a low-level protocol level).

Not that this is a problem for a well-designed library, of course. E.g. tokio-postgres in Rust does it if you use a promise-combinator for the first poll of multiple queries inside the same transaction.