This guide connects SQLAlchemy 2.1 and psycopg 3 to a sharded Neki table and makes the router reject any query that leaves out the shard key. Two session settings, __neki.fanout and __neki.tx_mode, are passed as connection options, so every connection in the pool has them before SQLAlchemy opens a transaction.
What you will build
A db.py module with an ORM model for an orders table sharded by customer_id, a write engine that rejects scatter queries and multi-shard transactions, and a read engine in autocommit mode for keyed reads across many customers.
engine (fanout=single, tx_mode=single)
session.get(Order, (customer_id, order_id)) -> one shard
select ... where order_id = 42 -> rejected: scatter
one Session touching customers on two shards -> rejected, rolled back
read_engine (same settings, AUTOCOMMIT)
keyed reads for many customers on one connection -> one shard per statement
AI Prompt
Using SQLAlchemy 2.1.4 with psycopg 3.3.6, connect to a sharded Neki table through the router and make every query include the shard key.
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.
- Pass connect_args={"options": "-c __neki.fanout=single -c __neki.tx_mode=single"} to create_engine. Do not run SET in a Session: SQLAlchemy has already begun a transaction, and the router rejects the change.
- Map orders with a composite primary key (customer_id, order_id) so Session.get needs the shard key.
- A Session's transaction lasts until commit or rollback. With tx_mode single, keep each Session on one customer, or on customers that live on one shard.
- For keyed reads across many customers, use a second engine with isolation_level="AUTOCOMMIT" and the same options.
- Check that a rejected multi-customer commit wrote nothing.
- 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, and Python 3.10 or later:
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 and an
orderstable holding 20,000 rows, sharded bycustomer_id. Create them with these commands:
pscale database create shopdb --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create shopdb 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 shopdb 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 shopdb 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)"
The COPY prints COPY 20000. Every customer has 10 orders.
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 shopdb --force --org "$PLANETSCALE_ORG".
1. Find two customers on different shards
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
Real output:
customer 1: sh3
customer 2: sh2
2. Install the libraries and write the module
python3 -m venv .venv
.venv/bin/pip install -q "sqlalchemy==2.1.4" "psycopg[binary]==3.3.6"
cat > db.py <<'EOF'
import os
from sqlalchemy import BigInteger, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
URL = (
f"postgresql+psycopg://{os.environ['PGUSER']}:{os.environ['PGPASSWORD']}"
f"@{os.environ['PGHOST']}:5432/postgres?sslmode=require"
)
NEKI_OPTIONS = {"options": "-c __neki.fanout=single -c __neki.tx_mode=single"}
# Writes and single-customer reads. Each Session is one transaction on one shard.
engine = create_engine(URL, connect_args=NEKI_OPTIONS, pool_pre_ping=True)
# Keyed reads for many customers on one connection, one statement at a time.
read_engine = create_engine(URL, connect_args=NEKI_OPTIONS, isolation_level="AUTOCOMMIT", pool_pre_ping=True)
class Base(DeclarativeBase):
pass
class Order(Base):
__tablename__ = "orders"
customer_id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
order_id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
total_cents: Mapped[int] = mapped_column(BigInteger)
EOF
3. Read with the shard key
.venv/bin/python - <<'PY'
from sqlalchemy import func, select
from sqlalchemy.orm import Session
from db import Order, engine
with Session(engine) as s:
o = s.get(Order, (1, 2000))
print("get:", o.customer_id, o.order_id, o.total_cents)
with Session(engine) as s:
print("orders for customer 1:", s.scalar(select(func.count()).select_from(Order).where(Order.customer_id == 1)))
PY
Real output:
get: 1 2000 62000
orders for customer 1: 10
4. See a query without the shard key rejected
.venv/bin/python - <<'PY'
from sqlalchemy import select
from sqlalchemy.orm import Session
from db import Order, engine
with Session(engine) as s:
try:
s.scalars(select(Order).where(Order.order_id == 2000)).all()
except Exception as ex:
print(type(ex.orig).__name__, ex.orig.sqlstate, str(ex.orig).splitlines()[0])
PY
Real output:
DatabaseError NK312 statement's fan-out (scatter) exceeds __neki.fanout (single)
5. Write in one transaction per customer
.venv/bin/python - <<'PY'
from sqlalchemy.orm import Session
from db import Order, engine
with Session(engine) as s:
s.add_all([Order(customer_id=1, order_id=900001, total_cents=500),
Order(customer_id=1, order_id=900002, total_cents=700)])
s.commit()
print("committed two orders for customer 1")
PY
Real output:
committed two orders for customer 1
6. See a transaction across two shards rejected
Customers 1 and 2 are on different shards:
.venv/bin/python - <<'PY'
from sqlalchemy import func, select
from sqlalchemy.orm import Session
from db import Order, engine
with Session(engine) as s:
s.add_all([Order(customer_id=1, order_id=900003, total_cents=100),
Order(customer_id=2, order_id=900004, total_cents=100)])
try:
s.commit()
except Exception as ex:
print("commit:", str(ex.orig).splitlines()[0])
with Session(engine) as s:
s.get(Order, (1, 2000))
try:
s.get(Order, (2, 1679))
except Exception as ex:
print("second read in the same Session:", str(ex.orig).splitlines()[0])
with Session(engine) as s:
print("rows written by the rejected commit:",
s.scalar(select(func.count()).select_from(Order).where(Order.customer_id == 1, Order.order_id == 900003)))
PY
Real output:
commit: __neki.tx_mode = 'single' forbids a transaction from touching more than one shard
second read in the same Session: __neki.tx_mode = 'single' forbids a transaction from touching more than one shard
rows written by the rejected commit: 0
The rejected commit wrote nothing. Reads count too: once a Session has touched one shard, a read for a customer on another shard fails until the Session commits or rolls back.
7. Read many customers with the autocommit engine
.venv/bin/python - <<'PY'
from sqlalchemy import func, select
from db import Order, read_engine
with read_engine.connect() as c:
counts = [c.scalar(select(func.count()).select_from(Order).where(Order.customer_id == cid)) for cid in range(1, 13)]
print("orders for customers 1 to 12:", counts)
PY
Real output (customer 1 has the 2 orders from step 5):
orders for customers 1 to 12: [12, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10]
Each statement commits on its own, so each one may go to a different shard. The same loop on engine.connect() fails on the first customer that lives on a different shard, because SQLAlchemy keeps the connection in one transaction until you commit.
Verify the result
.venv/bin/python - <<'PY'
from sqlalchemy import func, select
from sqlalchemy.orm import Session
from db import Order, engine
with Session(engine) as s:
print("keyed:", s.scalar(select(func.count()).select_from(Order).where(Order.customer_id == 1)))
with Session(engine) as s:
try:
s.scalar(select(func.count()).select_from(Order))
print("unkeyed: allowed")
except Exception as ex:
print("unkeyed:", ex.orig.sqlstate)
PY
Expected: keyed: 12 (10 loaded orders plus the 2 from step 5), then unkeyed: NK312.
How it works
The router reads __neki.fanout and __neki.tx_mode from each connection's startup options. With fanout=single, a statement that the router would send to more than one shard fails with SQLSTATE NK312. With tx_mode=single, a transaction that has used one shard cannot use another. SQLAlchemy 2.x begins a transaction on the first statement in a Session or a non-autocommit connection and keeps it until commit or rollback, so the transaction, not the statement, is what has to stay on one shard. The router itself is described on the Neki architecture page.
Common issues
Setting the parameters with SET
SET __neki.fanout = 'single' inside a Session that has already run a statement fails with parameter "__neki.fanout" cannot be changed during a transaction. Pass the settings in connect_args as in db.py.
A loop over customers on one connection
On engine.connect() (not autocommit), a loop of keyed reads failed at customer 2 with __neki.tx_mode = 'single' forbids a transaction from touching more than one shard. Use read_engine, or commit between customers.
Prepared statements
psycopg 3 prepares a statement on the server after it runs 5 times on a connection (prepare_threshold 5). Twelve runs of the same keyed query on one connection worked through the router.
Unkeyed queries without the strict options
With a plain create_engine(URL), select(Order).where(Order.order_id == 2000) succeeds and is sent to every shard. Nothing warns you. The connection options are what turn it into an error.
Next steps
- Delete the database with
pscale database delete shopdb --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), SQLAlchemy 2.1.4, psycopg 3.3.6"
command: "keyed count for customer 1, then an unkeyed count, through engine with fanout=single and tx_mode=single"
expected_result: "keyed: 12, then unkeyed: NK312"