Lightweight client for Prisma Postgres
npm install @prisma/ppgModern, lightweight, minimal serverless client for Prisma PostgresĀ®.
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
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.
`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.sql.collect();`
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
---
The prismaPostgres() function returns a feature-rich client with SQL template literals, transactions, and batch operations.
`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.
#### 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.sql;
// 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:
`tsSELECT * FROM users WHERE email = ${"test@example.com"}
const users = await ppg.sql.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:
`tsDELETE FROM users WHERE id = ${userId}
const affected = await ppg.sql.exec;Deleted ${affected} user(s)
console.log();`
#### 4. Explicit Parameter Query
Use the query() method with explicit positional parameters:
`ts`
const users = await ppg.query
#### 5. Explicit Parameter Exec
Use the exec() method for write operations:
`tsInserted ${affected} row(s)
const affected = await ppg.exec("INSERT INTO users (name, email) VALUES ($1, $2)", "Alice", "alice@example.com");
console.log();`
#### 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.sql.collect();
// COMMIT is executed automatically
return users[0].name;
});
console.log(result); // "Alice"
`
Rollback on error:
`tsINSERT INTO users (name) VALUES ('Bob')
try {
await ppg.transaction(async (tx) => {
await tx.sql.exec;`
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
await tx.exec("UPDATE users SET name = $1 WHERE id = $2", "New Name", userId);
const after = await tx.query
});
`
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
.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
}
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 |
---
The client() function provides direct control over query execution and session management.
`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).
#### 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
`tsAffected ${affected} rows
const affected = await cl.exec("INSERT INTO users (name, email) VALUES ($1, $2)", "Alice", "alice@example.com");
console.log();`
#### 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
}
`
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();
`
#### 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);
});
`
#### 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.
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);
`
---
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
}
}
---
Creates a client configuration with default parsers and serializers.
Returns:
- connectionString: string - The connection URLparsers: 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!));
`
Configuration:
- connectionString: string - PostgreSQL connection URLparsers?: ValueParser
- - Custom type parsers (use defaultClientConfig for defaults)serializers?: ValueSerializer
- - Custom type serializers (use defaultClientConfig for defaults)
Returns:
- sql - SQL template literal tagsql.exec
- - SQL template literal for exec operationsquery
- - Execute query with explicit parametersexec(sql, ...params): Promise
- - Execute command with explicit parameterstransaction
- - Execute interactive transactionbatch(...statements)
- - Execute batch operationsbatch()
- - Start fluent batch builder
Configuration:
- connectionString: string - PostgreSQL connection URLparsers?: ValueParser
- - Custom type parsers (use defaultClientConfig for defaults)serializers?: ValueSerializer
- - Custom type serializers (use defaultClientConfig for defaults)
Returns:
- query(sql, ...params): Promise - Execute queryexec(sql, ...params): Promise
- - Execute commandnewSession(config?: SessionConfig): Promise
- - Create new session
`ts
interface CollectableIterator
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;
}
``
---
Apache-2.0