Support for executing multiple statements at once?

[MathewYaldo]

Is there any support for executing multiple statements at once with Jet? For example, it's valid to be able to execute this full SQL all at once:

UPDATE category
SET category_name = 'Test'
WHERE category.category_id = 2;

DELETE FROM category
WHERE category.category_id = 3;

I'd like to be able to do this so that I can avoid multiple round trips to the database. I could not find anything on this in the documentation. One possible workaround I tried experimenting with is wrapping each individual statement as a CTE, but it does not seem to behave the same compared to if the statements were separate like in the example above.

[houtn11]

Jet doesn't support the execution of multiple statements. If you are using postgres, then definitely the way to go is with the CTEs.

MySQL doesn't support update and delete statements in CTE. For MySQL, since it uses ? as a parameter placeholder, you can write something like:

func ExecAll(sqlDB *sql.DB, stmts ...Statement) (sql.Result, error) {

	var allQueries string
	var allArgs []any

	for _, stmt := range stmts {
		query, args := stmt.Sql()

		allQueries += query
		allArgs = append(allArgs, args)
	}

	return sqlDB.Exec(allQueries, allArgs)
}

, to execute all statements in one db call.

[MathewYaldo]

@houtn11 Thanks for the insight on this. I am using postgresql. Just to clarify, the ExecAll function that you provided would only work for MySQL, correct? I tried running it with postgresql and got this error:
cannot insert multiple commands into a prepared statement

Regarding the CTE method, I had mentioned that this was not behaving properly and I believe I've found the cause. From https://www.postgresql.org/docs/current/queries-with.html:

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query. An example of this is that in

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

the outer SELECT would return the original prices before the action of the UPDATE, while in

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

the outer SELECT would return the updated data.

Basically, the order in which the separate CTEs are executed can be unpredictable, so this is not great for data-modifying statements that need to be in a specific order to work properly. To work around this, I've added dependencies among the statements like this to force the order and got the desired behavior:

cte1 := postgres.CTE("stmt1")
cte2 := postgres.CTE("stmt2")
cte3 := postgres.CTE("stmt3")

mainStmt := postgres.WITH(
	cte1.AS(stmt1.RETURNING(postgres.Int32(1).AS("number_one"))),
	cte2.AS(stmt2.RETURNING(postgres.SELECT(postgres.Int32(1)).FROM(cte1))),
	cte3.AS(stmt3.RETURNING(postgres.SELECT(postgres.Int32(1)).FROM(cte2))),
)(
	// A statement outside the WITH is needed.
	postgres.SELECT(postgres.Int32(1).AS("number_one")),
)

Where stmt1, stmt2, and stmt3 are standard statements like the ones in the initial post.

[houtn11]

I am using postgresql. Just to clarify, the ExecAll function that you provided would only work for MySQL, correct? I tried running it with postgresql and got this error:
cannot insert multiple commands into a prepared statement

Yeah, it is just for MySQL, but I'm not sure anymore if it will work with argument placeholders. Haven't tested it.

Basically, the order in which the separate CTEs are executed can be unpredictable, so this is not great for data-modifying statements that need to be in a specific order to work properly.

If your update and delete statements are modifying different rows, then if statements are executed concurrently, it shouldn't be an issue.

The problem can arise if they are modifying the same row. From the same PostgreSQL doc:

Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a row that was already updated in the same statement: only the update is performed. Therefore you should generally avoid trying to modify a single row twice in a single statement. In particular avoid writing WITH sub-statements that could affect the same rows changed by the main statement or a sibling sub-statement. The effects of such a statement will not be predictable.

[MathewYaldo]

It appears that pgx supports executing multiple statements at once through their Batch interface. While I'm not sure how it works internally, there is some information on it here.

@go-jet I noticed that there is an active pull request (#523) to support the pgx v5 interface and figured this may be a good time to ask if it's possible for batch statements to be supported alongside those changes?

I made some changes to a copy of the branch in #523 to add BatchQueryPgxV5 and batchQueryToSlicePgxV5 functions to qrm/qrm_pgx_v5.go (changes can be viewed here), and it appears to work without any major issues.

Sample code utilizing the changes:

    // Initialize db
	ctx := context.Background()
	conn, err := pgx.Connect(ctx, constants.Database)
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)

	var dest []struct {
		model.AttributeValue
	}

	deleteStmt, deleteStmtArgs := table.AttributeValue.
		DELETE().
		WHERE(table.AttributeValue.AttributeValue.EQ(postgres.String("Test"))).
		Sql()

	selectStmt, selectStmtArgs := table.AttributeValue.
		SELECT(table.AttributeValue.AllColumns).
		FROM(table.AttributeValue).
		Sql()

	batch := &pgx.Batch{}
	batch.Queue(selectStmt, selectStmtArgs...)
	batch.Queue(deleteStmt, deleteStmtArgs...)
	batchResults := conn.SendBatch(context.Background(), batch)
	defer batchResults.Close()

	_, err = qrm.BatchQueryPgxV5(ctx, conn, batchResults, &dest)
	if err != nil {
		log.Fatal(err)
	}
	
	resp, _ := json.MarshalIndent(dest, "", "  ")
	log.Println(string(resp))

I'll admit this may not be the best implementation, but it worked for most of the tests I performed. The only issue I encountered so far is that if any model in the dest structure utilizes an enum, I get this error:

can't scan into dest[4] (col: user.password_type): cannot scan unknown type (OID 47128) in text format into *interface {}

All that is to say that this seems feasible, but I'm not sure what a proper implementation would look like or how to resolve that scan error for an enum field.