Database Provider Architecture

August 4, 2026 · View on GitHub

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.

Overview

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.

Architecture

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)
│   │   ├── 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
│   ├── 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)

Provider Hierarchy

BaseDatabaseProvider (abstract)
├── SQLBaseProvider (abstract) ─────────────┐
│   ├── PostgresProvider                    │
│   ├── MySQLProvider                       │ SQL Databases
│   ├── SQLiteProvider                      │ (shared SQL utilities)
│   ├── OracleProvider                      │
│   ├── MSSQLProvider                       │
│   ├── ClickHouseProvider                  │
│   └── DruidProvider                       │
├── 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, placeholder generation). 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.

Being driver-free and reached over HTTP is not what decides the base class. ClickHouseProvider and DruidProvider add no driver either, and both extend SQLBaseProvider: double-quoted identifiers and LIMIT n OFFSET m are correct in both dialects, so identifier escaping, the LIMIT builder and the placeholder style are inherited rather than rewritten. Druid overrides only prepareQuery(), and only because it 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. See providers/clickhouse.md and providers/druid.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

Supported Databases

Eleven providers are supported. 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
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
LibreDB	`libredb`	Embedded (key-value)	providers/libredb.md

Core Interface

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>;

  // Schema operations
  getSchema(): Promise<TableSchema[]>;
  getTables(): Promise<string[]>;

  // Health & monitoring
  getHealth(): Promise<HealthInfo>;

  // Maintenance operations
  runMaintenance(type: MaintenanceType, target?: string): Promise<MaintenanceResult>;

  // Validation
  validate(): void;
}

Usage

Basic Usage (Recommended)

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 }
}));

Direct Provider Creation

import { createDatabaseProvider } from '@/lib/db/factory';

const provider = await createDatabaseProvider(connection, {
  pool: { min: 2, max: 10 },
  queryTimeout: 30000,
});

await provider.connect();
const schema = await provider.getSchema();
await provider.disconnect();

Non-SQL Query Formats

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 the API_DOCS.md MongoDB 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, the shared limiter, and NL2SQL. Its keyspaces are backtick-quoted three-part paths (`bucket`.`scope`.`collection`) — see providers/couchbase.md.

Configuration

Pool Configuration

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)
}

Query Timeout

Default query timeout is 60 seconds (60000ms). Configure per-provider:

const provider = await createDatabaseProvider(connection, {
  queryTimeout: 30000, // 30 seconds
});

Error Handling

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}`);
  }
}

Error Hierarchy

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 Features

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, Redis, MongoDB, Couchbase, ClickHouse, Apache Druid, 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.

Security Considerations

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

Performance Notes

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

Adding a New Provider

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.