Join us Nov 24 to watch Insights Agent turn slow queries into PRs. RSVP
Navigation

Reference

Use SQLAlchemy with Neki and Keep the Shard Key in Every Query

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.

  • pscale 0.344.0 with a service token that can create databases, psql 18.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 orders table holding 20,000 rows, sharded by customer_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-NEKI lists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish with pscale 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"