Admin UI SQL Client

SQL Client Backend REST Endpoints

Database Exploration

GET /api/v1/persistence/databases
- Purpose: List Databases
- Response: {"databases": ["mydb", "testdb", "analytics", "jansdb"]}

GET /api/v1/persistence/:databaseName/tables
- Purpose: List tables within a specific database/schema.
- Response: {"tables": ["users", "orders", "products"]}

GET /api/v1/persistence/:databaseName/:tableName/columns
- Purpose: List columns for a specific table.
- Response: 
[
    {"name": "id", "type": "INT", "nullable": false, "primaryKey": true},
    {"name": "name", "type": "VARCHAR(255)", "nullable": true, "primaryKey": false}
]

GET /api/v1/persistence/:databaseName/views
- Purpose: List views within a specific database/schema.
- Response: {"views": ["active_users_view", "product_summary_view"]}

GET /api/v1/persistence/:databaseName/:viewName/view-columns
- Purpose: List columns for a specific view, including their names and data types.
- Request Headers/Params: databaseName, viewName (as path parameters).
- Response (200 OK):
JSON
----
[
    {"name": "id", "type": "INT", "nullable": false},
    {"name": "username", "type": "VARCHAR(255)", "nullable": false},
    {"name": "email", "type": "VARCHAR(255)", "nullable": true}
]
(The exact properties like isPrimaryKey or isAutoIncrement might not be directly applicable to view columns, as views are derived from tables. Focus on name, type, and nullable.)

GET /api/v1/persistence/:databaseName/stored-procedures
- Purpose: List stored procedures within a specific database/schema.
- Response: {"stored_procedure": ["get_user_by_id", "update_product_stock"]}

GET /api/v1/persistence/stored-procedure-definition/:databaseName/:procedureName
- Purpose: Retrieve the full definition (source code) of a specific stored procedure.
- Request Headers/Params: connectionId (as query parameter or header), databaseName, procedureName (as path parameters).
- Response (200 OK):
JSON
----
{
    "name": "getUserById",
    "definition": "CREATE PROCEDURE `getUserById` (IN userId INT)\nBEGIN\n    SELECT * FROM users WHERE id = userId;\nEND"
}

GET /api/v1/persistence/:databaseName/:tableName/data
- Purpose: Get sample data from a table
- Query Params: limit (default 100), offset (default 0)
- Response: { columns: [names], rows: [values], totalCount }

Query Execution Endpoints

POST /api/v1/persistence/query
==============================
- Purpose: Execute a SQL query (e.g., SELECT, INSERT, UPDATE, DELETE, CREATE table/view).
- Request Body:
  ------------
JSON
{
    "query": "SELECT * FROM users WHERE id = ?",
    "params": [123] // Optional: parameters for prepared statements
}
- Response:
  --------
Success (200 OK):
JSON
{
    "results": [
        {"id": 1, "name": "Alice"},
        {"id": 2, "name": "Bob"}
    ],
    "affectedRows": 0, // For DML statements
    "columns": ["id", "name"], // Optional: column names for display
    "queryType": "SELECT" // Optional: "SELECT", "INSERT", "UPDATE", "DELETE", "DDL", etc.
}
(Note: results will be an array of objects for SELECT, affectedRows for DML, etc. You might need to adapt the response structure based on the SQL driver's output.)
Error (400 Bad Request, 500 Internal Server Error): {"error": "Query execution failed", "details": "SQL error message..."}

POST /api/v1/persistence/execute-script
=======================================
- Purpose: Execute multiple SQL statements or a larger SQL script.
- Request Body:
  ------------
JSON
{
    "script": "CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(255)); INSERT INTO products VALUES (1, 'Laptop');"
}
- Response: 
  --------
Similar to /api/v1/persistence/query, but might return an array of results for multiple statements, or just a success message if no data is returned.
Success (200 OK): {"message": "Script executed successfully", "results": [...]}
Error (400 Bad Request, 500 Internal Server Error): {"error": "Script execution failed", "details": "..."}