Database API Reference - InsForge Docs

Overview

The Database API provides PostgREST-style CRUD operations for your database tables. All requests require authentication headers.

Headers

Authorization: Bearer your-jwt-token-or-anon-key
Content-Type: application/json

Query Records

Retrieve records from a table with filtering, sorting, and pagination.

GET /api/database/records/{tableName}

Query Parameters

Parameter	Type	Description
`limit`	integer	Maximum records to return (1-1000, default: 100)
`offset`	integer	Records to skip for pagination (default: 0)
`order`	string	Sort order (e.g., `createdAt.desc`, `name.asc`)
`select`	string	Comma-separated columns to return
`{field}`	string	PostgREST filter (e.g., `status=eq.active`)

Filter Operators

Operator	Description	Example
`eq`	Equal	`status=eq.active`
`neq`	Not equal	`status=neq.deleted`
`gt`	Greater than	`age=gt.18`
`gte`	Greater than or equal	`price=gte.100`
`lt`	Less than	`quantity=lt.10`
`lte`	Less than or equal	`score=lte.50`
`like`	Pattern match (case-sensitive)	`name=like.john`
`ilike`	Pattern match (case-insensitive)	`email=ilike.*@gmail.com`
`in`	In list	`status=in.(active,pending)`
`is`	Is null/true/false	`deleted_at=is.null`

Example

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

Response

[
  {
    "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"
  }
]

Response Headers

Header	Description
`X-Total-Count`	Total records matching the query
`Content-Range`	Range of records returned (e.g., `0-99/1234`)

Create Records

Create one or more records in a table.

POST /api/database/records/{tableName}

Important: Request body MUST be an array, even for single records.

Headers

Header	Value	Description
`Prefer`	`return=representation`	Include to return created records

Example

# 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"}
  ]'

Response

Without Prefer header:

[]

With Prefer: return=representation:

[
  {
    "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"
  }
]

Update Records

Update records matching query filters.

PATCH /api/database/records/{tableName}

Query Parameters

Use filter operators to specify which records to update.

Headers

Header	Value	Description
`Prefer`	`return=representation`	Include to return updated records

Example

# 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"}'

Response

[
  {
    "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 Records

Delete records matching query filters.

DELETE /api/database/records/{tableName}

Query Parameters

Use filter operators to specify which records to delete.

Headers

Header	Value	Description
`Prefer`	`return=representation`	Include to return deleted records

Example

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

Response

Without Prefer header: 204 No Content With Prefer: return=representation:

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

Error Responses

Table Not Found (404)

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

Invalid Query (400)

{
  "error": "INVALID_QUERY",
  "message": "Invalid filter syntax",
  "statusCode": 400,
  "nextActions": "Check PostgREST filter documentation"
}

Validation Error (400)

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

Examples

Pagination

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

Complex Filters

# 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 (Insert or Update)

Insert a record or update it if a conflict occurs on a unique constraint.

POST /api/database/records/{tableName}

Headers

Header	Value	Description
`Prefer`	`resolution=merge-duplicates`	Update existing record on conflict
`Prefer`	`resolution=ignore-duplicates`	Ignore insert if record exists

Example

# 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
  }]'

Response

[
  {
    "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"
  }
]

Upsert requires a unique constraint (e.g., primary key or unique index) on the table. The conflict resolution is based on this constraint.

Call RPC Function

Execute a database function via PostgREST RPC. Supports all HTTP methods (GET, POST, PUT, PATCH, DELETE).

POST /api/database/rpc/{functionName}

Example

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

Response

{
  "total_posts": 42,
  "total_comments": 128,
  "last_activity": "2025-01-15T10:30:00Z"
}

Admin Endpoints

The following endpoints require admin authentication.

Headers for Admin Endpoints

Authorization: Bearer admin-jwt-token-Or-API-Key
Content-Type: application/json

List Database Functions

Get all database functions in the public schema. Requires admin authentication.

GET /api/database/functions

Example

curl "https://your-app.insforge.app/api/database/functions" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key"

Response

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

List Database Indexes

Get all database indexes. Requires admin authentication.

GET /api/database/indexes

Example

curl "https://your-app.insforge.app/api/database/indexes" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key"

Response

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

List RLS Policies

Get all Row Level Security policies. Requires admin authentication.

GET /api/database/policies

Example

curl "https://your-app.insforge.app/api/database/policies" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key"

Response

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

List Database Triggers

Get all database triggers. Requires admin authentication.

GET /api/database/triggers

Example

curl "https://your-app.insforge.app/api/database/triggers" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key"

Response

[
  {
    "name": "update_timestamp",
    "tableName": "posts",
    "timing": "BEFORE",
    "events": ["UPDATE"],
    "functionName": "update_updated_at_column",
    "enabled": true
  }
]

List Database Migrations

Get the history of successful custom migrations. InsForge stores these in system.custom_migrations, but the endpoint returns only the migration metadata and executed statements. Requires admin authentication.

GET /api/database/migrations

Example

curl "https://your-app.insforge.app/api/database/migrations" \
  -H "Authorization: Bearer admin-jwt-token-Or-API-Key"

Response

{
  "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"
    }
  ]
}

Create Database Migration

Create and immediately execute a custom migration against the database. The migration is recorded only if every statement succeeds. Requires admin authentication.

POST /api/database/migrations

Request Body

Field	Type	Required	Description
`version`	string	Yes	Numeric migration version (up to 64 digits). Accepts Drizzle-style sequential prefixes (e.g. `0001`) or a `YYYYMMDDHHmmss` timestamp. Versions are compared numerically. The InsForge CLI generates 14-digit UTC timestamps for local migration filenames.
`name`	string	Yes	Migration name using lowercase letters, numbers, and hyphens only
`sql`	string	Yes	SQL text to parse and execute

Example

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);"
  }'

Response

{
  "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"
}

Do not include BEGIN, COMMIT, or ROLLBACK inside a custom migration. InsForge executes the migration inside its own transaction.

Execute Raw SQL (Strict Mode)

Execute raw SQL query with strict sanitization. Blocks access to system tables and auth.users. Requires admin authentication.

POST /api/database/advance/rawsql

Request Body

Field	Type	Required	Description
`query`	string	Yes	SQL query to execute
`params`	array	No	Query parameters for parameterized queries

Example

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]
  }'

Response

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

Execute Raw SQL (Relaxed Mode)

Execute raw SQL query with relaxed sanitization. Allows SELECT and INSERT into system tables. Use with caution. Requires admin authentication.

POST /api/database/advance/rawsql/unrestricted

Request Body

Field	Type	Required	Description
`query`	string	Yes	SQL query to execute
`params`	array	No	Query parameters for parameterized queries

Example

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"
  }'

Response

{
  "rows": [...],
  "rowCount": 10,
  "command": "SELECT"
}

This endpoint has relaxed restrictions and can access system tables. Use only when necessary and ensure proper authorization.

Export Database

Export database schema and/or data in SQL or JSON format. Requires admin authentication.

POST /api/database/advance/export

Request Body

Field	Type	Required	Default	Description
`tables`	array	No	all	List of tables to export
`format`	string	No	sql	Export format (`sql` or `json`)
`includeData`	boolean	No	true	Include table data
`includeFunctions`	boolean	No	false	Include database functions
`includeSequences`	boolean	No	false	Include sequences
`includeViews`	boolean	No	false	Include views
`rowLimit`	integer	No	-	Maximum rows per table

Example

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
  }'

Response

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

Import Database

Import database from SQL file. Requires admin authentication.

POST /api/database/advance/import

Request (multipart/form-data)

Field	Type	Required	Default	Description
`file`	file	Yes	-	SQL file to import
`truncate`	boolean	No	false	Truncate existing tables before import

Example

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"

Response

{
  "filename": "backup.sql",
  "fileSize": 102400,
  "tables": ["posts", "comments", "users"],
  "rowsImported": 1500
}

Bulk Upsert

Bulk insert or update data from CSV or JSON file. Requires admin authentication.

POST /api/database/advance/bulk-upsert

Request (multipart/form-data)

Field	Type	Required	Description
`file`	file	Yes	CSV or JSON file containing data
`table`	string	Yes	Target table name
`upsertKey`	string	No	Column name for upsert conflict resolution

Example

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

Response

{
  "rowsAffected": 150,
  "totalRecords": 150,
  "table": "posts"
}

​Overview

​Headers

​Query Records

​Query Parameters

​Filter Operators

​Example

​Response

​Response Headers

​Create Records

​Headers

​Example

​Response

​Update Records

​Query Parameters

​Headers

​Example

​Response

​Delete Records

​Query Parameters

​Headers

​Example

​Response

​Error Responses

​Table Not Found (404)

​Invalid Query (400)

​Validation Error (400)

​Examples

​Pagination

​Complex Filters

​Upsert (Insert or Update)

​Headers

​Example

​Response

​Call RPC Function

​Example

​Response

​Admin Endpoints

​Headers for Admin Endpoints

​List Database Functions

​Example

​Response

​List Database Indexes

​Example

​Response

​List RLS Policies

​Example

​Response

​List Database Triggers

​Example

​Response

​List Database Migrations

​Example

​Response

​Create Database Migration

​Request Body

​Example

​Response

​Execute Raw SQL (Strict Mode)

​Request Body

​Example

​Response

​Execute Raw SQL (Relaxed Mode)

​Request Body

​Example

​Response

​Export Database

​Request Body

​Example

​Response

​Import Database

​Request (multipart/form-data)

​Example

​Response

​Bulk Upsert

​Request (multipart/form-data)

​Example

​Response

Overview

Headers

Query Records

Query Parameters

Filter Operators

Example

Response

Response Headers

Create Records

Headers

Example

Response

Update Records

Query Parameters

Headers

Example

Response

Delete Records

Query Parameters

Headers

Example

Response

Error Responses

Table Not Found (404)

Invalid Query (400)

Validation Error (400)

Examples

Pagination

Complex Filters

Upsert (Insert or Update)

Headers

Example

Response

Call RPC Function

Example

Response

Admin Endpoints

Headers for Admin Endpoints

List Database Functions

Example

Response

List Database Indexes

Example

Response

List RLS Policies

Example

Response

List Database Triggers

Example

Response

List Database Migrations

Example

Response

Create Database Migration

Request Body

Example

Response

Execute Raw SQL (Strict Mode)

Request Body

Example

Response

Execute Raw SQL (Relaxed Mode)

Request Body

Example

Response

Export Database

Request Body

Example

Response

Import Database

Request (multipart/form-data)

Example

Response

Bulk Upsert

Request (multipart/form-data)

Example

Response