> ## Documentation Index
> Fetch the complete documentation index at: https://docs.insforge.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Database SDK Reference

> Type-safe database operations using the InsForge TypeScript SDK

## Installation

<CodeGroup>
  ```bash npm theme={null}
  npm install @insforge/sdk@latest
  ```

  ```bash yarn theme={null}
  yarn add @insforge/sdk@latest
  ```

  ```bash pnpm theme={null}
  pnpm add @insforge/sdk@latest
  ```
</CodeGroup>

```javascript theme={null}
import { createClient } from '@insforge/sdk';

const insforge = createClient({
  baseUrl: 'https://your-app.insforge.app',
  anonKey: 'your-anon-key'  // Optional: for public/unauthenticated requests
});
```

Find the anon key with `npx @insforge/cli secrets get ANON_KEY`, or in the dashboard: click **Install** and open **API Keys**.

## 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 theme={null}
{
  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 theme={null}
  const { data, error } = await insforge.database
    .from('posts')
    .insert({ title: 'Hello World', content: 'My first post!' })
    .select()
  ```

  ```javascript Bulk insert theme={null}
  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 theme={null}
{
  "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 theme={null}
{
  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 theme={null}
  const { data, error } = await insforge.database
    .from('posts')
    .update({ title: 'Updated Title' })
    .eq('id', postId)
    .select()
  ```

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

### Output Example

```json theme={null}
{
  "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 theme={null}
{
  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 theme={null}
  const { error } = await insforge.database
    .from('posts')
    .delete()
    .eq('id', postId)
  ```

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

### Output Example

```json theme={null}
{
  "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 theme={null}
{
  data: Array<object> | null,
  error: Error | null,
  count?: number
}
```

### Examples

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

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

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

### Output Example

```json theme={null}
{
  "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 theme={null}
{
  data: T | T[] | null,
  error: Error | null
}
```

### Examples

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

  ```javascript Without parameters theme={null}
  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 theme={null}
// 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 theme={null}
// 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 theme={null}
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 theme={null}
{
  "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 theme={null}
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 theme={null}
{
  "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 theme={null}
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