@molecule/api-database

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.

Database core interface for molecule.dev.

Defines the standard interface for database providers, including both raw connection pools and the database-agnostic DataStore abstraction.

Quick Start

import {
  setStore,
  findById,
  findMany,
  create,
  updateById,
  deleteById,
} from '@molecule/api-database'
import { store } from '@molecule/api-database-postgresql'

// Wire the DataStore at app startup
setStore(store)

// CRUD operations — database-agnostic
const user = await findById<User>('users', userId)

const activeUsers = await findMany<User>('users', {
  where: [{ field: 'status', operator: '=', value: 'active' }],
  orderBy: [{ field: 'createdAt', direction: 'desc' }],
  limit: 50,
})

await create('users', { id, username, email })
await updateById('users', id, { name: 'New Name' })
await deleteById('users', id)

Type

core

Installation

npm install @molecule/api-database @molecule/api-bond @molecule/api-i18n

API

Interfaces

DatabaseConfig

Database connection options (host, port, credentials, pool size, timeouts, SSL).

interface DatabaseConfig {
  /**
   * Database host.
   */
  host?: string

  /**
   * Database port.
   */
  port?: number

  /**
   * Database name.
   */
  database?: string

  /**
   * Database user.
   */
  user?: string

  /**
   * Database password.
   */
  password?: string

  /**
   * Connection string (alternative to individual fields).
   */
  connectionString?: string

  /**
   * Maximum number of connections in the pool.
   */
  max?: number

  /**
   * Minimum number of connections in the pool.
   */
  min?: number

  /**
   * Connection timeout in milliseconds.
   */
  connectionTimeoutMillis?: number

  /**
   * Idle timeout in milliseconds.
   */
  idleTimeoutMillis?: number

  /**
   * Enable SSL.
   */
  ssl?:
    | boolean
    | {
        rejectUnauthorized?: boolean
        ca?: string
        key?: string
        cert?: string
      }
}

DatabaseConnection

Database connection interface.

interface DatabaseConnection {
  /**
   * Executes a parameterized query.
   *
   * @param text - SQL query text with placeholders ($1, $2, etc.)
   * @param values - Parameter values
   */
  query<T = Record<string, unknown>>(text: string, values?: unknown[]): Promise<QueryResult<T>>

  /**
   * Releases the connection back to the pool.
   */
  release(): void
}

DatabasePool

Database pool interface.

All database providers must implement this interface.

interface DatabasePool {
  /**
   * Executes a parameterized query using a pool connection.
   *
   * @param text - SQL query text with placeholders ($1, $2, etc.)
   * @param values - Parameter values
   */
  query<T = Record<string, unknown>>(text: string, values?: unknown[]): Promise<QueryResult<T>>

  /**
   * Acquires a connection from the pool.
   */
  connect(): Promise<DatabaseConnection>

  /**
   * Begins a transaction.
   */
  transaction?(): Promise<DatabaseTransaction>

  /**
   * Closes all connections in the pool.
   */
  end(): Promise<void>

  /**
   * Returns pool statistics (optional).
   */
  stats?(): {
    total: number
    idle: number
    waiting: number
  }
}

DatabaseProvider

Database provider interface.

interface DatabaseProvider {
  /**
   * The connection pool.
   */
  pool: DatabasePool

  /**
   * Creates a new pool with the given configuration.
   */
  createPool?(config: DatabaseConfig): DatabasePool
}

DatabaseTransaction

Database transaction with commit and rollback (extends DatabaseConnection).

interface DatabaseTransaction extends DatabaseConnection {
  /**
   * Commits the transaction.
   */
  commit(): Promise<void>

  /**
   * Rolls back the transaction.
   */
  rollback(): Promise<void>
}

DataStore

Abstract data store interface.

Provides database-agnostic CRUD methods. Each database bond implements these using its native query language.

interface DataStore {
  /**
   * Find a single record by its primary key.
   */
  findById<T = Record<string, unknown>>(table: string, id: string | number): Promise<T | null>

  /**
   * Find a single record matching conditions.
   */
  findOne<T = Record<string, unknown>>(table: string, where: WhereCondition[]): Promise<T | null>

  /**
   * Find many records with filtering, sorting, and pagination.
   */
  findMany<T = Record<string, unknown>>(table: string, options?: FindManyOptions): Promise<T[]>

  /**
   * Count records matching conditions.
   */
  count(table: string, where?: WhereCondition[]): Promise<number>

  /**
   * Insert a new record. Returns the inserted row.
   */
  create<T = Record<string, unknown>>(
    table: string,
    data: Record<string, unknown>,
  ): Promise<MutationResult<T>>

  /**
   * Update a record by primary key. Returns the updated row.
   */
  updateById<T = Record<string, unknown>>(
    table: string,
    id: string | number,
    data: Record<string, unknown>,
  ): Promise<MutationResult<T>>

  /**
   * Update records matching conditions.
   */
  updateMany(
    table: string,
    where: WhereCondition[],
    data: Record<string, unknown>,
  ): Promise<MutationResult>

  /**
   * Delete a record by primary key.
   */
  deleteById(table: string, id: string | number): Promise<MutationResult>

  /**
   * Delete records matching conditions.
   */
  deleteMany(table: string, where: WhereCondition[]): Promise<MutationResult>
}

FindManyOptions

Options for findMany queries.

interface FindManyOptions {
  where?: WhereCondition[]
  orderBy?: OrderBy[]
  limit?: number
  offset?: number
  select?: string[]
}

MutationResult

Result of a mutation operation.

interface MutationResult<T = Record<string, unknown>> {
  /** The affected/returned row, if any. */
  data: T | null
  /** Number of rows affected. */
  affected: number
}

OrderBy

Column sort order (field name and ascending/descending direction).

interface OrderBy {
  field: string
  direction: 'asc' | 'desc'
}

QueryResult

Query result with rows and metadata.

interface QueryResult<T = Record<string, unknown>> {
  /**
   * Array of rows returned by the query.
   */
  rows: T[]

  /**
   * Number of rows affected (for INSERT, UPDATE, DELETE).
   */
  rowCount: number | null

  /**
   * Column metadata (optional, provider-dependent).
   */
  fields?: Array<{
    name: string
    dataTypeID?: number
  }>
}

WhereCondition

Filter condition for queries.

interface WhereCondition {
  field: string
  operator:
    | '='
    | '!='
    | '>'
    | '<'
    | '>='
    | '<='
    | 'in'
    | 'not_in'
    /**
     * Case-insensitive SQL pattern match. `value` is used AS-IS as the raw
     * LIKE pattern: `%` matches any run of characters, `_` matches any
     * single character, and the CALLER is responsible for escaping any
     * literal `%` / `_` / `\` they don't want treated as wildcards. All
     * three first-party bonds (postgresql, mysql, sqlite) implement `like`
     * case-INSENSITIVELY — postgres via `ILIKE`, mysql/sqlite via
     * `LOWER()` on both sides — specifically so this operator has
     * IDENTICAL results across every bond regardless of column collation
     * or `PRAGMA case_sensitive_like`. Prior to this contract the postgres
     * bond escaped the value (making `like` an exact-match operator there
     * only) while sqlite/mysql passed it through raw — the same `{
     * operator: 'like', value: `%${search}%` }` filter silently matched
     * nothing on postgres and worked everywhere else. For human-typed
     * search input where the value should be a literal substring (not a
     * pattern the caller controls), use `ilike` instead — it escapes
     * wildcards and wraps `%…%` for you, so it is injection-safe for
     * arbitrary user text.
     */
    | 'like'
    /**
     * Case-insensitive substring match. The value is treated as a
     * literal substring (LIKE wildcards in the value are escaped) and
     * the bond wraps it with `%…%` to perform a contains-style match.
     * Use this for human-typed search input where case shouldn't matter.
     */
    | 'ilike'
    | 'is_null'
    | 'is_not_null'
  value?: unknown
}

Functions

connect()

Acquires a dedicated connection from the bonded pool. The connection must be released after use by calling connection.release().

function connect(): Promise<DatabaseConnection>

Returns: A database connection that must be released after use.

count(table, where)

Counts records matching the given filter conditions, or all records if no conditions are specified.

function count(table: string, where?: WhereCondition[]): Promise<number>

• table — The database table name.
• where — Optional filter conditions.

Returns: The number of matching records.

create(table, data)

Inserts a new record into the table. Returns the inserted row and the number of affected rows (always 1 on success).

function create(table: string, data: Record<string, unknown>): Promise<MutationResult<T>>

• table — The database table name.
• data — The column values to insert as key-value pairs.

Returns: A MutationResult with the inserted row and affected count.

deleteById(table, id)

Deletes a record identified by its primary key.

function deleteById(
  table: string,
  id: string | number,
): Promise<MutationResult<Record<string, unknown>>>

• table — The database table name.
• id — The primary key value of the record to delete.

Returns: A MutationResult with the affected count (1 if deleted, 0 if not found).

deleteMany(table, where)

Deletes all records matching the given filter conditions.

function deleteMany(
  table: string,
  where: WhereCondition[],
): Promise<MutationResult<Record<string, unknown>>>

• table — The database table name.
• where — Filter conditions to select records to delete.

Returns: A MutationResult with the number of deleted rows.

end()

Closes all connections in the bonded pool. Call during graceful shutdown.

function end(): Promise<void>

Returns: A promise that resolves when all connections have been closed.

findById(table, id)

Finds a single record by its primary key (id column).

function findById(table: string, id: string | number): Promise<T | null>

• table — The database table name.
• id — The primary key value to look up.

Returns: The matching record cast to T, or null if not found.

findMany(table, options)

Finds multiple records with optional filtering, sorting, pagination, and column selection.

function findMany(table: string, options?: FindManyOptions): Promise<T[]>

• table — The database table name.
• options — Query options including where, orderBy, limit, offset, and select.

Returns: Array of matching records cast to T.

findOne(table, where)

Finds a single record matching the given filter conditions. Returns the first match if multiple rows satisfy the conditions.

function findOne(table: string, where: WhereCondition[]): Promise<T | null>

• table — The database table name.
• where — Array of filter conditions to match against.

Returns: The matching record cast to T, or null if none found.

getPool()

Retrieves the bonded database pool, throwing if none is configured.

function getPool(): DatabasePool

Returns: The bonded database pool.

getStore()

Retrieves the bonded DataStore, throwing if none is configured.

function getStore(): DataStore

Returns: The bonded DataStore implementation.

hasPool()

Checks whether a database pool is currently bonded.

function hasPool(): boolean

Returns: true if a database pool is bonded.

hasStore()

Checks whether a DataStore is currently bonded.

function hasStore(): boolean

Returns: true if a DataStore is bonded.

query(text, values)

Executes a parameterized SQL query using the bonded pool.

function query(text: string, values?: unknown[]): Promise<QueryResult<T>>

• text — SQL query text with placeholders ($1, $2, etc.).
• values — Parameter values corresponding to the placeholders.

Returns: The query result containing rows, rowCount, and optional fields.

setPool(pool)

Registers a database connection pool as the active singleton. Called by bond packages during application startup.

function setPool(pool: DatabasePool): void

• pool — The database pool implementation to bond.

setStore(store)

Registers a DataStore implementation as the active singleton. Called by bond packages during application startup.

function setStore(store: DataStore): void

• store — The DataStore implementation to bond.

updateById(table, id, data)

Updates a record identified by its primary key. Returns the updated row and the number of affected rows.

function updateById(
  table: string,
  id: string | number,
  data: Record<string, unknown>,
): Promise<MutationResult<T>>

• table — The database table name.
• id — The primary key value of the record to update.
• data — The column values to update as key-value pairs.

Returns: A MutationResult with the updated row and affected count.

updateMany(table, where, data)

Updates all records matching the given filter conditions.

function updateMany(
  table: string,
  where: WhereCondition[],
  data: Record<string, unknown>,
): Promise<MutationResult<Record<string, unknown>>>

• table — The database table name.
• where — Filter conditions to select records to update.
• data — The column values to update as key-value pairs.

Returns: A MutationResult with the affected count.

Available Providers

Provider	Package
Cloudflare D1	@molecule/api-database-d1
MySQL	@molecule/api-database-mysql
PostgreSQL	@molecule/api-database-postgresql
SQLite	@molecule/api-database-sqlite

Injection Notes

Requirements

Peer dependencies:

• @molecule/api-bond ^1.0.1
• @molecule/api-i18n ^1.0.1

Runtime Dependencies

• @molecule/api-bond
• @molecule/api-i18n

Data-access contract — the rules code generators most often get wrong:

• Scope EVERY query by owner — the #1 security rule. A user must never read or write another user's rows. Add { field: 'user_id', operator: '=', value: getUserId(res) } to every list/read where, and for a single-row route load the row and 404 if it isn't the caller's (404, not 403 — don't leak existence). An unscoped findById/updateById/ deleteById on a client-supplied id is an IDOR (one user edits another's data). See the auth skill.
• CRUD goes through the exported data functions (findById, findOne, findMany, count, create, updateById, deleteById). Filter with a where ARRAY of { field, operator, value }: findMany('plants', { where: [{ field: 'speciesId', operator: '=', value: id }], limit: 50 }). A bare key object — findMany('plants', { speciesId: id }) — is NOT a valid FindManyOptions and will not filter.
• Raw SQL: import the standalone query(sql, values) from this package — NOT getStore().query(). The DataStore has no query; raw parameterized query lives on the pool/provider. The store also has NO .exec(), .prepare(), .run(), .get(), or .all() — those are driver methods (better-sqlite3 / pg) and calling them fails the type-check.
• Atomic multi-write = a MANUAL transaction on ONE connection; the store helpers do NOT join it. When several writes must be all-or-nothing (move funds between two accounts, create an order AND decrement its stock, accept a booking AND mark the slot taken), acquire a dedicated connection and drive raw SQL on IT: const conn = await connect() then try { await conn.query('BEGIN'); …writes…; await conn.query('COMMIT') } catch (e) { await conn.query('ROLLBACK').catch(() => {}); throw e } finally { conn.release() }. The load-bearing gotcha: the exported create/updateById/findMany/deleteById run on the POOL's own connection and commit INDEPENDENTLY — calling them between your BEGIN and COMMIT does NOT enroll them in the transaction, so a mid-way failure leaves those writes committed while the rest rolls back (a half-applied transfer). Inside a transaction do every read AND write with conn.query(sql, values) on that same conn, and ALWAYS release() in finally — a leaked connection permanently drains the pool. Prefer this only for true multi-row invariants; a single-row update is already atomic via updateById.
• Create tables only via timestamped .sql files in migrations/ (the migration runner applies them on startup) — NEVER programmatically through the store. Seed rows with create().
• Every id is a UUID string (the resource layer sets id = id || uuid() on create). So every primary key AND foreign key is a UUID-string column: id TEXT PRIMARY KEY (SQLite) / id UUID PRIMARY KEY (Postgres); FKs likewise (user_id TEXT / user_id UUID). NEVER INTEGER PRIMARY KEY AUTOINCREMENT or SERIAL — inserting a UUID string into an integer key fails at runtime (datatype mismatch) and breaks every create endpoint.
• like vs ilike — pick by who controls the wildcards. Both are case-insensitive on every bond (identical results across postgresql/ mysql/sqlite — see the WhereCondition['operator'] JSDoc for exactly how each bond gets there). like passes value through as a raw SQL pattern — YOU write the %/_; use it when you already built the pattern (e.g. a fixed prefix search 'admin_%'). ilike treats value as a literal substring, escapes it, and wraps %…% for you — use it for ANY human-typed search box ({ operator: 'ilike', value: userInput }), never like with a caller-built %${userInput}% string.

Translations

Translation strings are provided by @molecule/api-locales-database.