Database API

Database table management and record operations with PostgREST-style querying

The Database API provides comprehensive table schema management and CRUD operations for records with powerful filtering, sorting, and pagination.

Base URL

All database endpoints are prefixed with /api/database

---

Table Management

List Tables

GET /api/database/tables

curl https://your-app.region.insforge.app/api/database/tables \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

Retrieve list of all table names (admin only).

Response

[
  "posts",
  "comments",
  "categories"
]

Create Table

POST /api/database/tables

curl -X POST https://your-app.region.insforge.app/api/database/tables \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "tableName": "posts",
    "rlsEnabled": false,
    "columns": [
      {
        "name": "title",
        "type": "string",
        "nullable": false
      },
      {
        "name": "content",
        "type": "string",
        "nullable": true
      },
      {
        "name": "published",
        "type": "boolean",
        "nullable": false,
        "defaultValue": "false"
      },
      {
        "name": "userId",
        "type": "uuid",
        "nullable": false,
        "foreignKey": {
          "table": "auth.users",
          "column": "id",
          "onDelete": "CASCADE"
        }
      }
    ]
  }'

Create a new table with columns and constraints (admin only).

Body Parameters

tableName · string · required

Name of the table to create

rlsEnabled · boolean · default: "false"

Enable Row Level Security on the table

columns · array · required

Array of column definitions

Show Column properties

name · string · required

Column name

type · string · required

Data type: string, datetime, integer, float, boolean, uuid, json, file

nullable · boolean · required

Whether column allows NULL values

unique · boolean

Whether column values must be unique

defaultValue · string

Default value for the column

foreignKey · object

Foreign key constraint

Show Foreign key properties

table · string · required

Referenced table name

column · string · required

Referenced column name

onDelete · string · default: "NO ACTION"

Action on delete: CASCADE, SET NULL, NO ACTION, RESTRICT

Response

{
  "message": "Table created successfully",
  "tableName": "posts"
}

Get Table Schema

GET /api/database/tables/{tableName}/schema

curl https://your-app.region.insforge.app/api/database/tables/posts/schema \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

Retrieve complete schema information for a table (admin only).

Path Parameters

tableName · string · required

Name of the table

Response

{
  "tableName": "posts",
  "columns": [
    {
      "name": "id",
      "type": "uuid",
      "nullable": false,
      "unique": true,
      "default": "gen_random_uuid()",
      "isPrimaryKey": true,
      "foreignKey": null
    },
    {
      "name": "title",
      "type": "string",
      "nullable": false,
      "unique": false,
      "default": null,
      "isPrimaryKey": false,
      "foreignKey": null
    },
    {
      "name": "userId",
      "type": "uuid",
      "nullable": false,
      "unique": false,
      "default": null,
      "isPrimaryKey": false,
      "foreignKey": {
        "table": "auth.users",
        "column": "id",
        "on_delete": "CASCADE"
      }
    },
    {
      "name": "createdAt",
      "type": "datetime",
      "nullable": false,
      "unique": false,
      "default": "now()",
      "isPrimaryKey": false,
      "foreignKey": null
    }
  ]
}

Update Table Schema

PATCH /api/database/tables/{tableName}/schema

curl -X PATCH https://your-app.region.insforge.app/api/database/tables/posts/schema \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "addColumns": [
      {
        "columnName": "views",
        "type": "integer",
        "isNullable": false,
        "defaultValue": "0"
      }
    ],
    "updateColumns": [
      {
        "columnName": "title",
        "newColumnName": "post_title"
      }
    ],
    "addForeignKeys": [
      {
        "columnName": "categoryId",
        "foreignKey": {
          "referenceTable": "categories",
          "referenceColumn": "id",
          "onDelete": "SET NULL"
        }
      }
    ]
  }'

Modify table schema by adding/dropping/updating columns and constraints (admin only).

Path Parameters

tableName · string · required

Name of the table to modify

Body Parameters

addColumns · array

Add new columns

Show properties

columnName · string · required

Name of the new column

type · string · required

Data type: string, integer, float, boolean, datetime, uuid, json, file

isNullable · boolean · default: "true"

Whether column allows NULL

isUnique · boolean · default: "false"

Whether values must be unique

defaultValue · string

Default value

dropColumns · array

Column names to drop

updateColumns · array

Modify existing columns

Show properties

columnName · string · required

Current column name

newColumnName · string

New name for column

defaultValue · string

New default value

addForeignKeys · array

Add foreign key constraints

dropForeignKeys · array

Column names with foreign keys to drop

renameTable · object

Rename the table

Show properties

newTableName · string · required

New table name

Response

{
  "message": "Table schema updated successfully",
  "tableName": "posts",
  "operations": [
    "added 1 columns",
    "renamed 1 columns",
    "added 1 foreign keys"
  ]
}

Delete Table

DELETE /api/database/tables/{tableName}

curl -X DELETE https://your-app.region.insforge.app/api/database/tables/posts \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

Permanently delete a table and all its data (admin only).

Path Parameters

tableName · string · required

Name of the table to delete

Response

{
  "message": "Table deleted successfully",
  "tableName": "posts"
}

---

Record Operations

Query Records

GET /api/database/records/{tableName}

# Basic query
curl https://your-app.region.insforge.app/api/database/records/posts \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

# With filters and sorting
curl "https://your-app.region.insforge.app/api/database/records/posts?status=eq.published&limit=10&order=createdAt.desc&select=id,title,content" \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

Query records with PostgREST-style filtering, sorting, and pagination.

Path Parameters

tableName · string · required

Name of the table to query

Query Parameters

limit · integer · default: "100"

Maximum number of records (1-1000)

offset · integer · default: "0"

Number of records to skip

order · string

Sort order (e.g., “createdAt.desc”, “name.asc”)

select · string

Comma-separated columns to return

field · string

PostgREST-style filters (e.g., “status=eq.active”, “age=gt.18”)

Filter operators:

• eq - equals
• neq - not equals
• gt - greater than
• gte - greater than or equal
• lt - less than
• lte - less than or equal
• like - pattern match
• ilike - case-insensitive pattern match
• in - in list
• is - is null/not null

Response Headers

X-Total-Count · integer

Total number of matching records

Content-Range · string

Range of returned records (e.g., “0-99/1234”)

Response

[
  {
    "id": "248373e1-0aea-45ce-8844-5ef259203749",
    "title": "Getting Started with InsForge",
    "content": "This is a guide to help you get started...",
    "published": true,
    "createdAt": "2025-07-18T05:37:24.338Z",
    "updatedAt": "2025-07-18T05:37:24.338Z"
  },
  {
    "id": "348373e1-0aea-45ce-8844-5ef259203750",
    "title": "Advanced Database Queries",
    "content": "Learn how to write complex queries...",
    "published": true,
    "createdAt": "2025-07-19T08:15:10.123Z",
    "updatedAt": "2025-07-19T08:15:10.123Z"
  }
]

Create Records

POST /api/database/records/{tableName}

# Create single record
curl -X POST https://your-app.region.insforge.app/api/database/records/posts \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '[{
    "title": "My First Post",
    "content": "Hello world! This is my first post.",
    "published": true
  }]'

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

Create one or more records. Request body must be an array.

Path Parameters

tableName · string · required

Name of the table

Headers

Prefer · string

Set to return=representation to return created records (otherwise returns empty array)

Body

Array of objects with field values matching table schema.

Response

Without Prefer header

[]

Update Records

PATCH /api/database/records/{tableName}

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

# Update with filter
curl -X PATCH "https://your-app.region.insforge.app/api/database/records/posts?published=eq.false" \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "published": true
  }'

Update records matching query filters.

Path Parameters

tableName · string · required

Name of the table

Query Parameters

Use PostgREST-style filters (e.g., id=eq.123, status=eq.draft)

Headers

Prefer · string

Set to return=representation to return updated records

Body

Object with fields to update.

Response

With Prefer: return=representation

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

Delete Records

DELETE /api/database/records/{tableName}

# Delete by ID
curl -X DELETE "https://your-app.region.insforge.app/api/database/records/posts?id=eq.248373e1-0aea-45ce-8844-5ef259203749" \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

# Delete with filter
curl -X DELETE "https://your-app.region.insforge.app/api/database/records/posts?published=eq.false" \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  -H "Prefer: return=representation"

Delete records matching query filters.

Path Parameters

tableName · string · required

Name of the table

Query Parameters

Use PostgREST-style filters to select records to delete.

Headers

Prefer · string

Set to return=representation to return deleted records

Response

• Without Prefer header: 204 No Content
• With Prefer: return=representation: Array of deleted records

With Prefer: return=representation

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