Using the Auto-API JSON Layer

Oxibase features a zero-configuration “Auto-API” layer. When you run Oxibase with the embedded HTTP server enabled, every table and view in your database is automatically exposed as a RESTful JSON endpoint.

This allows frontend applications or external services to perform full CRUD (Create, Read, Update, Delete) operations directly against the database via HTTP without writing any backend routing logic.

Starting the Server

Start Oxibase with the serve command:

oxibase serve -d file:///path/to/my_db --port 8080

Endpoint Structure

The Auto-API layer exposes your tables under the /api/ prefix.

[HTTP METHOD] /api/:table_name

1. Read Data (GET)

To fetch data from a table, make a GET request to the table’s endpoint.

curl http://127.0.0.1:8080/api/users

Response:

[
  { "id": 1, "name": "Alice", "role": "Admin" },
  { "id": 2, "name": "Bob", "role": "Editor" }
]

Query Parameters

The GET endpoint supports several powerful query parameters to shape your data:

select: Choose specific columns to return (comma-separated).
- Example: /api/users?select=id,name
limit & offset: Paginate your results.
- Example: /api/users?limit=10&offset=20
order: Sort the results. Suffix the column with .asc or .desc.
- Example: /api/users?order=id.desc,name.asc
Filtering (col=eq.val): Filter rows by exact match using the eq. (equals) operator.
- Example: /api/users?role=eq.Admin

Combined Example:

# Get the names of the top 5 admins, ordered alphabetically
curl "http://127.0.0.1:8080/api/users?role=eq.Admin&select=name&order=name.asc&limit=5"

2. Insert Data (POST)

To insert new rows into a table, send a POST request with a JSON object payload representing the row.

curl -X POST http://127.0.0.1:8080/api/users \
  -H "Content-Type: application/json" \
  -d '{"id": 3, "name": "Charlie", "role": "Viewer"}'

Response:

{ "rows_affected": 1 }

Note: Oxibase automatically maps standard JSON types (strings, numbers, booleans, nulls) to their corresponding internal SQL data types.

3. Update Data (PATCH)

To modify existing rows, send a PATCH request. You must include a filter in the query string (e.g., ?id=eq.X) to target specific rows. The JSON payload should contain only the columns you wish to update.

curl -X PATCH "http://127.0.0.1:8080/api/users?id=eq.3" \
  -H "Content-Type: application/json" \
  -d '{"role": "Editor"}'

Response:

{ "rows_affected": 1 }

4. Delete Data (DELETE)

To remove rows from a table, send a DELETE request. Like the PATCH method, you must include a filter in the query string to prevent accidental full-table deletions.

curl -X DELETE "http://127.0.0.1:8080/api/users?id=eq.3"

Response:

{ "rows_affected": 1 }

Security & Architecture Notes

Zero Copy Engine: The JSON API layer reads rows directly from the MVCC storage engine and streams the serialized JSON to the client, ensuring high throughput and minimal memory overhead.
Transactions: Every API request (POST, PATCH, DELETE) is implicitly wrapped in an ACID-compliant transaction. If an error occurs during the operation, it is completely rolled back.
Errors: Invalid queries, type mismatches, or missing tables will return appropriate HTTP status codes (e.g., 404 Not Found for missing tables, 400 Bad Request for missing filters on updates/deletes, 500 Internal Server Error for SQL execution errors) along with a JSON body describing the error {"error": "..."}.