--- id: mysql-transaction title: MySQL Node.js Transaction sidebar_label: Transaction --- The `Transaction` object represents a transaction happening on a single physical connection to the underlying database. You can use it to execute queries, or start "nested transactions". If you throw an error within a transaction, it will be automatically aborted and rolled back, otherwise it will be committed when the transaction ends. ### `Transaction.query(SQLQuery): Promise` Run an SQL Query and get a promise for an array of results. If your query contains multiple statements, only the results of the final statement are returned. ```ts const result = await task.query(sql`SELECT 1 + 1 AS a`); result[0].a; // => 2 ``` > N.B. if a query throws an error, that transaction will become unusable. If you need to run a query that might error (e.g. selecting from a table that may or may not exist), you can wrap the query in a nested transaction to keep the error localised. ### `Transaction.query(SQLQuery[]): Promise` If you pass an array of SQLQueries, you will get an array in response where each element of the array is the results of one of the queries. ```ts const [resultA, resultB] = await task.query([ sql`SELECT 1 + 1 AS a`, sql`SELECT 1 + 1 AS b`, ]); resultA[0].a + resultB[0].b; // => 4 ``` ### `Transaction.queryStream(SQLQuery): AsyncIterable` Run an SQL Query and get an async iterable of the results. e.g. ```js for await (const record of db.queryStream(sql`SELECT * FROM massive_table`)) { console.log(result); } ``` ### `Transaction.queryNodeStream(SQLQuery): ReadableStream` Run an SQL Query and get a node.js readable stream of the results. e.g. ```js const Stringifier = require('newline-json').Stringifier; db.queryNodeStream(sql`SELECT * FROM massive_table`) .pipe(new Stringifier()) .pipe(process.stdout); ``` ### `Transaction.tx(fn: (tx: Transaction) => Promise, options?): Promise` Executes the callback `fn` within a "nested transaction" on that connection. A nested transaction wraps a regular transaction with 3 additional queries: 1. it executes `SAVEPOINT unique_id` just before invoking the callback function 2. it executes `ROLLBACK TO SAVEPOINT unique_id`, if the callback throws an error or returns a rejected promise 3. it executes `RELEASE SAVEPOINT unique_id`, if the callback does throw any error and does not return a rejected promise ```ts const result = await db.task(async (db) => { const resultA = await db.query(sql`SELECT 1 + 1 AS a`); const resultB = await db.query(sql`SELECT 1 + 1 AS b`); return resultA[0].a + resultB[0].b; }); // => 4 ``` ### `Transaction.task(fn): Promise` This method exists to mimic the API in `ConnectionPool.task`. It does not allocate a fresh connection or transaction, and simply returns `fn(this)`. ### `Transaction.addPostCommitStep(fn): Promise` Queue `fn` to be called after the top-level transaction is successfully committed. If this transaction is nested inside another transaction, `fn` will not be called until the outer most transaction has been committed. This can be used to clear caches after updating values.