Sprite Tutorials - Database Transactions

Transactions

ArcadeDB is a transactional database. This is preferred for applications that require a high level of data integrity. CRUD commands are required to be issued as part of a transaction as a way to ensure ACID compliance.

There are a two different ways to conduct transactions in Sprite. This tutorial will demonstate these methods.

Note: You don’t need to run this code; it will be put into practice in the next tutorial.

Prerequisites

Ensure you have completed the installation process, which includes installing ArcadeDB, running it, and accessing it from a TypeScript/JavaScript project with Sprite installed.
You have created a database called “ExampleDatabase”, as accomplished in the Create a Database tutorial

Instantiating SpriteDatabase

The following code demonstrates how to create a new instance of SpriteDatabase in your project:

import { SpriteDatabase } from '@valence-corp/sprite';

const db = new SpriteDatabase({
  username: 'aUsername', // root will be ok for this tutorial
  password: 'aPassword', // your password,
  address: 'http://localhost:2480', // default address for ArcadeDB
  databaseName: 'ExampleDatabase' // the existing database
});

Transaction Helper

You can think of a transaction as a scope in which a series of commands are executed as a unit of work in the database, similar to how a function groups programmatic operations within a scope.

This example uses the SpriteDatabase.transaction method to create a transaction scope. The transaction method accepts one argument, which is a callback. Upon initialization, the transaction method creates a new SpriteTransaction instance and passes it as a parameter to the callback (named trx in this case).

async function transactionHelperExample() {
  try {
    // Ensure a type exists for the record we will create.
    // Schema changes are non-transactional, and non-idempotent.
    // They do not require a transaction.
    await db.command('sql', 'CREATE document TYPE aDocument');

    db.transaction(async (trx) => {
      // the `crud` method only exists on the SpriteTransaction
      // class. It is the only method of conducting transactional
      // operations (CRUD) in Sprite.

      // Using parameterized commands to prevent SQL injection
      const [record] = await trx.crud(
        'sql',
        'INSERT INTO aDocument SET aProperty = :aProperty',
        { aProperty: 'aValue' }
      );
      console.log(record);
      // {
      //   '@rid': "#0:0",
      //   '@cat': 'd',
      //   '@type': 'aDocument',
      //   aProperty: 'aValue'
      // }
    });
  } catch (error) {
    throw new Error('There was an error during the example transaction', {
      cause: error
    });
  }
}

There is an SQL crud operation issued within the scope of the transaction callback. This tells the database that we intend to group this operation within the transaction scope. Remember that CRUD operations are transactional in ArcadeDB.

The transaction executes the callback passed to it, and then automatically commits the transaction. This tells the database that the transaction is closed, and it can execute all operations as a unit of work.

Note that the record is logged to the console prior to the transaction closing. ArcadeDB will send the preliminary record immediately, but won’t really add it to the database until the transaction is committed.

Manual Transactions

It could be necessary to manually orchestrate a transaction for greater control over the process. If something goes wrong before a transaction is committed (a powerloss, user error, or race condition), it might be necessary to rollback a transaction. This can be accomplished by using the SpriteTransaction.rollback() method.

This example creates a transaction (named trx). The transaction’s crud method is invoked to create a document, finally we use the rollback, this ensures that the created record was not added to the database, and the transaction is closed.

async function manualTransaction() {
  try {
    // ensure a type exists prior to
    // attemping to insert a document
    await db.command('sql', 'CREATE document TYPE aDocument');
    const trx = await db.newTransaction();

    // Using parameterized commands to prevent SQL injection
    const [record] = await trx.crud(
      'sql',
      'INSERT INTO aDocument SET aProperty = :aProperty',
      { aProperty: 'aValue' }
    );

    console.log(record);
    // {
    //   '@rid': "#0:0",
    //   '@cat': 'd',
    //   '@type': 'aDocument',
    //   aProperty: 'aValue'
    // }

    await trx.rollback();
    // await trx.commit();
  } catch (error) {
    throw new Error('There was an error during the example transaction', {
      cause: error
    });
  }
}

Note that the transaction also has a commit method, one could use that to commit the transaction instead.

Conclusion

In this tutorial, we demonstrated how to work with transactions in Sprite. We covered both manual transactions, and the transaction helper.

What’s Next?

The next section will demonstrate the various method of performing a select query to retrieve records.