Introducing Spring Data JDBC for ScalarDB

Spring Data JDBC for ScalarDB was first released in ScalarDB 3.8 and some improvements and new features were added in ScalarDB 3.9. This blog post introduces why we provide this feature and describes some details about usage.

Why we provide Spring Data JDBC for ScalarDB

ScalarDB is a very powerful tool to achieve database-agnostic and multidatabase/service ACID transactions. However, directly using the ScalarDB API can be challenging because you need to write a significant amount of code and consider how and when to call the API (e.g., rollback() and commit()) for transactions. To address this issue, to help users develop ScalarDB applications with a lot less code and more safety, we started developing Spring Data JDBC for ScalarDB.

We assume most ScalarDB users develop their applications in Java or other JVM languages. The Spring Framework is one of the most popular application frameworks when developing in Java and other JVM languages. With Spring Data JDBC for ScalarDB, we are committed to helping you develop your applications more easily and securely.

Some module options to integrate with the persistent layer of the Spring Framework include JDBC, Spring JDBC, Spring Data JDBC, and Spring Data JPA. After considering the learning curves and efficiencies, we chose to take advantage of Spring Data JDBC. We also considered Spring Data JPA, which has many features, but we noticed some complexities and a steep learning curve when researching the module.

https://hackmd.io/@ddubson/rkn-sR4wU

Overall architecture

In addition to ScalarDB Core (CRUD API), which directly communicates with underlying databases, we also offer ScalarDB SQL, which has a SQL interface, so that you can communicate with the ScalarDB layer via SQL. Spring Data JDBC can also communicate with databases via SQL, so it may seem that the functionalities can relatively easily collaborate through SQL. But we noticed there are gaps between what Spring Data JDBC expects on databases and how ScalarDB SQL actually works. Because of this, one of the main challenges when implementing Spring Data JDBC for ScalarDB was to fill the gaps and integrate both solutions seamlessly.

With this integration, you can call the Spring Data JDBC API to manipulate an underlying RDBMS and/or a NoSQL database through ScalarDB.

How to use Spring Data JDBC for ScalarDB

Using Spring Data JDBC for ScalarDB basically follows the same semantics and syntax as Spring Data JDBC, as described in Spring Data JDBC — Reference Documentation. Because of this, you can configure and use Spring Data JDBC for ScalarDB in a similar way to Spring Data JDBC.

Manipulating a database

To connect ScalarDB and its underlying databases via SQL, you must configure a JDBC connection URL in resources/application.properties.

spring.datasource.driver-class-name=com.scalar.db.sql.jdbc.SqlJdbcDriver
spring.datasource.url=jdbc:scalardb:\
?scalar.db.sql.connection_mode=direct\
&scalar.db.contact_points=jdbc:mysql://localhost:3306/my_app_ns\
&scalar.db.username=root\
&scalar.db.password=mysql\
&scalar.db.storage=jdbc\
&scalar.db.consensus_commit.isolation_level=SERIALIZABLE

In the Java source code, the @EnableScalarDbRepositories annotation must be configured to enable Spring Data JDBC for ScalarDB instead of @EnableJdbcRepositories for Spring Data JDBC.

@SpringBootApplication
@EnableScalarDbRepositories
public class MyApplication {
    @Autowired private AccountRepository accountRepository;

    ...
}

Then, you can write entity model classes and repository classes. When using Spring Data JDBC, you would use CrudRepository (or PagingAndSortingRepository.) However, in Spring Data JDBC for ScalarDB, you would use ScalarDbRepository instead.

@Table
public class Account {
  @Id public int id;
  public int balance;

  public Account(int id, int balance) {
    this.id = id;
    this.balance = balance;
  }
}

public interface AccountRepository
    extends ScalarDbRepository<Account, Integer> {

  default void transfer(int srcAccountId, int dstAccountId, int amount) {
    Account src = findById(srcAccountId).get();
    Account dst = findById(dstAccountId).get();
    update(new Account(src.id, src.balance - amount));
    update(new Account(dst.id, dst.balance + amount));
  }
}

By following these configurations and this implementation, you can write code to transfer a specific amount between the balances of two accounts within a transaction, guaranteeing ACID compliance with strict serializability.

// Transfer 5000 from account:42's balance to account:24's balance
accountRepository.transfer(42, 24, 5000);

A transaction for the transfer operation will be automatically committed if the transaction finishes successfully or will be rolled back if the transaction fails.

If you implement the same operation as above by using the ScalarDB API in a straightforward way, the implementation would look like the following:

 DistributedTransaction tx = transactionManager.start();

 try { 
   Account src = tx.get(Get.newBuilder()
       .namespace(NAMESPACE)
       .table(ACCOUNT_TABLE)
       .partitionKey(Key.ofInt(KEY_ID, 42)).build())
     .map(result ->
       new Account(
         result.getInt(KEY_ID),
         result.getInt(KEY_BALANCE))).get();

   Account dst = tx.get(Get.newBuilder()
       .namespace(NAMESPACE)
       .table(ACCOUNT_TABLE)
       .partitionKey(Key.ofInt(KEY_ID, 24)).build())
     .map(result ->
       new Account(
         result.getInt(KEY_ID),
         result.getInt(KEY_BALANCE))).get();

   tx.put(Put.newBuilder()
       .namespace(NAMESPACE)
       .table(ACCOUNT_TABLE)
       .partitionKey(Key.ofInt(KEY_ID, src.id))
       .intValue(KEY_BALANCE, src.balance - 5000).build());

   tx.put(Put.newBuilder()
       .namespace(NAMESPACE)
       .table(ACCOUNT_TABLE)
       .partitionKey(Key.ofInt(KEY_ID, dst.id))
       .intValue(KEY_BALANCE, dst.balance + 5000).build());

   tx.commit();
 } catch (Throwable e) { 
   tx.abort();
   throw e;
 }

We hope this example shows how Spring Data JDBC for ScalarDB simplifies development of Spring Framework–based applications that use ScalarDB.

Manipulating databases by leveraging the Multi-storage Transactions feature

If you want to manipulate multiple underlying databases within a transaction while guaranteeing ACID compliance with strict serializability, you can seamlessly leverage the Multi-storage Transactions feature in ScalarDB by configuring resources/application.properties as follows:

spring.datasource.driver-class-name=com.scalar.db.sql.jdbc.SqlJdbcDriver
spring.datasource.url=jdbc:scalardb:\
?scalar.db.sql.connection_mode=direct\
&scalar.db.storage=multi-storage\
&scalar.db.multi_storage.storages=north,south\
&scalar.db.multi_storage.namespace_mapping=north:north,south:south&scalar.db.multi_storage.default_storage=south\
&scalar.db.multi_storage.storages.north.storage=jdbc\
&scalar.db.multi_storage.storages.north.contact_points=jdbc:mysql://localhost:3306/\
&scalar.db.multi_storage.storages.north.username=${north_username}\
&scalar.db.multi_storage.storages.north.password=${north_password}\
&scalar.db.multi_storage.storages.south.storage=jdbc\
&scalar.db.multi_storage.storages.south.contact_points=jdbc:postgresql://localhost:5432/\
&scalar.db.multi_storage.storages.south.username=${south_username}\
&scalar.db.multi_storage.storages.south.password=${south_password}\
&scalar.db.consensus_commit.isolation_level=SERIALIZABLE\
&scalar.db.sql.default_namespace_name=my_app

This example configuration is designed to manage two underlying databases: MySQL and PostgreSQL. These databases correspond to the logical namespaces north and south respectively. Through Spring Data JDBC for ScalarDB, you can seamlessly access tables in the both namespaces, within a transaction.

Then, you can access tables over the multiple underlying databases by implementing model and repository classes.

// `schema` field is used to specify the underlying database
@Table(schema = "north", value = "account")
public class NorthAccount {
  @Id public final int id;
  public final Integer balance;
  public NorthAccount(int id, Integer balance) {
    this.id = id;
    this.balance = balance;
  }
}

@Table(schema = "south", value = "account")
public class SouthAccount {
  @Id public final int id;
  public final Integer balance;
  public SouthAccount(int id, Integer balance) {
    this.id = id;
    this.balance = balance;
  }
}

@Transactional
@Repository
public interface NorthAccountRepository
    extends ScalarDbRepository<NorthAccount, Integer> {

 default void transferToSouthAccount(
      SouthAccountRepository southAccountRepository,
      int fromId, int toId, int value) {
    NorthAccount fromEntity =
        findById(fromId).orElseThrow(
            () -> new AssertionError("Not found: " + fromId));
    SouthAccount toEntity =
        southAccountRepository
            .findById(toId)
            .orElseThrow(() -> new AssertionError("Not found: " + toId));
    update(new NorthAccount(fromEntity.id, fromEntity.balance - value));
    southAccountRepository.update(
      new SouthAccount(toEntity.id, toEntity.balance + value));
  }
}

@Transactional
@Repository
public interface SouthAccountRepository
    extends ScalarDbRepository<SouthAccount, Integer> {}

  @Autowired private NorthAccountRepository northAccountRepository;
  @Autowired private SouthAccountRepository southAccountRepository;

  ...

  northAccountRepository.transferToSouthAccount(
        southAccountRepository, 42, 24, 5000);

We have a few sample applications that utilize Spring Data JDBC for ScalarDB, which you can execute. If you’re interested, try the following ones:

• Sample application of Spring Data JDBC for ScalarDB
• Sample application of Spring Data JDBC for ScalarDB with Multi-storage Transactions
• Sample application of Spring Data JDBC for ScalarDB with Microservice Transactions

For more details, see Guide of Spring Data JDBC for ScalarDB.

Extensions from Spring Data JDBC

Spring Data JDBC for ScalarDB adds some extensions to the original Spring Data JDBC module.

Prohibiting CrudRepository#save(T t), and adding new insert(T t) and update(T t) methods in ScalarDbRepository

In Spring Data JDBC, both insertion and update records are handled with CrudRepository#save(T t), which decides which operation to do out of insertion and update based on whether the @Id field of a model instance received as an argument is null. CrudRepository#save(T t) inserts a new record if @Id field is null or updates an existing record otherwise. This behavior relies on the autoincrement column feature (ID Generation) in RDBMS, but ScalarDB doesn’t support this feature. There are some workarounds in Spring Data JDBC to address this issue, but those workarounds are not intuitive and require you to implement additional code.

We’ve introduced ScalarDbRepository, which inherits CrudRepository of Spring Data JDBC, and made the following customizations:

• Explicitly prohibit calling ScalarDbRepository#save(T t), which may be confusing, by throwing an exception.
• Add new insert(T t) and update(T t) methods to ScalarDbRepository. Both methods receive a model instance that has a non-null @Id field.

By using ScalarDbRepository, you can intuitively add and update records even with ScalarDB.

API for two-phase commit transactions

ScalarDB supports two-phase commit (2PC) transactions to execute a transaction that spans multiple processes or applications (e.g., microservices). Spring Data JDBC for ScalarDB also supports this feature with the following extensions:

Primitive 2PC transaction API

• Corresponds to the 2PC API (e.g., prepare(), commit() and rollback()) in ScalarDB.
• Supports explicitly controlling 2PC transaction operations in flexible and fine-grained ways. On the other hand, you will need to consider which API to call in a proper order when using the API.

High-level 2PC transaction API

• Provides a user-friendly API to cover common use cases. With this API, you don’t need to think about how and when to commit or rollback a transaction.
• For coordinator service, the following API is provided
  - executeTwoPcTransaction
• For participant services, the following APIs are provided
  - joinTransactionOnParticipant
  - resumeTransactionOnParticipant
  - prepareTransactionOnParticipant
  - validateTransactionOnParticipant
  - commitTransactionOnParticipant
  - rollbackTransactionOnParticipant

The default transaction manager for Spring Data JDBC always commits the transaction when quitting a CrudRepository API call. But the behavior doesn’t work well with 2PC transactions in ScalarDB because the feature needs to control commit timing. Because of this, we’ve introduced a custom transaction manager that inherits JdbcTransactionManager and doesn’t automatically commit transactions.

Let’s say you want to implement the following transactional operations, while ensuring rollback in case of failure, over a set of microservices.

With the high-level API of Spring Data JDBC for ScalarDB, you can focus on the business logic by hiding complicated transaction operations inside the API as follows:

Account Service (coordinator)

@Transactional(transactionManager = "scalarDbSuspendableTransactionManager")
@Repository
public interface AccountRepository
  extends ScalarDbTwoPcRepository<Account, Integer> {
  // findBydId(), insert(), update(), executeTwoPcTransaction()
  // and some more APIs are prepared automatically.
}

@Autowired private Account accountRepository;

...

accountRepository.executeTwoPcTransaction(
  // Business logic (CRUD operations) for local and
  // remote participants in execution phase.
  txId -> {
    // [local] Read the account's balance
    Optional<Account> stored = accountRepository.findById(account.id);
    if (!stored.isPresent()) {
      // Cancel the transaction if the account doesn't exist.
      // No need to retry.
      throw new ScalarDbNonTransientException(
          "The local state doesn't meet the condition.
                Aborting this transaction");
    }
    // [remote] Start a transaction with the transaction ID,
    //          read the item information and decrement the count
    Optional<Integer> price = stockService.purchaseItem(
                                             txId, account.id, itemName);
    if (price.isPresent()) {
      int currentBalance = stored.get().balance - price.get();
      if (currentBalance < 0) {
        // Cancel the transaction
        // if the global state doesn't meet the condition.
        // No need to retry.
        throw new ScalarDbNonTransientException(
            "The state of local and remote participants doesn't meet
                the condition. Aborting this transaction");
      }
      // [local] Decrease the account's balance for the item
      accountRepository.update(new Account2Pc(account.id, currentBalance));
      return currentBalance;
    }
    // Cancel the transaction
    // if the global state doesn't meet the condition.
    // No need to retry.
    throw new ScalarDbTransientException(
      "The remote state doesn't meet the condition.
          Aborting this transaction");
  },

  // Remote operations for Prepare/Validate/Commit/Rollback.
  // These transactional operations will be automatically executed.
  Arrays.asList(
        RemotePrepareCommitPhaseOperations.createSerializable(
          stockService::callPrepareTransaction,
          stockService::callValidateTransaction,
          stockService::callCommitTransaction,
          stockService::callRollbackTransaction)
);

Stock Service (participant)

@Transactional(transactionManager = "scalarDbSuspendableTransactionManager")
@Repository
public interface StockRepository
  extends ScalarDbTwoPcRepository<Stock, String> {
  // joinTransactionOnParticipant(), commitTransactionOnParticipant()
  // and some more APIs are prepared automatically.
}

@RestController
public class StockController {
  @Autowired private StockRepository stockRepository;

  @PostMapping("/purchaseItem")
  public Optional<Integer> purchaseItem(
               @RequestParam("transactionId") String transactionId,
               @RequestParam("accountId") int accountId,
               @RequestParam("itemName") String itemName) {
    // Join the global transaction and execute the CRUD operations in it
    return stockRepository.joinTransactionOnParticipant(txId, () -> {
        Optional<Item> item = stockRepository.findById(itemName);

        ...

        return Optional.of(item.price);
    });
  }

  @PostMapping("/prepareTransaction")
  public void prepareTransaction(
                  @RequestParam("transactionId") String transactionId) {
    return stockRepository.prepareTransactionOnParticipant(txId);
  }

  @PostMapping("/validateTransaction")
  public void validateTransaction(
                  @RequestParam("transactionId") String transactionId) {
    return stockRepository.validateTransactionOnParticipant(txId);
  }

  @PostMapping("/commitTransaction")
  public void commitTransaction(
                  @RequestParam("transactionId") String transactionId) {
    return stockRepository.commitTransactionOnParticipant(txId);
  }

  @PostMapping("/rollbackTransaction")
  public void rollbackTransaction(
                  @RequestParam("transactionId") String transactionId) {
    return stockRepository.rollbackTransactionOnParticipant(txId);
  }
}

Wrap-up

In this blog post, we introduced Spring Data JDBC for ScalarDB. We believe this feature makes the development of Spring Framework–based applications that use ScalarDB easier and more secure. Although there are some limitations that come from Spring Data JDBC itself and the integration of Spring Data JDBC and ScalarDB, our goal is to address those limitations in the future to help you simplify application development.