0
# Query Operations
1
2
Core CRUD operations including SELECT, INSERT, UPDATE, DELETE, and UPSERT with full type safety and flexible data manipulation.
3
4
## Capabilities
5
6
### SELECT Operations
7
8
Perform SELECT queries to retrieve data from tables and views.
9
10
```typescript { .api }
11
/**
12
* Perform a SELECT query on the table or view
13
* @param columns - The columns to retrieve, separated by commas. Columns can be renamed when returned with `customName:columnName`
14
* @param options - Named parameters
15
* @param options.head - When set to `true`, `data` will not be returned. Useful if you only need the count
16
* @param options.count - Count algorithm to use to count rows in the table or view
17
*/
18
select<Query extends string = '*'>(
19
columns?: Query,
20
options?: {
21
head?: boolean;
22
count?: 'exact' | 'planned' | 'estimated';
23
}
24
): PostgrestFilterBuilder<...>;
25
```
26
27
**Usage Examples:**
28
29
```typescript
30
import { PostgrestClient } from "@supabase/postgrest-js";
31
32
const client = new PostgrestClient("https://api.example.com");
33
34
// Select all columns
35
const { data: allUsers } = await client
36
.from("users")
37
.select("*");
38
39
// Select specific columns
40
const { data: userNames } = await client
41
.from("users")
42
.select("id, name, email");
43
44
// Select with column aliasing
45
const { data: aliasedUsers } = await client
46
.from("users")
47
.select("user_id:id, full_name:name, contact_email:email");
48
49
// Select with count
50
const { data, count } = await client
51
.from("users")
52
.select("*", { count: 'exact' });
53
54
// Head request (count only, no data)
55
const { count: userCount } = await client
56
.from("users")
57
.select("*", { head: true, count: 'exact' });
58
59
// Select with relationships
60
const { data: postsWithUsers } = await client
61
.from("posts")
62
.select(`
63
id,
64
title,
65
content,
66
users (
67
id,
68
name,
69
70
)
71
`);
72
```
73
74
### INSERT Operations
75
76
Insert new records into tables.
77
78
```typescript { .api }
79
/**
80
* Perform an INSERT into the table or view. By default, inserted rows are not returned.
81
* To return it, chain the call with `.select()`
82
* @param values - The values to insert. Pass an object to insert a single row or an array to insert multiple rows
83
* @param options - Named parameters
84
* @param options.count - Count algorithm to use to count inserted rows
85
* @param options.defaultToNull - Make missing fields default to `null`. Otherwise, use the default value for the column. Only applies for bulk inserts
86
*/
87
insert(
88
values: Row | Row[],
89
options?: {
90
count?: 'exact' | 'planned' | 'estimated';
91
defaultToNull?: boolean;
92
}
93
): PostgrestFilterBuilder<...>;
94
```
95
96
**Usage Examples:**
97
98
```typescript
99
// Insert single record
100
const { error } = await client
101
.from("users")
102
.insert({ name: "John Doe", email: "john@example.com" });
103
104
// Insert single record and return it
105
const { data: newUser, error } = await client
106
.from("users")
107
.insert({ name: "John Doe", email: "john@example.com" })
108
.select()
109
.single();
110
111
// Insert multiple records
112
const { data: newUsers, error } = await client
113
.from("users")
114
.insert([
115
{ name: "Alice", email: "alice@example.com" },
116
{ name: "Bob", email: "bob@example.com" }
117
])
118
.select();
119
120
// Insert with count
121
const { data, count, error } = await client
122
.from("users")
123
.insert([
124
{ name: "Charlie", email: "charlie@example.com" },
125
{ name: "Diana", email: "diana@example.com" }
126
], { count: 'exact' })
127
.select();
128
129
// Insert with explicit null handling
130
const { data, error } = await client
131
.from("users")
132
.insert([
133
{ name: "Eve" }, // email will be null if defaultToNull: true
134
{ name: "Frank", email: "frank@example.com" }
135
], { defaultToNull: true })
136
.select();
137
```
138
139
### UPDATE Operations
140
141
Update existing records in tables.
142
143
```typescript { .api }
144
/**
145
* Perform an UPDATE on the table or view. By default, updated rows are not returned.
146
* To return it, chain the call with `.select()` after filters
147
* @param values - The values to update with
148
* @param options - Named parameters
149
* @param options.count - Count algorithm to use to count updated rows
150
*/
151
update(
152
values: Partial<Row>,
153
options?: {
154
count?: 'exact' | 'planned' | 'estimated';
155
}
156
): PostgrestFilterBuilder<...>;
157
```
158
159
**Usage Examples:**
160
161
```typescript
162
// Update with filter
163
const { error } = await client
164
.from("users")
165
.update({ name: "John Smith" })
166
.eq("id", 123);
167
168
// Update and return updated records
169
const { data: updatedUsers, error } = await client
170
.from("users")
171
.update({ last_login: new Date().toISOString() })
172
.eq("active", true)
173
.select();
174
175
// Update with multiple filters
176
const { data, error } = await client
177
.from("users")
178
.update({ status: "verified" })
179
.eq("email_verified", true)
180
.gt("created_at", "2023-01-01")
181
.select();
182
183
// Update with count
184
const { data, count, error } = await client
185
.from("users")
186
.update({ updated_at: new Date().toISOString() })
187
.lt("last_login", "2023-01-01")
188
.select("id, name", { count: 'exact' });
189
```
190
191
### UPSERT Operations
192
193
Perform INSERT with conflict resolution (INSERT ... ON CONFLICT).
194
195
```typescript { .api }
196
/**
197
* Perform an UPSERT on the table or view. Allows you to perform the equivalent of `.insert()`
198
* if a row with the corresponding `onConflict` columns doesn't exist, or if it does exist,
199
* perform an alternative action depending on `ignoreDuplicates`
200
* @param values - The values to upsert with. Pass an object to upsert a single row or an array to upsert multiple rows
201
* @param options - Named parameters
202
* @param options.onConflict - Comma-separated UNIQUE column(s) to specify how duplicate rows are determined
203
* @param options.ignoreDuplicates - If `true`, duplicate rows are ignored. If `false`, duplicate rows are merged with existing rows
204
* @param options.count - Count algorithm to use to count upserted rows
205
* @param options.defaultToNull - Make missing fields default to `null`. Only applies when inserting new rows and doing bulk upserts
206
*/
207
upsert(
208
values: Row | Row[],
209
options?: {
210
onConflict?: string;
211
ignoreDuplicates?: boolean;
212
count?: 'exact' | 'planned' | 'estimated';
213
defaultToNull?: boolean;
214
}
215
): PostgrestFilterBuilder<...>;
216
```
217
218
**Usage Examples:**
219
220
```typescript
221
// Basic upsert (merge on primary key)
222
const { data, error } = await client
223
.from("users")
224
.upsert({ id: 123, name: "John Doe", email: "john@example.com" })
225
.select();
226
227
// Upsert with specific conflict column
228
const { data, error } = await client
229
.from("users")
230
.upsert(
231
{ email: "john@example.com", name: "John Doe", age: 30 },
232
{ onConflict: "email" }
233
)
234
.select();
235
236
// Upsert multiple records
237
const { data, error } = await client
238
.from("users")
239
.upsert([
240
{ email: "alice@example.com", name: "Alice" },
241
{ email: "bob@example.com", name: "Bob" }
242
], { onConflict: "email" })
243
.select();
244
245
// Ignore duplicates instead of merging
246
const { data, error } = await client
247
.from("users")
248
.upsert(
249
[
250
{ email: "existing@example.com", name: "Won't Override" },
251
{ email: "new@example.com", name: "Will Insert" }
252
],
253
{ onConflict: "email", ignoreDuplicates: true }
254
)
255
.select();
256
257
// Upsert with composite key
258
const { data, error } = await client
259
.from("user_preferences")
260
.upsert(
261
{ user_id: 123, setting_key: "theme", setting_value: "dark" },
262
{ onConflict: "user_id,setting_key" }
263
)
264
.select();
265
```
266
267
### DELETE Operations
268
269
Delete records from tables.
270
271
```typescript { .api }
272
/**
273
* Perform a DELETE on the table or view. By default, deleted rows are not returned.
274
* To return it, chain the call with `.select()` after filters
275
* @param options - Named parameters
276
* @param options.count - Count algorithm to use to count deleted rows
277
*/
278
delete(
279
options?: {
280
count?: 'exact' | 'planned' | 'estimated';
281
}
282
): PostgrestFilterBuilder<...>;
283
```
284
285
**Usage Examples:**
286
287
```typescript
288
// Delete with filter
289
const { error } = await client
290
.from("users")
291
.delete()
292
.eq("id", 123);
293
294
// Delete and return deleted records
295
const { data: deletedUsers, error } = await client
296
.from("users")
297
.delete()
298
.eq("active", false)
299
.select();
300
301
// Delete with multiple filters
302
const { data, error } = await client
303
.from("posts")
304
.delete()
305
.eq("published", false)
306
.lt("created_at", "2022-01-01")
307
.select("id, title");
308
309
// Delete with count
310
const { data, count, error } = await client
311
.from("logs")
312
.delete({ count: 'exact' })
313
.lt("created_at", "2023-01-01")
314
.select("id");
315
316
// Conditional delete with complex filters
317
const { data, error } = await client
318
.from("user_sessions")
319
.delete()
320
.or("expires_at.lt.now(),last_activity.lt.2023-01-01")
321
.select();
322
```
323
324
### Count Algorithms
325
326
All query operations support count algorithms for performance optimization:
327
328
- **`'exact'`**: Exact but slow count algorithm. Performs a `COUNT(*)` under the hood
329
- **`'planned'`**: Approximated but fast count algorithm. Uses the Postgres statistics under the hood
330
- **`'estimated'`**: Uses exact count for low numbers and planned count for high numbers
331
332
**Usage Examples:**
333
334
```typescript
335
// Exact count (slower but precise)
336
const { data, count } = await client
337
.from("users")
338
.select("*", { count: 'exact' });
339
340
// Planned count (faster but approximate)
341
const { data, count } = await client
342
.from("large_table")
343
.select("*", { count: 'planned' });
344
345
// Estimated count (hybrid approach)
346
const { data, count } = await client
347
.from("posts")
348
.select("*", { count: 'estimated' });
349
```
350
351
### Type Safety
352
353
All query operations maintain full type safety when using TypeScript with database schemas:
354
355
```typescript
356
interface Database {
357
public: {
358
Tables: {
359
users: {
360
Row: { id: number; name: string; email: string; active: boolean };
361
Insert: { name: string; email: string; active?: boolean };
362
Update: { name?: string; email?: string; active?: boolean };
363
};
364
};
365
};
366
}
367
368
const client = new PostgrestClient<Database>("https://api.example.com");
369
370
// Type-safe insert
371
const { data, error } = await client
372
.from("users")
373
.insert({
374
name: "John", // ✅ Required field
375
email: "john@example.com", // ✅ Required field
376
active: true // ✅ Optional field
377
// id: 123 // ❌ TypeScript error - not in Insert type
378
})
379
.select();
380
381
// Type-safe update
382
const { data, error } = await client
383
.from("users")
384
.update({
385
name: "Jane", // ✅ Optional in Update type
386
// invalid_field: "value" // ❌ TypeScript error
387
})
388
.eq("id", 123);
389
```