A TypeScript probe that verifies Neki's documented cross-shard transaction behavior: a default multi transaction can commit rows on two different shards, while __neki.tx_mode = 'single' rejects the same cross-shard attempt with error code NK313.
What you will build
A self-contained TypeScript probe that connects to a live Neki cluster, shards orders and inventory across two shards, and verifies Neki's documented cross-shard transaction semantics.
Connect to Neki -> discover a cross-shard order/product pair -> commit a default multi-shard transaction -> read each shard directly to confirm split -> retry with __neki.tx_mode = 'single' and catch NK313.
AI prompt
Build a TypeScript workflow that tests Neki's cross-shard transaction semantics. Create a two-shard Neki database, shard an orders table by order_id and an inventory table by product_id, then prove that the default mode allows the transaction to span shards and that `__neki.tx_mode = 'single'` prevents it.
Prerequisites
- Node.js 22 with npm
- pscale CLI installed and authenticated to your PlanetScale organization
- A PlanetScale organization with Neki access
- A Neki database with at least two shards in the same branch
- A Neki role with admin access and
NEKI_DATABASE_URLexported pscale branch shard listavailable to read your shard UIDs
The __neki.tx_mode modes and the NK313 error used below are described in the transactions reference; the topology primitives (shard_groups, shard_indexes, SET __neki.shard) come from the data-topology guide and the shard-targeting / fan-out guide; the end-to-end sharding walkthrough referenced in Next steps is the sharding quick-start.
Environment variables
NEKI_DATABASE_URL(secret): Neki connection string for an admin role
1. Add a second shard (if needed) and read the shard UIDs
Neki starts with one shard. Add another shard so the test can route rows to different servers. Save the two shard UIDs from the output for the topology file.
pscale branch shard create my-neki-db main \
--config-profile default --count 1 \
--org my-org
pscale branch shard list my-neki-db main --org my-org --format json
2. Apply the two-shard data topology
Replace <SHARD_1> and <SHARD_2> with the name values from the shard list. The topology keeps reference/catalog data on the authoritative shard and creates separate shard groups for orders (by order_id) and inventory (by product_id).
cat > data-topology.json <<'EOF'
{
"authoritative_shard_group": "meta",
"shard_indexes": {
"xxhash_order_id": { "type": "xxhash", "columns": ["order_id"] },
"xxhash_product_id": { "type": "xxhash", "columns": ["product_id"] }
},
"shard_groups": [
{ "uid": "meta", "key_ranges": [{ "shard_uid": "<SHARD_1>" }] },
{
"uid": "orders_group",
"default_shard_index": "xxhash_order_id",
"key_ranges": [
{ "shard_uid": "<SHARD_1>", "end": "80" },
{ "shard_uid": "<SHARD_2>", "start": "80" }
]
},
{
"uid": "inventory_group",
"default_shard_index": "xxhash_product_id",
"key_ranges": [
{ "shard_uid": "<SHARD_1>", "end": "80" },
{ "shard_uid": "<SHARD_2>", "start": "80" }
]
}
],
"databases": {
"postgres": {
"schemas": {
"public": {
"tables": {
"orders": { "shard_group": "orders_group" },
"inventory": { "shard_group": "inventory_group" }
}
}
}
}
},
"default_shard_group": "meta"
}
EOF
pscale branch data-topology update my-neki-db main \
--org my-org --format json < data-topology.json
3. Create the orders and inventory tables
Create the two tables on the router. The topology already decides which shard holds each row.
PGPASSWORD=... psql "$NEKI_DATABASE_URL" -c "
CREATE TABLE IF NOT EXISTS orders (
order_id bigint PRIMARY KEY,
amount numeric(10,2) NOT NULL
);
CREATE TABLE IF NOT EXISTS inventory (
product_id bigint PRIMARY KEY,
quantity integer NOT NULL
);" 2>/dev/null || npx tsx -e "
import { Client } from 'pg';
const c = new Client({ connectionString: process.env.NEKI_DATABASE_URL });
await c.connect();
await c.query(\`CREATE TABLE IF NOT EXISTS orders (order_id bigint PRIMARY KEY, amount numeric(10,2) NOT NULL)\`);
await c.query(\`CREATE TABLE IF NOT EXISTS inventory (product_id bigint PRIMARY KEY, quantity integer NOT NULL)\`);
await c.end();
"
4. Install the TypeScript dependencies
Install pg, tsx, and TypeScript in the project directory.
npm init -y
npm install pg @types/pg tsx typescript
5. Implement the test probe in testCrossShardTx.ts
The script discovers one order_id and product_id that land on different shards, commits a cross-shard transaction in the default mode, then shows that __neki.tx_mode = 'single' rejects the same pattern with error code NK313.
import { Client } from 'pg';
interface Topology {
shard_groups: Array<{
uid: string;
key_ranges: Array<{ shard_uid: string }>;
}>;
}
async function discoverCrossShardPair(client: Client) {
const topoRes = await client.query(`SELECT data_topology_json FROM __neki.get_data_topology()`);
const topology: Topology = JSON.parse(topoRes.rows[0].data_topology_json);
const group = topology.shard_groups.find((g) => g.uid === 'orders_group');
if (!group || group.key_ranges.length < 2) {
throw new Error('This example needs a shard group with at least two shards.');
}
const [shardA, shardB] = group.key_ranges.map((r) => r.shard_uid);
const ids = Array.from({ length: 100 }, (_, i) => i + 1);
await client.query(
`INSERT INTO inventory (product_id, quantity)
SELECT v, 10 FROM unnest($1::bigint[]) AS v
ON CONFLICT (product_id) DO NOTHING`,
[ids]
);
let idOnA: number | null = null;
let idOnB: number | null = null;
for (const [idx, shard] of [shardA, shardB].entries()) {
await client.query(`SET __neki.shard = '${shard}'`);
const res = await client.query('SELECT product_id FROM inventory ORDER BY product_id LIMIT 1');
await client.query('RESET __neki.shard');
if (res.rows.length === 0) {
throw new Error(`No rows on shard ${shard}; cannot determine a cross-shard pair.`);
}
const id = Number(res.rows[0].product_id);
if (idx === 0) idOnA = id; else idOnB = id;
}
return { shardA, shardB, orderId: idOnA!, productId: idOnB! };
}
async function readShard(client: Client, shardUid: string, table: string, id: number, idCol: string) {
await client.query(`SET __neki.shard = '${shardUid}'`);
const res = await client.query(`SELECT * FROM ${table} WHERE ${idCol} = $1`, [id]);
await client.query('RESET __neki.shard');
return res.rows;
}
async function testMultiMode(client: Client, shardA: string, shardB: string, orderId: number, productId: number) {
console.log('\n--- Default __neki.tx_mode = multi ---');
await client.query('BEGIN');
await client.query(`INSERT INTO orders (order_id, amount) VALUES ($1, 99.00)`, [orderId]);
await client.query(`UPDATE inventory SET quantity = quantity - 1 WHERE product_id = $1`, [productId]);
await client.query('COMMIT');
console.log('multi-shard transaction committed');
console.log(`orders on ${shardA}:`, JSON.stringify(await readShard(client, shardA, 'orders', orderId, 'order_id')));
console.log(`orders on ${shardB}:`, JSON.stringify(await readShard(client, shardB, 'orders', orderId, 'order_id')));
console.log(`inventory on ${shardA}:`, JSON.stringify(await readShard(client, shardA, 'inventory', productId, 'product_id')));
console.log(`inventory on ${shardB}:`, JSON.stringify(await readShard(client, shardB, 'inventory', productId, 'product_id')));
}
async function testSingleMode(client: Client, orderId: number, productId: number) {
console.log('\n--- __neki.tx_mode = single ---');
await client.query(`SET __neki.tx_mode = 'single'`);
await client.query('BEGIN');
await client.query(`INSERT INTO orders (order_id, amount) VALUES ($1, 99.00)`, [orderId]);
let err: any = null;
try {
await client.query(`UPDATE inventory SET quantity = quantity - 1 WHERE product_id = $1`, [productId]);
} catch (e) {
err = e;
}
try { await client.query('ROLLBACK'); } catch {}
console.log('cross-shard attempt rejected:', err?.message);
console.log('error code:', err?.code);
const orders = await client.query('SELECT * FROM orders');
console.log('orders after rollback:', JSON.stringify(orders.rows));
}
async function main() {
const url = process.env.NEKI_DATABASE_URL;
if (!url) throw new Error('NEKI_DATABASE_URL not set');
const client = new Client({ connectionString: url });
await client.connect();
const version = await client.query('SELECT version()');
console.log('server:', version.rows[0].version);
await client.query(`DELETE FROM orders`);
await client.query(`DELETE FROM inventory`);
const { shardA, shardB, orderId, productId } = await discoverCrossShardPair(client);
console.log(`using order_id=${orderId} (shard ${shardA}) and product_id=${productId} (shard ${shardB})`);
await testMultiMode(client, shardA, shardB, orderId, productId);
await client.query(`DELETE FROM orders`);
await testSingleMode(client, orderId, productId);
await client.end();
}
main().catch((e) => { console.error(e); process.exit(1); });
6. Run the verified probe
Execute the probe against the live Neki database. The output shows the cross-shard commit under the default mode and the NK313 rejection under single-shard mode.
npx tsx testCrossShardTx.ts
Verify the result
Run against PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) (Neki) on 2026-11-05.
cd /workspace/code-example && npx tsx testCrossShardTx.ts
Observed output:
server: PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) (Neki)
using order_id=2 (shard shq4ekm5m9u0d7) and product_id=1 (shard sh3rfguaap6iw5)
--- Default __neki.tx_mode = multi ---
multi-shard transaction committed
orders on shq4ekm5m9u0d7: [{"order_id":"2","amount":"99.00"}]
orders on sh3rfguaap6iw5: []
inventory on shq4ekm5m9u0d7: []
inventory on sh3rfguaap6iw5: [{"product_id":"1","quantity":9}]
--- __neki.tx_mode = single ---
cross-shard attempt rejected: __neki.tx_mode = 'single' forbids a transaction from touching more than one shard
error code: NK313
orders after rollback: []
Expected: A default-mode transaction commits order and inventory rows to two different shards, and setting __neki.tx_mode = 'single' rejects the cross-shard attempt with NK313.
How it works
Neki routers parse each SQL statement and use the data topology to decide which shard(s) must run it. The default transaction mode, multi, allows a single transaction to touch more than one shard, but Neki commits each shard independently rather than using a two-phase commit. The probe inserts an order on one shard and updates inventory on another, then uses SET __neki.shard to read each shard directly and confirm the split. Switching to __neki.tx_mode = 'single' before BEGIN tells Neki to reject any transaction that would cross a shard boundary, surfacing NK313 instead of risking a half-written checkout.
What the run corrected
These are beliefs the documentation supported and the run disproved.
Assumed: A failing statement inside a multi-shard transaction would leave the successful shard's writes behind. Observed: When a statement fails in the live run, the whole multi-shard transaction rolls back and no rows remain. Evidence: The testPartial run returned an empty orders table after the inventory UPDATE violated a CHECK constraint and the script issued ROLLBACK.
Assumed: DEFERRABLE CHECK constraints could be used to force a commit-stage failure on one shard and demonstrate a partial commit. Observed: Neki rejects DEFERRABLE CHECK constraints at parse time with 'CHECK constraints cannot be marked DEFERRABLE'. Evidence: The createTables attempt with a DEFERRABLE constraint failed with code 42601 and a Neki router lexer source file.
Assumed: The rejection of a cross-shard transaction in single mode would be a generic Postgres error. Observed: Neki returns a specific error code NK313 with the message '__neki.tx_mode = 'single' forbids a transaction from touching more than one shard'. Evidence: The live single-mode run caught err.code = 'NK313' and the exact message.
Common issues
pscale role create returns an error about Vitess databases
Cause: For Neki databases you must use pscale role create, not pscale password create.
Fix: Use pscale role create <db> <branch> <role-name> --inherited-roles postgres and export the returned database_url.
The data topology update is rejected for an unknown shard UID
Cause: The JSON used a placeholder or a shard name instead of the shard uid returned by pscale branch shard list.
Fix: Use the exact shard uid (the name field) from pscale branch shard list in the key_ranges entries.
Both test rows appear on the same shard
Cause: The chosen order_id and product_id hash to the same key range.
Fix: Use the script's discoverCrossShardPair helper, which reads each shard directly with SET __neki.shard to find a value on each side.
A single-shard transaction is rejected in single mode even though it looks local
Cause: The statement is missing an equality predicate on the shard key, so Neki cannot prove it stays on one shard.
Fix: Add an explicit shard-key equality, e.g. WHERE product_id = $1, or use the primary key.
DEFERRABLE CHECK constraints are rejected during CREATE TABLE
Cause: The Neki parser currently does not support DEFERRABLE CHECK constraints.
Fix: Use non-deferrable CHECK constraints, or model commit-time validation in application-level compensating logic.
Next steps
- Set __neki.tx_mode = 'single' at the start of checkout paths that must stay atomic, so Neki fails loudly instead of spanning shards
- Model cross-shard checkout steps as a saga or outbox pattern, using Neki's single-shard transactions and idempotent compensating steps
- Add an automated assertion in CI that any multi-shard checkout query throws NK313 when tx_mode is single
- Add a third shard and test the __neki.fanout levels for scatter vs. multi vs. single routing
verification:
status: verified
tested_at: "2026-11-05"
product_version: "PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) (Neki)"
command: "apply a two-shard topology for orders/inventory, discover a cross-shard order_id/product_id pair, commit a default multi-mode transaction that writes to both shards, then retry with __neki.tx_mode='single' and catch the rejection"
expected_result: "multi-mode transaction commits with orders landing on shardA (e.g. shq4ekm5m9u0d7) and inventory on shardB (e.g. sh3rfguaap6iw5); single-mode retry throws err.code='NK313' with message '__neki.tx_mode = \\'single\\' forbids a transaction from touching more than one shard'; orders table empty after rollback"