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>Cockroach Labs Maven Packages</name>
    <url>https://maven.pkg.github.com/cockroachlabs-field/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:cockroachlabs-field/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 provides a familiar and consistent Spring-based programming model for CockroachDB as a SQL database. It bundles the CockroachDB JDBC driver and provides meta-annotations for transactions, connection pooling support, and a shell client for ad-hoc queries.

Kai Niemi's Blog