@aizvi/auth-postgres

A PostgreSQL storage adapter for @aizvi/auth. Point it at a connection string, or a pg connection you already have, and your auth system has somewhere to store users and sessions.

It creates its own users and mobile_auth_sessions tables the first time it runs, and never touches anything else in your database.

Install

npm install @aizvi/auth @aizvi/auth-postgres
pnpm add @aizvi/auth @aizvi/auth-postgres
yarn add @aizvi/auth @aizvi/auth-postgres
bun add @aizvi/auth @aizvi/auth-postgres

Quick start

The easiest way to use this package: give it a connection string, and it opens a connection pool for you.

import { createAuthRouter } from '@aizvi/auth';
import { postgresAdapter } from '@aizvi/auth-postgres';

app.use(
  '/auth',
  createAuthRouter({
    adapter: await postgresAdapter({ connectionString: process.env.DATABASE_URL! }),
    mailer: myEmailSender,
    jwtSecret: process.env.JWT_SECRET!,
  })
);

postgresAdapter() is async, unlike @aizvi/auth-sqlite's sqliteAdapter(). Opening a pool and creating the tables both need a round trip to the database, so there's no synchronous "open and go" for Postgres the way there is for a local SQLite file.

Which driver does this use?

postgresAdapter() opens its connection pool with pg (node-postgres), the standard Postgres driver for Node.js. It also works unchanged under Bun, since pg is pure JavaScript.

Already have a connection? Use that instead

If your app already has its own pg.Pool, pg.Client, or a @electric-sql/pglite instance, you don't need postgresAdapter() to open a second pool. Hand your existing connection straight to createPostgresAuthAdapter() instead:

import { Pool } from 'pg';
import { createAuthRouter } from '@aizvi/auth';
import { createPostgresAuthAdapter } from '@aizvi/auth-postgres';

const pool = new Pool({ connectionString: process.env.DATABASE_URL }); // the pool your app already uses

app.use(
  '/auth',
  createAuthRouter({
    adapter: await createPostgresAuthAdapter(pool),
    mailer: myEmailSender,
    jwtSecret: process.env.JWT_SECRET!,
  })
);

This works with any client that has a query(text, params) method returning { rows }, which covers pg.Pool, pg.Client, and @electric-sql/pglite. That way you only ever have one open pool to your database, shared between your auth tables and everything else your app stores.

If you're adding auth to an app that already has its own users table with the same columns this package expects (see Schema below), createPostgresAuthAdapter() simply uses it as is. It only creates the tables if they don't already exist.

Note that createPostgresAuthAdapter() doesn't add a .close() method the way @aizvi/auth-sqlite's adapter does. A connection you already opened is yours to close however your own driver requires (pool.end() for pg, db.close() for pglite); the adapter never closes it for you. Only the pool postgresAdapter() opens itself comes with .close(), since in that case this package owns the pool's lifecycle.

More examples

Sharing one pool between auth and the rest of your app:

import { Pool } from 'pg';
import { createPostgresAuthAdapter } from '@aizvi/auth-postgres';

// Your app's single, shared pool, used everywhere.
export const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// Your own tables, created however you already do it.
await pool.query('CREATE TABLE IF NOT EXISTS posts (id TEXT PRIMARY KEY, title TEXT NOT NULL)');

// The auth adapter reuses the exact same pool.
export const authAdapter = await createPostgresAuthAdapter(pool);

Running the migration yourself, ahead of time:

import { Pool } from 'pg';
import { migrate, createPostgresAuthAdapter } from '@aizvi/auth-postgres';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
await migrate(pool); // creates users / mobile_auth_sessions if they don't exist yet

// ...later, once you're ready to build the router:
const adapter = await createPostgresAuthAdapter(pool);

Using this alongside Prisma. Prisma's client doesn't expose a query(text, params) method, so it can't be passed to createPostgresAuthAdapter() directly. Since this package manages its own users and mobile_auth_sessions tables independently of your Prisma schema anyway, open a small separate pg.Pool with the same connection string instead:

import { PrismaClient } from '@prisma/client';
import { Pool } from 'pg';
import { createPostgresAuthAdapter } from '@aizvi/auth-postgres';

export const prisma = new PrismaClient(); // the rest of your app keeps using this

const authPool = new Pool({ connectionString: process.env.DATABASE_URL });
export const authAdapter = await createPostgresAuthAdapter(authPool);

Two connections to the same database is normal and much simpler than adapting Prisma's $queryRawUnsafe to this package's driver interface.

Testing against a real Postgres without a server, using @electric-sql/pglite (a real Postgres compiled to WASM, runs in-process):

import { PGlite } from '@electric-sql/pglite';
import { createPostgresAuthAdapter } from '@aizvi/auth-postgres';

const db = new PGlite(); // in-memory, no server, no Docker
const adapter = await createPostgresAuthAdapter(db);

Schema

On first use, this creates:

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  verification_code TEXT,
  verification_expires TEXT,
  reset_code TEXT,
  reset_expires TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS mobile_auth_sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id),
  refresh_token_hash TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  revoked_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

Only the SHA-256 hash of a refresh token is ever stored, never the raw token itself.

API

postgresAdapter(options)

| Option | Required | Description | | ------------------ | -------- | ------------------------------------------------------------------------------ | | connectionString | yes | A Postgres connection string, for example postgres://user:pass@host:5432/db. |

Any other field is passed straight through to pg's Pool constructor (max, ssl, idleTimeoutMillis, and so on).

Returns a promise for an adapter ready to pass to createAuthRouter. Also has a .close() method if you need to close the pool manually. Most apps never need to call this.

createPostgresAuthAdapter(db)

Takes an already open client (anything implementing query(text, params) returning { rows }; see src/driver.ts for the exact minimal interface) and returns a promise for an adapter with the same methods, minus .close().

migrate(db)

Runs the table creation step on its own, if you want to control exactly when it happens rather than letting postgresAdapter/ createPostgresAuthAdapter run it for you automatically.

Code of Conduct

See CODE_OF_CONDUCT.md.

License

MIT (see license.txt)