---
title: Database SDK Reference
description: Type-safe database operations using the InsForge TypeScript SDK
---

## Installation

import Installation from '/snippets/sdk-installation.mdx';

<Installation />

## insert()

Insert new records into a table.

### Parameters

- `values` (object | Array, required) - Data to insert. Single object or array for bulk insert
- `options.count` ('exact' | 'planned' | 'estimated', optional) - Include count of inserted rows

### Returns

```typescript
{
  data: Array<object> | null,
  error: Error | null,
  count?: number
}
```

<Note>
Chain `.select()` after `.insert()` to return the inserted data
</Note>

### Examples

<CodeGroup>
```javascript Single insert
const { data, error } = await insforge.database
  .from('posts')
  .insert({ title: 'Hello World', content: 'My first post!' })
  .select()
```

```javascript Bulk insert
const { data, error } = await insforge.database
  .from('posts')
  .insert([
    { title: 'First Post', content: 'Hello everyone!' },
    { title: 'Second Post', content: 'Another update.' }
  ])
  .select()
```
</CodeGroup>

### Output Example

```json
{
  "data": [
    { "id": "789", "title": "Hello World", "content": "My first post!", "created_at": "2024-01-15T10:30:00Z" }
  ],
  "error": null
}
```

---

## update()

Update existing records in a table. Must use filters to target specific rows.

### Parameters

- `values` (object, required) - Fields to update
- `options.count` ('exact' | 'planned' | 'estimated', optional) - Include count of updated rows

### Returns

```typescript
{
  data: Array<object> | null,
  error: Error | null,
  count?: number
}
```

<Warning>
Always use filters like `.eq()` or `.in()` to specify which rows to update
</Warning>

### Examples

<CodeGroup>
```javascript Update by ID
const { data, error } = await insforge.database
  .from('posts')
  .update({ title: 'Updated Title' })
  .eq('id', postId)
  .select()
```

```javascript Update multiple
const { data, error } = await insforge.database
  .from('tasks')
  .update({ status: 'completed' })
  .in('id', ['task-1', 'task-2'])
  .select()
```
</CodeGroup>

### Output Example

```json
{
  "data": [
    { "id": "123", "title": "Updated Title", "content": "My first post!", "updated_at": "2024-01-15T11:00:00Z" }
  ],
  "error": null
}
```

---

## delete()

Delete records from a table. Must use filters to target specific rows.

### Parameters

- `options.count` ('exact' | 'planned' | 'estimated', optional) - Include count of deleted rows

### Returns

```typescript
{
  data: Array<object> | null,
  error: Error | null,
  count?: number
}
```

<Warning>
Always use filters to specify which rows to delete
</Warning>

### Examples

<CodeGroup>
```javascript Delete by ID
const { error } = await insforge.database
  .from('posts')
  .delete()
  .eq('id', postId)
```

```javascript Delete with filter
const { error } = await insforge.database
  .from('sessions')
  .delete()
  .lt('expires_at', new Date())
```
</CodeGroup>

### Output Example

```json
{
  "data": null,
  "error": null
}
```

---

## select()

Query records from a table or view.

### Parameters

- `columns` (string, optional) - Comma-separated column names. Use `*` for all columns
- `options.count` ('exact' | 'planned' | 'estimated', optional) - Include total row count
- `options.head` (boolean, optional) - Return only count, no data

### Returns

```typescript
{
  data: Array<object> | null,
  error: Error | null,
  count?: number
}
```

### Examples

<CodeGroup>
```javascript Get all
const { data, error } = await insforge.database
  .from('posts')
  .select()
```

```javascript Specific columns
const { data, error } = await insforge.database
  .from('posts')
  .select('id, title, content')
```

```javascript With relationships
const { data, error } = await insforge.database
  .from('posts')
  .select('*, comments(id, content)')
```
</CodeGroup>

### Output Example

```json
{
  "data": [
    { "id": "123", "title": "Hello World", "content": "My first post!" },
    { "id": "456", "title": "Second Post", "content": "Another update." }
  ],
  "error": null
}
```

---

## rpc()

Call PostgreSQL stored functions (RPC - Remote Procedure Call).

### Parameters

- `functionName` (string, required) - Name of the PostgreSQL function to call
- `args` (object, optional) - Arguments to pass to the function

### Returns

```typescript
{
  data: T | T[] | null,
  error: Error | null
}
```

### Examples

<CodeGroup>
```javascript With parameters
const { data, error } = await insforge.database
  .rpc('get_user_stats', { user_id: '123' })
```

```javascript Without parameters
const { data, error } = await insforge.database
  .rpc('get_all_active_users')
```
</CodeGroup>

---

## Filters

Chain filters to narrow down query results. All filters take `(column, value)` as parameters.

| Filter | Description | Example |
|--------|-------------|---------|
| `.eq(column, value)` | Equals | `.eq('status', 'active')` |
| `.neq(column, value)` | Not equals | `.neq('status', 'banned')` |
| `.gt(column, value)` | Greater than | `.gt('age', 18)` |
| `.gte(column, value)` | Greater than or equal | `.gte('price', 100)` |
| `.lt(column, value)` | Less than | `.lt('stock', 10)` |
| `.lte(column, value)` | Less than or equal | `.lte('priority', 3)` |
| `.like(column, pattern)` | Case-sensitive pattern (use `%` wildcard) | `.like('name', '%Widget%')` |
| `.ilike(column, pattern)` | Case-insensitive pattern (use `%` wildcard) | `.ilike('email', '%@gmail.com')` |
| `.in(column, array)` | Value in array | `.in('status', ['pending', 'active'])` |
| `.is(column, value)` | Exactly equals (for null checks) | `.is('deleted_at', null)` |

```javascript
// Example: Chain multiple filters
const { data } = await insforge.database
  .from('products')
  .select()
  .eq('category', 'electronics')
  .gte('price', 50)
  .lte('price', 500)
  .is('in_stock', true)
```

---

## Modifiers

Control how query results are returned.

| Modifier | Description | Example |
|----------|-------------|---------|
| `.order(column, options)` | Sort results. Options: `{ ascending: true/false, nullsFirst: true/false }` | `.order('created_at', { ascending: false })` |
| `.limit(count)` | Limit number of rows | `.limit(10)` |
| `.range(from, to)` | Get rows between indices (pagination) | `.range(0, 9)` |
| `.single()` | Return object instead of array (throws if multiple) | `.single()` |
| `.maybeSingle()` | Return object or null (no error) | `.maybeSingle()` |

```javascript
// Example: Pagination with sorting
const { data } = await insforge.database
  .from('posts')
  .select()
  .order('created_at', { ascending: false })
  .range(0, 9)
```

---

## Common Patterns

### Pagination with Count

```javascript
const page = 1;
const pageSize = 10;
const from = (page - 1) * pageSize;
const to = from + pageSize - 1;

const { data, count } = await insforge.database
  .from('posts')
  .select('*', { count: 'exact' })
  .range(from, to)
  .order('created_at', { ascending: false })

console.log(`Page ${page}: ${data.length} of ${count} total`)
```

**Output:**
```json
{
  "data": [
    { "id": "1", "title": "Post 1", "created_at": "2024-01-15T10:00:00Z" },
    { "id": "2", "title": "Post 2", "created_at": "2024-01-14T10:00:00Z" }
  ],
  "count": 50,
  "error": null
}
```

### Filtered Search

```javascript
const { data } = await insforge.database
  .from('products')
  .select('id, name, price, category')
  .eq('category', 'electronics')
  .gte('price', 50)
  .lte('price', 500)
  .order('price', { ascending: true })
  .limit(20)
```

**Output:**
```json
{
  "data": [
    { "id": "101", "name": "USB Cable", "price": 59.99, "category": "electronics" },
    { "id": "102", "name": "Keyboard", "price": 89.99, "category": "electronics" }
  ],
  "error": null
}
```

### Using with Authentication Hooks

Combine database queries with `useUser()` or `useAuth()` hooks to fetch user-specific data:

```tsx
import { useUser } from '@insforge/react'; // or '@insforge/react-router'
import { insforge } from './lib/insforge';
import { useEffect, useState } from 'react';

function MyProfile() {
  const { user, isLoaded } = useUser();
  const [posts, setPosts] = useState([]);

  useEffect(() => {
    if (user) {
      // Fetch user's posts from database
      insforge.database
        .from('posts')
        .select('*')
        .eq('user_id', user.id)
        .then(({ data }) => setPosts(data));
    }
  }, [user]);

  if (!isLoaded) return <div>Loading...</div>;
  if (!user) return <div>Not signed in</div>;

  return (
    <div>
      <h1>{user.profile?.name}</h1>
      <p>{user.email}</p>
      <h2>My Posts: {posts.length}</h2>
      {posts.map(post => (
        <div key={post.id}>{post.title}</div>
      ))}
    </div>
  );
}
```

**Key points:**
- Use `user.id` to filter data for the authenticated user
- Check `isLoaded` before accessing `user` to avoid race conditions
- Check `!user` to handle unauthenticated state
