> ## 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.

# 数据库 API 参考

> 通过 REST API 实现 PostgREST 风格的数据库操作

## 概述

数据库 API 为你的数据库表提供 PostgREST 风格的 CRUD 操作。所有请求都需要身份验证请求头。

## 请求头

```bash theme={null}
Authorization: Bearer your-jwt-token-or-anon-key
Content-Type: application/json
```

***

## 查询记录

从表中检索记录，支持过滤、排序和分页。

```
GET /api/database/records/{tableName}
```

### 查询参数

| 参数        | 类型      | 描述                                    |
| --------- | ------- | ------------------------------------- |
| `limit`   | integer | 返回的最大记录数（1-1000，默认值：100）              |
| `offset`  | integer | 分页时要跳过的记录数（默认值：0）                     |
| `order`   | string  | 排序方式（例如 `createdAt.desc`、`name.asc`）  |
| `select`  | string  | 要返回的列，用逗号分隔                           |
| `{field}` | string  | PostgREST 过滤条件（例如 `status=eq.active`） |

### 过滤操作符

| 操作符     | 描述                  | 示例                           |
| ------- | ------------------- | ---------------------------- |
| `eq`    | 等于                  | `status=eq.active`           |
| `neq`   | 不等于                 | `status=neq.deleted`         |
| `gt`    | 大于                  | `age=gt.18`                  |
| `gte`   | 大于或等于               | `price=gte.100`              |
| `lt`    | 小于                  | `quantity=lt.10`             |
| `lte`   | 小于或等于               | `score=lte.50`               |
| `like`  | 模式匹配（区分大小写）         | `name=like.*john*`           |
| `ilike` | 模式匹配（不区分大小写）        | `email=ilike.*@gmail.com`    |
| `in`    | 在列表中                | `status=in.(active,pending)` |
| `is`    | 是否为 null/true/false | `deleted_at=is.null`         |

### 示例

```bash theme={null}
# Get all posts
curl "https://your-app.insforge.app/api/database/records/posts" \
  -H "Authorization: Bearer your-jwt-token"

# Get posts with filters
curl "https://your-app.insforge.app/api/database/records/posts?status=eq.published&order=createdAt.desc&limit=10" \
  -H "Authorization: Bearer your-jwt-token"

# Select specific columns
curl "https://your-app.insforge.app/api/database/records/posts?select=id,title,author" \
  -H "Authorization: Bearer your-jwt-token"
```

### 响应

```json theme={null}
[
  {
    "id": "248373e1-0aea-45ce-8844-5ef259203749",
    "title": "Getting Started with InsForge",
    "content": "This is a guide to help you get started...",
    "createdAt": "2025-07-18T05:37:24.338Z",
    "updatedAt": "2025-07-18T05:37:24.338Z"
  }
]
```

### 响应头

| 请求头             | 描述                      |
| --------------- | ----------------------- |
| `X-Total-Count` | 匹配查询的总记录数               |
| `Content-Range` | 返回的记录范围（例如 `0-99/1234`） |

***

## 创建记录

在表中创建一条或多条记录。

```
POST /api/database/records/{tableName}
```

<Warning>
  **重要提示**：请求体必须是数组，即使只创建单条记录也是如此。
</Warning>

### 请求头

| 请求头      | 值                       | 描述            |
| -------- | ----------------------- | ------------- |
| `Prefer` | `return=representation` | 包含此项以返回已创建的记录 |

### 示例

```bash theme={null}
# Create a single record
curl -X POST "https://your-app.insforge.app/api/database/records/posts" \
  -H "Authorization: Bearer your-jwt-token" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '[{
    "title": "My First Post",
    "content": "Hello world!",
    "published": true
  }]'

# Create multiple records
curl -X POST "https://your-app.insforge.app/api/database/records/posts" \
  -H "Authorization: Bearer your-jwt-token" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '[
    {"title": "Post 1", "content": "Content 1"},
    {"title": "Post 2", "content": "Content 2"}
  ]'
```

### 响应

不包含 `Prefer` 请求头时：

```json theme={null}
[]
```

包含 `Prefer: return=representation` 时：

```json theme={null}
[
  {
    "id": "248373e1-0aea-45ce-8844-5ef259203749",
    "title": "My First Post",
    "content": "Hello world!",
    "published": true,
    "createdAt": "2025-07-18T05:37:24.338Z",
    "updatedAt": "2025-07-18T05:37:24.338Z"
  }
]
```

***

## 更新记录

更新与查询过滤条件匹配的记录。

```
PATCH /api/database/records/{tableName}
```

### 查询参数

使用过滤操作符指定要更新的记录。

### 请求头

| 请求头      | 值                       | 描述            |
| -------- | ----------------------- | ------------- |
| `Prefer` | `return=representation` | 包含此项以返回已更新的记录 |

### 示例

```bash theme={null}
# Update a single record by ID
curl -X PATCH "https://your-app.insforge.app/api/database/records/posts?id=eq.248373e1-0aea-45ce-8844-5ef259203749" \
  -H "Authorization: Bearer your-jwt-token" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{
    "title": "Updated Post Title",
    "content": "This content has been updated."
  }'

# Update multiple records
curl -X PATCH "https://your-app.insforge.app/api/database/records/posts?status=eq.draft" \
  -H "Authorization: Bearer your-jwt-token" \
  -H "Content-Type: application/json" \
  -d '{"status": "archived"}'
```

### 响应

```json theme={null}
[
  {
    "id": "248373e1-0aea-45ce-8844-5ef259203749",
    "title": "Updated Post Title",
    "content": "This content has been updated.",
    "createdAt": "2025-01-01T00:00:00Z",
    "updatedAt": "2025-01-21T11:00:00Z"
  }
]
```

***

## 删除记录

删除与查询过滤条件匹配的记录。

```
DELETE /api/database/records/{tableName}
```

### 查询参数

使用过滤操作符指定要删除的记录。

### 请求头

| 请求头      | 值                       | 描述            |
| -------- | ----------------------- | ------------- |
| `Prefer` | `return=representation` | 包含此项以返回已删除的记录 |

### 示例

```bash theme={null}
# Delete by ID
curl -X DELETE "https://your-app.insforge.app/api/database/records/posts?id=eq.248373e1-0aea-45ce-8844-5ef259203749" \
  -H "Authorization: Bearer your-jwt-token"

# Delete with Prefer header to see deleted records
curl -X DELETE "https://your-app.insforge.app/api/database/records/posts?status=eq.archived" \
  -H "Authorization: Bearer your-jwt-token" \
  -H "Prefer: return=representation"
```

### 响应

不包含 `Prefer` 请求头时：`204 No Content`

包含 `Prefer: return=representation` 时：

```json theme={null}
[
  {
    "id": "248373e1-0aea-45ce-8844-5ef259203749",
    "title": "Deleted Post",
    "createdAt": "2025-01-01T00:00:00Z"
  }
]
```

***

## 错误响应

### 表未找到 (404)

```json theme={null}
{
  "error": "TABLE_NOT_FOUND",
  "message": "Table 'nonexistent' does not exist",
  "statusCode": 404,
  "nextActions": "Check table name and try again"
}
```

### 无效查询 (400)

```json theme={null}
{
  "error": "INVALID_QUERY",
  "message": "Invalid filter syntax",
  "statusCode": 400,
  "nextActions": "Check PostgREST filter documentation"
}
```

### 验证错误 (400)

```json theme={null}
{
  "error": "VALIDATION_ERROR",
  "message": "Invalid field type: expected boolean for 'published'",
  "statusCode": 400,
  "nextActions": "Ensure field types match the table schema"
}
```

***

## 示例

### 分页

```bash theme={null}
# Page 1 (first 20 records)
curl "https://your-app.insforge.app/api/database/records/posts?limit=20&offset=0"

# Page 2 (next 20 records)
curl "https://your-app.insforge.app/api/database/records/posts?limit=20&offset=20"
```

### 复杂过滤条件

```bash theme={null}
# Multiple conditions
curl "https://your-app.insforge.app/api/database/records/posts?status=eq.published&author_id=eq.123&order=createdAt.desc"

# Search with pattern
curl "https://your-app.insforge.app/api/database/records/users?email=ilike.*@company.com"

# In list
curl "https://your-app.insforge.app/api/database/records/orders?status=in.(pending,processing)"

# Null check
curl "https://your-app.insforge.app/api/database/records/tasks?completed_at=is.null"
```

### Upsert（插入或更新）

插入一条记录，如果与唯一约束发生冲突则更新该记录。

```
POST /api/database/records/{tableName}
```

#### 请求头

| 请求头      | 值                              | 描述         |
| -------- | ------------------------------ | ---------- |
| `Prefer` | `resolution=merge-duplicates`  | 冲突时更新现有记录  |
| `Prefer` | `resolution=ignore-duplicates` | 记录已存在时忽略插入 |

#### 示例

```bash theme={null}
# Upsert: insert or update on conflict
curl -X POST "https://your-app.insforge.app/api/database/records/user_settings" \
  -H "Authorization: Bearer your-jwt-token" \
  -H "Content-Type: application/json" \
  -H "Prefer: resolution=merge-duplicates,return=representation" \
  -d '[{
    "user_id": "123e4567-e89b-12d3-a456-426614174000",
    "theme": "dark",
    "notifications": true
  }]'
```

#### 响应

```json theme={null}
[
  {
    "id": "550e8400-e29b-41d4-a716-446655440000",
    "user_id": "123e4567-e89b-12d3-a456-426614174000",
    "theme": "dark",
    "notifications": true,
    "createdAt": "2025-01-01T00:00:00Z",
    "updatedAt": "2025-01-21T11:00:00Z"
  }
]
```

<Note>
  Upsert 需要表上存在唯一约束（例如主键或唯一索引）。冲突解决方式基于该约束。
</Note>

***

## 调用 RPC 函数

通过 PostgREST RPC 执行数据库函数。支持所有 HTTP 方法（GET、POST、PUT、PATCH、DELETE）。

```
POST /api/database/rpc/{functionName}
```

### 示例

```bash theme={null}
# Call a function with parameters
curl -X POST "https://your-app.insforge.app/api/database/rpc/get_user_stats" \
  -H "Authorization: Bearer your-jwt-token" \
  -H "Content-Type: application/json" \
  -d '{"user_id": "123e4567-e89b-12d3-a456-426614174000"}'

# Call a function without parameters
curl -X POST "https://your-app.insforge.app/api/database/rpc/get_total_count" \
  -H "Authorization: Bearer your-jwt-token"
```

### 响应

```json theme={null}
{
  "total_posts": 42,
  "total_comments": 128,
  "last_activity": "2025-01-15T10:30:00Z"
}
```

***

## 管理员接口

以下接口需要管理员身份验证。

### 管理员接口的请求头

```bash theme={null}
Authorization: Bearer admin-jwt-token-Or-API-Key
Content-Type: application/json
```

***

## 列出数据库函数

获取 public 模式下的所有数据库函数。**需要管理员身份验证。**

```
GET /api/database/functions
```

### 示例

```bash theme={null}
curl "https://your-app.insforge.app/api/database/functions" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key"
```

### 响应

```json theme={null}
[
  {
    "name": "get_user_stats",
    "schema": "public",
    "language": "plpgsql",
    "returnType": "json",
    "arguments": "user_id uuid",
    "definition": "BEGIN ... END;"
  }
]
```

***

## 列出数据库索引

获取所有数据库索引。**需要管理员身份验证。**

```
GET /api/database/indexes
```

### 示例

```bash theme={null}
curl "https://your-app.insforge.app/api/database/indexes" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key"
```

### 响应

```json theme={null}
[
  {
    "name": "posts_pkey",
    "tableName": "posts",
    "columns": ["id"],
    "isUnique": true,
    "isPrimary": true,
    "definition": "CREATE UNIQUE INDEX posts_pkey ON public.posts USING btree (id)"
  }
]
```

***

## 列出 RLS 策略

获取所有行级安全（Row Level Security）策略。**需要管理员身份验证。**

```
GET /api/database/policies
```

### 示例

```bash theme={null}
curl "https://your-app.insforge.app/api/database/policies" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key"
```

### 响应

```json theme={null}
[
  {
    "name": "Users can view own posts",
    "tableName": "posts",
    "command": "SELECT",
    "roles": ["authenticated"],
    "using": "(auth.uid() = user_id)",
    "withCheck": null
  }
]
```

***

## 列出数据库触发器

获取所有数据库触发器。**需要管理员身份验证。**

```
GET /api/database/triggers
```

### 示例

```bash theme={null}
curl "https://your-app.insforge.app/api/database/triggers" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key"
```

### 响应

```json theme={null}
[
  {
    "name": "update_timestamp",
    "tableName": "posts",
    "timing": "BEFORE",
    "events": ["UPDATE"],
    "functionName": "update_updated_at_column",
    "enabled": true
  }
]
```

***

## 列出数据库迁移

获取成功执行的自定义迁移历史记录。InsForge 会将这些记录存储在 `system.custom_migrations` 中，但该接口只返回迁移的元数据和已执行的语句。**需要管理员身份验证。**

```
GET /api/database/migrations
```

### 示例

```bash theme={null}
curl "https://your-app.insforge.app/api/database/migrations" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key"
```

### 响应

```json theme={null}
{
  "migrations": [
    {
      "version": "20260416170500",
      "name": "create-posts-table",
      "statements": [
        "CREATE TABLE posts (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), title TEXT NOT NULL);"
      ],
      "createdAt": "2026-04-16T17:05:00.000Z"
    }
  ]
}
```

***

## 创建数据库迁移

创建并立即对数据库执行一次自定义迁移。只有当每条语句都执行成功时，该迁移才会被记录。**需要管理员身份验证。**

```
POST /api/database/migrations
```

### 请求体

| 字段        | 类型     | 是否必需 | 描述                                                                                                                                 |
| --------- | ------ | ---- | ---------------------------------------------------------------------------------------------------------------------------------- |
| `version` | string | 是    | 数字形式的迁移版本号（最多 64 位）。可以是 Drizzle 风格的顺序前缀（例如 `0001`），也可以是 `YYYYMMDDHHmmss` 格式的时间戳。版本号按数字大小比较。InsForge CLI 会为本地迁移文件名生成 14 位的 UTC 时间戳。 |
| `name`    | string | 是    | 迁移名称，只能使用小写字母、数字和连字符                                                                                                               |
| `sql`     | string | 是    | 要解析并执行的 SQL 文本                                                                                                                     |

### 示例

```bash theme={null}
curl -X POST "https://your-app.insforge.app/api/database/migrations" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key" \
  -H "Content-Type: application/json" \
  -d '{
    "version": "20260416170500",
    "name": "create-posts-table",
    "sql": "CREATE TABLE posts (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), title TEXT NOT NULL);"
  }'
```

### 响应

```json theme={null}
{
  "version": "20260416170500",
  "name": "create-posts-table",
  "statements": [
    "CREATE TABLE posts (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), title TEXT NOT NULL);"
  ],
  "createdAt": "2026-04-16T17:05:00.000Z",
  "message": "Migration executed successfully"
}
```

<Warning>
  请勿在自定义迁移中包含 `BEGIN`、`COMMIT` 或 `ROLLBACK`。InsForge 会在自己的事务中执行该迁移。
</Warning>

***

## 执行原始 SQL（严格模式）

使用严格清理规则执行原始 SQL 查询。禁止访问系统表和 auth.users。**需要管理员身份验证。**

```
POST /api/database/advance/rawsql
```

### 请求体

| 字段       | 类型     | 是否必需 | 描述           |
| -------- | ------ | ---- | ------------ |
| `query`  | string | 是    | 要执行的 SQL 查询  |
| `params` | array  | 否    | 用于参数化查询的查询参数 |

### 示例

```bash theme={null}
curl -X POST "https://your-app.insforge.app/api/database/advance/rawsql" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT * FROM posts WHERE published = $1",
    "params": [true]
  }'
```

### 响应

```json theme={null}
{
  "rows": [
    {
      "id": "248373e1-0aea-45ce-8844-5ef259203749",
      "title": "My Post",
      "published": true
    }
  ],
  "rowCount": 1,
  "command": "SELECT"
}
```

***

## 执行原始 SQL（宽松模式）

使用宽松清理规则执行原始 SQL 查询。允许对系统表执行 SELECT 和 INSERT。**请谨慎使用。需要管理员身份验证。**

```
POST /api/database/advance/rawsql/unrestricted
```

### 请求体

| 字段       | 类型     | 是否必需 | 描述           |
| -------- | ------ | ---- | ------------ |
| `query`  | string | 是    | 要执行的 SQL 查询  |
| `params` | array  | 否    | 用于参数化查询的查询参数 |

### 示例

```bash theme={null}
curl -X POST "https://your-app.insforge.app/api/database/advance/rawsql/unrestricted" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT * FROM auth.users LIMIT 10"
  }'
```

### 响应

```json theme={null}
{
  "rows": [...],
  "rowCount": 10,
  "command": "SELECT"
}
```

<Warning>
  此接口限制较为宽松，可以访问系统表。请仅在必要时使用，并确保具备适当的授权。
</Warning>

***

## 导出数据库

以 SQL 或 JSON 格式导出数据库结构和/或数据。**需要管理员身份验证。**

```
POST /api/database/advance/export
```

### 请求体

| 字段                 | 类型      | 是否必需 | 默认值   | 描述                   |
| ------------------ | ------- | ---- | ----- | -------------------- |
| `tables`           | array   | 否    | all   | 要导出的表列表              |
| `format`           | string  | 否    | sql   | 导出格式（`sql` 或 `json`） |
| `includeData`      | boolean | 否    | true  | 包含表数据                |
| `includeFunctions` | boolean | 否    | false | 包含数据库函数              |
| `includeSequences` | boolean | 否    | false | 包含序列                 |
| `includeViews`     | boolean | 否    | false | 包含视图                 |
| `rowLimit`         | integer | 否    | -     | 每个表的最大行数             |

### 示例

```bash theme={null}
curl -X POST "https://your-app.insforge.app/api/database/advance/export" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key" \
  -H "Content-Type: application/json" \
  -d '{
    "tables": ["posts", "comments"],
    "format": "sql",
    "includeData": true,
    "rowLimit": 1000
  }'
```

### 响应

```json theme={null}
{
  "format": "sql",
  "content": "CREATE TABLE posts (...); INSERT INTO posts VALUES (...);",
  "tables": ["posts", "comments"]
}
```

***

## 导入数据库

从 SQL 文件导入数据库。**需要管理员身份验证。**

```
POST /api/database/advance/import
```

### 请求（multipart/form-data）

| 字段         | 类型      | 是否必需 | 默认值   | 描述          |
| ---------- | ------- | ---- | ----- | ----------- |
| `file`     | file    | 是    | -     | 要导入的 SQL 文件 |
| `truncate` | boolean | 否    | false | 导入前清空现有表    |

### 示例

```bash theme={null}
curl -X POST "https://your-app.insforge.app/api/database/advance/import" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key" \
  -F "file=@backup.sql" \
  -F "truncate=false"
```

### 响应

```json theme={null}
{
  "filename": "backup.sql",
  "fileSize": 102400,
  "tables": ["posts", "comments", "users"],
  "rowsImported": 1500
}
```

***

## 批量插入或更新

从 CSV 或 JSON 文件批量插入或更新数据。**需要管理员身份验证。**

```
POST /api/database/advance/bulk-upsert
```

### 请求（multipart/form-data）

| 字段          | 类型     | 是否必需 | 描述                  |
| ----------- | ------ | ---- | ------------------- |
| `file`      | file   | 是    | 包含数据的 CSV 或 JSON 文件 |
| `table`     | string | 是    | 目标表名                |
| `upsertKey` | string | 否    | 用于冲突解决的列名           |

### 示例

```bash theme={null}
# Import from CSV
curl -X POST "https://your-app.insforge.app/api/database/advance/bulk-upsert" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key" \
  -F "file=@data.csv" \
  -F "table=posts" \
  -F "upsertKey=id"

# Import from JSON
curl -X POST "https://your-app.insforge.app/api/database/advance/bulk-upsert" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key" \
  -F "file=@data.json" \
  -F "table=posts"
```

### 响应

```json theme={null}
{
  "rowsAffected": 150,
  "totalRecords": 150,
  "table": "posts"
}
```