This guide connects a Spring Boot 4.1 service to a sharded Neki table through its default HikariCP pool. The JDBC URL passes two router settings as startup options, so the router rejects any query without the shard key and any @Transactional method that reaches a second shard.
What you will build
GET /customers/{id}/orders -> WHERE customer_id = ? -> one shard
GET /orders/count -> no shard key -> 409, rejected by the router
POST /customers/{id}/orders -> @Transactional, one customer -> committed
POST /transfer?from=1&to=2 -> @Transactional, customers on 2 shards -> 409, nothing written
AI Prompt
Using Spring Boot 4.1.2 on Java 21 with its default HikariCP pool and the PostgreSQL JDBC driver, connect a service to a sharded Neki table through the router.
Requirements:
- Create the Neki database with the PlanetScale CLI (pscale 0.344.0): --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, two added shards (three in total), and a data topology that shards orders by customer_id. Wait until every shard answers a direct SELECT 1 with SET __neki.shard.
- Set spring.datasource.url to jdbc:postgresql://<host>:5432/postgres?sslmode=require&options=-c%20__neki.fanout%3Dsingle%20-c%20__neki.tx_mode%3Dsingle. The options value is URL-encoded.
- Use JdbcTemplate with ? parameters and include customer_id in every WHERE clause and INSERT.
- Keep each @Transactional method on one customer, or on customers on one shard.
- Map DataAccessException to an HTTP error that shows the router's message.
- Run the verification step below before finishing.
Prerequisites
A PlanetScale organization with access to the Neki Platform Preview. Create an account if you do not have one, and see Neki availability and access for how an organization administrator joins the preview.
pscale0.344.0 with a service token that can create databases,psql18.6,jq,curl,python3, Java 21 (21.0.13 was used) and Maven 3.9 or later (3.10.0 was used). On macOS,brew install openjdk@21 maveninstalls both:
export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
export JAVA_HOME=/opt/homebrew/opt/openjdk@21 PATH=/opt/homebrew/opt/openjdk@21/bin:$PATH
java -version 2>&1 | head -1
- A Neki database with three shards and an
orderstable holding 20,000 rows, sharded bycustomer_id. Create them with these commands:
pscale database create ordersvc --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create ordersvc main admin --inherited-roles postgres --org "$PLANETSCALE_ORG" --format json > role.json
export PGHOST=$(jq -r .access_host_url role.json) PGUSER=$(jq -r .username role.json) \
PGPASSWORD=$(jq -r .password role.json) PGPORT=5432 PGDATABASE=postgres PGSSLMODE=require
rm role.json
until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
pscale branch shard create ordersvc main --config-profile default --count 2 --org "$PLANETSCALE_ORG"
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "3" ]; do sleep 10; done
for u in $(psql -At -c "SELECT uid FROM __neki.list_shards()"); do
until psql -At -c "SET __neki.shard='$u';" -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
done
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE orders (customer_id bigint NOT NULL, order_id bigint NOT NULL, total_cents bigint NOT NULL, PRIMARY KEY (customer_id, order_id))" \
2>&1 | grep -v NOTICE
SHARD_1=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh1'")
SHARD_2=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh2'")
SHARD_3=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh3'")
cat > data-topology.json <<JSON
{
"shard_indexes": { "xxhash_customer_id": { "type": "xxhash", "columns": ["customer_id"] } },
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "orders_by_customer", "default_shard_index": "xxhash_customer_id",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"orders": { "shard_group": "orders_by_customer" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update ordersvc main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10
python3 -c "
for i in range(1, 20001): print(f'{(i*7919)%2000+1},{i},{(i*31)%100000}')" > orders.csv
psql -c "\copy orders (customer_id, order_id, total_cents) FROM 'orders.csv' WITH (FORMAT csv)"
for c in 1 2; do
for n in sh2 sh3; do
u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
[ "$(psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) FROM orders WHERE customer_id = $c" | tail -1)" != "0" ] && echo "customer $c: $n"
done
done
The COPY prints COPY 20000, and every customer has 10 orders. The last loop shows that customers 1 and 2 are on different shards:
customer 1: sh3
customer 2: sh2
Note: Output samples are trimmed to the relevant lines. Each shard bills as a three-node cluster.
PS-10-AWS-ARM-NEKIlists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish withpscale database delete ordersvc --force --org "$PLANETSCALE_ORG".
1. Create the project
mkdir -p orders/src/main/java/com/example/orders orders/src/main/resources
cd orders
cat > pom.xml <<'EOF'
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>4.1.2</version>
<relativePath/>
</parent>
<groupId>com.example</groupId>
<artifactId>orders</artifactId>
<version>0.0.1</version>
<properties>
<java.version>21</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-webmvc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
EOF
cat > src/main/resources/application.properties <<'EOF'
spring.datasource.url=jdbc:postgresql://${PGHOST}:5432/postgres?sslmode=require&options=-c%20__neki.fanout%3Dsingle%20-c%20__neki.tx_mode%3Dsingle
spring.datasource.username=${PGUSER}
spring.datasource.password=${PGPASSWORD}
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.keepalive-time=60000
spring.datasource.hikari.connection-timeout=5000
EOF
cat > src/main/java/com/example/orders/OrdersApplication.java <<'EOF'
package com.example.orders;
import java.util.List;
import java.util.Map;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.dao.DataAccessException;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
@SpringBootApplication
public class OrdersApplication {
public static void main(String[] args) {
SpringApplication.run(OrdersApplication.class, args);
}
}
@Service
class OrderService {
private final JdbcTemplate jdbc;
OrderService(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
List<Map<String, Object>> ordersFor(long customerId) {
return jdbc.queryForList(
"SELECT order_id, total_cents FROM orders WHERE customer_id = ? ORDER BY order_id", customerId);
}
long countAll() {
return jdbc.queryForObject("SELECT count(*) FROM orders", Long.class);
}
@Transactional
void addOrders(long customerId, long firstOrderId, int n) {
for (int i = 0; i < n; i++) {
jdbc.update("INSERT INTO orders (customer_id, order_id, total_cents) VALUES (?, ?, ?)",
customerId, firstOrderId + i, 100);
}
}
@Transactional
void transfer(long fromCustomer, long toCustomer, long orderId) {
jdbc.update("INSERT INTO orders (customer_id, order_id, total_cents) VALUES (?, ?, ?)",
fromCustomer, orderId, -100);
jdbc.update("INSERT INTO orders (customer_id, order_id, total_cents) VALUES (?, ?, ?)",
toCustomer, orderId + 1, 100);
}
}
@RestController
class OrderController {
private final OrderService orders;
OrderController(OrderService orders) {
this.orders = orders;
}
@GetMapping("/customers/{id}/orders")
List<Map<String, Object>> list(@PathVariable long id) {
return orders.ordersFor(id);
}
@GetMapping("/orders/count")
long count() {
return orders.countAll();
}
@PostMapping("/customers/{id}/orders")
String add(@PathVariable long id, @RequestParam long firstOrderId, @RequestParam int n) {
orders.addOrders(id, firstOrderId, n);
return "added " + n;
}
@PostMapping("/transfer")
String transfer(@RequestParam long from, @RequestParam long to, @RequestParam long orderId) {
orders.transfer(from, to, orderId);
return "ok";
}
@ExceptionHandler(DataAccessException.class)
ResponseEntity<String> dbError(DataAccessException ex) {
return ResponseEntity.status(409).body(ex.getMostSpecificCause().getMessage().split("\n")[0]);
}
}
EOF
2. Build and start the service
mvn -q -DskipTests package
unzip -l target/orders-0.0.1.jar | grep -oE "(HikariCP|postgresql|spring-boot)-[0-9.]+\.jar" | sort -u
nohup java -jar target/orders-0.0.1.jar --server.port=8089 > app.log 2>&1 &
until curl -s -o /dev/null localhost:8089/customers/1/orders; do sleep 1; done
grep -o "HikariPool-1 - Start completed." app.log
Real output:
HikariCP-7.0.2.jar
postgresql-42.7.14.jar
spring-boot-4.1.2.jar
HikariPool-1 - Start completed.
Spring Boot 4.1.2 brings HikariCP 7.0.2 and the PostgreSQL JDBC driver 42.7.14.
3. Read one customer's orders
curl -s localhost:8089/customers/1/orders | jq -c '.[0:3]'
curl -s localhost:8089/customers/1/orders | jq length
Real output:
[{"order_id":2000,"total_cents":62000},{"order_id":4000,"total_cents":24000},{"order_id":6000,"total_cents":86000}]
10
4. Call an endpoint that leaves out the shard key
curl -s -w " [%{http_code}]\n" localhost:8089/orders/count
Real output:
ERROR: statement's fan-out (scatter) exceeds __neki.fanout (single) [409]
5. Write for one customer in a transaction
curl -s -X POST "localhost:8089/customers/1/orders?firstOrderId=990001&n=3"; echo
Real output:
added 3
6. Call a transaction that spans two shards
curl -s -w " [%{http_code}]\n" -X POST "localhost:8089/transfer?from=1&to=2&orderId=990101"
psql -At -c "SELECT count(*) FROM orders WHERE customer_id IN (1, 2) AND order_id IN (990101, 990102)"
Real output:
ERROR: __neki.tx_mode = 'single' forbids a transaction from touching more than one shard [409]
0
The insert for customer 2, on a different shard from customer 1, was rejected, and Spring rolled back the whole method, so neither row was written.
Verify the result
curl -s localhost:8089/customers/1/orders | jq length
curl -s -o /dev/null -w "%{http_code}\n" localhost:8089/orders/count
Expected: 13 (10 loaded orders plus 3 from step 5), then 409. Stop the service afterwards with kill %1 or pkill -f orders-0.0.1.jar.
How it works
The PostgreSQL JDBC driver sends the options URL parameter as the connection's startup options, and HikariCP opens every pooled connection with the same URL, so each one carries __neki.fanout=single and __neki.tx_mode=single from the start. With fanout=single, a statement the router would send to more than one shard fails. With tx_mode=single, a transaction that has used one shard cannot use another, and the failure surfaces as a DataAccessException inside the @Transactional method, which rolls the whole method back. The router is described on the Neki architecture page.
Common issues
Spaces and equals signs in the options parameter
This guide URL-encodes the options value in spring.datasource.url (-c%20__neki.fanout%3Dsingle), which is the form that was tested. If the router accepts unkeyed queries, check that the parameter survived: SHOW __neki.fanout on a pooled connection returns single when the option was applied and scatter, the default, when it was not.
Prepared statements
The JDBC driver switches to server-side prepared statements after a statement runs 5 times on a connection (prepareThreshold). Eight repeated calls to the keyed endpoint in testing all returned 200.
Pool size and the router
In testing, 400 client connections to the router opened at once, while SHOW max_connections through the router reported 30. Size maximum-pool-size for your application's concurrency rather than for that value.
Building with an older Java
With Java 8 on JAVA_HOME, mvn package failed at spring-boot-maven-plugin:4.1.2:repackage with Unable to load the mojo 'repackage'. Spring Boot 4.1 targets Java 17 or later. Check java -version and JAVA_HOME before building.
Next steps
- Delete the database with
pscale database delete ordersvc --force --org "$PLANETSCALE_ORG". - Read the Neki data topology guide for choosing the shard key and placing related tables in the same shard group.
verification:
status: verified
tested_at: "2026-10-28"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview), Spring Boot 4.1.2, HikariCP 7.0.2, PostgreSQL JDBC 42.7.14, Java 21.0.13"
command: "curl -s localhost:8089/customers/1/orders | jq length; curl -s -o /dev/null -w '%{http_code}' localhost:8089/orders/count"
expected_result: "13, then 409"