1# Patterns
2 
3See [README.md](./README.md), [configuration.md](./configuration.md), [api.md](./api.md).
4 
5## High-Traffic Read-Heavy
6 
7```typescript
8const sql = postgres(env.HYPERDRIVE.connectionString, {max: 5, prepare: true});
9 
10// Cacheable: popular content
11const posts = await sql`SELECT * FROM posts WHERE published = true ORDER BY views DESC LIMIT 20`;
12 
13// Cacheable: user profiles
14const [user] = await sql`SELECT id, username, bio FROM users WHERE id = ${userId}`;
15```
16 
17**Benefits:** Trending/profiles cached (60s), connection pooling handles spikes.
18 
19## Mixed Read/Write
20 
21```typescript
22interface Env {
23  HYPERDRIVE_CACHED: Hyperdrive;    // max_age=120
24  HYPERDRIVE_REALTIME: Hyperdrive;  // caching disabled
25}
26 
27// Reads: cached
28if (req.method === "GET") {
29  const sql = postgres(env.HYPERDRIVE_CACHED.connectionString, {prepare: true});
30  const products = await sql`SELECT * FROM products WHERE category = ${cat}`;
31}
32 
33// Writes: no cache (immediate consistency)
34if (req.method === "POST") {
35  const sql = postgres(env.HYPERDRIVE_REALTIME.connectionString, {prepare: true});
36  await sql`INSERT INTO orders ${sql(data)}`;
37}
38```
39 
40## Analytics Dashboard
41 
42```typescript
43const client = new Client({connectionString: env.HYPERDRIVE.connectionString});
44await client.connect();
45 
46// Aggregate queries cached (use fixed timestamps for caching)
47const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString();
48const dailyStats = await client.query(`
49  SELECT DATE(created_at) as date, COUNT(*) as orders, SUM(amount) as revenue
50  FROM orders WHERE created_at >= $1
51  GROUP BY DATE(created_at) ORDER BY date DESC
52`, [thirtyDaysAgo]);
53 
54const sevenDaysAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString();
55const topProducts = await client.query(`
56  SELECT p.name, COUNT(oi.id) as count, SUM(oi.quantity * oi.price) as revenue
57  FROM order_items oi JOIN products p ON oi.product_id = p.id
58  WHERE oi.created_at >= $1
59  GROUP BY p.id, p.name ORDER BY revenue DESC LIMIT 10
60`, [sevenDaysAgo]);
61```
62 
63**Benefits:** Expensive aggregations cached (avoid NOW() for cacheability), dashboard instant, reduced DB load.
64 
65## Multi-Tenant
66 
67```typescript
68const tenantId = req.headers.get("X-Tenant-ID");
69const sql = postgres(env.HYPERDRIVE.connectionString, {prepare: true});
70 
71// Tenant-scoped queries cached separately
72const docs = await sql`
73  SELECT * FROM documents 
74  WHERE tenant_id = ${tenantId} AND deleted_at IS NULL
75  ORDER BY updated_at DESC LIMIT 50
76`;
77```
78 
79**Benefits:** Per-tenant caching, shared connection pool, protects DB from multi-tenant load.
80 
81## Geographically Distributed
82 
83```typescript
84// Worker runs at edge nearest user
85// Connection setup at edge (fast), pooling near DB (efficient)
86const sql = postgres(env.HYPERDRIVE.connectionString, {prepare: true});
87const [user] = await sql`SELECT * FROM users WHERE id = ${userId}`;
88 
89return Response.json({
90  user,
91  serverRegion: req.cf?.colo,  // Edge location
92});
93```
94 
95**Benefits:** Edge setup + DB pooling = global → single-region DB without replication.
96 
97## Multi-Query + Smart Placement
98 
99For Workers making **multiple queries** per request, enable Smart Placement to execute near DB:
100 
101```jsonc
102// wrangler.jsonc
103{
104  "placement": {"mode": "smart"},
105  "hyperdrive": [{"binding": "HYPERDRIVE", "id": "<ID>"}]
106}
107```
108 
109```typescript
110const sql = postgres(env.HYPERDRIVE.connectionString, {prepare: true});
111 
112// Multiple queries benefit from Smart Placement
113const [user] = await sql`SELECT * FROM users WHERE id = ${userId}`;
114const orders = await sql`SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC LIMIT 10`;
115const stats = await sql`SELECT COUNT(*) as total, SUM(amount) as spent FROM orders WHERE user_id = ${userId}`;
116 
117return Response.json({user, orders, stats});
118```
119 
120**Benefits:** Worker executes near DB → reduces latency for each query. Without Smart Placement, each query round-trips from edge.
121 
122## Connection Pooling
123 
124Operates in **transaction mode**: connection acquired per transaction, `RESET` on return.
125 
126**SET statements:**
127```typescript
128// ✅ Within transaction
129await client.query("BEGIN");
130await client.query("SET work_mem = '256MB'");
131await client.query("SELECT * FROM large_table");  // Uses SET
132await client.query("COMMIT");  // RESET after
133 
134// ✅ Single statement
135await client.query("SET work_mem = '256MB'; SELECT * FROM large_table");
136 
137// ❌ Across queries (may get different connection)
138await client.query("SET work_mem = '256MB'");
139await client.query("SELECT * FROM large_table");  // SET not applied
140```
141 
142**Best practices:**
143```typescript
144// ❌ Long transactions block pooling
145await client.query("BEGIN");
146await processThousands();  // Connection held entire time
147await client.query("COMMIT");
148 
149// ✅ Short transactions
150await client.query("BEGIN");
151await client.query("UPDATE users SET status = $1 WHERE id = $2", [status, id]);
152await client.query("COMMIT");
153 
154// ✅ SET LOCAL within transaction
155await client.query("BEGIN");
156await client.query("SET LOCAL work_mem = '256MB'");
157await client.query("SELECT * FROM large_table");
158await client.query("COMMIT");
159```
160 
161## Performance Tips
162 
163**Enable prepared statements (required for caching):**
164```typescript
165const sql = postgres(connectionString, {prepare: true});  // Default, enables caching
166```
167 
168**Optimize connection settings:**
169```typescript
170const sql = postgres(connectionString, {
171  max: 5,             // Stay under Workers' 6 connection limit
172  fetch_types: false, // Reduce latency if not using arrays
173  idle_timeout: 60,   // Match Worker lifetime
174});
175```
176 
177**Write cache-friendly queries:**
178```typescript
179// ✅ Cacheable (deterministic)
180await sql`SELECT * FROM products WHERE category = 'electronics' LIMIT 10`;
181 
182// ❌ Not cacheable (volatile NOW())
183await sql`SELECT * FROM logs WHERE created_at > NOW()`;
184 
185// ✅ Cacheable (parameterized timestamp)
186const ts = Date.now();
187await sql`SELECT * FROM logs WHERE created_at > ${ts}`;
188```
189 
190See [gotchas.md](./gotchas.md) for limits, troubleshooting.
191