322 lines
6.7 KiB
Markdown
322 lines
6.7 KiB
Markdown
# 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)**
|
|
|
|
```json
|
|
{ "user": "dbuser", "pass": "secret" }
|
|
```
|
|
|
|
**Response**
|
|
|
|
```json
|
|
{ "ok": true }
|
|
```
|
|
|
|
On failure: HTTP 401 with `{ "ok": false, "error": "..." }`.
|
|
|
|
---
|
|
|
|
### `GET /api/session`
|
|
|
|
Returns current session authentication status. Does not require authentication.
|
|
|
|
**Response**
|
|
|
|
```json
|
|
{ "authenticated": true, "user": "dbuser" }
|
|
```
|
|
|
|
---
|
|
|
|
### `POST /api/logout`
|
|
|
|
Destroys the server-side session and clears the session cookie.
|
|
|
|
**Response**
|
|
|
|
```json
|
|
{ "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**
|
|
|
|
```json
|
|
[
|
|
{
|
|
"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**
|
|
|
|
```json
|
|
{
|
|
"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**
|
|
|
|
```json
|
|
{ "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**
|
|
|
|
```json
|
|
{ "schema": "mydb", "table": "orders", "row": { "name": "Test", "qty": 5 } }
|
|
```
|
|
|
|
**Response**
|
|
|
|
```json
|
|
{ "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**
|
|
|
|
```json
|
|
{ "schema": "mydb", "table": "orders", "row": { "id": 123, "name": "Updated", "qty": 10 } }
|
|
```
|
|
|
|
**Response**
|
|
|
|
```json
|
|
{ "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**
|
|
|
|
```json
|
|
{ "schema": "mydb", "table": "orders", "row": { "id": 123 } }
|
|
```
|
|
|
|
**Response**
|
|
|
|
```json
|
|
{ "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**
|
|
|
|
```json
|
|
{
|
|
"schema": "mydb",
|
|
"table": "orders",
|
|
"rows": [ { "id": 1 }, { "id": 2 }, { "id": 5 } ]
|
|
}
|
|
```
|
|
|
|
**Response**
|
|
|
|
```json
|
|
{ "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)**
|
|
|
|
```json
|
|
{ "schema": "mydb", "table": "orders", "rows": [ { "name": "A", "qty": 1 }, … ] }
|
|
```
|
|
|
|
**Response**
|
|
|
|
```json
|
|
{
|
|
"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**
|
|
|
|
```json
|
|
{ "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**
|
|
|
|
```json
|
|
{ "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`.
|