Build a TypeScript/Express API that writes to a Neki primary and reads from Neki replicas by setting __neki.target=REPLICA on the replica connection pool.
What you will build
A TypeScript Express API that sends writes to a Neki primary and read-only queries to Neki replicas.
npm run migrate creates the table -> npm run start opens two pg Pools -> GET /todos queries the replica pool with __neki.target=REPLICA -> POST /todos writes via the primary pool -> the next replica read reflects the new row.
AI prompt
Build a TypeScript web API that demonstrates routing read-only queries to Neki replicas and writes to the primary. Use two Postgres connection pools, confirm the replica session shows __neki.target=REPLICA, and verify the written row appears in replica reads.
Prerequisites
- Node.js 22+ and npm
- A Neki database with a ready branch (PlanetScale dashboard or API)
- Primary and migration Postgres connection strings from your Neki Connect page
- curl and jq for the verification script
The Neki connecting guide documents the two-pool pattern used here, and the read-only replicas reference explains the __neki.target=REPLICA session setting the replica pool relies on.
Environment variables
NEKI_PRIMARY_DATABASE_URL(secret): Connection string for the Neki primary router (writes)NEKI_REPLICA_DATABASE_URL(secret): Connection string for the Neki router used for reads; the app adds options=-c __neki.target=REPLICANEKI_MIGRATION_DATABASE_URL(secret): Connection string for a role that can create tables, e.g. inherits postgres and neki_viewer
1. Create the project and install dependencies
Initialize a Node.js project, install the Postgres driver, Express, dotenv, and TypeScript tooling.
mkdir neki-replica-example && cd neki-replica-example
npm init -y
npm install express pg dotenv
npm install --save-dev typescript ts-node @types/node @types/express @types/pg
2. Add TypeScript configuration
Create a tsconfig.json that compiles the src/ directory into dist/.
{
"compilerOptions": {
"target": "ES2022",
"module": "commonjs",
"lib": ["ES2022"],
"outDir": "./dist",
"rootDir": "./src",
"strict": true,
"esModuleInterop": true,
"skipLibCheck": true,
"forceConsistentCasingInFileNames": true,
"resolveJsonModule": true
},
"include": ["src/**/*"]
}
3. Configure environment variables
Create a .env file from the three Neki connection-string environment variables you have already exported. No credential values are written into the project files.
printenv | grep -E '^(NEKI_PRIMARY_DATABASE_URL|NEKI_REPLICA_DATABASE_URL|NEKI_MIGRATION_DATABASE_URL)=' > .env
4. Create the migration script
Use a migration role to create the todo_items table. Neki recommends keeping DDL credentials out of the application runtime.
import 'dotenv/config';
import { Client } from 'pg';
const migrationUrl = process.env.NEKI_MIGRATION_DATABASE_URL;
if (!migrationUrl) {
throw new Error('NEKI_MIGRATION_DATABASE_URL is required');
}
async function bootstrap(): Promise<void> {
const client = new Client({ connectionString: migrationUrl });
await client.connect();
await client.query(`
CREATE TABLE IF NOT EXISTS todo_items (
id SERIAL PRIMARY KEY,
task TEXT NOT NULL,
done BOOLEAN DEFAULT false
)
`);
console.log('Migration applied');
await client.end();
}
bootstrap().catch((err) => {
console.error(err);
process.exit(1);
});
5. Implement the read/write server
Create src/server.ts with two pg Pools: one for the primary and one for replicas. The replica pool sets options=-c __neki.target=REPLICA so Neki routes reads to replicas. GET /todos reads from replicas; POST /todos writes to the primary.
import 'dotenv/config';
import express, { Request, Response } from 'express';
import { Pool, QueryResult } from 'pg';
interface Todo {
id: number;
task: string;
done: boolean;
}
const primaryUrl = process.env.NEKI_PRIMARY_DATABASE_URL;
const replicaUrl = process.env.NEKI_REPLICA_DATABASE_URL;
if (!primaryUrl) {
throw new Error('NEKI_PRIMARY_DATABASE_URL is required');
}
if (!replicaUrl) {
throw new Error('NEKI_REPLICA_DATABASE_URL is required');
}
const primaryPool = new Pool({
connectionString: primaryUrl,
});
const replicaPool = new Pool({
connectionString: replicaUrl,
options: '-c __neki.target=REPLICA',
});
async function seedIfEmpty(): Promise<Todo | undefined> {
const { rowCount } = await primaryPool.query<Todo>(
'SELECT id FROM todo_items LIMIT 1'
);
if (rowCount === 0) {
const result = await primaryPool.query<Todo>(
'INSERT INTO todo_items (task, done) VALUES ($1, $2) RETURNING id, task, done',
['Verify Neki replica routing', true]
);
return result.rows[0];
}
return undefined;
}
const app = express();
app.use(express.json());
app.get('/todos', async (_req: Request, res: Response) => {
try {
const result: QueryResult<Todo> = await replicaPool.query(
'SELECT id, task, done FROM todo_items ORDER BY id DESC LIMIT 100'
);
const target = await replicaPool.query('SHOW __neki.target');
res.json({
source: 'replica',
nekiTarget: (target.rows[0] as Record<string, string>)['__neki.target'],
count: result.rowCount,
todos: result.rows,
});
} catch (err) {
console.error('Replica read failed:', err);
res.status(500).json({ error: 'Replica read failed' });
}
});
app.post('/todos', async (req: Request, res: Response) => {
const { task, done = false } = req.body;
if (!task || typeof task !== 'string') {
res.status(400).json({ error: 'task is required' });
return;
}
try {
const result = await primaryPool.query<Todo>(
'INSERT INTO todo_items (task, done) VALUES ($1, $2) RETURNING id, task, done',
[task, done]
);
res.status(201).json({ source: 'primary', todo: result.rows[0] });
} catch (err) {
console.error('Primary write failed:', err);
res.status(500).json({ error: 'Primary write failed' });
}
});
app.get('/', async (_req: Request, res: Response) => {
const primary = await primaryPool.query<{ db: string; usr: string }>(
'SELECT current_database() AS db, current_user AS usr'
);
const replica = await replicaPool.query('SHOW __neki.target');
res.json({
primary: primary.rows[0],
replica: {
__neki_target: (replica.rows[0] as Record<string, string>)['__neki.target'],
},
});
});
async function bootstrap(): Promise<void> {
const seeded = await seedIfEmpty();
if (seeded) {
console.log('Seeded primary with:', seeded);
}
const port = process.env.PORT ? parseInt(process.env.PORT, 10) : 3000;
app.listen(port, () => {
console.log(`Neki replica-routing API listening on http://localhost:${port}`);
});
}
bootstrap().catch((err) => {
console.error(err);
process.exit(1);
});
6. Add npm scripts and a verification wrapper
Add build, migrate, and start scripts to package.json. Then create verify.sh to build, migrate, start the server, and curl the endpoints.
{
"scripts": {
"build": "tsc",
"migrate": "node dist/migrate.js",
"start": "node dist/server.js",
"dev": "ts-node src/server.ts"
}
}
7. Run the verified workflow
Source the .env file and run the verification script. The output should show GET /todos served from replica with nekiTarget=REPLICA, and the row written to the primary should appear in the subsequent replica read.
source .env
bash verify.sh
Verify the result
Run against Neki public router / PostgreSQL on 2026-11-05.
source .env && bash verify.sh
Observed output:
> code-example@1.0.0 build
> tsc
> code-example@1.0.0 migrate
> node dist/migrate.js
Migration applied
> code-example@1.0.0 start
> node dist/server.js
Neki replica-routing API listening on http://localhost:3000
--- GET / ---
{
"primary": {
"db": "postgres",
"usr": "pscale_api_hpnry10pcmfr"
},
"replica": {
"__neki_target": "REPLICA"
}
}
--- GET /todos (replica) ---
{
"source": "replica",
"nekiTarget": "REPLICA",
"count": 4,
"todos": [...]
}
--- POST /todos (primary) ---
{
"source": "primary",
"todo": {
"id": 3,
"task": "Read from a Neki replica",
"done": false
}
}
--- GET /todos after write (replica) ---
{
"source": "replica",
"nekiTarget": "REPLICA",
"count": 5,
"todos": [...]
}
Expected: GET /todos returns source=replica and nekiTarget=REPLICA, POST /todos writes to the primary, and the new row appears in the next replica read.
How it works
Neki routers inspect a Postgres session parameter, __neki.target, to decide whether to route statements to primaries or replicas. Setting options=-c __neki.target=REPLICA when creating the replica connection pool applies that target for every query in that pool. The primary pool omits the option, so writes go to the primary. The example seeds a row through the primary, exposes a read endpoint that queries only the replica pool, and confirms the target with SHOW __neki.target. The same connection option is what you would set in an Entity Framework Core Npgsql connection string for a read-only DbContext in ASP.NET Core.
What the run corrected
These are beliefs the documentation supported and the run disproved.
Assumed: SHOW __neki.target returns a column named setting. Observed: The result row uses the key __neki.target itself. Evidence: The live query returned rows such as { '__neki.target': 'REPLICA' }, not { setting: 'REPLICA' }.
Assumed: A branch reported ready by the API is immediately able to serve all queries. Observed: The first few queries after ready returned NK205 / no healthy sidecars and query-buffer timeouts. Evidence: CREATE TABLE initially failed with 'no healthy sidecars available for shard ... SIDECAR_TYPE_PRIMARY' and later succeeded after retries.
Assumed: A role with pg_read_all_data and pg_write_all_data can create tables. Observed: CREATE TABLE failed with permission denied for schema public. Evidence: The same role could insert once the table existed, but could not execute CREATE TABLE until a role with postgres privileges created it.
Assumed: Fetching a role again would return its password. Observed: GET /roles returned password: null; the password is only present in the create response. Evidence: The create-role response included password, while subsequent GET /roles/{id} responses omitted it.
Common issues
Replica reads are empty immediately after a write
Cause: Replicas apply WAL asynchronously; a freshly written row can lag behind on the replica.
Fix: Poll the replica with a bounded backoff, or check __neki.replica_recency and fall back to the primary when freshness matters.
permission denied for schema public when the app starts
Cause: An application role with pg_read_all_data/pg_write_all_data cannot run DDL such as CREATE TABLE.
Fix: Create tables with a separate migration role that inherits postgres and neki_viewer, then grant the app role usage and DML privileges.
authentication failed when reusing a role after creation
Cause: The PlanetScale API returns the plaintext password only on role creation; GET /roles returns password: null.
Fix: Save the password from the create-role response, or reset the role password via the API.
no healthy sidecars available or query buffer timeout right after the branch shows ready
Cause: Neki routers and sidecars need a short time to become fully healthy after the API reports ready.
Fix: Retry connections and queries with a bounded backoff for the first minute after provisioning.
Next steps
- Route all read-only endpoints through replicaPool and keep all writes on primaryPool.
- Add a freshness check using __neki.replica_recency before running reports on replicas, falling back to the primary when replicas are too stale.
- Move the migration script into CI using a dedicated migration role with postgres and neki_viewer privileges, and scope the app role to pg_read_all_data and pg_write_all_data only.
- In ASP.NET Core with EF Core, register a second read-only DbContext whose Npgsql connection string includes options=-c __neki.target=REPLICA.
verification:
status: verified
tested_at: "2026-11-05"
product_version: "Neki public router / PostgreSQL"
command: "run the Express API against a live Neki branch with primary + replica pools, confirm GET /todos reports nekiTarget=REPLICA and POST /todos writes to the primary, then re-read from the replica"
expected_result: "GET / shows replica.__neki_target=REPLICA; GET /todos returns source=replica with nekiTarget=REPLICA; POST /todos returns source=primary with the new row; subsequent GET /todos on the replica returns the newly written row"