Using Database Transactions to Write Queries in Strapi

Author: Wildan Mubarok

Outline

• Introduction
• Prerequisites
• How Strapi Handles Database
• Strapi Query Functions
• Writing Database Transactions
• Safety on Errors
• Safety on Race Conditions
• Performance Tuning
• Conclusion

When we write software, we always want to make it as flawless as possible. While we always strive for it hard enough, sometimes bugs still can appear in a production system. So this is why, when we write software, we have to prepare for the worst when things go wrong.

When software goes wrong, it will halt the code execution and returns an error message immediately. That is easy to implement, but what happens if it comes to a halt while it's in the middle of writing something in the database?

Let's say you’re creating a website with a payment system. Imagine user A wants to deposit some amount of his balance to user B. The server first decreases user A’s balance, but before the server can increase user B’s balance it crashes. This would be a disaster, how much of the users' balance would be gone missing, simply because of technical problems? This is why we have to learn to use Database Transactions.

A database transaction is a concept to merge several database queries such that we want to treat them as “a whole”. A transaction will either execute completely (committed) or not at all (rolled back). When a transaction has started, all execution will happen in the memory and not yet be written until it is fully committed. If we had used a database transaction in our problem above, the changes would only have happened in memory and not be committed, which makes users’ deposits untouched.

Most database libraries provide a way to use database transactions. That’s including Knex, which is used by Strapi through Bookshelf. Strapi is a headless content management system (CMS) built with React.js and Node.js. It is easy to learn. It supports PostgreSQL, SQLite, MySQL, and MariaDB. If you start to write code to implement custom routes in Strapi, this tutorial is for you. We’ll learn how to write queries for custom routes in Strapi with Database Transaction.

Prerequisites

Before you can jump into this content, you need to have a basic understanding of the following.

1. Basic knowledge of writing JavaScript for Node.js
2. Basic knowledge to request an API endpoint (with Postman)
3. Basic understanding of Strapi - get started here.
4. Basic understanding of Backend Customization in Strapi - learn more here.
5. Downloaded and installed latest Node.js v14. # How Strapi Handles Database

Strapi uses Bookshelf.js library to send database queries in an ORM fashion. Bookshelf itself is powered by Knex.js, which is a SQL query builder. Knex.js supports popular SQL-based database engines like PostgreSQL, SQLite, MySQL, and MariaDB, which are also supported by Strapi. Knex.js also supports database transactions, which then makes Bookshelf also provides support for it. With a basic understanding of both libraries, we can add Database transaction support to Strapi queries.

Strapi Query Functions

Referencing from the Backend customization API documentation, we recall these query methods:

    // find
    strapi.query(entity).find(params, populate);
    // findOne
    strapi.query(entity).findOne(params, populate);
    // create
    strapi.query(entity).create(attributes);
    // update
    strapi.query(entity).update(params, attributes);
    // delete
    strapi.query(entity).delete(params);

Because database transactions are an undocumented feature within Strapi, we need to look at the query function declarations in the source code. Digging deeper in the source code, we'll learn that we can pass the [transacting](https://github.com/strapi/strapi/blob/v3.6.8/packages/strapi-connector-bookshelf/lib/queries.js#L67-L177) parameter of Bookshelf as an extra parameter to the query functions so we can run the query inside a transaction. I summarize these query declarations here:

    // find
    strapi.query(entity).find(params, populate, { transacting })
    // findOne
    strapi.query(entity).findOne(params, populate, { transacting });
    // create
    strapi.query(entity).create(attributes, { transacting });
    // update
    strapi.query(entity).update(params, attributes, { transacting });
    // delete
    strapi.query(entity).delete(params, { transacting });

Writing Database Transactions

So now know that we can use { transacting } as an extra parameter. But how do we use it? If we look at the Bookshelf documentation source, we can set this parameter with the callback parameter of Bookshelf.transaction() which is just an alias to a Knex.js Transaction. And then on the Backend customization page, we know that we can get the Knex.js instance within Strapi with:

    const knex = strapi.connections.default;

With a bit of reading from Knex.js Transaction documentation, we can start writing our query with transactions. The transaction callback will be something like this:

    await strapi.connections.default.transaction(async(transacting) => {
      // our queries with { transacting } set here...
    });

Now lets we create a quick example project to demonstrate our problem before using database transactions in Strapi. Start by running this command in your favorite terminal:

    npx create-strapi-app@3 strapi-api --quickstart

This will create a new Strapi project in the folder strapi-api. Before we can start writing we should create some collections for demonstration. Run our project by running this command:

    npm run develop

Open the admin dashboard, enter the new login credentials (only for the first time), go to Content-Types Builder, click on Create new collection type and create a new Receipt collection type for recording transactions with attributes below.

You also need to add balance to the existing User collection. We use this attribute to track users' balance.

Now, let's create a new controller to handle balance transfers. Open the Receipt controller and write this code. I have added comments for explanation.

.api/receipt/controllers/receipt.js

    'use strict';
    const { sanitizeEntity } = require('strapi-utils');

    module.exports = {
        async transferBalance(ctx) {
            // Read from POST body
            const { amount, from, to } = ctx.request.body;

            return await strapi.connections.default.transaction(async (transacting) => {
                // Find the user
                const fromUser = await strapi.query('user', 'users-permissions').findOne({ id: from }, null, { transacting });
                const toUser = await strapi.query('user', 'users-permissions').findOne({ id: to }, null, { transacting });

                // Check if the user exists
                if (!fromUser || !toUser) {
                    return ctx.badRequest(null, 'User not found');
                }
                // Check if the user has enough balance
                if (fromUser.balance < amount) {
                    return ctx.badRequest(null, 'Insufficient balance');
                }

                // Update the user balance
                fromUser.balance -= amount;
                toUser.balance += amount;

                // Record the transaction
                const receipt = await strapi.query('receipt').create({
                    sender: fromUser.id,
                    recipient: toUser.id,
                    amount,
                }, { transacting });

                // Save the user
                await strapi.query('user', 'users-permissions').update({ id: from }, fromUser, { transacting });
                await strapi.query('user', 'users-permissions').update({ id: to }, toUser, { transacting });

                // respond with the receipt (don't forget to sanitize our output!)
                return sanitizeEntity(receipt, { model: strapi.models.receipt });
            });
        }
    };

We also need to add a route to route config of Receipt. After that, we can call the controller function from API.

.api/receipt/config/routes.json

    {
      "routes": [
        // ...
        {
          "method": "POST",
          "path": "/receipts/transfer-balance",
          "handler": "receipt.transferBalance",
          "config": {
            "policies": []
          }
        }
      ]
    }

Then we need to allow this endpoint to be accessed publicly so we can request it with Postman. In the sidebar, Go to Settings, Roles, Public, then at the Application section, look for transferbalance and click Save. Remember this is not the right way to give access to this endpoint in real life, we just only do it for an easy demonstration!

Now let’s add two new users using Admin Dashboard. Go to Users in the sidebar then Add New Users. Let’s call them alpha and bravo and set each balance to 1000. If you haven’t added any users beforehand, the ids should be 1 and 2.

Now open Postman and try to call our controller function, by sending the following POST to ``.

If everything is done correctly, we would receive this response.

`json
{
"id": 1,
"sender": {
"id": 1,
"username": "alpha",
"email": "alpha@example.com",
"provider": "local",
"confirmed": false,
"blocked": false,
"role": 1,
"balance": 900,
"created_at": "2021-11-15T08:25:05.013Z",
"updated_at": "2021-11-15T09:30:33.243Z"
},
"recipient": {
"id": 2,
"username": "bravo",
"email": "bravo@example.com",
"provider": "local",
"confirmed": false,
"blocked": false,
"role": 1,
"balance": 1100,
"created_at": "2021-11-15T08:25:55.309Z",
"updated_at": "2021-11-15T09:30:33.279Z"
},
"amount": 100,
"created_at": "2021-11-15T09:31:27.483Z",
"updated_at": "2021-11-15T09:31:27.494Z"
}
`

We can see that the transfer from alpha to bravo for the amount of 100 has been successfully done! You can also confirm both user balances and the new Receipt item in the Admin Dashboard.

Safety on Errors

Why bother using database transactions when there’s seems to be no difference at all compared to when we don’t use it?

That kind of assumption could be our biggest mistake in the long term.

Using database transactions creates a guarantee that there will be no query that’s executed only halfway no matter it’s caused by a software error or even a hardware power outage.

So for our demonstration, let’s rewrite our function without database transaction.

`js
'use strict';
const { sanitizeEntity } = require("strapi-utils/lib");
module.exports = {
async transferBalance(ctx) {
// Read from POST body
const { amount, from, to } = ctx.request.body;
// Find the user
const fromUser = await strapi.query('user', 'users-permissions').findOne({ id: from }, null);
const toUser = await strapi.query('user', 'users-permissions').findOne({ id: to }, null);
// Check if the user has enough balance
if (fromUser.balance < amount) {
return ctx.badRequest(null, 'Insufficient balance');
}
// Save the user data
await strapi.query('user', 'users-permissions').update({ id: from }, { balance: fromUser.balance -= amount });
await strapi.query('user', 'users-permissions').update({ id: to }, { balance: toUser.balance += amount });
// Record the transaction
let receipt = await strapi.query('receipt').create({
sender: fromUser.id,
recipient: toUser.id,
amount,
});
// respond with the receipt (don't forget to sanitize our output!)
return sanitizeEntity(receipt, { model: strapi.models.receipt });
}
};
`

What would happen if we send data with to to a non-existing user ID? The controller will throw on line 16, simply because toUser is null (it does not exist). Because line 15 is already been executed, the sender balance transferred will simply go out of nowhere.

That is the danger of doing multiple queries without a transaction. Of course, our example above is straightforwardly not secure, it’s easy to spot mistakes there. But the real world is messy, in some point in the future our functions are often 100-ish lines long and we wouldn’t know that a vulnerability existed until it’s become too late.

Safety on Race Conditions

Another important feature of database transactions is they’re safe from a race condition. To explain that, we need to take a stress test to our functions above.

Let’s try the insecure one (without database transaction) first. First things first, we need to reset both user balances to 1000 each (you can modify them in Admin Dashboard), then we run a thousand transfer requests in parallel. Let’s do this in a browser. Open your browser and the console tab in the browser development tools (Press f12). Paste and run the below code.

`js
// loop a thousand times and send fetch() simultaneously!
for (let i = 0; i < 1000; i++) {
fetch('http://localhost:1337/receipts/transfer-balance', {
body: '{"from": 1, "to":2, "amount":1}',
method: 'POST',
headers: {
'content-type': 'application/json'
}
})
}
`

Take a moment to understand what we are doing. We are transferring an amount of 1, from account alpha to bravo, a thousand times. If the starting balance for both is 1000, then we would expect the alpha balance to end in 0 and bravo end with 2000.

If we use the controller without a database transaction, the result will looks something like this:

While the controller with a database transaction ends as we expected:

You might be asking, why it can be like that? Because database transactions are not just about error safety, it’s also about consistency. By using database transactions those queries are guaranteed to be isolated, not have any else query interrupting between the way. The database can make it possible by exclusively locking any row that’s been used in a transaction, forcing other modifications are waiting until it’s either committed or rolled back.

Consistency and Isolation are important aspects of the database world. If we need to mutate (changing iteratively) a row of data, we need to use a database transaction. Of course, it’s not just about money or balance, think like a voting system, a gift card, or even articles’ view count. It’s important to wrap any iteratively changing data in a database transaction.

Performance Tuning

Most of the time, using database transactions won’t introduce any noticeable performance impact. However, if you encounter that, Knex.js lets you choose how much “safety” you need for a specific transaction. These are known as Isolation Levels. There are four levels of isolations, a less restrictive level will give some performance boost in the trade of less stability. These isolation levels (ordered from the strongest one) are:

• Serializable. (this is the strongest)
• Repeatable reads. (this is the default)
• Read Committed.
• Read Uncommitted. (this is the weakest)

Depending on your use case, it may be best (or not) to leave with the default level. You can read on this Wikipedia page for further reading. To apply a specific level, you can read the isolation levels section of Knex.js documentation. I will summarize how to do that here in the code below:

`js
try {
const isolationLevel = 'read committed';
const transacting = await knex.transaction({isolationLevel});
// ... database query here ...
await transacting.commit();
} catch (err) {
await transacting.rollback();
}
`

Be warned that SQLite doesn’t support setting isolation levels, so if you use SQLite for development only, don't override it.

`js
const isProduction = strapi.config.environment === 'production';
const isolationLevel = isProduction ? 'read committed' : null;
const transacting = await knex.transaction({isolationLevel});
// ... database query below ...
`

Conclusion

This article demonstrated how to safely write queries using Database Transaction in Strapi. We also learn how important it can be in production servers.

You can download the source code from this code repository.

Let me know you have any suggestions and what you will be building with the knowledge.