This guide keeps a player's wallet, inventory and purchase history on one shard of Neki, so a purchase is one ordinary single-shard Postgres transaction. Setting __neki.tx_mode=single makes the router reject any transaction that would touch a second shard.
What you will build
wallets (player_id, coins) --+
inventory (player_id, item_id, qty) --+-- shard group by_player, xxhash(player_id)
purchases (player_id, purchase_id, ...) --+
BEGIN; debit wallet; upsert inventory; insert purchase; COMMIT; -> one shard
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, build a game backend on Neki where a purchase changes a player's wallet and inventory in one transaction.
Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, and wait until every shard answers a direct SELECT 1 with SET __neki.shard. Use one database role for every step.
- Shard wallets, inventory and purchases by player_id with {"type":"xxhash","columns":["player_id"]}, in one shard group, with player_id as the first column of every primary key.
- Run the purchase as BEGIN, UPDATE wallets, INSERT inventory with ON CONFLICT DO UPDATE, INSERT purchases, COMMIT. Every statement filters or inserts on player_id, so the transaction stays on one shard.
- Keep a CHECK (coins >= 0) constraint on the wallet. A purchase the player cannot afford fails and the transaction rolls back every statement.
- Set __neki.tx_mode=single as a connection option for game servers. A transaction that touches two shards then fails with an error instead of running across shards.
- 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, andpython3:
export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
- A Neki database with three shards. Create it with these commands:
pscale database create gamebackend --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create gamebackend 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 gamebackend 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
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'")
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 gamebackend --force --org "$PLANETSCALE_ORG".
1. Create the tables and shard them by player
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE wallets (player_id bigint PRIMARY KEY, coins bigint NOT NULL CHECK (coins >= 0))" \
-c "CREATE TABLE inventory (player_id bigint NOT NULL, item_id int NOT NULL, qty int NOT NULL CHECK (qty >= 0), PRIMARY KEY (player_id, item_id))" \
-c "CREATE TABLE purchases (player_id bigint NOT NULL, purchase_id bigint GENERATED ALWAYS AS IDENTITY, item_id int NOT NULL, price bigint NOT NULL, PRIMARY KEY (player_id, purchase_id))" \
2>&1 | grep -v NOTICE
cat > data-topology.json <<JSON
{
"shard_indexes": { "player": { "type": "xxhash", "columns": ["player_id"] } },
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "by_player", "default_shard_index": "player",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"wallets": { "shard_group": "by_player" },
"inventory": { "shard_group": "by_player" },
"purchases": { "shard_group": "by_player" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update gamebackend main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
psql -q -c "INSERT INTO wallets SELECT p, 1000 FROM generate_series(1, 100) p"
psql -At -c "SELECT count(*), sum(coins) FROM wallets"
Real output:
applied
100|100000
2. Write the purchase as one transaction
cat > buy.sh <<'EOF'
#!/bin/bash
# buy.sh <player_id> <item_id> <price>
psql -q -At -v ON_ERROR_STOP=1 -v p="$1" -v i="$2" -v price="$3" <<'SQL'
BEGIN;
UPDATE wallets SET coins = coins - :price WHERE player_id = :p;
INSERT INTO inventory (player_id, item_id, qty) VALUES (:p, :i, 1)
ON CONFLICT (player_id, item_id) DO UPDATE SET qty = inventory.qty + 1;
INSERT INTO purchases (player_id, item_id, price) VALUES (:p, :i, :price);
COMMIT;
SQL
EOF
chmod +x buy.sh
./buy.sh 7 1 50
psql -At -c "SELECT coins FROM wallets WHERE player_id = 7" \
-c "SELECT item_id || ' x' || qty FROM inventory WHERE player_id = 7" \
-c "SELECT count(*) FROM purchases WHERE player_id = 7"
Real output:
950
1 x1
1
The wallet dropped from 1000 to 950, and the inventory row and the purchase row exist.
3. Confirm the three rows live on one shard
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT 1 FROM wallets w JOIN inventory i USING (player_id) WHERE w.player_id = 7" | grep -oE 'Route \[[A-Za-z]+\]'
for n in sh2 sh3; do
u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
echo "$n: $(psql -At -c "SET __neki.shard='$u';" -c "SELECT (SELECT count(*) FROM wallets WHERE player_id = 7) || ' wallet, ' || (SELECT count(*) FROM inventory WHERE player_id = 7) || ' inventory, ' || (SELECT count(*) FROM purchases WHERE player_id = 7) || ' purchases'" | tail -1)"
done
Real output:
Route [EqualUnique]
sh2: 0 wallet, 0 inventory, 0 purchases
sh3: 1 wallet, 1 inventory, 1 purchases
A join on player_id is one EqualUnique route, and querying each data shard directly shows every row for player 7 on the same shard.
4. Roll back an unaffordable purchase
./buy.sh 7 2 5000 2>&1 | grep -oE 'violates check constraint "[a-z_]+"'
psql -At -c "SELECT coins FROM wallets WHERE player_id = 7" \
-c "SELECT count(*) FROM inventory WHERE player_id = 7 AND item_id = 2" \
-c "SELECT count(*) FROM purchases WHERE player_id = 7"
Real output:
violates check constraint "wallets_coins_check"
950
0
1
The debit failed the coins >= 0 check, and the whole transaction rolled back: the wallet is unchanged, there is no row for item 2, and the purchase count is still 1.
5. Buy concurrently
psql -q -c "UPDATE wallets SET coins = 300 WHERE player_id = 9"
for i in $(seq 1 10); do
( ./buy.sh 9 1 50 >/dev/null 2>&1 && echo bought || echo declined ) &
done | sort | uniq -c
wait
psql -At -c "SELECT coins FROM wallets WHERE player_id = 9" \
-c "SELECT qty FROM inventory WHERE player_id = 9 AND item_id = 1" \
-c "SELECT count(*) FROM purchases WHERE player_id = 9"
Real output:
6 bought
4 declined
0
6
6
Ten simultaneous purchases of a 50-coin item against a 300-coin wallet produced six purchases and four declines. Concurrent transactions on the same wallet row run one after another on that shard, so the wallet never goes below zero.
6. Reject a transaction that spans two shards
for p in 1 2 7 8; 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 wallets WHERE player_id = $p" | tail -1)" = 1 ] && echo "player $p is on $n"
done
done
trade() {
PGOPTIONS='-c __neki.tx_mode=single' psql -q -At -v ON_ERROR_STOP=1 -v a="$1" -v b="$2" <<'SQL' 2>&1 | grep -oE "forbids a transaction from touching more than one shard" || echo "committed"
BEGIN;
UPDATE wallets SET coins = coins - 10 WHERE player_id = :a;
UPDATE wallets SET coins = coins + 10 WHERE player_id = :b;
COMMIT;
SQL
}
echo "players 7 and 1: $(trade 7 1)"
echo "players 7 and 8: $(trade 7 8)"
Real output:
player 1 is on sh3
player 2 is on sh2
player 7 is on sh3
player 8 is on sh2
players 7 and 1: committed
players 7 and 8: forbids a transaction from touching more than one shard
Players 7 and 1 share a shard, so their trade commits. Players 7 and 8 are on different shards, so the router rejects the transaction when its second statement reaches the other shard, and the first statement's change is rolled back.
Verify the result
psql -At -c "SELECT coins FROM wallets WHERE player_id = 9" \
-c "SELECT qty FROM inventory WHERE player_id = 9 AND item_id = 1" \
-c "SELECT count(*) FROM purchases WHERE player_id = 9" \
-c "SELECT coins FROM wallets WHERE player_id = 8" \
-c "SELECT coins FROM wallets WHERE player_id = 7"
Expected: 0, 6, 6 for player 9, 1000 for player 8, and 940 for player 7. Player 7 spent 50 coins on the purchase and sent 10 to player 1; the rejected trade with player 8 changed neither wallet.
How it works
xxhash on player_id places a player's wallet, inventory and purchase rows on one shard, and every statement in the purchase carries player_id, so the router sends the whole transaction to that shard. Postgres there provides the usual guarantees: the CHECK constraint, the row lock that serializes concurrent purchases, and rollback of every statement when one fails. __neki.tx_mode defaults to multi, which lets a transaction span shards; single turns a second shard into an error. See the Neki data topology guide for how shard groups place related tables together.
Common issues
A cross-shard transaction commits in the default mode
With the default __neki.tx_mode=multi, a transaction that updated players 7 and 8 committed in testing. The router did not reject it, and this guide did not test what happens if one shard fails during the commit. Set __neki.tx_mode=single for game servers so a trade between players on different shards fails visibly, and move value between players with a queue rather than one transaction.
Pass tx_mode as a connection option
__neki.tx_mode works as a startup option, as in PGOPTIONS='-c __neki.tx_mode=single'. In single mode a scatter query inside a transaction also fails, because it touches every shard.
Statements without player_id leave the shard
A statement that does not filter on player_id, such as SELECT count(*) FROM wallets, runs on every shard. Inside a single transaction it fails with the same error as the cross-shard trade, so keep reporting queries outside the purchase transaction.
Next steps
- Delete the database with
pscale database delete gamebackend --force --org "$PLANETSCALE_ORG". - Read the guide on completing a cross-shard transfer with an outbox table for moving value between players on different shards.
- Read the Neki data topology guide for placing related tables in one shard group.
verification:
status: verified
tested_at: "2026-10-29"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "wallet, inventory and purchase counts for player 9; wallets of players 8 and 7"
expected_result: "0, 6, 6 for player 9, 1000 for player 8, 940 for player 7"