Discussion: deprecation of execute_many() and fetch_many() on prepared statement interfaces and potential replacement

[abonander]

#3007 (released in 0.7.4) deprecates execute_many() and fetch_many() in the query*() family of functions. These functions were meant to allow executing a single query string containing multiple SQL statements while still using bind parameters. These will be removed in 0.8.0.

This is because only SQLite ever supported this feature, due to the idiosyncrasy of its prepared statement interface being the primary way to execute SQL against a database. All other databases forbid multiple statements in one string when using prepared statements as a defense against SQL injection. Thus, it's extremely confusing to have such methods as part of a database-agnostic API. And, it was never supported by the query*!() macros because it was unclear what they should return when processing a query string with multiple statements.

To support multiple queries in one prepared statement for arbitrary database backends would require parsing the string to find ; delimiters, prepare it as multiple statements, and then execute them in an implicit transaction. Doing this much behind the scenes would be antithetical to the design of SQLx. The SQLite driver had to implement this to support migrations in line with the other drivers, but sqlite3_prepare() in the C API is explicitly designed to incrementally parse a single query string into multiple statements for this exact reason, so we at least don't have to parse SQL ourselves.

The raw_sql API added in #3007 includes the ability to execute multiple statements in one query string, with the caveat that bind parameters cannot be used (as they require the prepared statement interface).

If anyone needs to be able to execute multiple query statements in one string, with bind parameters, using the SQLite driver, this issue is open to discuss what that API should look like. Supporting this in other drivers that have similar support in their prepared statement interfaces is an option, but supporting for databases like Postgres which don't is out of scope. Be prepared to explain why you cannot work around this by explicitly executing multiple statements in a transaction.

This issue will be closed at the conclusion of the next release cycle (0.8.x) if no one responds to it by then, under the assumption that no one had a problem with the deprecation and removal of the aforementioned APIs.

[francois-random]

Hello, I'm currently using fetch_many with mysql to detect multiple resultsets returned by a single call to a stored procedure.
I don't see other ways to deal with multiple resultsets in the public interface of sqlx, but it seems important to keep a similar function (with a different name if you think it's better).
In the following example, there are 3 different resultsets (the second resultset has two rows), that I can detect with Either::Left.

delimiter $
CREATE PROCEDURE my_proc(IN value INTEGER)
Sp:BEGIN
    SELECT value;
    SELECT 7*value UNION SELECT value*value;
    SELECT value*value, 7*value, value*value;
END$
delimiter ;

    let rows = sqlx::query("call my_proc(10);").fetch_many(db_executor);
    while let Some(either) = rows.try_next().await? {
        match either {
            Either::Left(result) => {
                // new resultset
            }
            Either::Right(row) => {
                // new row
            }
        }
    }

[francois-random]

Just saying that perhaps at the origin fetch_many and exec_many meant many resultsets, not many statements

[genusistimelord]

I do find myself looking for a good solution for a execute many and i felt the stream and such kind-of turned me away from using the current functions. So i ended up splitting my code into parts to execute separately. Though to me I think it would be more Efficient to be able to have some sort of Raw API that can Build separate scripts and execute them and return them for you, For the systems that don't support multiple. Or to allow ways to Execute multiples without binds would be fine as well as normally I only do this for data that I don't need to worry about SQL injection's. If we do add such an ability we need to Comment on them letting people know these are acceptable to SQL injection attacks if used improperly.

For things like Creating Tables and all the types to go along with the tables would be better done in a single Execution versus splitting them up which shouldn't need prepared statements to function. Would also be nice if the end user only had to deal with a Vec of returns from these multiple scripts in a single execution. however, either way is fine as long as we have good examples.

[abonander]

I'm not against retaining some sort of abstraction for this, the biggest issue is it doesn't seem to belong on the Executor trait because it doesn't apply to all databases. Postgres really doesn't have an equivalent, for example.

Even MySQL vs SQLite, the semantics differ somewhat. MySQL, one query can return multiple results whereas SQLite it's one result set per query but you can have multiple queries in one string. It seems weird to have both of those covered by the same abstraction.

I'm not hostile to the use cases presented here, but I also have no idea what the right answer is.

[genusistimelord]

I think it would be fine to move this into something higher than the Executor. As then you can handle the way each thing works across all the databases supported. It would be much easier to maintain then at least. Maybe have some sort of Preparer Trait and Type that can do the splitting or combining based on the database and Execute them using the correct Executor functions. doing this could also allow binder buildings across multiple sets. At least with a Preparer Level we can answer for the different abstractions and let the end user know how it works per database. were in the extractor itself we shouldnt do this.

[eirnym]

In other languages/frameworks fetch_many is a standard name for fetching query results in batches (for streaming purposes), while execute_many is dedicated to run many queries at once. The only suggested application for execute_many I've met was many INSERT queries. Thus naming for fetch function is not obvious for a programmer from other languages.

AsyncPG (Python async driver for binary protocol for PostgreSQL) somewhat deprecates execute_many, as binary data takes less space and nobody should do additional encoding/decoding from/into a string