Skip to content

API_DOCS.md

Latest commit

 

History

History
1038 lines (827 loc) · 26.3 KB

API_DOCS.md

File metadata and controls

1038 lines (827 loc) · 26.3 KB

LibreDB Studio API Documentation

Version: 0.5.2 Base URL: https://your-domain.com or http://localhost:3000 Content-Type: application/json

Table of Contents

Overview

LibreDB Studio provides a RESTful API for database management operations. The API supports PostgreSQL, MySQL, SQLite, Oracle, SQL Server, MongoDB, and Redis.

Key Features

  • JWT Authentication - Secure token-based authentication stored in HTTP-only cookies
  • Multi-Database Support - PostgreSQL, MySQL, SQLite, Oracle, SQL Server, MongoDB, Redis
  • AI-Powered Queries - Natural language to SQL with streaming responses
  • Real-time Health Monitoring - Database metrics and performance insights

Request Format

All API requests must include:

  • Content-Type: application/json header
  • Valid authentication cookie (except public endpoints)

Response Format

Responses are JSON. There is no global envelope — each endpoint returns its own shape (documented per-endpoint below). Common patterns:

// Auth endpoints
{ "success": true, "role": "admin" }

// Data / storage endpoints return the payload (or a bare ack) directly
{ "rows": [], "rowCount": 0, "pagination": { } }
{ "ok": true }

// Errors
{ "error": "Human-readable message" }   // some also include "code" and/or an HTTP "status"

Authentication

LibreDB Studio uses JWT (JSON Web Tokens) for authentication. Tokens are stored in HTTP-only cookies for security.

Authentication Flow

  1. Client sends credentials to /api/auth/login
  2. Server validates and returns JWT in auth-token cookie
  3. Client includes cookie in subsequent requests
  4. Middleware validates token on protected routes

Roles

Role Access Level
admin Full access including maintenance operations and admin panel
user Query execution, schema viewing (no maintenance)

Public Endpoints (No Auth Required)

Authentication is enforced centrally by the middleware (src/proxy.ts), not by individual route handlers: every route requires a valid auth-token cookie except the routes below. (This is why handlers like /api/ai/* don't call getSession() themselves — the middleware has already gated them.)

  • /api/auth/* — login, logout, me, and OIDC login/callback
  • /api/db/health — excluded from the middleware for both methods; GET is fully public, while POST performs its own session check and returns JSON 401 if unauthenticated
  • GET /api/storage/config — storage-mode discovery (returns { provider, serverMode }, no user data)

Unauthenticated requests to any other (middleware-gated) route are redirected to /login. A few allowlisted handlers self-check instead and return JSON — e.g. POST /api/db/health (401) and GET /api/auth/me ({ "authenticated": false }).

API Endpoints

Auth API

POST /api/auth/login

Authenticate user and create session.

Request:

{
  "email": "admin@libredb.org",
  "password": "your-password"
}

Response (200 OK):

{
  "success": true,
  "role": "admin"
}

Response (401 Unauthorized):

{
  "success": false,
  "message": "Invalid email or password"
}

Notes:

  • Both email and password are required; matched against ADMIN_EMAIL/ADMIN_PASSWORD or USER_EMAIL/USER_PASSWORD environment variables
  • Sets auth-token HTTP-only cookie on success

POST /api/auth/logout

Terminate current session.

Request: No body required

Response (200 OK):

{
  "success": true
}

When NEXT_PUBLIC_AUTH_PROVIDER=oidc, the response also includes the provider's RP-initiated logout URL for the client to redirect to:

{
  "success": true,
  "redirectUrl": "https://issuer.example.com/v2/logout?..."
}

Notes:

  • Clears the auth-token cookie

GET /api/auth/me

Get current authenticated user information.

Response (200 OK):

{
  "authenticated": true,
  "user": {
    "role": "admin",
    "username": "admin@libredb.org"
  }
}

Response (401 Unauthorized):

{
  "authenticated": false
}

The user object is the JWT session payload (role, username). It is a public route in the middleware but self-checks the cookie, returning { "authenticated": false } when absent/invalid.

Database API

GET /api/db/health

Simple health check for load balancers and container orchestration.

Authentication: Not required

Response (200 OK):

{
  "status": "healthy",
  "timestamp": "2025-12-24T12:00:00.000Z",
  "service": "libredb-studio"
}

POST /api/db/health

Detailed health check for a specific database connection.

Authentication: Required

Request:

{
  "connection": {
    "id": "conn-123",
    "name": "Production DB",
    "type": "postgres",
    "host": "localhost",
    "port": 5432,
    "database": "mydb",
    "user": "admin",
    "password": "secret"
  }
}

Response (200 OK):

{
  "activeConnections": 5,
  "databaseSize": "256 MB",
  "cacheHitRatio": "99.2%",
  "slowQueries": [
    {
      "query": "SELECT * FROM large_table...",
      "calls": 150,
      "avgTime": "245ms"
    }
  ],
  "activeSessions": [
    {
      "pid": 12345,
      "user": "admin",
      "database": "mydb",
      "state": "active",
      "query": "SELECT * FROM users",
      "duration": "1.5s"
    }
  ]
}

Response (503 Service Unavailable):

{
  "error": "Connection failed: timeout",
  "activeConnections": 0,
  "databaseSize": "N/A",
  "cacheHitRatio": "N/A",
  "slowQueries": [],
  "activeSessions": []
}

POST /api/db/query

Execute SQL query on connected database.

Authentication: Required

Request:

{
  "connection": {
    "id": "conn-123",
    "name": "My Database",
    "type": "postgres",
    "host": "localhost",
    "port": 5432,
    "database": "mydb",
    "user": "admin",
    "password": "secret"
  },
  "sql": "SELECT id, name, email FROM users WHERE active = true LIMIT 100"
}

Response (200 OK):

{
  "rows": [
    { "id": 1, "name": "John Doe", "email": "john@example.com" },
    { "id": 2, "name": "Jane Smith", "email": "jane@example.com" }
  ],
  "fields": ["id", "name", "email"],
  "rowCount": 2,
  "executionTime": 12,
  "pagination": {
    "limit": 500,
    "offset": 0,
    "hasMore": false,
    "totalReturned": 2,
    "wasLimited": false
  }
}

The pagination object reports the auto-limiting applied by the server (default 500 rows). wasLimited is true when the server injected a LIMIT the query didn't specify; hasMore indicates more rows are available — re-request with a higher offset to page. See docs/editor/query-optimization.md.

Response (400 Bad Request):

{
  "error": "syntax error at or near \"SELEC\"",
  "code": "QUERY_ERROR"
}

Response (408 Request Timeout):

{
  "error": "Query timed out. Please try a simpler query or increase timeout."
}
MongoDB Query Format

For MongoDB connections, the sql field should contain a JSON query:

{
  "connection": {
    "type": "mongodb",
    "connectionString": "mongodb://localhost:27017/mydb"
  },
  "sql": "{\"collection\":\"users\",\"operation\":\"find\",\"filter\":{\"active\":true},\"options\":{\"limit\":50}}"
}

Supported MongoDB Operations:

  • find - Query documents
  • findOne - Get single document
  • insertOne - Insert document
  • insertMany - Insert multiple documents
  • updateOne - Update single document
  • updateMany - Update multiple documents
  • deleteOne - Delete single document
  • deleteMany - Delete multiple documents
  • aggregate - Aggregation pipeline
  • count - Count documents matching a filter (runs countDocuments internally)
  • distinct - Distinct values for a field (the field is taken from the first key of options.projection)
Redis Query Format

Redis is a key-value store, so the sql field carries a Redis command instead of SQL. Two interchangeable formats are accepted.

1. Plain command (whitespace-separated, single/double-quoted arguments preserved):

{
  "connection": {
    "type": "redis",
    "host": "localhost",
    "port": 6379,
    "database": "0"
  },
  "sql": "HGETALL user:1"
}

2. JSON command object:

{
  "connection": {
    "type": "redis",
    "host": "localhost",
    "port": 6379
  },
  "sql": "{\"command\":\"GET\",\"args\":[\"user:123\"]}"
}

Result shaping — the response is normalised into the standard rows / fields / rowCount envelope:

Redis reply Rendered as
Simple string / status (GET, PING, SET) single result column
Integer (DEL, DBSIZE, INCR) result column as (integer) N
nil / empty list (nil) / (empty list)
Array (KEYS, SMEMBERS, LRANGE) index + value columns
Hash (HGETALL) field + value columns
INFO section + key + value columns

Notes:

  • Schema introspection (/api/db/schema) uses a non-blocking SCAN and groups keys by prefix, presenting each prefix (e.g. user:*) as a "table".
  • Monitoring/health endpoints derive their data from INFO, SLOWLOG GET, and CLIENT LIST.
  • Invalid JSON, a missing command field, or an unknown/failed Redis command returns 400 Bad Request with code QUERY_ERROR.

POST /api/db/schema

Get database schema including tables, columns, indexes, and foreign keys.

Authentication: Required

Request:

{
  "id": "conn-123",
  "name": "My Database",
  "type": "postgres",
  "host": "localhost",
  "port": 5432,
  "database": "mydb",
  "user": "admin",
  "password": "secret"
}

Response (200 OK):

[
  {
    "name": "users",
    "rowCount": 1500,
    "size": "2.4 MB",
    "columns": [
      {
        "name": "id",
        "type": "integer",
        "nullable": false,
        "isPrimary": true,
        "defaultValue": "nextval('users_id_seq')"
      },
      {
        "name": "email",
        "type": "varchar(255)",
        "nullable": false,
        "isPrimary": false
      },
      {
        "name": "created_at",
        "type": "timestamp",
        "nullable": true,
        "isPrimary": false,
        "defaultValue": "CURRENT_TIMESTAMP"
      }
    ],
    "indexes": [
      {
        "name": "users_pkey",
        "columns": ["id"],
        "unique": true
      },
      {
        "name": "users_email_idx",
        "columns": ["email"],
        "unique": true
      }
    ],
    "foreignKeys": [
      {
        "columnName": "org_id",
        "referencedTable": "organizations",
        "referencedColumn": "id"
      }
    ]
  }
]

Response (503 Service Unavailable):

{
  "error": "Connection failed: ECONNREFUSED"
}

POST /api/db/maintenance

Run database maintenance operations.

Authentication: Required (Admin only)

Request:

{
  "connection": {
    "id": "conn-123",
    "name": "Production DB",
    "type": "postgres",
    "host": "localhost",
    "port": 5432,
    "database": "mydb",
    "user": "admin",
    "password": "secret"
  },
  "type": "vacuum",
  "target": "users"
}

Parameters:

Field Type Required Description
connection object Yes Database connection configuration
type string Yes Maintenance operation type
target string No Target table name or PID (for kill)

Maintenance Types:

Type PostgreSQL MySQL SQLite Description
vacuum VACUUM ANALYZE OPTIMIZE VACUUM Reclaim storage and update statistics
analyze ANALYZE ANALYZE ANALYZE Update query planner statistics
reindex REINDEX - REINDEX Rebuild indexes
optimize - OPTIMIZE - Optimize table (MySQL only)
check - CHECK PRAGMA integrity_check Check table integrity
kill pg_terminate_backend KILL - Terminate a session by PID

Response (200 OK):

{
  "success": true,
  "executionTime": 1234,
  "message": "VACUUM completed successfully"
}

Response (403 Forbidden):

{
  "error": "Unauthorized. Admin access required."
}

Response (400 Bad Request):

{
  "error": "Invalid maintenance type. Valid types: vacuum, analyze, reindex, kill, optimize, check"
}

AI API

POST /api/ai/chat

Generate SQL queries using AI with streaming response.

Authentication: Required

Request:

{
  "prompt": "Show me all users who signed up in the last 30 days",
  "databaseType": "postgres",
  "schemaContext": "Table: users (id, email, name, created_at, status)"
}

Parameters:

Field Type Required Description
prompt string Yes Natural language query or question
databaseType string No Database type for syntax (default: postgres)
schemaContext string No Schema info for context-aware queries
queryLanguage string No "sql" (default) or "json" (MongoDB MQL)
conversationHistory array No Prior {role, content} messages for multi-turn context

Response (200 OK - Streaming):

Returns text/plain with chunked transfer encoding. The response streams the generated SQL:

SELECT id, email, name, created_at
FROM users
WHERE created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC;

Response (401 Unauthorized):

{
  "error": "Invalid API key. Please check your configuration."
}

Response (429 Too Many Requests):

{
  "error": "AI usage limit reached. Please try again later or check your billing status."
}

Response (400 Bad Request):

{
  "error": "The prompt was blocked by safety filters."
}

LLM Configuration:

Configure AI provider via environment variables:

LLM_PROVIDER=gemini          # gemini, openai, ollama, custom
LLM_API_KEY=your-api-key
LLM_MODEL=gemini-2.5-flash   # Model name
LLM_API_URL=http://localhost:11434/v1  # For ollama/custom

The 401/429/400 responses above are surfaced from the configured LLM provider (bad API key, quota, safety filter), not from session auth — session auth is already enforced by the middleware before the handler runs.

Other AI endpoints

All AI endpoints are POST, auth-required (via middleware), and stream text/plain. They share the optional schemaContext and databaseType fields; the table lists each one's primary input and purpose.

Endpoint Key input Purpose
POST /api/ai/nl2sql question (+ optional queryLanguage, conversationHistory) Natural language → SQL/MongoDB query (multi-turn)
POST /api/ai/explain query (+ optional explainPlan) Explain an EXPLAIN plan and suggest optimizations
POST /api/ai/query-safety query Pre-execution risk analysis; streams a JSON verdict (riskLevel, warnings[], recommendation)
POST /api/ai/impact query (a DDL statement) Predict the impact of a schema change before running it
POST /api/ai/index-advisor slowQueries / indexStats / tableStats (all optional) Recommend missing/unused/duplicate indexes
POST /api/ai/autopilot performance metrics — slowQueries, indexStats, tableStats, performanceMetrics, overview (all optional) Full performance-optimization report
POST /api/ai/describe-schema schemaContext (+ optional mode: "table"|"database") Auto-generate schema documentation

nl2sql, explain, query-safety, impact, and describe-schema validate their key field and return 400 { "error": "… is required" } if it's missing. index-advisor and autopilot accept fully partial payloads (no required field) — they degrade to a best-effort analysis from whatever is provided.

Storage API

The write-through storage sync layer (see docs/STORAGE.md). Data is per-user, keyed by the session username.

GET /api/storage/config

Public. Returns the active storage configuration so the client can discover whether server-side storage is enabled.

{ "provider": "local", "serverMode": false }

provider is "local" | "sqlite" | "postgres"; serverMode is true whenever provider is not "local".

GET /api/storage

Auth required. Returns all stored collections for the current user. 404 if server-side storage is not enabled.

PUT /api/storage/{collection}

Auth required. Replaces one collection's data. collection must be one of the known STORAGE_COLLECTIONS; invalid names or a missing data field return 400.

// Request
{ "data": { } }
// Response
{ "ok": true }

POST /api/storage/migrate

Auth required. Merges a client's localStorage payload into server storage on first sign-in.

// Response
{ "ok": true, "migrated": ["connections", "queryHistory"] }

Connections API

GET /api/connections/managed

Auth required. Returns seed/managed connections for the current user's role, with secrets (password, connectionString) stripped. cacheHint is the client cache TTL in ms (SEED_CACHE_TTL_MS, default 60000). See docs/SEED_CONNECTIONS.md.

{ "connections": [], "cacheHint": 60000 }

Admin API

Both require an admin role (enforced in-handler in addition to the middleware); non-admins get 403 { "error": "Unauthorized. Admin access required." }.

GET /api/admin/audit

Returns audit events. Optional query params: type (filter by event type), limit (default 100). Response: { "events": [], "total": 0 }. POST /api/admin/audit appends an event (user auto-filled from the session).

POST /api/admin/fleet-health

Body { "connections": [...] }; returns per-connection health { "results": [{ connectionId, status, latencyMs, ... }] }. 400 if connections is missing.

Internal routes (not part of this public reference). The frontend also calls several internal /api/db/* endpoints that mirror provider internals and change with the UI: multi-query, schema/list, schema/relations, transaction, cancel, disconnect, test-connection, monitoring, pool-stats, profile, provider-meta, schema-snapshot. They're auth-gated by the middleware like everything else; consult the route handlers in src/app/api/db/ for their shapes.

Data Types

DatabaseConnection

interface DatabaseConnection {
  id: string;              // Unique identifier
  name: string;            // Display name
  type: DatabaseType;      // Database type
  host?: string;           // Hostname or IP
  port?: number;           // Port number
  user?: string;           // Username
  password?: string;       // Password
  database?: string;       // Database name
  connectionString?: string; // Full connection string (alternative)
  createdAt: Date;         // Creation timestamp
}

type DatabaseType = 'postgres' | 'mysql' | 'sqlite' | 'mongodb' | 'redis' | 'oracle' | 'mssql';

TableSchema

interface TableSchema {
  name: string;            // Table name
  columns: ColumnSchema[]; // Column definitions
  indexes: IndexSchema[];  // Index definitions
  foreignKeys?: ForeignKeySchema[];
  rowCount?: number;       // Approximate row count
  size?: string;           // Table size (e.g., "2.4 MB")
}

interface ColumnSchema {
  name: string;            // Column name
  type: string;            // Data type
  nullable: boolean;       // Allows NULL
  isPrimary: boolean;      // Primary key
  defaultValue?: string;   // Default value
}

interface IndexSchema {
  name: string;            // Index name
  columns: string[];       // Indexed columns
  unique: boolean;         // Unique constraint
}

interface ForeignKeySchema {
  columnName: string;      // Local column
  referencedTable: string; // Foreign table
  referencedColumn: string; // Foreign column
}

QueryResult

interface QueryResult {
  rows: any[];             // Result rows
  fields: string[];        // Column names
  rowCount: number;        // Number of rows returned
  executionTime: number;   // Execution time in ms
  explainPlan?: any;       // Query execution plan (if requested)
}

HealthInfo

interface HealthInfo {
  activeConnections: number;
  databaseSize: string;
  cacheHitRatio: string;
  slowQueries: SlowQuery[];
  activeSessions: ActiveSession[];
}

interface SlowQuery {
  query: string;           // Query text (truncated)
  calls: number;           // Number of executions
  avgTime: string;         // Average execution time
}

interface ActiveSession {
  pid: number | string;    // Process/Session ID
  user: string;            // Database user
  database: string;        // Database name
  state: string;           // Session state
  query: string;           // Current query
  duration: string;        // Query duration
}

Error Handling

HTTP Status Codes

Code Description
200 Success
400 Bad Request - Invalid parameters or query syntax
401 Unauthorized - Missing or invalid authentication
403 Forbidden - Insufficient permissions
408 Request Timeout - Query exceeded time limit
429 Too Many Requests - Rate limit exceeded (AI)
500 Internal Server Error
503 Service Unavailable - Database connection failed

Error Response Format

{
  "error": "Human-readable error message",
  "code": "ERROR_CODE"
}

Error Codes

Code Description
QUERY_ERROR SQL syntax or execution error
CONNECTION_ERROR Database connection failed
TIMEOUT_ERROR Query exceeded time limit
AUTH_ERROR Authentication failed
CONFIG_ERROR Invalid configuration

Rate Limiting

AI Endpoint

The AI chat endpoint (/api/ai/chat) is subject to rate limits from the underlying LLM provider:

Provider Limits
Gemini 15 RPM (free tier)
OpenAI Varies by plan
Ollama No limits (local)

Database Operations

Database operations have a default timeout of 60 seconds (DEFAULT_QUERY_TIMEOUT).

Examples

cURL Examples

Login

curl -X POST http://localhost:3000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"password": "admin123"}' \
  -c cookies.txt

Execute Query

curl -X POST http://localhost:3000/api/db/query \
  -H "Content-Type: application/json" \
  -b cookies.txt \
  -d '{
    "connection": {
      "id": "1",
      "name": "Local PG",
      "type": "postgres",
      "host": "localhost",
      "port": 5432,
      "database": "mydb",
      "user": "postgres",
      "password": "postgres"
    },
    "sql": "SELECT * FROM users LIMIT 10"
  }'

Get Schema

curl -X POST http://localhost:3000/api/db/schema \
  -H "Content-Type: application/json" \
  -b cookies.txt \
  -d '{
    "id": "1",
    "name": "Local PG",
    "type": "postgres",
    "host": "localhost",
    "port": 5432,
    "database": "mydb",
    "user": "postgres",
    "password": "postgres"
  }'

AI Query Generation

curl -X POST http://localhost:3000/api/ai/chat \
  -H "Content-Type: application/json" \
  -b cookies.txt \
  -d '{
    "prompt": "Count users by country",
    "databaseType": "postgres",
    "schemaContext": "users(id, name, country, created_at)"
  }'

Health Check

curl http://localhost:3000/api/db/health

Run Maintenance (Admin)

curl -X POST http://localhost:3000/api/db/maintenance \
  -H "Content-Type: application/json" \
  -b cookies.txt \
  -d '{
    "connection": {
      "id": "1",
      "name": "Local PG",
      "type": "postgres",
      "host": "localhost",
      "port": 5432,
      "database": "mydb",
      "user": "postgres",
      "password": "postgres"
    },
    "type": "vacuum",
    "target": "users"
  }'

JavaScript/TypeScript Examples

// Login and execute query
async function executeQuery(sql: string) {
  // Login
  await fetch('/api/auth/login', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ password: 'admin123' }),
    credentials: 'include'
  });

  // Execute query
  const response = await fetch('/api/db/query', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    credentials: 'include',
    body: JSON.stringify({
      connection: {
        id: '1',
        name: 'My DB',
        type: 'postgres',
        host: 'localhost',
        port: 5432,
        database: 'mydb',
        user: 'postgres',
        password: 'postgres'
      },
      sql
    })
  });

  return response.json();
}

// Stream AI response
async function streamAIQuery(prompt: string) {
  const response = await fetch('/api/ai/chat', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    credentials: 'include',
    body: JSON.stringify({
      prompt,
      databaseType: 'postgres',
      schemaContext: 'users(id, name, email)'
    })
  });

  const reader = response.body?.getReader();
  const decoder = new TextDecoder();

  while (true) {
    const { done, value } = await reader!.read();
    if (done) break;
    console.log(decoder.decode(value));
  }
}

Environment Variables

Variable Required Description
JWT_SECRET Yes (prod) JWT signing secret (min 32 chars)
ADMIN_PASSWORD Yes (prod) Admin user password
USER_PASSWORD Yes (prod) Regular user password
LLM_PROVIDER No AI provider: gemini, openai, ollama, custom
LLM_API_KEY No AI provider API key
LLM_MODEL No AI model name
LLM_API_URL No Custom AI endpoint URL

Changelog

v0.5.2

  • Added memory optimization with dynamic imports
  • Improved Docker deployment for low-memory environments
  • Added serverExternalPackages for native modules

v0.5.0

  • Full MongoDB support
  • Strategy Pattern for database providers
  • LLM provider abstraction

v0.4.0

  • AI Query Assistant with streaming
  • Multi-provider LLM support

Last Updated: December 2025