@idempotix/postgres

PostgreSQL storage adapter for Idempotix.

Installation

npm install @idempotix/core @idempotix/postgres pg

Quick Start

import { postgres } from '@idempotix/postgres';
import { express as idempotent } from '@idempotix/express';

// From environment variable (IDEMPOTIX_POSTGRES_URL)
app.post('/orders', idempotent({ storage: postgres() }), handler);

Why PostgreSQL?

If your app already uses PostgreSQL, you don't need additional infrastructure:

• ✅ No Redis/Upstash to manage
• ✅ Uses your existing database
• ✅ ACID transactions for atomic locking
• ✅ Works with any PostgreSQL-compatible DB

Configuration

import { postgres } from '@idempotix/postgres';

// From environment variable
const storage = postgres();

// From URL
const storage = postgres('postgres://user:pass@localhost:5432/mydb');

// With options
const storage = postgres({
  url: 'postgres://localhost:5432/mydb',
  tableName: 'my_idempotency_keys', // default: 'idempotix_keys'
  schema: 'app', // default: 'public'
  autoCreateTable: true, // default: true
});

// With existing pg Pool
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const storage = postgres({ pool });

Environment Variables

| Variable | Description | | ------------------------ | ------------------------- | | IDEMPOTIX_POSTGRES_URL | PostgreSQL connection URL |

Table Schema

The adapter auto-creates this table (disable with autoCreateTable: false):

CREATE TABLE IF NOT EXISTS idempotix_keys (
  key TEXT PRIMARY KEY,
  status TEXT NOT NULL CHECK (status IN ('in_progress', 'completed')),
  hash TEXT,
  data JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX idempotix_keys_expires_at_idx ON idempotix_keys (expires_at);

Cleanup

Unlike Redis, PostgreSQL doesn't auto-expire rows. Call cleanup() periodically:

import { postgres } from '@idempotix/postgres';

const storage = postgres();

// In a cron job or scheduled task
const deleted = await storage.cleanup();
console.log(`Cleaned up ${deleted} expired entries`);

Or set up a PostgreSQL cron extension (pg_cron):

SELECT cron.schedule('cleanup-idempotix', '0 * * * *',
  $$DELETE FROM idempotix_keys WHERE expires_at < NOW()$$
);

Cloud Providers

Supabase

const storage = postgres(process.env.SUPABASE_DB_URL);

Neon

const storage = postgres(process.env.DATABASE_URL);

AWS RDS

const storage = postgres('postgres://user:[email protected]:5432/mydb');

Vercel Postgres

import { postgres } from '@idempotix/postgres';

const storage = postgres(process.env.POSTGRES_URL);

Usage with Express

import { express as idempotent, configure } from '@idempotix/express';
import { postgres } from '@idempotix/postgres';

const idempotent = configure({
  storage: postgres(),
  ttl: '1h',
});

app.post('/orders', idempotent(), orderHandler);
app.post('/payments', idempotent({ required: true }), paymentHandler);

Usage with Next.js

import { next } from '@idempotix/next';
import { postgres } from '@idempotix/postgres';

export const POST = next({ storage: postgres() })(handler);

Performance Considerations

PostgreSQL is excellent for idempotency but has different characteristics than Redis:

| Aspect | PostgreSQL | Redis | | -------------- | ------------------- | --------------- | | Latency | ~1-5ms | ~0.1-1ms | | Throughput | Good | Excellent | | Persistence | Built-in | Optional | | Infrastructure | Likely already have | May need to add |

For most applications, PostgreSQL is fast enough. Use Redis if you need sub-millisecond latency at very high throughput.

Connection Pooling

The adapter uses a connection pool. For serverless environments, consider using a pooler:

// With PgBouncer or Supabase pooler
const storage = postgres('postgres://user:[email protected]:6543/mydb?pgbouncer=true');

License

MIT