MysqlTransaction

Function

MySQL transaction handle. Intra-transaction SQL reuses the same underlying transaction in sequence, which is suitable for multiple SQLs that require atomic commit or rollback.

Syntax

Method

Method	Parameters	Return value	Description
query(sql)	sql:String	MysqlTransaction	Set intra-transaction SQL.
bind(value1, value2, ...)	Any value	MysqlTransaction	Append binding parameters.
all()	None	Array	Execute a query and return multiple rows.
one()	None	Object/empty	Execute the query and return a single row; returns empty if there are no results.
exec()	None	Object	Execute writing SQL and return SQL execution statistics object; fields are shown below.
commit()	None	Bool	Commit the transaction and return true on success.
rollback()	None	Bool	Rollback the transaction, return true on success.
close()	None	Bool	The active transaction will be rolled back and closed; returns false if it has ended.
status()	None	String	Returns active, committed, rolled_back, closed, or failed.
sql(render_binds)	Bool, optional	String	Returns the SQL preview text; default render bind parameters.

Return value

Type	Description
MysqlTransaction	query/bind Returns a new lightweight builder, but sharing the same transaction state.
Array	all Returns an array of row objects. For row object fields, see "Query row object fields" below.
Object/Empty	one returns a single row object when there is a result; returns `empty` when there is no result. For row object fields, see "Query row object fields" below.
Object	exec Returns the SQL execution statistics object. See "exec returns object fields" below for fields.
Bool	Status results of commit, rollback, close.
String	The text result of status or sql.

Query row object fields

The row objects returned by all() and one() are dynamic objects with fields derived from SQL result set column names.

Field	Type	Must exist	Description
Any column name	Any	No	The column value returned by the database. The field name is the result set column name; when the field does not exist, the read result is `empty`. Database NULL returns `null`.

exec returns object fields

Field	Type	Must exist	Description
total	Int	Yes	The number of bound rows processed by `exec()` within this transaction. `binds()` batch execution is not currently supported within a transaction, so it is currently fixed to `1`.
rows_affected	Int	Yes	The number of affected rows reported by the database.
last_insert_id	Int	Yes	The last insert ID reported by the database.
batch_count	Int	Yes	The actual number of batches split executed. Batch execution is not currently supported within a transaction, so it is currently fixed to `1`.
batch_size	Int	Yes	The batch size for the current execution. `batch()` is not currently supported within transactions, so it is currently `0`.
workers	Int	Yes	The number of concurrent workers used by the current execution. The same underlying transaction is reused sequentially within a transaction, so currently `1`.

Code Example

Notes

Do not reuse the same transaction object concurrently; an error will be reported if used again during transaction execution.
Batch execution configuration of binds, batch, and workers is not currently supported within the transaction.
Uncommitted transactions should call rollback or close; SQLx will attempt to rollback when the object is discarded, but the script should explicitly close the resource.
BT.stats().mysql.transactions_active can be used to observe the current number of active transactions.