LibreDB Studio API Documentation

August 7, 2026 · View on GitHub

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

Overview
Authentication
API Endpoints
Data Types
Error Handling
Rate Limiting
Examples

Overview

LibreDB Studio provides a RESTful API for database management operations. The API supports PostgreSQL, MySQL, SQLite, Oracle, SQL Server, MongoDB, Couchbase, ClickHouse, Apache Druid, 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, Couchbase, ClickHouse, Apache Druid, 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" }   // route handlers that call createErrorResponse also add "code" and "statusCode" (and sometimes "retryable" / "details"); handlers that return errors inline may send just "error"

Authentication

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

Authentication Flow

Client sends credentials to /api/auth/login
Server validates and returns JWT in auth-token cookie
Client includes cookie in subsequent requests
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 in the request body; matched against ADMIN_EMAIL/ADMIN_PASSWORD or USER_EMAIL/USER_PASSWORD environment variables. ADMIN_PASSWORD is mandatory; the USER_* account exists only when USER_PASSWORD is set
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.

Bound parameters (optional):

{
  "connection": { "type": "mysql", "host": "localhost", "database": "mydb" },
  "sql": "UPDATE users SET `name` = ? WHERE `id` = ?",
  "params": ["O'Brien", 7]
}

params binds the statement's positional placeholders through the driver, so a value never becomes statement text. Use it for any statement built from data rather than typed by a person — a value carrying \' would otherwise close its own string literal on MySQL or ClickHouse and have the rest read as SQL. The placeholder form is the dialect's own: $n (PostgreSQL), ? (MySQL, SQLite), :n (Oracle), @pn (SQL Server).

Each element must be a string, number, boolean or null; anything else is rejected with 400 rather than handed to the driver. POST /api/db/transaction accepts the same field for its query action.

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)

Couchbase Query Format

Couchbase speaks SQL++, a SQL dialect, so the sql field carries an ordinary statement — there is no JSON envelope. Two things differ from the other SQL providers:

connection.database carries the bucket (one bucket per connection), and connection.port is the management port (8091, or 18091 with TLS). The query port is discovered from GET /pools/default/nodeServices at connect time and is never configured.
Keyspaces are backtick-quoted three-part paths. SELECT * nests the document under the keyspace name and never yields the document key, so generated statements alias and project it explicitly.

{
  "connection": {
    "type": "couchbase",
    "host": "127.0.0.1",
    "port": 8091,
    "user": "Administrator",
    "password": "password123",
    "database": "travel"
  },
  "sql": "SELECT META(d).id AS __id, d.* FROM `travel`.`inventory`.`hotel` AS d LIMIT 50"
}

Notes:

Every statement is sent with scan_consistency: request_plus, so a SELECT issued right after an INSERT sees the new rows (the cluster default, not_bounded, does not).
A statement against a keyspace with no usable index returns 400 Bad Request with code QUERY_ERROR, and the message carries the runnable remedy (CREATE PRIMARY INDEX ON `travel`.`inventory`.`hotel` ). A document whose key is known needs no index at all: SELECT d.* FROM ... AS d USE KEYS ["hotel::1"].
POST /api/db/maintenance accepts analyze (UPDATE STATISTICS, Enterprise Edition only), reindex (BUILD INDEX over deferred indexes) and kill (a request id), each requiring a target.
Full reference: docs/providers/couchbase.md.

ClickHouse Query Format

ClickHouse speaks ordinary SQL over its HTTP interface, so the sql field carries a plain statement — no JSON envelope, the same as PostgreSQL or MySQL. Two things differ from the other SQL providers:

A statement ending in an explicit FORMAT ... or SETTINGS ... clause is sent unchanged: the server rejects a LIMIT appended after either, so the auto-limiter skips injection and wasLimited is false for those statements.
written_rows is the only mutation count the server reports, so rowCount on an ALTER TABLE ... UPDATE or a lightweight DELETE FROM is 0 even though the statement applied — this mirrors the server's own reporting, it is not a bug.

{
  "connection": {
    "type": "clickhouse",
    "host": "localhost",
    "port": 8123,
    "user": "default",
    "password": "",
    "database": "default"
  },
  "sql": "SELECT * FROM events LIMIT 50"
}

Notes:

POST /api/db/maintenance accepts optimize (OPTIMIZE TABLE ... FINAL), analyze (statistics from system.parts) and kill (KILL QUERY WHERE query_id = ... SYNC).
Full reference: docs/providers/clickhouse.md.

Apache Druid Query Format

Druid speaks SQL over POST /druid/v2/sql, so the sql field carries a plain statement. Three things differ from the other SQL providers:

There is no database and no connectionString. INFORMATION_SCHEMA.SCHEMATA reports exactly one catalog, always druid, so a connection is host + port alone. user/password are optional and are sent as HTTP basic auth for a cluster running druid-basic-security; a default install ignores the Authorization header entirely. port is the Router's 8888; the Broker's 8082 serves the identical endpoint and needs no other change.
Druid SQL cannot write. UPDATE and DELETE are not in the grammar, CREATE TABLE is a syntax error, and INSERT / REPLACE are refused by the native engine ("consider using MSQ"). Each comes back as 400 / QUERY_ERROR carrying Druid's own message, which names the reason and the alternative. POST /api/db/maintenance accepts no operation at all for a druid connection.
A statement ending in OFFSET n with no LIMIT is sent unchanged, so wasLimited is false: Druid rejects OFFSET n LIMIT m ("'OFFSET start LIMIT count' is not allowed under the current SQL conformance level"), so the auto-limiter must not append one there. Every other statement is limited normally.

{
  "connection": {
    "type": "druid",
    "host": "localhost",
    "port": 8888
  },
  "sql": "SELECT * FROM \"libredb_demo\" LIMIT 50"
}

Notes:

A duplicate output name (a join projecting two ids, say) is disambiguated rather than dropped: fields carries id and id (2), and both columns reach the grid.
ORDER BY on a non-__time column of a plain table scan is refused by the planner ("SQL query requires ordering a table by non-time column"). Order by __time, or aggregate with GROUP BY.
Druid uses Calcite's reserved-word list, which is large and surprising — SELECT 1 AS one is a syntax error — so every generated identifier is double-quoted.
Integers wider than $2^{53}$ are returned as exact strings rather than as JSON numbers, so no value is silently rounded on the way to the grid. ARRAY columns arrive as JSON strings ("[1,2]"), which is what Druid's own clients show.
Full reference: docs/providers/druid.md.

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	-	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": "Operation 'vacuum' not supported for this database. Supported: analyze, optimize, check, kill"
}

The handler validates against the target provider's capabilities: type is required ({ "error": "Maintenance type is required" }), the provider must support maintenance at all, and the requested operation must be in that provider's supported set (see the matrix above) — otherwise a 400 is returned listing what the provider does support.

A druid connection fails the second check whatever the type is, with { "error": "Maintenance operations not supported for this database" }: no maintenance operation is reachable from Druid SQL, so its supported set is empty by design. Compaction and retention are Coordinator and task concerns, and Druid publishes no catalog of running queries, so there is no id for kill to name.

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 (Couchbase: the bucket; Druid: unused, it has one catalog)
  connectionString?: string; // Full connection string (alternative; Druid has no URI form, host + port only)
  createdAt: Date;         // Creation timestamp
}

type DatabaseType = 'postgres' | 'mysql' | 'sqlite' | 'mongodb' | 'redis' | 'oracle' | 'mssql' | 'libredb' | 'couchbase' | 'clickhouse' | 'druid';

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)
  warnings?: QueryWarning[];             // Notices the engine attached; ABSENT when it reported none
  columnTypes?: Record<string, string>;  // Declared type per column, keyed by its name in `fields`
}

interface QueryWarning {
  message: string;         // The notice, as the engine worded it
  code?: number | string;  // The engine's own identifier, when it reported one
}

Both optional channels are filled only by providers whose source declares them, and absence is the signal: a run that produced no warnings omits the field rather than sending [], so a client can decide what to render from the field's presence alone. columnTypes is the declared type of this result, which is the only source for a computed column or an ad-hoc projection — the schema has no catalog entry to answer with.

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)
`499`	Client Closed Request - Query cancelled by the client
`500`	Internal Server Error
`502`	Bad Gateway - LLM streaming failure
`503`	Service Unavailable - Database connection failed or LLM misconfigured

Error Response Format

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

Error Codes

These are the values of the code field emitted by createErrorResponse (src/lib/api/error-codes.ts):

Code	Description
`QUERY_ERROR`	SQL syntax or execution error (400)
`QUERY_CANCELLED`	Query cancelled by the client (499)
`CONFIG_ERROR`	Invalid database configuration (400)
`AUTH_ERROR`	Authentication failed (401)
`TIMEOUT_ERROR`	Query exceeded time limit (408)
`CONNECTION_ERROR`	Database connection failed (503)
`POOL_EXHAUSTED`	Connection pool exhausted (503)
`DATABASE_ERROR`	Generic database error (500)
`LLM_SAFETY`	Prompt blocked by safety filters (400)
`LLM_AUTH`	Invalid LLM API key (401)
`LLM_RATE_LIMIT`	LLM usage/rate limit reached (429)
`LLM_CONFIG`	LLM misconfigured (503)
`LLM_STREAM`	LLM streaming failure (502)
`LLM_ERROR`	Generic LLM error
`INTERNAL_ERROR`	Unhandled server error (500)
`NETWORK_ERROR`	Network failure

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)

curl -X POST http://localhost:3000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email": "admin@libredb.org", "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({ email: 'admin@libredb.org', 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`	Recommended	JWT signing secret (min 32 chars). Auto-generated at boot if unset unless `AUTH_BOOTSTRAP=off` (see `.env.example`). Set but shorter than 32 chars: the server exits at startup with code 1 rather than serving a deployment whose logins all fail with 503
`ADMIN_PASSWORD`	Recommended	Admin account password. Auto-generated and printed once at boot if unset unless `AUTH_BOOTSTRAP=off`
`ADMIN_EMAIL`	No	Admin login email (default `admin@libredb.org`)
`USER_PASSWORD`	No	Optional lower-privilege account password; the `user` account exists only when this is set
`USER_EMAIL`	No	Regular-user login email (default `user@libredb.org`, only used when `USER_PASSWORD` is set)
`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

API changes ship with the product releases; see the GitHub releases for the per-version changelog instead of a manually maintained copy here.

Last Updated: 2026-07-04