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 POST /api/rpc/:procedure_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 }

5. Service Invocation / Stored Procedures (POST /api/rpc)

To invoke a stored procedure over HTTP, you can use the /api/rpc/:procedure_name endpoint. This allows you to expose complex business logic and multi-statement transactions to the web without writing a custom backend.

Send a POST request with a JSON object payload. The keys in the JSON object must match the input parameter names of the stored procedure.

# Assuming a procedure: CREATE PROCEDURE update_inventory(product_id INT, quantity INT, OUT success BOOLEAN)
curl -X POST http://127.0.0.1:8080/api/rpc/update_inventory \
  -H "Content-Type: application/json" \
  -d '{"product_id": 123, "quantity": 10}'

Response: The response is a JSON object containing the values of the OUT or INOUT parameters returned by the procedure.

{
  "success": true
}

Accessing HTTP Metadata from Procedures

When a procedure is invoked via /api/rpc/, it can read the incoming HTTP request headers using the built-in oxibase::get_http_header('header_name') SQL function. This is useful for passing authentication tokens, user agents, or custom metadata securely into your business logic.

CREATE PROCEDURE get_my_ip(OUT ip_address TEXT)
LANGUAGE plsql AS $$
BEGIN
    -- Read a header injected by a reverse proxy
    ip_address = get_http_header('x-forwarded-for');
END;
$$;

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": "..."}.