The Spring Data CockroachDB project aims to provide a familiar and consistent Spring-based programming model for CockroachDB as a SQL database.

The primary goal of the Spring Data project is to make it easier to build Spring-powered applications that use new data access technologies such as relational databases, non-relational databases, map-reduce frameworks, and cloud-based data services.

CockroachDB is a distributed SQL database built on a transactional and strongly-consistent key-value store. It scales horizontally; survives disk, machine, rack, and even datacenter failures with minimal latency disruption and no manual intervention; supports strongly-consistent ACID transactions; and provides a familiar SQL API for structuring, manipulating, and querying data.

Features

• Bundles the CockroachDB JDBC driver

• Meta-annotations for declaring:

  • Retryable transactions

  • Read-only transactions

  • Strong and stale follower-reads

  • Custom session variables including timeouts

• AOP aspects for:

  • Retrying transactions on serialization conflicts

  • Configuring session variables, like follower-reads

• Connection pool factory settings for HikariCP

• Datasource proxy logging via TTDDYY

• Simple JDBC shell client for ad-hoc queries and testing

Getting Started

Here is a quick teaser of an application using Spring Data JPA Repositories in Java:

@Repository
public interface AccountRepository extends JpaRepository<Account, UUID> {
    Optional<Account> findByName(String name);

    @Query(value = "select a.balance "
            + "from Account a "
            + "where a.id = ?1")
    BigDecimal findBalanceById(UUID id);

    @Query(value = "select a.balance "
            + "from account a AS OF SYSTEM TIME follower_read_timestamp() "
            + "where a.id = ?1", nativeQuery = true)
    BigDecimal findBalanceSnapshotById(UUID id);

    @Query(value = "select a "
            + "from Account a "
            + "where a.id in (?1)")
    @Lock(LockModeType.PESSIMISTIC_READ)
    List<Account> findAllForUpdate(Set<UUID> ids);
}

@Service
public class AccountService {
    @Autowired
    private AccountRepository accountRepository;

    @NotTransactional
    public Account create(Account account) {
        return accountRepository.save(account);
    }

    @NotTransactional
    public Account findByName(String name) {
        return accountRepository.findByName(name)
                .orElseThrow(() -> new ObjectRetrievalFailureException(Account.class, name));
    }

    @NotTransactional
    public Account findById(UUID id) {
        return accountRepository.findById(id).orElseThrow(() -> new ObjectRetrievalFailureException(Account.class, id));
    }

    @TransactionBoundary
    @Retryable
    public Account update(Account account) {
        Account accountProxy = accountRepository.getReferenceById(account.getId());
        accountProxy.setName(account.getName());
        accountProxy.setDescription(account.getDescription());
        accountProxy.setBalance(account.getBalance());
        accountProxy.setClosed(account.isClosed());
        return accountRepository.save(accountProxy);
    }

    @NotTransactional
    public BigDecimal getBalance(UUID id) {
        return accountRepository.findBalanceById(id);
    }

    @TransactionBoundary(timeTravel = @TimeTravel(mode = TimeTravelMode.FOLLOWER_READ), readOnly = true)
    public BigDecimal getBalanceSnapshot_Explicit(UUID id) {
        return accountRepository.findBalanceById(id);
    }

    @NotTransactional
    public BigDecimal getBalanceSnapshot_Implicit(UUID id) {
        return accountRepository.findBalanceSnapshotById(id);
    }

    @TransactionBoundary
    public void delete(UUID id) {
        accountRepository.deleteById(id);
    }

    @TransactionBoundary
    public void deleteAll() {
        accountRepository.deleteAll();
    }
}

@Configuration
@EnableTransactionManagement(order = AdvisorOrder.TRANSACTION_ADVISOR)
@EnableJpaRepositories(basePackages = {"org.acme.bank"})
public class BankApplication {
    @Bean
    public TransactionRetryAspect retryAspect() {
        return new TransactionRetryAspect();
    }

    @Bean
    public TransactionBoundaryAspect transactionBoundaryAspect(JdbcTemplate jdbcTemplate) {
        return new TransactionBoundaryAspect(jdbcTemplate);
    }
}

Maven configuration

Add this dependency to your pom.xml file:

<dependency>
    <groupId>io.cockroachdb</groupId>
    <artifactId>spring-data-cockroachdb</artifactId>
    <version>{version}</version>
</dependency>

Then add the Maven repository to your pom.xml file (alternatively in Maven's settings.xml):

<repository>
    <id>cockroachdb-jdbc</id>
    <name>Maven Packages</name>
    <url>https://maven.pkg.github.com/cloudneutral/cockroachdb-jdbc</url>
    <snapshots>
        <enabled>true</enabled>
    </snapshots>
</repository>

Finally, you need to authenticate to GitHub Packages by creating a personal access token (classic) that includes the read:packages scope. For more information, see Authenticating to GitHub Packages.

Add your personal access token to the servers section in your settings.xml:

<server>
    <id>github</id>
    <username>your-github-name</username>
    <password>your-access-token</password>
</server>

Now you should be able to build your project with the JDBC driver as a dependency:

mvn clean install

Alternatively, you can just clone the repository and build it locally using mvn install.

Modules

There are several modules in this project:

spring-data-cockroachdb

Provides a Spring Data module for CockroachDB, bundling the CockroachDB JDBC driver, connection pooling support via Hikari and meta-annotations and AOP aspects for client-side retry logic, as an alternative to JDBC driver level retries.

spring-data-cockroachdb-shell

An interactive spring shell client for ad-hoc SQL queries and CockroachDB settings and metadata introspection using the CockroachDB JDBC driver.

spring-data-cockroachdb-distribution

Distribution packaging of runnable artifacts including the shell client and JDBC driver, in tar.gz format. Activated via Maven profile, see build section further down in this page.

spring-data-cockroachdb-it

Integration and functional test harness. Activated via Maven profile, see build section further down in this page.

Building from Source

Spring Data CockroachDB requires Java 17 (or later) LTS.

Prerequisites

• JDK17+ LTS for building (OpenJDK compatible)

• Maven 3+ (optional, embedded)

If you want to build with the regular mvn command, you will need Maven v3.x or above.

Install the JDK (Linux):

sudo apt-get -qq install -y openjdk-17-jdk

Install the JDK (macOS):

brew install openjdk@17

Dependencies

This project depends on the CockroachDB JDBC driver whose artifacts are available in GitHub Packages.

Clone the project

git clone git@github.com:cloudneutral/spring-data-cockroachdb.git
cd spring-data-cockroachdb

Build the project

chmod +x mvnw
./mvnw clean install

If you want to build with the regular mvn command, you will need Maven v3.5.0 or above.

Build the distribution

./mvnw -P distribution clean install

The distribution tar.gz is now found in spring-data-cockroachdb-distribution/target.

Run Integration Tests

The integration tests will run through a series of contended workloads to exercise the retry mechanism and other JDBC driver features.

First, start a local CockroachDB node or cluster and create the database:

cockroach sql --insecure --host=localhost -e "CREATE database spring_data_test"

Then activate the integration test Maven profile:

./mvnw -P it clean install

See the pom.xml file for changing the database URL and other settings (under ìt profile).

Conclusion

The Spring Data CockroachDB project offers a Spring-based programming model for CockroachDB, a distributed SQL database. It simplifies building Spring-powered applications with new data access technologies and includes features like bundling the CockroachDB JDBC driver, meta-annotations for transactions, connection pooling support, and a shell client for ad-hoc queries. The project requires Java 17 or later and can be built using Maven.