0
# Database Operations
1
2
Database operations provide type-safe access to PostgreSQL tables, views, and functions through the PostgREST API. All database operations support filtering, ordering, pagination, and complex joins with full TypeScript support.
3
4
## Capabilities
5
6
### Table and View Queries
7
8
Query tables and views with a fluent API that supports complex filtering, joins, and aggregations.
9
10
```typescript { .api }
11
/**
12
* Query a table or view
13
* @param relation - The table or view name to query
14
* @returns PostgrestQueryBuilder for chaining operations
15
*/
16
from<TableName extends string & keyof Schema['Tables']>(
17
relation: TableName
18
): PostgrestQueryBuilder<ClientOptions, Schema, Schema['Tables'][TableName], TableName>;
19
20
from<ViewName extends string & keyof Schema['Views']>(
21
relation: ViewName
22
): PostgrestQueryBuilder<ClientOptions, Schema, Schema['Views'][ViewName], ViewName>;
23
24
interface PostgrestQueryBuilder<ClientOptions, Schema, Table, TableName> {
25
// Data selection
26
select(columns?: string, options?: { head?: boolean; count?: 'exact' | 'planned' | 'estimated' }): PostgrestFilterBuilder;
27
28
// Data modification
29
insert(values: Table['Insert'] | Table['Insert'][], options?: { upsert?: boolean; onConflict?: string; ignoreDuplicates?: boolean }): PostgrestFilterBuilder;
30
update(values: Table['Update'], options?: { count?: 'exact' | 'planned' | 'estimated' }): PostgrestFilterBuilder;
31
upsert(values: Table['Insert'] | Table['Insert'][], options?: { onConflict?: string; ignoreDuplicates?: boolean }): PostgrestFilterBuilder;
32
delete(options?: { count?: 'exact' | 'planned' | 'estimated' }): PostgrestFilterBuilder;
33
}
34
35
interface PostgrestFilterBuilder<ClientOptions, Schema, Row, Result, RelationName, Relationships, Operation> extends PromiseLike<PostgrestResponse<Result>> {
36
// Filtering
37
eq(column: keyof Row, value: any): PostgrestFilterBuilder;
38
neq(column: keyof Row, value: any): PostgrestFilterBuilder;
39
gt(column: keyof Row, value: any): PostgrestFilterBuilder;
40
gte(column: keyof Row, value: any): PostgrestFilterBuilder;
41
lt(column: keyof Row, value: any): PostgrestFilterBuilder;
42
lte(column: keyof Row, value: any): PostgrestFilterBuilder;
43
like(column: keyof Row, pattern: string): PostgrestFilterBuilder;
44
ilike(column: keyof Row, pattern: string): PostgrestFilterBuilder;
45
is(column: keyof Row, value: boolean | null): PostgrestFilterBuilder;
46
in(column: keyof Row, values: any[]): PostgrestFilterBuilder;
47
contains(column: keyof Row, value: any): PostgrestFilterBuilder;
48
containedBy(column: keyof Row, value: any): PostgrestFilterBuilder;
49
rangeGt(column: keyof Row, range: string): PostgrestFilterBuilder;
50
rangeGte(column: keyof Row, range: string): PostgrestFilterBuilder;
51
rangeLt(column: keyof Row, range: string): PostgrestFilterBuilder;
52
rangeLte(column: keyof Row, range: string): PostgrestFilterBuilder;
53
rangeAdjacent(column: keyof Row, range: string): PostgrestFilterBuilder;
54
overlaps(column: keyof Row, value: any): PostgrestFilterBuilder;
55
textSearch(column: keyof Row, query: string, options?: { type?: 'plain' | 'phrase' | 'websearch'; config?: string }): PostgrestFilterBuilder;
56
match(query: Record<keyof Row, any>): PostgrestFilterBuilder;
57
not(column: keyof Row, operator: string, value: any): PostgrestFilterBuilder;
58
or(filters: string, options?: { foreignTable?: string }): PostgrestFilterBuilder;
59
filter(column: keyof Row, operator: string, value: any): PostgrestFilterBuilder;
60
61
// Ordering
62
order(column: keyof Row, options?: { ascending?: boolean; nullsFirst?: boolean; foreignTable?: string }): PostgrestFilterBuilder;
63
64
// Pagination
65
range(from: number, to: number, options?: { foreignTable?: string }): PostgrestFilterBuilder;
66
limit(count: number, options?: { foreignTable?: string }): PostgrestFilterBuilder;
67
68
// Result limiting
69
single(): PostgrestFilterBuilder<ClientOptions, Schema, Row, Row, RelationName, Relationships, Operation>;
70
maybeSingle(): PostgrestFilterBuilder<ClientOptions, Schema, Row, Row | null, RelationName, Relationships, Operation>;
71
72
// Response configuration
73
csv(): PostgrestFilterBuilder<ClientOptions, Schema, Row, string, RelationName, Relationships, Operation>;
74
geojson(): PostgrestFilterBuilder<ClientOptions, Schema, Row, Record<string, any>, RelationName, Relationships, Operation>;
75
explain(options?: { analyze?: boolean; verbose?: boolean; settings?: boolean; buffers?: boolean; wal?: boolean; format?: 'text' | 'json' }): Promise<{ data: any; error: PostgrestError | null }>;
76
77
// Response handling
78
abortSignal(signal: AbortSignal): PostgrestFilterBuilder;
79
then<TResult1 = PostgrestResponse<Result>, TResult2 = never>(
80
onfulfilled?: ((value: PostgrestResponse<Result>) => TResult1 | PromiseLike<TResult1>) | null,
81
onrejected?: ((reason: any) => TResult2 | PromiseLike<TResult2>) | null
82
): PromiseLike<TResult1 | TResult2>;
83
}
84
85
interface PostgrestResponse<T> {
86
data: T | null;
87
error: PostgrestError | null;
88
count: number | null;
89
status: number;
90
statusText: string;
91
}
92
93
interface PostgrestSingleResponse<T> {
94
data: T | null;
95
error: PostgrestError | null;
96
}
97
98
interface PostgrestMaybeSingleResponse<T> {
99
data: T | null;
100
error: PostgrestError | null;
101
}
102
103
class PostgrestError extends Error {
104
message: string;
105
details: string;
106
hint: string;
107
code: string;
108
}
109
```
110
111
**Usage Examples:**
112
113
```typescript
114
// Basic select
115
const { data, error } = await supabase
116
.from('users')
117
.select('*');
118
119
// Select specific columns
120
const { data, error } = await supabase
121
.from('users')
122
.select('id, name, email');
123
124
// Filtering
125
const { data, error } = await supabase
126
.from('users')
127
.select('*')
128
.eq('status', 'active')
129
.gte('age', 18);
130
131
// Complex filtering
132
const { data, error } = await supabase
133
.from('posts')
134
.select('title, content, users(name)')
135
.eq('published', true)
136
.ilike('title', '%javascript%')
137
.order('created_at', { ascending: false })
138
.range(0, 9);
139
140
// Insert data
141
const { data, error } = await supabase
142
.from('users')
143
.insert([
144
{ name: 'John Doe', email: 'john@example.com' },
145
{ name: 'Jane Smith', email: 'jane@example.com' }
146
])
147
.select();
148
149
// Update data
150
const { data, error } = await supabase
151
.from('users')
152
.update({ status: 'inactive' })
153
.eq('last_login', null);
154
155
// Delete data
156
const { data, error } = await supabase
157
.from('users')
158
.delete()
159
.eq('status', 'inactive');
160
161
// Upsert (insert or update)
162
const { data, error } = await supabase
163
.from('users')
164
.upsert({ id: 1, name: 'Updated Name' }, { onConflict: 'id' });
165
```
166
167
### Database Functions (RPC)
168
169
Call PostgreSQL functions with parameters and receive typed results.
170
171
```typescript { .api }
172
/**
173
* Perform a function call
174
* @param fn - The function name to call
175
* @param args - The arguments to pass to the function call
176
* @param options - Named parameters
177
* @returns PostgrestFilterBuilder for handling the function result
178
*/
179
rpc<FnName extends string & keyof Schema['Functions']>(
180
fn: FnName,
181
args?: Schema['Functions'][FnName]['Args'],
182
options?: {
183
/** When set to true, data will not be returned. Useful if you only need the count */
184
head?: boolean;
185
/** When set to true, the function will be called with read-only access mode */
186
get?: boolean;
187
/** Count algorithm to use to count rows returned by set-returning functions */
188
count?: 'exact' | 'planned' | 'estimated';
189
}
190
): PostgrestFilterBuilder<
191
ClientOptions,
192
Schema,
193
Schema['Functions'][FnName]['Returns'] extends any[]
194
? Schema['Functions'][FnName]['Returns'][number] extends Record<string, unknown>
195
? Schema['Functions'][FnName]['Returns'][number]
196
: never
197
: never,
198
Schema['Functions'][FnName]['Returns'],
199
FnName,
200
null,
201
'RPC'
202
>;
203
```
204
205
**Usage Examples:**
206
207
```typescript
208
// Call a function without parameters
209
const { data, error } = await supabase
210
.rpc('get_user_count');
211
212
// Call a function with parameters
213
const { data, error } = await supabase
214
.rpc('get_users_by_status', { status_filter: 'active' });
215
216
// Call a function and apply filters to the result
217
const { data, error } = await supabase
218
.rpc('search_products', { search_term: 'laptop' })
219
.gte('price', 500)
220
.order('price', { ascending: true });
221
222
// Call a function with head-only response
223
const { data, error, count } = await supabase
224
.rpc('expensive_calculation', { param: 'value' }, { head: true, count: 'exact' });
225
```
226
227
### Schema Switching
228
229
Switch between different database schemas for multi-tenant applications or organization.
230
231
```typescript { .api }
232
/**
233
* Select a schema to query or perform function calls
234
* The schema needs to be on the list of exposed schemas inside Supabase
235
* @param schema - The schema to query
236
* @returns PostgrestClient configured for the specified schema
237
*/
238
schema<DynamicSchema extends string & keyof Omit<Database, '__InternalSupabase'>>(
239
schema: DynamicSchema
240
): PostgrestClient<
241
Database,
242
ClientOptions,
243
DynamicSchema,
244
Database[DynamicSchema] extends GenericSchema ? Database[DynamicSchema] : any
245
>;
246
```
247
248
**Usage Examples:**
249
250
```typescript
251
// Switch to a different schema
252
const { data, error } = await supabase
253
.schema('inventory')
254
.from('products')
255
.select('*');
256
257
// Call functions in a specific schema
258
const { data, error } = await supabase
259
.schema('analytics')
260
.rpc('calculate_metrics', { date_range: '7d' });
261
262
// Chain schema operations
263
const publicUsers = await supabase.from('users').select('*');
264
const adminUsers = await supabase.schema('admin').from('users').select('*');
265
```
266
267
## Advanced Query Patterns
268
269
### Joins and Relationships
270
271
```typescript
272
// Inner joins using foreign key relationships
273
const { data, error } = await supabase
274
.from('posts')
275
.select(`
276
title,
277
content,
278
users (
279
name,
280
281
)
282
`);
283
284
// Multiple level joins
285
const { data, error } = await supabase
286
.from('comments')
287
.select(`
288
text,
289
posts (
290
title,
291
users (
292
name
293
)
294
)
295
`);
296
297
// Filtering on joined tables
298
const { data, error } = await supabase
299
.from('posts')
300
.select('title, users(name)')
301
.eq('users.status', 'active');
302
```
303
304
### Aggregations and Counting
305
306
```typescript
307
// Get count with data
308
const { data, error, count } = await supabase
309
.from('users')
310
.select('*', { count: 'exact' })
311
.eq('status', 'active');
312
313
// Get count only
314
const { data, error, count } = await supabase
315
.from('users')
316
.select('*', { head: true, count: 'exact' })
317
.eq('status', 'active');
318
319
// Use different count algorithms
320
const { count } = await supabase
321
.from('large_table')
322
.select('*', { head: true, count: 'estimated' });
323
```
324
325
### Full-Text Search
326
327
```typescript
328
// Basic text search
329
const { data, error } = await supabase
330
.from('articles')
331
.select('title, content')
332
.textSearch('content', 'javascript programming');
333
334
// Advanced text search with configuration
335
const { data, error } = await supabase
336
.from('articles')
337
.select('title, content')
338
.textSearch('content', 'javascript & programming', {
339
type: 'websearch',
340
config: 'english'
341
});
342
```
343
344
### Error Handling
345
346
```typescript
347
const { data, error } = await supabase
348
.from('users')
349
.select('*')
350
.eq('id', userId);
351
352
if (error) {
353
console.error('Database error:', {
354
message: error.message,
355
details: error.details,
356
hint: error.hint,
357
code: error.code
358
});
359
return;
360
}
361
362
// Use data safely
363
console.log('Users:', data);
364
```