@prisma/ppg

Modern, lightweight, minimal serverless client for Prisma Postgres®.

Installation

Install this package using your package manager and registry of choice:

* npm: npm install @prisma/ppg
* pnpm: pnpm add @prisma/ppg (or pnpm add jsr:@prisma/ppg)
* Yarn: yarn add @prisma/ppg
* Bun: bun add @prisma/ppg
* Deno: deno add jsr:@prisma/ppg
* esm.sh CDN: https://esm.sh/@prisma/ppg

Prisma Postgres Direct TCP Connection URL

Important: This client requires a Prisma Postgres Direct TCP Connection URL. This URL format is specific to Prisma Postgres and has the following structure:

``
postgres://identifier:key@db.prisma.io:5432/postgres?sslmode=require
`

You can find this connection string in the API Keys section of your Prisma Postgres dashboard. Other standard PostgreSQL connection strings will not work with this client.

Quick Start

`ts
import { prismaPostgres, defaultClientConfig } from "@prisma/ppg";

const ppg = prismaPostgres(
defaultClientConfig(process.env.PRISMA_DIRECT_TCP_URL!)
);

// SQL template literals with automatic parameterization
const users = await ppg.sqlSELECT * FROM users WHERE id = ${userId}.collect();
`

Usage Guide

This library provides two APIs:

1. prismaPostgres() - High-level API with SQL template literals, transactions, and batch operations (recommended for most users)
2. client() - Low-level untyped API with explicit parameter passing and session management

---

High-Level API: prismaPostgres()

The prismaPostgres() function returns a feature-rich client with SQL template literals, transactions, and batch operations.

$3

`ts
import { prismaPostgres, defaultClientConfig } from "@prisma/ppg";

// Recommended: Use defaultClientConfig to include default parsers and serializers
const ppg = prismaPostgres(
defaultClientConfig("postgres://identifier:key@db.prisma.io:5432/postgres?sslmode=require")
);

// Or manually configure (no default parsers/serializers):
const ppgCustom = prismaPostgres({
connectionString: "postgres://identifier:key@db.prisma.io:5432/postgres?sslmode=require",
parsers: [/ your custom parsers /],
serializers: [/ your custom serializers /]
});
`

> Important: Use defaultClientConfig() to automatically include default parsers and serializers for common PostgreSQL types (Date, JSON, BigInt, etc.). Without it, you'll need to manually configure parsers and serializers.

$3

#### 1. SQL Template Literals - Async Iterator

Execute queries using tagged template literals. Values are automatically parameterized to prevent SQL injection.

`ts
type User = { id: string; name: string; email: string };

const users = ppg.sqlSELECT * FROM users;

// Stream results one by one
for await (const user of users) {
console.log(user.id, user.name, user.email);
}
`

#### 2. SQL Template Literals - Collect All Rows

Collect all results into an array:

`ts
const users = await ppg.sqlSELECT * FROM users WHERE email = ${"test@example.com"}.collect();
console.log(users[0].name);
`

#### 3. SQL Template Literals - Exec Mode

Use sql.exec for INSERT, UPDATE, DELETE operations that return affected row counts:

`ts
const affected = await ppg.sql.execDELETE FROM users WHERE id = ${userId};
console.log(Deleted ${affected} user(s));
`

#### 4. Explicit Parameter Query

Use the query() method with explicit positional parameters:

`ts
const users = await ppg.query("SELECT * FROM users WHERE email = $1", "test@example.com").collect();
`

#### 5. Explicit Parameter Exec

Use the exec() method for write operations:

`ts
const affected = await ppg.exec("INSERT INTO users (name, email) VALUES ($1, $2)", "Alice", "alice@example.com");
console.log(Inserted ${affected} row(s));
`

$3

#### Interactive Transactions

Execute multiple queries within an automatic transaction. The transaction commits on success or rolls back on error:

`ts
const result = await ppg.transaction(async (tx) => {
// BEGIN is executed automatically

await tx.sql.execINSERT INTO users (name) VALUES ('Alice');

const users = await tx.sqlSELECT * FROM users WHERE name = 'Alice'.collect();

// COMMIT is executed automatically
return users[0].name;
});

console.log(result); // "Alice"
`

Rollback on error:

`ts
try {
await ppg.transaction(async (tx) => {
await tx.sql.execINSERT INTO users (name) VALUES ('Bob');
throw new Error("Something went wrong");
// ROLLBACK is executed automatically
});
} catch (error) {
console.log("Transaction rolled back");
}
`

Use query() and exec() methods within transactions:

`ts
await ppg.transaction(async (tx) => {
const before = await tx.query("SELECT * FROM users WHERE id = $1", userId).collect();

await tx.exec("UPDATE users SET name = $1 WHERE id = $2", "New Name", userId);

const after = await tx.query("SELECT * FROM users WHERE id = $1", userId).collect();
});
`

$3

Execute multiple statements in a single round-trip within an automatic transaction.

#### Array Syntax

`ts
const [users, affected, counts] = await ppg.batch<[User[], number, { count: bigint }[]]>(
{ query: "SELECT * FROM users WHERE id < $1", parameters: [5] },
{ exec: "INSERT INTO users (name) VALUES ($1)", parameters: ["Charlie"] },
{ query: "SELECT COUNT(*)::int8 as count FROM users", parameters: [] }
);

console.log(users); // User[]
console.log(affected); // number
console.log(counts[0].count); // bigint
`

#### Fluent Builder API

`ts
const [users, affected, counts] = await ppg.batch()
.query("SELECT * FROM users WHERE id = $1", userId)
.exec("UPDATE users SET name = $1 WHERE id = $2", "Updated Name", userId)
.query<{ count: bigint }>("SELECT COUNT(*)::int8 as count FROM users")
.run();
`

Batch operations are atomic - they rollback automatically on error:

`ts
try {
await ppg.batch<[number, User[]]>(
{ exec: "INSERT INTO users (name) VALUES ($1)", parameters: ["Dave"] },
{ query: "SELECT * FROM invalid_table", parameters: [] } // Fails
);
} catch (error) {
// First insert is rolled back
}
`

$3

When using defaultClientConfig(), the client automatically handles PostgreSQL types:

`ts
import { prismaPostgres, defaultClientConfig } from "@prisma/ppg";

const ppg = prismaPostgres(defaultClientConfig(process.env.DATABASE_URL!));

// JSON/JSONB - automatic parsing
const rows = await ppg.sql<{ data: { key: string } }>
SELECT '{"key": "value"}'::jsonb as data
.collect();
console.log(rows[0].data.key); // "value" (already parsed)

// BigInt - parsed to JavaScript BigInt
const bigints = await ppg.sql<{ big: bigint }>
SELECT 9007199254740991::int8 as big
.collect();
console.log(typeof bigints[0].big); // "bigint"

// Date/Timestamp - parsed to Date objects
const dates = await ppg.sql<{ created: Date }>
SELECT NOW() as created
.collect();
console.log(dates[0].created instanceof Date); // true

// Date serialization - automatic conversion
const testDate = new Date("2024-01-15T10:30:00Z");
await ppg.sql.execINSERT INTO events (timestamp) VALUES (${testDate});

// Null handling
const rows2 = await ppg.sql<{ id: number | null; name: string | null }>
SELECT NULL::int as id, 'test'::text as name
.collect();
`

#### Default Type Mappings

With defaultClientConfig(), the following types are automatically parsed:

| PostgreSQL Type | JavaScript Type | OID |
|----------------|-----------------|-----|
| boolean | boolean | 16 |
| int2, int4 | number | 21, 23 |
| int8 | bigint | 20 |
| float4, float8 | number | 700, 701 |
| text, varchar | string | 25, 1043 |
| json, jsonb | object | 114, 3802 |
| date | Date | 1082 |
| time | string | 1083 |
| timestamp | Date | 1114 |
| timestamptz | Date | 1184 |

---

Low-Level API: client()

The client() function provides direct control over query execution and session management.

$3

`ts
import { client, defaultClientConfig } from "@prisma/ppg";

// Recommended: Use defaultClientConfig to include default parsers and serializers
const cl = client(
defaultClientConfig("postgres://identifier:key@db.prisma.io:5432/postgres?sslmode=require")
);

// Or manually configure:
const clCustom = client({
connectionString: "postgres://identifier:key@db.prisma.io:5432/postgres?sslmode=require",
parsers: [/ your custom parsers /],
serializers: [/ your custom serializers /]
});
`

> Important: Use defaultClientConfig() to get automatic type parsing (Date, JSON, BigInt, etc.) and serialization (Date, BigInt, Number).

$3

#### 1. Simple Query

`ts
const result = await cl.query("SELECT * FROM users WHERE id = $1", userId);

console.log(result.columns); // Column metadata
const rows = await result.rows.collect(); // Collect all rows
console.log(rows[0].values); // Access row values as array
`

#### 2. Exec for Write Operations

`ts
const affected = await cl.exec("INSERT INTO users (name, email) VALUES ($1, $2)", "Alice", "alice@example.com");
console.log(Affected ${affected} rows);
`

#### 3. Async Iteration

`ts
const result = await cl.query("SELECT * FROM users");

for await (const row of result.rows) {
console.log(row.values); // Process one row at a time
}
`

$3

Sessions provide stateful connections for transactions and sequential operations.

#### Creating and Using Sessions

`ts
const session = await cl.newSession();

// Execute queries in the session
const result = await session.query("SELECT 1");
const rows = await result.rows.collect();

// Close the session when done
session.close();
`

#### Using Declaration (Automatic Cleanup)

`ts
{
using session = await cl.newSession();

await session.query("SELECT 1");

// Session automatically closes when leaving scope
}
`

#### Manual Transactions with Sessions

`ts
const session = await cl.newSession();

await session.query("BEGIN");
try {
await session.query("INSERT INTO users (name) VALUES ($1)", "Alice");
const result = await session.query("SELECT * FROM users WHERE name = $1", "Alice");
await session.query("COMMIT");
} catch (error) {
await session.query("ROLLBACK");
throw error;
} finally {
session.close();
}
`

#### Concurrent Queries in Session

Sessions can handle concurrent queries via WebSocket multiplexing:

`ts
const session = await cl.newSession();

const [res1, res2, res3] = await Promise.all([
session.query("SELECT 1"),
session.query("SELECT 2"),
session.query("SELECT 3")
]);

const rows1 = await res1.rows.collect();
const rows2 = await res2.rows.collect();
const rows3 = await res3.rows.collect();

session.close();
`

$3

#### Column Metadata

`ts
const result = await cl.query("SELECT id, name FROM users");

result.columns.forEach(col => {
console.log(col.name); // Column name
console.log(col.oid); // PostgreSQL type OID
});
`

#### Row Values

`ts
const result = await cl.query("SELECT id, name FROM users");
const rows = await result.rows.collect();

rows.forEach(row => {
const [id, name] = row.values;
console.log(id, name);
});
`

$3

#### Custom Parsers

You can override or add parsers on top of the defaults:

`ts
import { client, defaultClientConfig } from "@prisma/ppg";
import type { ValueParser } from "@prisma/ppg";

const uuidParser: ValueParser = {
oid: 2950, // UUID type OID
parse: (value: string | null) => value ? value.toUpperCase() : null
};

const config = defaultClientConfig("postgres://identifier:key@db.prisma.io:5432/postgres?sslmode=require");
const cl = client({
...config,
parsers: [...config.parsers, uuidParser], // Add to defaults
});

// Or replace defaults entirely:
const clCustom = client({
connectionString: "postgres://identifier:key@db.prisma.io:5432/postgres?sslmode=require",
parsers: [uuidParser], // Only your custom parsers
});
`

#### Custom Serializers

Add custom serializers on top of defaults:

`ts
import { client, defaultClientConfig } from "@prisma/ppg";
import type { ValueSerializer } from "@prisma/ppg";

class Point { constructor(public x: number, public y: number) {} }

const pointSerializer: ValueSerializer = {
supports: (value: unknown): value is Point => value instanceof Point,
serialize: (value: Point) => (${value.x},${value.y})
};

const config = defaultClientConfig("postgres://identifier:key@db.prisma.io:5432/postgres?sslmode=require");
const cl = client({
...config,
serializers: [pointSerializer, ...config.serializers], // Your serializer first
});

await cl.query("INSERT INTO locations (point) VALUES ($1)", new Point(10, 20));
`

> Note: Custom serializers are checked in order. Put your custom serializers before defaults so they take precedence.

$3

Send binary data efficiently:

`ts
import { byteArrayParameter, boundedByteStreamParameter, BINARY, TEXT } from "@prisma/ppg";

// From Uint8Array
const bytes = new Uint8Array([1, 2, 3, 4]);
const param = byteArrayParameter(bytes, BINARY);
await cl.query("INSERT INTO files (data) VALUES ($1)", param);

// From ReadableStream
const stream = getReadableStream();
const streamParam = boundedByteStreamParameter(stream, BINARY, 1024);
await cl.query("INSERT INTO files (data) VALUES ($1)", streamParam);
`

---

Error Handling

The library provides a structured error hierarchy:

`ts
import {
GenericError, // Base error class
ValidationError, // Invalid input or configuration
HttpResponseError, // HTTP transport errors
WebSocketError, // WebSocket transport errors
DatabaseError // PostgreSQL errors
} from "@prisma/ppg";

try {
await ppg.sqlSELECT * FROM invalid_table.collect();
} catch (error) {
if (error instanceof DatabaseError) {
console.log(error.code); // PostgreSQL error code (e.g., "42P01")
console.log(error.details); // Additional error details
}
}
`

---

API Reference

$3

Creates a client configuration with default parsers and serializers.

Returns:
- connectionString: string - The connection URL
- parsers: ValueParser[] - Default type parsers (Date, JSON, BigInt, etc.)
- serializers: ValueSerializer[] - Default type serializers (Date, BigInt, Number)

`ts
import { prismaPostgres, defaultClientConfig } from "@prisma/ppg";

const ppg = prismaPostgres(defaultClientConfig(process.env.DATABASE_URL!));
`

$3

Configuration:
- connectionString: string - PostgreSQL connection URL
- parsers?: ValueParser[] - Custom type parsers (use defaultClientConfig for defaults)
- serializers?: ValueSerializer[] - Custom type serializers (use defaultClientConfig for defaults)

Returns:
- sql - SQL template literal tag
- sql.exec - SQL template literal for exec operations
- query(sql, ...params): CollectableIterator - Execute query with explicit parameters
- exec(sql, ...params): Promise - Execute command with explicit parameters
- transaction(callback): Promise - Execute interactive transaction
- batch(...statements) - Execute batch operations
- batch() - Start fluent batch builder

$3

Configuration:
- connectionString: string - PostgreSQL connection URL
- parsers?: ValueParser[] - Custom type parsers (use defaultClientConfig for defaults)
- serializers?: ValueSerializer[] - Custom type serializers (use defaultClientConfig for defaults)

Returns:
- query(sql, ...params): Promise - Execute query
- exec(sql, ...params): Promise - Execute command
- newSession(config?: SessionConfig): Promise - Create new session

$3

`ts
interface CollectableIterator extends AsyncIterableIterator {
collect(): Promise;
}

interface Resultset {
columns: Column[];
rows: CollectableIterator;
}

interface Row {
values: unknown[];
}

interface Column {
name: string;
oid: number;
}

interface Session extends Statements, Disposable {
readonly active: boolean;
close(): void;
}
``

---

License

Apache-2.0