turborfq/bible-local/api.md

API Reference

All endpoints are under the /api prefix. Responses are JSON unless noted. Authentication is required for all endpoints except /api/login, /api/session, and /api/logout.

---

Authentication

POST /api/login

Test MariaDB credentials and store them in the session. The database host and port are taken from server-side environment variables (DB_HOST, DB_PORT) — they are not part of the request.

Request body (JSON)

{ "user": "dbuser", "pass": "secret" }

Response

{ "ok": true }

On failure: HTTP 401 with { "ok": false, "error": "..." }.

---

GET /api/session

Returns current session authentication status. Does not require authentication.

Response

{ "authenticated": true, "user": "dbuser" }

---

POST /api/logout

Destroys the server-side session and clears the session cookie.

Response

{ "ok": true }

---

Schema Tree

GET /api/tree

Returns the list of schemas (databases) and their tables that the session user can access. System schemas (information_schema, mysql, performance_schema, sys) are excluded.

Response

[
  {
    "name": "mydb",
    "tables": ["orders", "products", "customers"]
  }
]

---

Table Metadata

GET /api/table/meta

Returns column definitions, primary key info, and foreign key relationships for one table.

Query params

Param	Required	Description
schema	yes	Database name
table	yes	Table name

Response

{
  "columns": [
    {
      "COLUMN_NAME": "id",
      "DATA_TYPE": "int",
      "COLUMN_TYPE": "int(11)",
      "COLUMN_KEY": "PRI",
      "IS_NULLABLE": false,
      "COLUMN_DEFAULT": null,
      "HAS_DEFAULT": false,
      "EXTRA": "auto_increment",
      "COLUMN_COMMENT": "",
      "IS_AUTO_INCREMENT": true,
      "ORDINAL_POSITION": 1,
      "IS_REQUIRED": false,
      "EDITOR_TYPE": "number",
      "IS_FOREIGN_KEY": false,
      "FOREIGN_KEY": null
    }
  ],
  "primaryKey": ["id"],
  "totalColumns": 5,
  "foreignKeys": [
    {
      "COLUMN_NAME": "customer_id",
      "REFERENCED_TABLE_SCHEMA": "mydb",
      "REFERENCED_TABLE_NAME": "customers",
      "REFERENCED_COLUMN_NAME": "id"
    }
  ]
}

EDITOR_TYPE values: "number", "datetime", "time", "tickCross", "input".

---

Data Operations

POST /api/table/data

Paginated, filtered, optionally sorted SELECT.

Request body (JSON)

Field	Required	Description
schema	yes	Database name
table	yes	Table name
page	no	Page number (default 1)
size	no	Rows per page (default 50, max 5000)
filters	no	Array of { "field": "col", "value": "val" } — matched with LIKE %val%
sort	no	Array with one element: [{ "field": "col", "dir": "ASC" }]

Response

{ "data": [ {…}, {…} ], "total": 342, "last_page": 7, "current_page": 1 }

---

POST /api/table/insert

Insert a new row. auto_increment columns are ignored even if present.

Request body

{ "schema": "mydb", "table": "orders", "row": { "name": "Test", "qty": 5 } }

Response

{ "inserted": true, "id": "123" }

On failure: HTTP 400 with { "error": true, "message": "..." }.

---

POST /api/table/update

Update a row. The primary key is read from table metadata on the server; row must include PK field(s).

Request body

{ "schema": "mydb", "table": "orders", "row": { "id": 123, "name": "Updated", "qty": 10 } }

Response

{ "updated": 1 }

On failure: HTTP 400 with { "error": true, "message": "..." }.

---

POST /api/table/delete

Delete a single row. row must include PK field(s).

Request body

{ "schema": "mydb", "table": "orders", "row": { "id": 123 } }

Response

{ "deleted": 1 }

---

POST /api/table/delete-batch

Delete multiple rows in a single transaction. For single-column PKs uses batched WHERE … IN (…) (batch size 500). For composite PKs deletes row by row within the same transaction.

Request body

{
  "schema": "mydb",
  "table": "orders",
  "rows": [ { "id": 1 }, { "id": 2 }, { "id": 5 } ]
}

Response

{ "deleted": 3, "errors": 0, "errorMessages": [] }

---

CSV

POST /api/table/import-csv

Bulk-insert pre-parsed rows into the target table. CSV parsing is done on the frontend; the backend receives an array of row objects. Runs inside a single transaction — if a critical error occurs the whole import is rolled back; per-row errors are collected and reported without stopping the import.

Request body (JSON)

{ "schema": "mydb", "table": "orders", "rows": [ { "name": "A", "qty": 1 }, … ] }

Response

{
  "inserted": 98,
  "errors": 2,
  "errorMessages": ["Row CSV #5: …"],
  "failedRows": [ { "row": {…}, "error": "…", "line": 5 } ]
}

On critical failure: HTTP 500 with { "error": "…", "inserted": 0, "errors": 1 }.

---

POST /api/table/export-csv

Export table data as a CSV string. Active filters and sort are applied. Only the columns listed in the columns field are exported. CSV uses ; as delimiter.

Request body (JSON)

Field	Required	Description
schema	yes	Database name
table	yes	Table name
columns	yes	Array of column objects (same shape as from /api/table/meta)
filters	no	Same format as /api/table/data
sort	no	Same format as /api/table/data

Response

{ "csv": "col1;col2\r\nval1;val2\r\n…", "rowCount": 342 }

---

Foreign Key Values

GET /api/fk-values

Returns distinct values from a referenced column for autocomplete. Without search: returns up to 1000 values. With search: returns all matching values (no limit).

Query params

Param	Required	Description
schema	yes	Referenced schema
table	yes	Referenced table
column	yes	Referenced column
search	no	Filter string — matched with LIKE %search%

Response

{ "values": ["foo", "bar"], "total": 500, "loaded": 42 }

total — total distinct count before filtering. loaded — count of values returned.

---

Backup

GET /api/backup/database/{name}

Download a gzip-compressed mysqldump of a single database.

URL param: name — database name.

Response: application/gzip attachment, filename YYYY-MM-DD-{name}.sql.gz.

---

GET /api/backup/all

Download a gzip-compressed mysqldump of all databases the user can access (system schemas excluded).

Response: application/gzip attachment, filename YYYY-MM-DD-backup_all.sql.gz.