This document describes the modular database provider architecture implemented using the Strategy Pattern. It serves both as the architecture overview and as a step-by-step tutorial for adding a new database provider.
Per-provider detail lives in
docs/providers/. Each provider has its own prime reference (docs/providers/<type-id>.md) covering connection, query format, schema, monitoring, maintenance, capabilities, error handling, testing, and known limitations. This document is the cross-cutting architecture + authoring companion to those per-provider docs.
The database abstraction layer (src/lib/db/) provides a unified interface for multiple database types while maintaining type safety, connection pooling, and consistent error handling. Each database type is a self-contained provider class. Adding a new one requires no changes to routes, components, or existing providers.
src/lib/db/
├── index.ts # Public exports
├── types.ts # Interfaces & Types
├── errors.ts # Custom error classes
├── factory.ts # Provider Factory
├── base-provider.ts # Abstract base class
├── providers/
│ ├── sql/ # SQL Database Providers
│ │ ├── sql-base.ts # SQL-specific base class
│ │ ├── postgres.ts # PostgreSQL Strategy
│ │ ├── mysql.ts # MySQL Strategy
│ │ ├── sqlite.ts # SQLite Strategy
│ │ ├── sqlite-driver.ts # SQLite runtime driver adapter (bun:sqlite | node:sqlite)
│ │ ├── libsql/ # libSQL Strategy (SQLite's dialect over the Hrana protocol, no driver)
│ │ │ ├── index.ts # LibSQLProvider
│ │ │ ├── transport.ts # LibSQLTransport seam + neutral result types
│ │ │ ├── hrana-transport.ts # The one HTTP implementation (fetch); POST /v2/pipeline
│ │ │ └── introspect.ts # sqlite_master + pragma_* + dbstat -> schema and sizes
│ │ ├── duckdb/ # DuckDB Strategy (embedded analytical engine, native N-API driver)
│ │ │ ├── index.ts # DuckDBProvider
│ │ │ ├── client.ts # The one file that imports @duckdb/node-api (instance, connection, interrupt)
│ │ │ ├── introspect.ts # duckdb_* table functions + pragma_storage_info -> schema, sizes, health
│ │ │ └── values.ts # result -> QueryResult and DuckDB type text -> the product's own names
│ │ ├── oracle.ts # Oracle Strategy
│ │ ├── mssql.ts # SQL Server Strategy
│ │ ├── clickhouse/ # ClickHouse Strategy (SQL over HTTP, no driver)
│ │ │ ├── index.ts # ClickHouseProvider
│ │ │ ├── transport.ts # ClickHouseTransport seam + neutral result types
│ │ │ ├── http-transport.ts # The one HTTP implementation (fetch)
│ │ │ └── introspect.ts # system.* catalogs (databases/tables/columns/data_skipping_indices)
│ │ ├── druid/ # Apache Druid Strategy (SQL over POST /druid/v2/sql, no driver)
│ │ │ ├── index.ts # DruidProvider
│ │ │ ├── transport.ts # DruidTransport seam + neutral result types + error categories
│ │ │ ├── http-transport.ts # The one HTTP implementation (fetch)
│ │ │ └── introspect.ts # INFORMATION_SCHEMA datasources + sys.servers/segments/tasks
│ │ ├── search/ # Elasticsearch + OpenSearch Strategy (SQL over HTTP, no driver)
│ │ │ ├── index.ts # ElasticsearchProvider, OpenSearchProvider (two ids, one module)
│ │ │ ├── transport.ts # SearchTransport seam + neutral result types + error categories
│ │ │ ├── http-transport.ts # The one HTTP implementation (fetch); the dialect table lives here
│ │ │ └── introspect.ts # _cat/indices + _mapping -> tables and columns
│ │ ├── cassandra/ # Apache Cassandra Strategy (CQL over the native protocol)
│ │ │ ├── index.ts # CassandraProvider
│ │ │ ├── transport.ts # CassandraTransport seam + neutral result + fault categories
│ │ │ ├── driver-transport.ts # The one file that imports cassandra-driver
│ │ │ └── introspect.ts # system_schema + system_views -> schema and monitoring
│ │ └── trino/ # Apache Trino Strategy (SQL over the client protocol, no driver)
│ │ ├── index.ts # TrinoProvider
│ │ ├── transport.ts # TrinoTransport seam + error categories + the dialect descriptor
│ │ ├── http-transport.ts # The one HTTP implementation (fetch); the nextUri page loop
│ │ └── introspect.ts # information_schema tree + system.runtime/metadata + jmx monitoring
│ ├── document/ # Document Database Providers
│ │ ├── mongodb.ts # MongoDB Strategy
│ │ └── couchbase/ # Couchbase Strategy (SQL++ over REST, no driver)
│ │ ├── index.ts # CouchbaseProvider
│ │ ├── transport.ts # CouchbaseTransport seam + neutral result types
│ │ ├── http-transport.ts # Query REST + management REST (the one implementation)
│ │ ├── keyspace.ts # display name <-> backtick-quoted keyspace path
│ │ └── introspect.ts # system:* catalogs + INFER
│ ├── keyvalue/ # Key-Value Providers
│ │ └── redis.ts # Redis Strategy
│ └── embedded/ # Embedded (in-process) Providers
│ └── libredb.ts # LibreDB Strategy
└── utils/
├── pool-manager.ts # Connection pool utilities
└── query-limiter.ts # SELECT auto-LIMIT (analyzeQuery/applyQueryLimit)
BaseDatabaseProvider (abstract)
├── SQLBaseProvider (abstract) ─────────────┐
│ ├── PostgresProvider │
│ ├── MySQLProvider │ SQL Databases
│ ├── SQLiteProvider │ (shared SQL utilities)
│ ├── LibSQLProvider │
│ ├── DuckDBProvider │
│ ├── OracleProvider │
│ ├── MSSQLProvider │
│ ├── ClickHouseProvider │
│ ├── DruidProvider │
│ ├── ElasticsearchProvider │
│ ├── OpenSearchProvider │
│ ├── TrinoProvider │
│ └── CassandraProvider │
├── MongoDBProvider ────────────────────────┤ Document Database
├── CouchbaseProvider ──────────────────────┤ Document Database (SQL++ over REST)
├── RedisProvider ──────────────────────────┤ Key-Value Store
└── LibreDBProvider ────────────────────────┘ Embedded (key-value)
SQLBaseProvider provides SQL-specific helpers (LIMIT injection, identifier escaping). Non-SQL databases like MongoDB, Redis, and LibreDB extend BaseDatabaseProvider directly. LibreDB is embedded (opened in-process from a file, like SQLite) but, having no SQL, it is a key-value-style provider rather than a SQL one.
Couchbase is the one provider that speaks a SQL dialect (SQL++) without extending SQLBaseProvider: SQL++ quotes identifiers with doubled backticks, which escapeIdentifier() produces for no existing type, so it owns its quoting and expresses its SQL-ness through queryLanguage: 'sql' in the capabilities instead. See providers/couchbase.md.
Cassandra is the counter-example to the whole HTTP/driver framing: it needs a driver (a binary
protocol over TCP) and still extends SQLBaseProvider, because the base class is about statement
TEXT and CQL agrees with it on identifier quoting and on LIMIT n. Its prepareQuery() override
carries three dialect traps rather than one - no OFFSET at all, ALLOW FILTERING must stay last,
and a line comment must be closed by a newline (CQL has // as well as --) - and the seam behind
it is a driver adapter rather than an HTTP one. See providers/cassandra.md.
Being driver-free and reached over HTTP is not what decides the base class. ClickHouseProvider, DruidProvider and TrinoProvider add no driver either, and all three extend SQLBaseProvider: double-quoted identifiers are correct in each dialect, so identifier escaping is inherited rather than rewritten. A placeholder helper is not inherited either — SQLBaseProvider no longer has one (#304 removed it). Two of them override only prepareQuery(), and for opposite reasons. Druid rejects OFFSET n LIMIT m — a statement that already ends in an OFFSET is therefore sent unlimited instead of being rewritten into a syntax error. Trino rejects the other order: its grammar is [ OFFSET count ] [ LIMIT count ], so measured on 476 ... LIMIT 3 OFFSET 1 answers mismatched input 'OFFSET' while the transposed form returns the rows, and the override transposes what the shared limiter emitted rather than rewriting the statement. See providers/clickhouse.md, providers/druid.md and providers/trino.md.
The search providers are the same pattern with one twist: two type-ids, one module. ElasticsearchProvider and OpenSearchProvider are thin subclasses of an internal base in providers/sql/search/, because measured against live servers the two products differ only in wire detail (endpoint path, envelope keys, fault names — one row each in the transport's dialect table) and in exactly one thing above the wire. That one thing is OFFSET: LIMIT 2 OFFSET 1 is HTTP 200 on OpenSearch 3.8.0 and HTTP 400 parsing_exception on Elasticsearch 9.1.4, so prepareQuery() is overridden to refuse the second page on Elasticsearch rather than send a clause the grammar has no rule for, or silently drop it and hand the editor page one to append as if it were page two. The difference is declared as a per-product trait, never asked as this.dialect === … — the same rule CLAUDE.md states for === 'mongodb'. See providers/elasticsearch.md and providers/opensearch.md.
The SQLiteProvider loads its embedded driver at runtime through sqlite-driver.ts: bun:sqlite under Bun, node:sqlite under plain Node (Node >= 24 built-in). Set LIBREDB_SQLITE_DRIVER=bun|node to force a driver. better-sqlite3 is not used by the DB provider — it is only the SQLite driver for the storage layer (src/lib/storage/).
Key files:
| File | Purpose |
|---|---|
src/lib/types.ts |
DatabaseType union, DatabaseConnection interface |
src/lib/db/types.ts |
DatabaseProvider interface, ProviderCapabilities, ProviderLabels |
src/lib/db/base-provider.ts |
Abstract base class with default implementations |
src/lib/db/providers/sql/sql-base.ts |
SQL-specific base (extend this for SQL databases) |
src/lib/db/factory.ts |
Provider creation + caching |
src/lib/db-ui-config.ts |
Icons, colors, form fields per database type |
How it flows:
Frontend Backend
──────── ───────
ConnectionModal /api/db/provider-meta
→ selects DB type → getOrCreateProvider(conn)
→ form fields from → provider.getCapabilities()
db-ui-config.ts → provider.getLabels()
→ returns { capabilities, labels }
useProviderMetadata hook ←─────
→ capabilities, labels
Studio.tsx
→ passes metadata to all components
→ components use labels for text, capabilities for feature flags
QueryEditor /api/db/query
→ user writes query → getOrCreateProvider(conn)
→ Ctrl+Enter → provider.prepareQuery(sql, opts)
→ provider.query(prepared.query)
→ returns rows + pagination
Seventeen type-ids are supported by sixteen provider modules — elasticsearch and opensearch share
one, providers/sql/search/. The count is derived from the exhaustive SHIPPED record in
src/lib/db/compatibility.ts rather than written here twice. For
the per-provider reference (driver, pooling, query format,
monitoring, limitations, …) see the prime docs in docs/providers/:
| Provider | type-id | Family | Reference |
|---|---|---|---|
| PostgreSQL | postgres |
SQL | providers/postgres.md |
| MySQL | mysql |
SQL | providers/mysql.md |
| Oracle | oracle |
SQL | providers/oracle.md |
| Microsoft SQL Server | mssql |
SQL | providers/mssql.md |
| SQLite | sqlite |
SQL (embedded) | providers/sqlite.md |
| libSQL | libsql |
SQL (SQLite over a network) | providers/libsql.md |
| DuckDB | duckdb |
SQL (embedded, analytical) | providers/duckdb.md |
| Redis | redis |
Key-Value | providers/redis.md |
| MongoDB | mongodb |
Document | providers/mongodb.md |
| Couchbase | couchbase |
Document (SQL++) | providers/couchbase.md |
| ClickHouse | clickhouse |
SQL | providers/clickhouse.md |
| Apache Druid | druid |
SQL (read-only) | providers/druid.md |
| Elasticsearch | elasticsearch |
Search (SQL, read-only) | providers/elasticsearch.md |
| OpenSearch | opensearch |
Search (SQL, read-only) | providers/opensearch.md |
| Apache Trino | trino |
SQL (federated query engine) | providers/trino.md |
| Apache Cassandra | cassandra |
SQL-shaped (CQL, wide-column) | providers/cassandra.md |
| LibreDB | libredb |
Embedded (key-value) | providers/libredb.md |
interface DatabaseProvider {
readonly type: DatabaseType;
readonly config: DatabaseConnection;
// Connection lifecycle
connect(): Promise<void>;
disconnect(): Promise<void>;
isConnected(): boolean;
// Query execution
query(sql: string, params?: unknown[]): Promise<QueryResult>;
// The object surface: containers, counts, listings, and one object's or a whole
// folder's columns, indexes and foreign keys
listContainers(parent?: readonly string[]): Promise<Container[]>;
countObjects(container: readonly string[]): Promise<Record<string, KindCount>>;
listObjects(container: readonly string[], kind: string): Promise<DatabaseObject[]>;
describeObject(path: readonly string[], kind: string): Promise<ObjectDetail>;
describeObjects(container: readonly string[], kind: string, limit?: number): Promise<ObjectDetailBatch>;
// Health & monitoring
getHealth(): Promise<HealthInfo>;
// Maintenance operations
runMaintenance(type: MaintenanceType, target?: string): Promise<MaintenanceResult>;
// Validation
validate(): void;
}import { getOrCreateProvider } from '@/lib/db';
// SQL Database
const sqlConnection = {
id: 'my-postgres',
name: 'Production DB',
type: 'postgres',
host: 'localhost',
port: 5432,
database: 'mydb',
user: 'admin',
password: 'secret',
createdAt: new Date(),
};
const sqlProvider = await getOrCreateProvider(sqlConnection);
const result = await sqlProvider.query('SELECT * FROM users LIMIT 10');
// MongoDB
const mongoConnection = {
id: 'my-mongo',
name: 'MongoDB Atlas',
type: 'mongodb',
connectionString: 'mongodb+srv://user:pass@cluster.mongodb.net/mydb',
createdAt: new Date(),
};
const mongoProvider = await getOrCreateProvider(mongoConnection);
const docs = await mongoProvider.query(JSON.stringify({
collection: 'users',
operation: 'find',
filter: { age: { $gt: 18 } },
options: { limit: 10 }
}));import { createDatabaseProvider } from '@/lib/db/factory';
const provider = await createDatabaseProvider(connection, {
pool: { min: 2, max: 10 },
queryTimeout: 30000,
});
await provider.connect();
const schemas = await provider.listContainers();
const tables = await provider.listObjects(['public'], 'table');
const { details } = await provider.describeObjects(['public'], 'table');
await provider.disconnect();The non-SQL providers take a JSON query rather than SQL. The full format, operation list, and worked examples live in their prime docs:
- MongoDB (MQL —
{collection, operation, filter, pipeline, update, documents, options}): providers/mongodb.md and theAPI_DOCS.mdMongoDB Query Format section. - Redis (plain command or
{command, args}): providers/redis.md.
Couchbase is deliberately not in that list: SQL++ is a SQL dialect, so a Couchbase connection
takes ordinary SQL in the sql field and inherits the SQL editor and the shared limiter.
Its keyspaces are backtick-quoted three-part paths (`bucket`.`scope`.`collection`) — see
providers/couchbase.md.
interface PoolConfig {
min: number; // Minimum connections (default: 2)
max: number; // Maximum connections (default: 10)
idleTimeout: number; // Close idle after ms (default: 30000)
acquireTimeout: number; // Wait timeout ms (default: 60000)
}Default query timeout is 60 seconds (60000ms). Configure per-provider:
const provider = await createDatabaseProvider(connection, {
queryTimeout: 30000, // 30 seconds
});Custom error classes provide detailed error information:
import {
DatabaseError,
ConnectionError,
QueryError,
TimeoutError,
isDatabaseError,
isConnectionError,
isQueryError,
} from '@/lib/db/errors';
try {
await provider.query(sql);
} catch (error) {
if (isConnectionError(error)) {
console.log(`Connection failed to ${error.host}:${error.port}`);
} else if (isQueryError(error)) {
console.log(`Query error: ${error.message}, SQL: ${error.sql}`);
} else if (isDatabaseError(error)) {
console.log(`Database error: ${error.code}`);
}
}DatabaseError (base)
├── DatabaseConfigError - Configuration errors
├── ConnectionError - Connection failures
├── AuthenticationError - Invalid credentials
├── PoolExhaustedError - No available connections
├── QueryError - SQL/MQL syntax/execution errors
├── TimeoutError - Query/connection timeouts
└── QueryCancelledError - Query cancelled by the user
Provider-specific behaviour — pooling model, SSL/encryption, pagination, monitoring sources,
maintenance operations, and known limitations — is documented per provider under
docs/providers/. Start there for anything specific to PostgreSQL, MySQL,
Oracle, SQL Server, SQLite, libSQL, DuckDB, Redis, MongoDB, Couchbase, ClickHouse, Apache Druid,
Elasticsearch, OpenSearch, Apache Trino, Apache Cassandra, or LibreDB.
Not every provider has every feature, and the docs record the absences rather than glossing over
them. Druid is the sharpest case: its SQL has no UPDATE, no DELETE and no CREATE TABLE, no
maintenance operation is reachable from SQL, it has no user-defined indexes and no foreign keys, and
it keeps no query log — so supportsCreateTable and supportsMaintenance are false, and
getIndexStats(), getSlowQueries() and getPerformanceMetrics() return empty or zeroed values
that are the truth about the engine rather than a fallback.
The search providers are the same shape and go one step further: supportsExplain is false too,
because neither product's SQL endpoint returns a plan this repo can render, so the Explain button and
tab are hidden rather than degraded. getSlowQueries(), getActiveSessions(), getIndexStats() and
getPerformanceMetrics() are all deliberately empty — the slow log is a file on the node, a request
is not a session, every mapped field is indexed so no secondary-index object exists to describe, and
the cache counters live in stats APIs outside the transport seam. getPerformanceMetrics() returning
{} rather than zeroes is load-bearing: DEFAULT_THRESHOLDS scores cacheHitRatio with
direction: "below", so a "neutral" 0 would paint a critical cache fault on every healthy cluster,
while an absent ratio reads as healthy.
Trino states its absences from a different direction: it is a query engine, so what it cannot
report is not missing but owned by somebody else. It stores nothing, so databaseSize is the string
"N/A" and the storage panel lists the catalogs with their connectors instead of bytes. Its
information_schema holds eight views and neither table_constraints nor key_column_usage
(measured), so no connector can declare a key through it — declaresForeignKeys and
supportsInlineRowEdit are both false, and getIndexStats() returns [] without sending a
statement at all. getPerformanceMetrics() reports queriesPerSecond and omits every other field
for the same reason the search providers omit theirs: there are no transactions, no buffer pool, no
locks and no checkpoints to measure. The one operation it can genuinely perform is kill, verified
end to end. See providers/trino.md.
- Parameterized queries prevent SQL injection
- MongoDB queries are JSON-parsed, preventing injection
- Connection credentials are never logged
- Pool connections are properly cleaned up
- SSL is auto-enabled for known cloud providers
- Connection pooling provides 5-10x speedup for repeated queries
- Idle connections are automatically closed after 30 seconds
- Query timeouts prevent runaway queries
- Schema queries are optimized with LIMIT clauses
- MongoDB uses estimated document counts for performance
This document describes the architecture and the providers that ship today. The step-by-step guide
to adding a new one — including how to decide whether it needs a driver at all — lives in
ADDING_A_PROVIDER.md.