0
# Result Transformation
1

2
Result processing including column selection, ordering, pagination, response formatting (CSV, GeoJSON), and query optimization tools.
3

4
## Capabilities
5

6
### Column Selection for Modifications
7

8
When using INSERT, UPDATE, UPSERT, or DELETE operations, by default the modified rows are not returned. Use select() to return specific columns from the modified data.
9

10
```typescript { .api }
11
/**
12
 * Perform a SELECT on the query result. By default, `.insert()`, `.update()`, `.upsert()`,
13
 * and `.delete()` do not return modified rows. By calling this method, modified rows are
14
 * returned in `data`.
15
 * @param columns - The columns to retrieve, separated by commas
16
 */
17
select<Query extends string = '*'>(columns?: Query): PostgrestTransformBuilder<...>;
18
```
19

20
**Usage Examples:**
21

22
```typescript
23
import { PostgrestClient } from "@supabase/postgrest-js";
24

25
const client = new PostgrestClient("https://api.example.com");
26

27
// Return inserted data
28
const { data: newUser } = await client
29
  .from("users")
30
  .insert({ name: "John Doe", email: "john@example.com" })
31
  .select("id, name, email");
32

33
// Return updated data with specific columns
34
const { data: updatedUser } = await client
35
  .from("users")
36
  .update({ name: "Jane Doe" })
37
  .eq("id", 123)
38
  .select("id, name, updated_at");
39

40
// Return all columns after upsert
41
const { data: upsertedUser } = await client
42
  .from("users")
43
  .upsert({ email: "jane@example.com", name: "Jane" })
44
  .select("*");
45

46
// Return deleted records
47
const { data: deletedPosts } = await client
48
  .from("posts")
49
  .delete()
50
  .eq("status", "archived")
51
  .select("id, title, deleted_at");
52
```
53

54
### Ordering and Sorting
55

56
Control the order of returned results with flexible sorting options.
57

58
```typescript { .api }
59
/**
60
 * Order the query result by `column`. You can call this method multiple times to order
61
 * by multiple columns. You can order referenced tables, but it only affects the ordering
62
 * of the parent table if you use `!inner` in the query.
63
 * @param column - The column to order by
64
 * @param options - Named parameters
65
 * @param options.ascending - Sort direction (default: true)
66
 * @param options.nullsFirst - Position of null values (default: false)
67
 * @param options.referencedTable - Reference table for ordering
68
 * @param options.foreignTable - Foreign table for ordering (deprecated)
69
 */
70
order(
71
  column: string,
72
  options?: {
73
    ascending?: boolean;
74
    nullsFirst?: boolean;
75
    referencedTable?: string;
76
    foreignTable?: string; // deprecated
77
  }
78
): this;
79
```
80

81
**Usage Examples:**
82

83
```typescript
84
// Basic ascending order
85
const { data: usersByName } = await client
86
  .from("users")
87
  .select("*")
88
  .order("name");
89

90
// Descending order
91
const { data: newestPosts } = await client
92
  .from("posts")
93
  .select("*")
94
  .order("created_at", { ascending: false });
95

96
// Multiple column ordering
97
const { data: sortedUsers } = await client
98
  .from("users")
99
  .select("*")
100
  .order("department")
101
  .order("name");
102

103
// Null handling
104
const { data: usersWithNulls } = await client
105
  .from("users")
106
  .select("*")
107
  .order("last_login", { ascending: false, nullsFirst: true });
108

109
// Order by referenced table
110
const { data: postsWithAuthors } = await client
111
  .from("posts")
112
  .select(`
113
    *,
114
    users (
115
      id,
116
      name
117
    )
118
  `)
119
  .order("created_at", { ascending: false })
120
  .order("name", { referencedTable: "users" });
121

122
// Complex ordering
123
const { data: complexSorted } = await client
124
  .from("products")
125
  .select("*")
126
  .order("category")
127
  .order("price", { ascending: false })
128
  .order("name");
129
```
130

131
### Pagination and Limiting
132

133
Control the number and range of returned results for pagination.
134

135
```typescript { .api }
136
/**
137
 * Limit the query result by `count`
138
 * @param count - The maximum number of rows to return
139
 * @param options - Named parameters
140
 * @param options.referencedTable - Reference table for limit
141
 * @param options.foreignTable - Foreign table for limit (deprecated)
142
 */
143
limit(
144
  count: number,
145
  options?: {
146
    referencedTable?: string;
147
    foreignTable?: string; // deprecated
148
  }
149
): this;
150

151
/**
152
 * Limit the query result by starting at an offset `from` and ending at the offset `to`.
153
 * Only records within this range are returned. This respects the query order and if there
154
 * is no order clause the range could behave unexpectedly. The `from` and `to` values are
155
 * 0-based and inclusive.
156
 * @param from - The starting index from which to limit the result
157
 * @param to - The last index to which to limit the result
158
 * @param options - Named parameters
159
 * @param options.referencedTable - Reference table for range
160
 * @param options.foreignTable - Foreign table for range (deprecated)
161
 */
162
range(
163
  from: number,
164
  to: number,
165
  options?: {
166
    referencedTable?: string;
167
    foreignTable?: string; // deprecated
168
  }
169
): this;
170
```
171

172
**Usage Examples:**
173

174
```typescript
175
// Simple limit
176
const { data: first10Users } = await client
177
  .from("users")
178
  .select("*")
179
  .limit(10);
180

181
// Pagination with range (page 2, 10 items per page)
182
const { data: page2Users } = await client
183
  .from("users")
184
  .select("*")
185
  .order("created_at")
186
  .range(10, 19); // Items 11-20 (0-based, inclusive)
187

188
// Pagination with limit and offset pattern
189
const page = 3;
190
const pageSize = 20;
191
const { data: page3Users } = await client
192
  .from("users")
193
  .select("*")
194
  .order("id")
195
  .range((page - 1) * pageSize, page * pageSize - 1);
196

197
// Limit on referenced table
198
const { data: usersWithRecentPosts } = await client
199
  .from("users")
200
  .select(`
201
    *,
202
    posts (
203
      id,
204
      title,
205
      created_at
206
    )
207
  `)
208
  .limit(5) // Limit users
209
  .limit(3, { referencedTable: "posts" }); // Limit posts per user
210

211
// Range on referenced table
212
const { data: usersWithPostRange } = await client
213
  .from("users")
214
  .select(`
215
    *,
216
    posts (
217
      id,
218
      title
219
    )
220
  `)
221
  .range(0, 9) // First 10 users
222
  .range(0, 4, { referencedTable: "posts" }); // First 5 posts per user
223
```
224

225
### Response Format Control
226

227
Transform the response format for different data consumption needs.
228

229
```typescript { .api }
230
/**
231
 * Return `data` as a single object instead of an array of objects. Query result must be
232
 * one row (e.g. using `.limit(1)`), otherwise this returns an error.
233
 */
234
single(): PostgrestBuilder<ClientOptions, ResultOne>;
235

236
/**
237
 * Return `data` as a single object instead of an array of objects. Query result must be
238
 * zero or one row (e.g. using `.limit(1)`), otherwise this returns an error.
239
 */
240
maybeSingle(): PostgrestBuilder<ClientOptions, ResultOne | null>;
241

242
/**
243
 * Return `data` as a string in CSV format
244
 */
245
csv(): PostgrestBuilder<ClientOptions, string>;
246

247
/**
248
 * Return `data` as an object in GeoJSON format
249
 */
250
geojson(): PostgrestBuilder<ClientOptions, Record<string, unknown>>;
251
```
252

253
**Usage Examples:**
254

255
```typescript
256
// Get single record (must return exactly 1 row)
257
const { data: user } = await client
258
  .from("users")
259
  .select("*")
260
  .eq("id", 123)
261
  .single(); // data is User object, not User[]
262

263
// Get single record or null (0 or 1 rows)
264
const { data: user } = await client
265
  .from("users")
266
  .select("*")
267
  .eq("email", "might-not-exist@example.com")
268
  .maybeSingle(); // data is User | null
269

270
// Get results as CSV
271
const { data: csvData } = await client
272
  .from("users")
273
  .select("name, email, created_at")
274
  .csv(); // data is string in CSV format
275

276
// Get geographic data as GeoJSON
277
const { data: geoData } = await client
278
  .from("locations")
279
  .select("*")
280
  .geojson(); // data is GeoJSON object
281

282
// Typical single record patterns
283
const { data: currentUser } = await client
284
  .from("users")
285
  .select("*")
286
  .eq("id", userId)
287
  .single();
288

289
const { data: latestPost } = await client
290
  .from("posts")
291
  .select("*")
292
  .order("created_at", { ascending: false })
293
  .limit(1)
294
  .single();
295
```
296

297
### Query Analysis and Debugging
298

299
Tools for analyzing and debugging query performance.
300

301
```typescript { .api }
302
/**
303
 * Return `data` as the EXPLAIN plan for the query. You need to enable the db_plan_enabled
304
 * setting before using this method.
305
 * @param options - Named parameters
306
 * @param options.analyze - Include execution statistics
307
 * @param options.verbose - Verbose output
308
 * @param options.settings - Include settings
309
 * @param options.buffers - Include buffer usage
310
 * @param options.wal - Include WAL usage
311
 * @param options.format - Output format
312
 */
313
explain(
314
  options?: {
315
    analyze?: boolean;
316
    verbose?: boolean;
317
    settings?: boolean;
318
    buffers?: boolean;
319
    wal?: boolean;
320
    format?: 'json' | 'text';
321
  }
322
): PostgrestBuilder<ClientOptions, Record<string, unknown>[] | string>;
323
```
324

325
**Usage Examples:**
326

327
```typescript
328
// Basic query plan
329
const { data: plan } = await client
330
  .from("users")
331
  .select("*")
332
  .eq("active", true)
333
  .explain();
334

335
// Detailed analysis with execution stats
336
const { data: detailedPlan } = await client
337
  .from("large_table")
338
  .select("*")
339
  .gt("created_at", "2023-01-01")
340
  .explain({
341
    analyze: true,
342
    verbose: true,
343
    buffers: true
344
  });
345

346
// JSON format for programmatic analysis
347
const { data: jsonPlan } = await client
348
  .from("complex_query")
349
  .select(`
350
    *,
351
    related_table (
352
      id,
353
      name
354
    )
355
  `)
356
  .explain({ format: 'json', analyze: true });
357

358
// Text format for human reading
359
const { data: textPlan } = await client
360
  .from("performance_test")
361
  .select("*")
362
  .in("category", ["important", "urgent"])
363
  .explain({ format: 'text', analyze: true });
364
```
365

366
### Transaction Control
367

368
Control transaction behavior for testing and development.
369

370
```typescript { .api }
371
/**
372
 * Rollback the query. `data` will still be returned, but the query is not committed.
373
 */
374
rollback(): this;
375
```
376

377
**Usage Examples:**
378

379
```typescript
380
// Test insert without committing
381
const { data: testUser } = await client
382
  .from("users")
383
  .insert({ name: "Test User", email: "test@example.com" })
384
  .select()
385
  .rollback(); // Data returned but not committed
386

387
// Test complex operation
388
const { data: results } = await client
389
  .from("orders")
390
  .update({ status: "processed" })
391
  .eq("payment_status", "completed")
392
  .select()
393
  .rollback(); // See what would be updated without committing
394
```
395

396
### Request Control
397

398
Control request behavior and cancellation.
399

400
```typescript { .api }
401
/**
402
 * Set the AbortSignal for the fetch request
403
 * @param signal - The AbortSignal to use for the fetch request
404
 */
405
abortSignal(signal: AbortSignal): this;
406
```
407

408
**Usage Examples:**
409

410
```typescript
411
// Request cancellation
412
const controller = new AbortController();
413

414
// Cancel request after 5 seconds
415
setTimeout(() => controller.abort(), 5000);
416

417
const { data, error } = await client
418
  .from("large_table")
419
  .select("*")
420
  .abortSignal(controller.signal);
421

422
// User-initiated cancellation
423
const searchController = new AbortController();
424

425
const searchPromise = client
426
  .from("documents")
427
  .select("*")
428
  .textSearch("content", "search terms")
429
  .abortSignal(searchController.signal);
430

431
// Cancel if user starts new search
432
function handleNewSearch() {
433
  searchController.abort();
434
  // Start new search...
435
}
436
```
437

438
### PostgREST 13+ Features
439

440
Advanced features available in PostgREST version 13 and later.
441

442
```typescript { .api }
443
/**
444
 * Set the maximum number of rows that can be affected by the query. Only available in
445
 * PostgREST v13+ and only works with PATCH and DELETE methods.
446
 * @param value - The maximum number of rows that can be affected
447
 */
448
maxAffected(value: number): this;
449
```
450

451
**Usage Examples:**
452

453
```typescript
454
// Prevent accidental mass updates
455
const { data, error } = await client
456
  .from("users")
457
  .update({ updated_at: new Date().toISOString() })
458
  .eq("active", true)
459
  .maxAffected(100) // Fail if more than 100 rows would be affected
460
  .select();
461

462
// Safe delete with limit
463
const { data, error } = await client
464
  .from("old_logs")
465
  .delete()
466
  .lt("created_at", "2022-01-01")
467
  .maxAffected(1000) // Prevent deleting more than 1000 rows
468
  .select("id");
469

470
// Batch processing with safety limit
471
const { data, error } = await client
472
  .from("pending_emails")
473
  .update({ status: "processing" })
474
  .eq("status", "queued")
475
  .maxAffected(50) // Process max 50 emails at once
476
  .select();
477
```
478

479
### Type Override Control
480

481
Override the type of returned data with flexible merge options.
482

483
```typescript { .api }
484
/**
485
 * Override the type of the returned `data` field in the response.
486
 * @typeParam NewResult - The new type to cast the response data to
487
 * @typeParam Options - Optional type configuration (defaults to { merge: true })
488
 * @typeParam Options.merge - When true, merges the new type with existing return type. When false, replaces the existing types entirely (defaults to true)
489
 */
490
overrideTypes<
491
  NewResult,
492
  Options extends { merge?: boolean } = { merge: true }
493
>(): PostgrestBuilder<...>;
494

495
/**
496
 * Override the type of the returned `data` (deprecated)
497
 * @deprecated Use overrideTypes<yourType, { merge: false }>() method at the end of your call chain instead
498
 */
499
returns<NewResult>(): PostgrestBuilder<...>;
500
```
501

502
**Usage Examples:**
503

504
```typescript
505
// Merge with existing types (default behavior)
506
interface CustomFields {
507
  computed_score: number;
508
  display_name: string;
509
}
510

511
const { data: enhancedUsers } = await client
512
  .from("users")
513
  .select("id, name, email") // Original fields: { id, name, email }
514
  .overrideTypes<CustomFields>(); 
515
// Result type: { id, name, email, computed_score, display_name }
516

517
// Replace existing types completely
518
interface SimpleUser {
519
  id: number;
520
  display_name: string;
521
}
522

523
const { data: simpleUsers } = await client
524
  .from("users")
525
  .select("*")
526
  .overrideTypes<SimpleUser, { merge: false }>(); 
527
// Result type: { id, display_name } only
528

529
// Override array result types
530
interface ProcessedData {
531
  processed_at: string;
532
  result: any;
533
}
534

535
const { data: processedResults } = await client
536
  .from("raw_data")
537
  .select("*")
538
  .overrideTypes<ProcessedData[]>(); // Explicit array type
539

540
// Override for single object results
541
const { data: processedUser } = await client
542
  .from("users")
543
  .select("*")
544
  .eq("id", 123)
545
  .single()
546
  .overrideTypes<CustomUser>(); // Single object override
547

548
// Type-safe merging with complex types
549
interface UserWithMetrics {
550
  total_orders: number;
551
  last_login: string;
552
  preferences: {
553
    theme: 'light' | 'dark';
554
    notifications: boolean;
555
  };
556
}
557

558
const { data: usersWithMetrics } = await client
559
  .from("users")
560
  .select("id, name, email") // Base fields preserved
561
  .overrideTypes<UserWithMetrics>(); // Additional fields merged
562
// Result: { id, name, email, total_orders, last_login, preferences }
563

564
// Error handling with type overrides
565
try {
566
  const { data } = await client
567
    .from("complex_view")
568
    .select("*")
569
    .overrideTypes<MyCustomType>()
570
    .throwOnError();
571
  // data is properly typed as MyCustomType[]
572
} catch (error) {
573
  console.error("Query failed:", error);
574
}
575
```
576

577
### Method Chaining
578

579
All transformation methods can be chained together for complex result processing:
580

581
```typescript
582
// Complex transformation pipeline
583
const { data: processedResults } = await client
584
  .from("analytics")
585
  .select(`
586
    id,
587
    event_name,
588
    user_id,
589
    created_at,
590
    metadata,
591
    users (
592
      id,
593
      name,
594
      segment
595
    )
596
  `)
597
  .gte("created_at", "2023-01-01")
598
  .eq("event_name", "purchase")
599
  .order("created_at", { ascending: false })
600
  .order("name", { referencedTable: "users" })
601
  .limit(100)
602
  .range(0, 49) // First 50 results
603
  .limit(5, { referencedTable: "users" }); // Limit user data per event
604
```