0
# Database Operations
1

2
Complete PostgreSQL database operations including CRUD operations, stored procedures, schema management, and query building through PostgREST integration. Provides fluent query builders and comprehensive filtering capabilities.
3

4
## Capabilities
5

6
### Table Operations
7

8
Access database tables for CRUD operations using query builders that provide a fluent interface for constructing database queries.
9

10
```python { .api }
11
def table(self, table_name: str):
12
    """
13
    Create a query builder for the specified table.
14

15
    Parameters:
16
    - table_name: Name of the database table
17

18
    Returns:
19
    Query builder for table operations (select, insert, update, delete)
20
    
21
    Note: This is the preferred method in Python since 'from' is a reserved keyword.
22
    """
23

24
def from_(self, table_name: str):
25
    """
26
    Alternative method for table operations (equivalent to table()).
27

28
    Parameters:
29
    - table_name: Name of the database table
30

31
    Returns:
32
    Query builder for table operations
33
    """
34
```
35

36
**Usage Examples:**
37

38
```python
39
# Select all records
40
countries = supabase.table("countries").select("*").execute()
41

42
# Select specific columns
43
countries = supabase.table("countries").select("name, code").execute()
44

45
# Insert single record
46
result = supabase.table("countries").insert({"name": "Germany", "code": "DE"}).execute()
47

48
# Insert multiple records
49
result = supabase.table("countries").insert([
50
    {"name": "Germany", "code": "DE"},
51
    {"name": "France", "code": "FR"}
52
]).execute()
53

54
# Update records
55
result = supabase.table("countries").update({"name": "Deutschland"}).eq("code", "DE").execute()
56

57
# Delete records
58
result = supabase.table("countries").delete().eq("code", "DE").execute()
59

60
# Complex filtering
61
countries = (supabase.table("countries")
62
    .select("name, population")
63
    .gt("population", 1000000)
64
    .order("population", desc=True)
65
    .limit(10)
66
    .execute())
67
```
68

69
### Schema Selection
70

71
Select a specific PostgreSQL schema for database operations, enabling multi-schema database designs.
72

73
```python { .api }
74
def schema(self, schema: str):
75
    """
76
    Select a database schema for subsequent operations.
77

78
    Parameters:
79
    - schema: Name of the PostgreSQL schema
80

81
    Returns:
82
    PostgREST client configured for the specified schema
83

84
    Note: The schema must be exposed in Supabase settings.
85
    """
86
```
87

88
**Usage Example:**
89

90
```python
91
# Switch to private schema
92
private_schema = supabase.schema("private")
93
private_data = private_schema.table("sensitive_data").select("*").execute()
94

95
# Use different schemas in same session
96
public_countries = supabase.table("countries").select("*").execute()
97
private_users = supabase.schema("private").table("users").select("*").execute()
98
```
99

100
### Stored Procedure Calls
101

102
Execute PostgreSQL stored procedures and functions with parameters, supporting various call modes and result handling.
103

104
```python { .api }
105
def rpc(
106
    self,
107
    fn: str,
108
    params: Optional[Dict[Any, Any]] = None,
109
    count: Optional[CountMethod] = None,
110
    head: bool = False,
111
    get: bool = False
112
):
113
    """
114
    Execute a stored procedure or function.
115

116
    Parameters:
117
    - fn: Name of the stored procedure/function to call
118
    - params: Parameters to pass to the function (default: {})
119
    - count: Method for counting returned rows (exact, planned, estimated)
120
    - head: When True, only return count without data (useful for count-only queries)
121
    - get: When True, execute with read-only access mode
122

123
    Returns:
124
    FilterRequestBuilder for applying additional filters to RPC results
125

126
    Note: Functions must be defined in the database and exposed via API.
127
    """
128
```
129

130
**Usage Examples:**
131

132
```python
133
# Simple function call without parameters
134
result = supabase.rpc("get_total_users").execute()
135

136
# Function call with parameters
137
result = supabase.rpc("get_users_by_country", {"country_code": "US"}).execute()
138

139
# Function call with count
140
result = supabase.rpc("search_users", {"query": "john"}, count="exact").execute()
141
print(f"Found {result.count} users")
142

143
# Read-only function call
144
result = supabase.rpc("calculate_stats", {"date_range": "2023"}, get=True).execute()
145

146
# Function call with additional filtering
147
filtered_result = (supabase.rpc("get_active_users")
148
    .gt("last_login", "2023-01-01")
149
    .order("last_login", desc=True)
150
    .limit(100)
151
    .execute())
152
```
153

154
### PostgREST Client Access
155

156
Direct access to the underlying PostgREST client for advanced operations and configuration.
157

158
```python { .api }
159
@property
160
def postgrest(self) -> SyncPostgrestClient | AsyncPostgrestClient:
161
    """
162
    Direct access to PostgREST client instance.
163

164
    Returns:
165
    PostgREST client (sync or async depending on parent client type)
166
    
167
    Provides full PostgREST API including:
168
    - Advanced query building and filtering
169
    - Custom HTTP headers and authentication
170
    - Schema selection and table operations
171
    - Stored procedure execution
172
    """
173
```
174

175
**Usage Example:**
176

177
```python
178
# Direct PostgREST access for advanced operations
179
postgrest_client = supabase.postgrest
180

181
# Use PostgREST client directly
182
result = postgrest_client.from_("countries").select("*").execute()
183

184
# Access with custom schema
185
result = postgrest_client.schema("analytics").from_("reports").select("*").execute()
186
```
187

188
## Query Builder Operations
189

190
The table operations return query builders that support extensive filtering, ordering, and data manipulation operations.
191

192
### Common Query Builder Methods
193

194
```python { .api }
195
# Filtering methods
196
.eq(column, value)          # Equal to
197
.neq(column, value)         # Not equal to  
198
.gt(column, value)          # Greater than
199
.gte(column, value)         # Greater than or equal
200
.lt(column, value)          # Less than
201
.lte(column, value)         # Less than or equal
202
.like(column, pattern)      # Pattern matching
203
.ilike(column, pattern)     # Case-insensitive pattern matching
204
.is_(column, value)         # IS comparison (for NULL)
205
.in_(column, values)        # IN clause
206
.contains(column, value)    # Contains (for arrays/JSON)
207
.contained_by(column, value) # Contained by (for arrays/JSON)
208

209
# Ordering and limiting
210
.order(column, desc=False)  # Order by column
211
.limit(count)               # Limit results
212
.offset(count)              # Skip results
213
.range(start, end)          # Range of results
214

215
# Data operations
216
.select(columns)            # Select columns
217
.insert(data)               # Insert data
218
.update(data)               # Update data  
219
.delete()                   # Delete data
220
.upsert(data)               # Insert or update
221

222
# Execution
223
.execute()                  # Execute the query
224
```
225

226
**Complex Query Examples:**
227

228
```python
229
# Advanced filtering with multiple conditions
230
users = (supabase.table("users")
231
    .select("id, name, email, created_at")
232
    .gte("created_at", "2023-01-01")
233
    .like("email", "%@company.com")
234
    .neq("status", "inactive")
235
    .order("created_at", desc=True)
236
    .limit(50)
237
    .execute())
238

239
# JSON column operations
240
posts = (supabase.table("posts")
241
    .select("*")
242
    .contains("tags", ["python", "tutorial"])
243
    .execute())
244

245
# Count with filtering
246
count_result = (supabase.table("orders")
247
    .select("*", count="exact")
248
    .gte("total", 100)
249
    .head()
250
    .execute())
251
print(f"High-value orders: {count_result.count}")
252
```
253

254
## Error Handling
255

256
Database operations can raise various exceptions depending on the type of error encountered.
257

258
```python { .api }
259
# Database-specific exceptions (from postgrest)
260
class PostgrestAPIError(Exception):
261
    """PostgREST API errors including constraint violations, permission errors"""
262

263
class PostgrestAPIResponse:
264
    """PostgREST response wrapper with data and metadata"""
265
```
266

267
**Error Handling Examples:**
268

269
```python
270
from postgrest import PostgrestAPIError
271

272
try:
273
    # Insert with potential constraint violation
274
    result = supabase.table("users").insert({
275
        "email": "duplicate@example.com",
276
        "name": "User"
277
    }).execute()
278
except PostgrestAPIError as e:
279
    print(f"Database error: {e}")
280
    # Handle constraint violations, permission errors, etc.
281

282
try:
283
    # Query with potential schema/table issues
284
    result = supabase.table("nonexistent_table").select("*").execute()
285
except PostgrestAPIError as e:
286
    print(f"Table not found: {e}")
287
```
288

289
## Performance Considerations
290

291
```python
292
# Use select() to limit columns returned
293
users = supabase.table("users").select("id, name").execute()  # Good
294
users = supabase.table("users").select("*").execute()         # Avoid for large tables
295

296
# Use pagination for large datasets
297
page_size = 100
298
offset = 0
299
while True:
300
    batch = (supabase.table("large_table")
301
        .select("*")
302
        .range(offset, offset + page_size - 1)
303
        .execute())
304
    
305
    if not batch.data:
306
        break
307
        
308
    process_batch(batch.data)
309
    offset += page_size
310

311
# Use indexes and efficient filtering
312
# Good: Filter on indexed columns
313
users = supabase.table("users").select("*").eq("email", "user@example.com").execute()
314

315
# Less efficient: Pattern matching on non-indexed columns  
316
users = supabase.table("users").select("*").like("description", "%keyword%").execute()
317
```