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

Reference

Keep updated_at Current on a Neki Distributed Table with a Trigger

Use standard Postgres DDL through the Neki router to add an updated_at column, create a BEFORE UPDATE trigger function, and attach the trigger to a table in a Neki shard group. The router sends the DDL to every shard, and the trigger fires on the shard that holds the row.

What you will build

A TypeScript script that adds a BEFORE UPDATE trigger to a Neki sharded table so that any UPDATE automatically refreshes the updated_at column on the shard that owns the row.

Connect to Neki router → verify ShardGroup in query plan → CREATE TABLE in shard group → CREATE OR REPLACE FUNCTION set_updated_at() → CREATE TRIGGER BEFORE UPDATE → INSERT → UPDATE → assert updated_at advanced only for the updated tenant.

AI prompt

Build a TypeScript example that connects to a Neki database, verifies the table is in a sharded shard group, creates a BEFORE UPDATE trigger to keep updated_at current, and proves the timestamp advances only for the updated row.

Prerequisites

  • Node.js 22 or later and npm
  • A Neki database branch with at least one shard group and a shard index defined on your shard key column
  • A PlanetScale Postgres role with privileges to create tables, functions, and triggers on the branch
  • pscale CLI 0.350.0 or later for managing the data topology
  • The pg npm package (example pins pg@8.23.1, tsx@4.23.15, typescript@7.0.2)

Background on the native-DDL path used here (CREATE TABLE / FUNCTION / TRIGGER forwarded to every shard) lives in the schema-changes guide and the platform preview limitations guide; shard placement via databases.<db>.schemas.<schema>.tables[<table>].shard_group is documented in the data-topology guide; the connection flow follows the Neki connecting guide; role creation via the API is covered by create_role.

Environment variables

  • DATABASE_URL (secret): Postgres connection string for the Neki branch, including the branch-routed username and password
  • NEKI_TABLE: Optional table name to create and test; defaults to events_test
  • NEKI_SHARD_GROUP: Optional expected shard group name; defaults to tenant_data

1. Place the table in a sharded shard group

Neki routes a table according to the data topology. Creating a table through the router lands it in the default unsharded shard group unless the topology lists it in a shard group. Fetch the current topology, add your table under databases.postgres.schemas.public.tables with the desired shard_group, and apply it. The table does not need to exist yet; Neki will route it to the shard group once it is created.

pscale branch data-topology get "$NEKI_DATABASE" "$NEKI_BRANCH" \
  --org "$PLANETSCALE_ORG" --format json | jq '.data_topology' > topology.json

# Edit topology.json to add:
#   "events_test": { "shard_group": "tenant_data" }
# under databases.postgres.schemas.public.tables, using your shard group name.

pscale branch data-topology update "$NEKI_DATABASE" "$NEKI_BRANCH" \
  --org "$PLANETSCALE_ORG" --format json < topology.json

2. Install dependencies

Install the Postgres driver and TypeScript runner in a fresh project.

mkdir -p neki-updated-at-trigger && cd neki-updated-at-trigger
npm init -y
npm install pg@8.23.1 tsx@4.23.15 typescript@7.0.2

3. Create the trigger script

The script connects to Neki, creates the distributed table, verifies the query plan routes to the expected shard group, creates a trigger function that sets NEW.updated_at to NOW(), attaches a BEFORE UPDATE trigger, inserts sample rows, updates one row, and asserts that only the updated row's timestamp advanced.

import { Client } from "pg";

const databaseUrl = process.env.DATABASE_URL;
if (!databaseUrl) {
  throw new Error("DATABASE_URL is required");
}

const tableName = process.env.NEKI_TABLE || "events_test";
const expectedShardGroup = process.env.NEKI_SHARD_GROUP || "tenant_data";
const pgConnectionString = databaseUrl.replace(/([?&])sslrootcert=system(&|$)/, "$1").replace(/[?&]$/, "");

async function main() {
  const client = new Client({
    connectionString: pgConnectionString,
    ssl: { rejectUnauthorized: true },
  });
  await client.connect();

  try {
    const versionResult = await client.query<{ version: string }>(
      "SELECT version();"
    );
    const version = versionResult.rows[0].version;
    console.log("Connected to:", version);
    if (!version.includes("Neki")) {
      throw new Error("This example expects a Neki database");
    }

    await client.query(`DROP TABLE IF EXISTS public.${tableName}`);
    await client.query(`
      CREATE TABLE public.${tableName} (
        event_id bigserial PRIMARY KEY,
        tenant_id bigint NOT NULL,
        event_time timestamp without time zone NOT NULL DEFAULT now(),
        units bigint NOT NULL,
        updated_at timestamp without time zone DEFAULT now()
      )
    `);
    console.log(`Created distributed table public.${tableName}`);

    const planResult = await client.query<{ "QUERY PLAN": string }>(
      `EXPLAIN (NEKI_PLAN, COSTS OFF, FORMAT TEXT) SELECT * FROM public.${tableName} WHERE tenant_id = $1`,
      [1]
    );
    console.log("Query plan for tenant_id lookup:");
    planResult.rows.forEach((row) => console.log(row["QUERY PLAN"]));

    const shardGroup = planResult.rows
      .map((r) => r["QUERY PLAN"])
      .join(" ")
      .match(/ShardGroup:\s*(\S+)/)?.[1];
    if (shardGroup !== expectedShardGroup) {
      throw new Error(
        `Table public.${tableName} is in shard group "${shardGroup}", expected "${expectedShardGroup}". Update the data topology before running this example.`
      );
    }
    console.log(`Confirmed table is in shard group: ${shardGroup}`);

    await client.query(`
      CREATE OR REPLACE FUNCTION public.set_updated_at()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql
    `);
    console.log("Created trigger function public.set_updated_at");

    await client.query(`
      CREATE TRIGGER trigger_set_updated_at
        BEFORE UPDATE ON public.${tableName}
        FOR EACH ROW
        EXECUTE FUNCTION public.set_updated_at()
    `);
    console.log(`Attached trigger to public.${tableName}`);

    const insertResult = await client.query<{ event_id: number; tenant_id: number; updated_at: Date }>(
      `INSERT INTO public.${tableName} (tenant_id, event_time, units)
       VALUES (1, NOW(), 100), (2, NOW(), 200), (3, NOW(), 300)
       RETURNING event_id, tenant_id, updated_at`
    );
    console.log("Inserted rows:", JSON.stringify(insertResult.rows, null, 2));

    await new Promise((resolve) => setTimeout(resolve, 50));

    const updateResult = await client.query<{ event_id: number; tenant_id: number; updated_at: Date }>(
      `UPDATE public.${tableName} SET units = units + 1 WHERE tenant_id = $1
       RETURNING event_id, tenant_id, updated_at`,
      [1]
    );
    console.log("Updated row:", JSON.stringify(updateResult.rows[0], null, 2));

    const selectResult = await client.query<{ event_id: number; tenant_id: number; units: number; updated_at: Date }>(
      `SELECT event_id, tenant_id, units, updated_at FROM public.${tableName} ORDER BY tenant_id`
    );
    console.log("Final rows:");
    selectResult.rows.forEach((row) => console.log(row));

    const inserted = insertResult.rows.find((r) => Number(r.tenant_id) === 1)!;
    const updated = updateResult.rows[0];
    if (updated.updated_at <= inserted.updated_at) {
      throw new Error("updated_at was not advanced by the trigger");
    }
    console.log("\nSuccess: updated_at advanced from", inserted.updated_at, "to", updated.updated_at);
  } finally {
    await client.query(`DROP TABLE IF EXISTS public.${tableName}`);
    await client.query(`DROP FUNCTION IF EXISTS public.set_updated_at()`);
    await client.end();
  }
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});

4. Run the verified workflow

Make sure DATABASE_URL is exported in your environment and run the script. The script creates the table, trigger, and test rows, then cleans them up.

export DATABASE_URL="$DATABASE_URL"
npx tsx updated_at_trigger.ts

Verify the result

Run against PostgreSQL 18.6 (Neki) on 2026-11-05.

export DATABASE_URL="$DATABASE_URL" && npx tsx updated_at_trigger.ts

Observed output:

Connected to: PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) (Neki)
Created distributed table public.events_test
Query plan for tenant_id lookup:
Route [AnyShard]
  Query: SELECT events_test.event_id, events_test.tenant_id, events_test.event_time, events_test.units, events_test.updated_at FROM public.events_test WHERE tenant_id = $1
  ShardGroup: tenant_data
Confirmed table is in shard group: tenant_data
Created trigger function public.set_updated_at
Attached trigger to public.events_test
Inserted rows: [
  {
    "event_id": "1",
    "tenant_id": "1",
    "updated_at": "2026-11-05T19:09:25.939Z"
  },
  {
    "event_id": "2",
    "tenant_id": "2",
    "updated_at": "2026-11-05T19:09:25.939Z"
  },
  {
    "event_id": "3",
    "tenant_id": "3",
    "updated_at": "2026-11-05T19:09:25.939Z"
  }
]
Updated row: {
  "event_id": "1",
  "tenant_id": "1",
  "updated_at": "2026-11-05T19:09:26.064Z"
}
Final rows:
{
  event_id: '1',
  tenant_id: '1',
  units: '101',
  updated_at: 2026-11-05T19:09:26.064Z
}
{
  event_id: '2',
  tenant_id: '2',
  units: '200',
  updated_at: 2026-11-05T19:09:25.939Z
}
{
  event_id: '3',
  tenant_id: '3',
  units: '300',
  updated_at: 2026-11-05T19:09:25.939Z
}

Success: updated_at advanced from 2026-11-05T19:09:25.939Z to 2026-11-05T19:09:26.064Z

Expected: The trigger advances updated_at only for the row that is updated, while rows for other tenant_ids keep their original timestamps.

How it works

Neki exposes a single Postgres endpoint. The script sends standard CREATE TABLE, CREATE FUNCTION, and CREATE TRIGGER statements through the pg driver. The Neki router forwards native DDL to every managed shard, so the table definition, trigger function, and trigger exist on each shard. When the application later updates a row, Neki routes the UPDATE to the shard that holds the row (determined by tenant_id and the xxhash shard index), and Postgres fires the BEFORE UPDATE trigger on that shard, setting NEW.updated_at to NOW().

What the run corrected

These are beliefs the documentation supported and the run disproved.

  • Assumed: Creating a table with a shard-key column is automatically sharded by Neki. Observed: A table created through the router lands in the default unsharded shard group until it is explicitly listed in a shard_group in the data topology. Evidence: EXPLAIN (NEKI_PLAN) for the newly created events_test showed ShardGroup: shlk5lenh5wr0n (the default group); after applying the topology with shard_group: tenant_data it showed ShardGroup: tenant_data.

  • Assumed: A point lookup on the shard key in a sharded table would produce Route [EqualUnique]. Observed: With a single physical shard the plan showed Route [AnyShard]; the ShardGroup field is the reliable indicator that the table is distributed. Evidence: The live EXPLAIN (NEKI_PLAN) output showed Route [AnyShard] and ShardGroup: tenant_data for tenant_id = $1.

  • Assumed: The existing usage_events table could be altered to add the trigger. Observed: The table was owned by a different role, so ALTER TABLE and CREATE TRIGGER failed with must be owner of table. Evidence: The error was returned by the shard Postgres after attempting ALTER TABLE public.usage_events; pg_tables showed tableowner pscale_api_wjmrf61ws32a.

Common issues

Script throws: Table public.events_test is in shard group "shlk5lenh5wr0n", expected "tenant_data"

Cause: The table was created through the router but is not listed in a sharded shard group in the data topology, so Neki routes it to the default unsharded group.

Fix: Add the table to the desired shard_group in the data topology with pscale branch data-topology update before running the script.

ERROR: must be owner of table when creating the trigger

Cause: The connected role does not own the table and does not have TRIGGER privilege on it.

Fix: Connect with a role that owns the table, or GRANT the owning role to your application role and set it as the current role.

updated_at does not change on UPDATE

Cause: The trigger is defined as AFTER UPDATE or the function does not assign NEW.updated_at and return NEW.

Fix: Use CREATE TRIGGER ... BEFORE UPDATE ... FOR EACH ROW and make the function assign NEW.updated_at = NOW(); RETURN NEW;.

A second router rejects the table or trigger right after creation

Cause: Native DDL commits on each shard but other routers cache the old schema view briefly.

Fix: Run the __neki.wait_for_ddl(schema_version, cluster_version) notice emitted by the DDL statement before issuing dependent queries through other routers.

Next steps

  • Replace the sample table with your production sharded table and add the updated_at column with ALTER TABLE ... ADD COLUMN instead of recreating it.
  • Run __neki.wait_for_ddl(schema_version, cluster_version) after the CREATE TRIGGER if other routers need to see the new trigger before they accept dependent queries.
  • Wrap the CREATE TRIGGER in a managed schema-change workflow with __neki.online_ddl_create for large tables where direct DDL would lock traffic.
  • Use set_config('neki.target', 'replica', false) and read the row back from a replica to verify the trigger's change is also visible on replicas.
verification:
  status: verified
  tested_at: "2026-11-05"
  product_version: "PostgreSQL 18.6 (Neki)"
  command: "run updated_at_trigger.ts against a Neki branch: create a tenant-sharded table with a BEFORE UPDATE trigger that sets updated_at = now(), insert rows for three tenants, update one row, and compare updated_at across tenants"
  expected_result: "the trigger advances updated_at on only the row that was updated; rows for the other tenant_ids keep their original timestamps"