Use cases often exist where you need to batch a number of SQL statements together that are dependent on one another succeeding, and if any of them fail to execute then it is expected that the collection of queries is all rolled back to the state prior to when it was executed.

A classic example of this is the following:

John is transferring $100 from his account into Jane’s account. The following transaction needs to occur:

1. Reduce John’s balance by $100

2. Increment Jane’s balance by $100

If the second step of that transaction were to fail, we need to make certain that John still has that $100 in his account and the money isn’t lost into the banks systems forever.

Execute Transaction

When we go to execute a transaction, the body of our POST endpoint changes structure to support multiple queries. Normally the body contains an object with two keys, sql and params, but for transactions those objects are now wrapped in an array with a key name of transaction as you will see in the example below.

Here is an example on how to run an array of interdependent statements.

Copycurl --location 'https://starbasedb.brayden-b8b.workers.dev/query' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/json' \
--data '{
    "transaction": [
        {
            "sql": "INSERT INTO users (user_id) VALUES (?);",
            "params": [1]
        },
        {
            "sql": "SELECT * FROM users WHERE user_id=?;",
            "params": [1]
        },
    ]
}'