@molecule/api-database-remote

@molecule/api-database-remote — connect to USER-supplied databases (Postgres / MySQL / SQLite) for inspection, schema browsing, and ad-hoc query execution.

This is distinct from @molecule/api-database (the app's own abstract DataStore). That package is the singleton bond category for the application's primary persistence; this package is a separate driver pool keyed by a user-supplied connection string, intended for the database-admin flagship app and similar features where end-users register external databases to browse.

Why the split:

• The app's DataStore is wired once at startup with a single fixed connection. A user-facing database explorer must support N parallel connections, each opened lazily, each with its own readonly / timeout / pool-size policy.
• The DataStore exposes high-level CRUD (findOne, findMany, …); this utility exposes the low-level surface a UI needs (listSchemas, listTables, describeTable, raw runQuery).

Security:

• runQuery(sql, params) requires parameterized SQL — never interpolate user input into the SQL string.
• readonly: true adds a defence-in-depth keyword sniff (rejects INSERT / UPDATE / DELETE / DDL). Production deployments should ALSO connect with a read-only DB role.
• Per-query timeoutMs and maxRows caps protect against runaway queries; defaults are 30s / 1000 rows.

Network reality: connections are raw TCP from the API process to the user-supplied host (5432 / 3306 by default) — they do NOT traverse HTTP_PROXY-style egress proxies. In deployments with default-deny egress (e.g. molecule.dev sandboxes, where only HTTP(S) via the egress proxy is permitted), remote Postgres/MySQL connections fail at connect with a timeout (RemoteDbError code connection-failed). Direct outbound TCP to the database host/port must be allowed for this package to work; SQLite is exempt (local file path, no network).

Drivers (pg, mysql2, better-sqlite3) are declared as optional peer dependencies and lazy-loaded — only install the engines you need.

Locale bonds are intentionally not used — error messages on the thrown RemoteDbError are developer-facing English (handler-error pattern). Map error.code to translated user-facing strings in the calling handler.

Auto-generated, AI-first package reference for the molecule.dev ecosystem. It is written to be read by coding agents as much as by people, and is generated from this package's source — edit src/index.ts JSDoc, not this file.

Quick Start

import { connectRemote } from '@molecule/api-database-remote'

const db = await connectRemote({
  url: 'postgresql://reader:secret@db.example.com/analytics',
  type: 'postgresql',
  readonly: true,
})

const schemas = await db.listSchemas()
const tables = await db.listTables('public')
const schema = await db.describeTable('public', 'users')

const result = await db.runQuery(
  'SELECT * FROM users WHERE created_at > $1',
  [new Date('2024-01-01')],
  { timeoutMs: 5_000, maxRows: 100 },
)

await db.disconnect()

Type

utility

Installation

npm install @molecule/api-database-remote better-sqlite3 mysql2 pg
npm install -D @types/better-sqlite3 @types/pg

API

Interfaces

ConnectRemoteHooks

Per-driver factory hooks — pass these to connectRemote to swap the real pg / mysql2 / better-sqlite3 driver for a hand-rolled mock during testing.

interface ConnectRemoteHooks {
  /** Factory for `pg.Pool`. Defaults to `(cfg) => new pg.Pool(cfg)`. */
  pgPoolFactory?: PgPoolFactory
  /** Factory for `mysql2/promise.createPool`. Defaults to the real one. */
  mysqlPoolFactory?: MysqlPoolFactory
  /** Factory for `better-sqlite3`. Defaults to `new BetterSqlite3(...)`. */
  sqliteFactory?: SqliteDbFactory
}

PgQueryResult

Subset of pg.QueryResult consumed by PgPoolLike.query.

interface PgQueryResult<T = Record<string, unknown>> {
  rows: T[]
  rowCount: number | null
  fields?: Array<{ name: string; dataTypeID?: number }>
}

PgPoolClientLike

Subset of pg.PoolClient returned by PgPoolLike.connect.

interface PgPoolClientLike {
  query<T = Record<string, unknown>>(text: string, values?: unknown[]): Promise<PgQueryResult<T>>
  release(err?: Error | boolean): void
}

PgPoolLike

Subset of pg.Pool we use.

interface PgPoolLike {
  query<T = Record<string, unknown>>(text: string, values?: unknown[]): Promise<PgQueryResult<T>>
  connect(): Promise<PgPoolClientLike>
  end(): Promise<void>
}

MysqlField

Field-info subset (mysql2 calls these "fields").

interface MysqlField {
  name: string
  type?: number
  columnType?: number
}

MysqlOkPacket

mysql2 OkPacket-like shape for INSERT/UPDATE/DELETE.

interface MysqlOkPacket {
  affectedRows: number
  insertId?: number
  changedRows?: number
}

MysqlPoolLike

Subset of mysql2/promise.Pool we use.

interface MysqlPoolLike {
  query(sql: string, values?: unknown[]): Promise<MysqlQueryResult>
  end(): Promise<void>
}

SqliteStatementLike

Subset of a prepared better-sqlite3 statement we use.

interface SqliteStatementLike {
  all(...params: unknown[]): unknown[]
  run(...params: unknown[]): { changes: number; lastInsertRowid: number | bigint }
  raw(toggle: boolean): SqliteStatementLike
  columns(): Array<{ name: string; type: string | null }>
  reader: boolean
}

SqliteDbLike

Subset of better-sqlite3.Database we use.

interface SqliteDbLike {
  prepare(sql: string): SqliteStatementLike
  pragma(source: string, options?: { simple?: boolean }): unknown
  close(): void
  readonly: boolean
}

RemoteDbConnection

Configuration for connectRemote. Connection strings are passed to the underlying driver as-is, so they may use any driver-supported URL scheme (postgresql://, mysql://, file path / :memory: for SQLite).

interface RemoteDbConnection {
  /**
   * Connection URL (Postgres / MySQL) or filesystem path (SQLite). For
   * SQLite, `:memory:` selects an anonymous in-memory database.
   */
  url: string
  /** Engine selector — picks the underlying driver. */
  type: RemoteDbType
  /**
   * When `true`, mutating SQL (`INSERT` / `UPDATE` / `DELETE` / DDL) is
   * rejected by `RemoteDb.runQuery` via a keyword sniff. Defaults to
   * `false`.
   *
   * Note: the keyword sniff is a defence-in-depth check, NOT a substitute
   * for the database engine's own permissions / read-only role. Production
   * deployments should also configure a read-only DB user.
   */
  readonly?: boolean
  /**
   * Maximum number of pooled connections (Postgres / MySQL only). Defaults
   * to `4` to keep the per-user-database fan-out small. Ignored for SQLite
   * (which uses a single in-process handle).
   */
  poolSize?: number
}

RunQueryOptions

Per-query overrides for RemoteDb.runQuery.

interface RunQueryOptions {
  /**
   * Hard timeout in milliseconds — the query is aborted if it exceeds this.
   * Defaults to `30_000` (30s).
   */
  timeoutMs?: number
  /**
   * Maximum number of rows to return. Excess rows are dropped and
   * `QueryResult.truncated` is set to `true`. Defaults to `1_000`.
   */
  maxRows?: number
}

QueryColumn

Column metadata returned by RemoteDb.runQuery. The shape is normalized across all three drivers — only the column name is guaranteed.

interface QueryColumn {
  /** Column name as the engine reported it. */
  name: string
  /**
   * Engine-specific data-type hint (Postgres OID name, MySQL type code,
   * SQLite declared type). May be undefined when the driver doesn't expose
   * it for the given query.
   */
  dataType?: string
}

QueryResult

Result of a successful RemoteDb.runQuery call. Identical shape regardless of which engine produced it.

interface QueryResult {
  /** Column metadata in the order returned by the engine. */
  columns: QueryColumn[]
  /**
   * Result rows. Each row is a plain object keyed by column name; values
   * are whatever the driver returned (numbers, strings, dates, buffers,
   * etc.). For mutating statements, this is an empty array.
   */
  rows: Array<Record<string, unknown>>
  /**
   * Number of rows actually returned (for `SELECT`) or affected (for
   * `INSERT` / `UPDATE` / `DELETE`).
   */
  rowCount: number
  /** Wall-clock time spent executing the query, in milliseconds. */
  executionTimeMs: number
  /**
   * `true` if the result was capped at `RunQueryOptions.maxRows`.
   * Callers should warn the user that more rows exist server-side.
   */
  truncated?: boolean
}

ColumnSchema

A normalized table-column definition returned by RemoteDb.describeTable.

interface ColumnSchema {
  /** Column name. */
  name: string
  /** Engine-reported data type (e.g. `integer`, `varchar(255)`, `INTEGER`). */
  dataType: string
  /** `true` if the column allows `NULL`. */
  nullable: boolean
  /** `true` if the column is part of the primary key. */
  primaryKey: boolean
  /** Default-value expression as the engine reported it, if any. */
  defaultValue?: string
}

IndexSchema

A normalized index definition returned as part of TableSchema.

interface IndexSchema {
  /** Index name. */
  name: string
  /** Columns covered by the index, in order. */
  columns: string[]
  /** `true` if the index enforces uniqueness. */
  unique: boolean
}

ForeignKeySchema

A normalized foreign-key constraint returned as part of TableSchema.

interface ForeignKeySchema {
  /** Constraint name (engine-supplied, may be auto-generated). */
  name: string
  /** Local columns participating in the FK. */
  columns: string[]
  /** Schema name of the referenced table (Postgres / MySQL). */
  referencedSchema?: string
  /** Referenced table name. */
  referencedTable: string
  /** Referenced columns, paired positionally with `columns`. */
  referencedColumns: string[]
}

TableSchema

Full schema of a single table — the result of RemoteDb.describeTable.

interface TableSchema {
  /** Schema (Postgres) / database (MySQL) / `'main'` (SQLite). */
  schema: string
  /** Table name. */
  name: string
  /** Column definitions, in declared order. */
  columns: ColumnSchema[]
  /** Indexes (including the implicit primary-key index). */
  indexes: IndexSchema[]
  /** Foreign-key constraints declared on this table. */
  foreignKeys: ForeignKeySchema[]
}

SchemaInfo

A schema / database listed by RemoteDb.listSchemas.

interface SchemaInfo {
  /** Schema name (Postgres) / database name (MySQL) / `'main'` (SQLite). */
  name: string
}

TableInfo

A table listed by RemoteDb.listTables.

interface TableInfo {
  /** Containing schema / database. */
  schema: string
  /** Table name. */
  name: string
  /** `'table'` for ordinary tables, `'view'` for views. */
  type: 'table' | 'view'
}

RemoteDb

Connected remote-database handle returned by connectRemote. All methods are async; calling any after RemoteDb.disconnect throws a RemoteDbError with code === 'not-connected'.

RemoteDb is intentionally an opaque interface — driver internals (pg.Pool, mysql2.Connection, better-sqlite3.Database) are NOT exposed.

interface RemoteDb {
  /** Engine type the connection was opened with. */
  readonly type: RemoteDbType
  /** `true` if the connection was opened with `readonly: true`. */
  readonly readonly: boolean
  /**
   * List user-visible schemas (Postgres) / databases (MySQL). For SQLite,
   * always returns `[{ name: 'main' }]`.
   */
  listSchemas(): Promise<SchemaInfo[]>
  /**
   * List tables and views in a schema. When `schema` is omitted, the
   * default schema is used (`public` for Postgres, the connected database
   * for MySQL, `main` for SQLite).
   */
  listTables(schema?: string): Promise<TableInfo[]>
  /**
   * Inspect a single table's columns, indexes, and foreign keys.
   *
   * @throws {RemoteDbError} `code === 'table-not-found'` if no such table.
   */
  describeTable(schemaName: string, tableName: string): Promise<TableSchema>
  /**
   * Execute a parameterized SQL query. Parameter placeholders are
   * driver-native: `$1, $2, …` for Postgres, `?` for MySQL and SQLite.
   *
   * String interpolation is forbidden — pass user input through `params`
   * to avoid SQL injection.
   *
   * @throws {RemoteDbError} `code === 'readonly-violation'` if the query is
   *   mutating and the connection was opened with `readonly: true`.
   * @throws {RemoteDbError} `code === 'timeout'` if the query exceeds
   *   `opts.timeoutMs`.
   * @throws {RemoteDbError} `code === 'query-failed'` for engine errors.
   */
  runQuery(sql: string, params?: unknown[], opts?: RunQueryOptions): Promise<QueryResult>
  /** Close the connection / pool. Idempotent. */
  disconnect(): Promise<void>
}

Types

PgPoolFactory

Constructor signature compatible with new pg.Pool(config).

type PgPoolFactory = (config: { connectionString: string; max?: number }) => PgPoolLike

MysqlRow

A single row returned by mysql2 for SELECT.

type MysqlRow = Record<string, unknown>

MysqlQueryResult

mysql2 query result tuple — [rows, fields].

type MysqlQueryResult = [MysqlRow[], MysqlField[]] | [MysqlOkPacket, MysqlField[] | undefined]

MysqlPoolFactory

Constructor signature compatible with mysql2/promise.createPool(config).

type MysqlPoolFactory = (config: {
  uri: string
  connectionLimit?: number
  multipleStatements?: false
}) => MysqlPoolLike

SqliteDbFactory

Constructor signature compatible with new BetterSqlite3(file, options).

type SqliteDbFactory = (
  file: string,
  options?: { readonly?: boolean; fileMustExist?: boolean; timeout?: number },
) => SqliteDbLike

RemoteDbType

Supported remote database engines. The driver is selected from this value; each maps 1:1 to an optional peer dependency:

type	driver
postgresql	pg
mysql	mysql2
sqlite	better-sqlite3

type RemoteDbType = 'postgresql' | 'mysql' | 'sqlite'

RemoteDbErrorCode

Stable error code surfaced on RemoteDbError. Map these to translated user-facing strings in the calling handler — this utility is intentionally locale-bond-free (handler-error pattern).

type RemoteDbErrorCode =
  | 'connection-failed'
  | 'driver-not-installed'
  | 'invalid-config'
  | 'not-connected'
  | 'query-failed'
  | 'readonly-violation'
  | 'table-not-found'
  | 'timeout'
  | 'unsupported-type'

Classes

RemoteDbError

Strongly-typed error thrown by connectRemote and any RemoteDb method.

class RemoteDbError extends Error {
  /** Stable machine-readable error code — switch on this in handlers. */
  readonly code: RemoteDbErrorCode

  constructor(code: RemoteDbErrorCode, message: string, options?: ErrorOptions) {
    super(message, options)
    this.name = 'RemoteDbError'
    this.code = code
  }
}

Functions

connectRemote(connection, hooks)

Connect to a user-supplied remote database for inspection and query execution. Distinct from the app's own DataStore — this is a separate driver pool, keyed by connection.url, intended for the database-admin flagship app and similar features where end-users register databases to browse.

Drivers are loaded lazily — only the engine the caller actually uses will be import()ed, so missing optional peer deps (pg, mysql2, better-sqlite3) only trip a driver-not-installed error when that engine is selected.

function connectRemote(
  connection: RemoteDbConnection,
  hooks?: ConnectRemoteHooks,
): Promise<RemoteDb>

• connection — Engine type, URL / path, optional read-only flag, optional pool size.
• hooks — Optional driver-factory overrides for testing.

Returns: A connected RemoteDb.

isMutating(sql)

Returns true if the supplied SQL string mutates state under the read-only sniff. Whitespace and SQL comments are stripped before the leading verb is matched.

The sniff is conservative — anything that isn't a recognized read-only verb (SELECT, WITH, EXPLAIN, SHOW, DESCRIBE, DESC, PRAGMA) starting the query is treated as mutating.

function isMutating(sql: string): boolean

• sql — Raw SQL text supplied to runQuery.

Returns: true if the query is rejected in readonly mode.

createMysqlRemoteDb(pool, defaultDb, readonly)

Build a RemoteDb backed by a mysql2/promise pool. The pool is owned by the returned handle — disconnect() calls pool.end().

function createMysqlRemoteDb(pool: MysqlPoolLike, defaultDb: string, readonly: boolean): RemoteDb

• pool — Connected mysql2 pool.
• defaultDb — Database name parsed from the connection URI; used as the default schema for RemoteDb.listTables when none is supplied.
• readonly — When true, mutating SQL is rejected before the pool sees it.

defaultMysqlPoolFactory()

Lazy-import mysql2/promise.createPool. Throws RemoteDbError code === 'driver-not-installed' when the driver is missing.

function defaultMysqlPoolFactory(): Promise<MysqlPoolFactory>

parseMysqlDatabase(uri)

Extract the database name from a mysql://user:pass@host:port/db URI. Returns an empty string if the URI has no path.

function parseMysqlDatabase(uri: string): string

createPgRemoteDb(pool, readonly)

Build a RemoteDb backed by a pg.Pool.

function createPgRemoteDb(pool: PgPoolLike, readonly: boolean): RemoteDb

• pool — Connected pool. Ownership transfers — the caller must NOT keep a reference to the pool, as disconnect() will drain it.
• readonly — When true, mutating SQL is rejected before the pool sees it.

Returns: A connected handle.

defaultPgPoolFactory()

Construct the default pg.Pool factory by lazy-importing pg. Throws RemoteDbError code === 'driver-not-installed' if the driver is not installed in the host project.

function defaultPgPoolFactory(): Promise<PgPoolFactory>

collectIndexes(rows)

Aggregate flat per-column index rows into a deduplicated list of IndexSchema objects.

function collectIndexes(
  rows: { index_name: string; column_name: string; is_unique: boolean }[],
): IndexSchema[]

collectForeignKeys(rows)

Aggregate flat per-column foreign-key rows into a deduplicated list of ForeignKeySchema objects.

function collectForeignKeys(
  rows: {
    constraint_name: string
    column_name: string
    foreign_schema: string
    foreign_table: string
    foreign_column: string
  }[],
): ForeignKeySchema[]

raceWithTimeout(promise, timeoutMs, onTimeout)

Race a promise against a timeout. The onTimeout hook fires once when the timeout wins so the caller can release driver-level resources.

function raceWithTimeout(promise: Promise<T>, timeoutMs: number, onTimeout: () => void): Promise<T>

createSqliteRemoteDb(db, readonly)

Build a RemoteDb backed by a better-sqlite3 database handle. Ownership transfers — disconnect() calls db.close().

function createSqliteRemoteDb(db: SqliteDbLike, readonly: boolean): RemoteDb

quoteIdentifier(name)

Quote a SQLite identifier (schema, table, index name). PRAGMA statements do NOT accept bound parameters, so callers must inline names — quoting is the only defence against injection.

function quoteIdentifier(name: string): string

defaultSqliteFactory()

Lazy-import better-sqlite3. Throws RemoteDbError code === 'driver-not-installed' when missing.

function defaultSqliteFactory(): Promise<SqliteDbFactory>

Constants

DEFAULT_TIMEOUT_MS

Default per-query timeout in milliseconds — applied when RunQueryOptions.timeoutMs is not set.

const DEFAULT_TIMEOUT_MS: 30000

DEFAULT_MAX_ROWS

Default cap on rows returned by a single RemoteDb.runQuery call. Excess rows are dropped and truncated: true is set on the result.

const DEFAULT_MAX_ROWS: 1000

DEFAULT_POOL_SIZE

Default pg.Pool size for remote inspection connections.

const DEFAULT_POOL_SIZE: 4

Injection Notes

Requirements

Peer dependencies:

• better-sqlite3 ^12.0.0
• mysql2 ^3.0.0
• pg ^8.0.0

Runtime Dependencies

• better-sqlite3
• mysql2
• pg