@molecule/api-database

Database core interface for molecule.dev.

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

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

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
}

OrderBy

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

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

FindManyOptions

Options for findMany queries.

interface FindManyOptions {
  where?: WhereCondition[]
  /**
   * The order the rows come back in, applied before `limit`/`offset`.
   *
   * **Row order is unspecified unless this is given** — see the note on
   * `DataStore.findMany`. Pass it for every list whose order the caller
   * depends on, not only for a list that "looks" sorted.
   */
  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
}

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.
   *
   * **Row order is unspecified unless `orderBy` is given.** No bond sorts for
   * you — no database does. The rows come back in whatever order the plan
   * produces: an un-indexed full scan yields heap order (which on SQLite is
   * often insertion order), but a `where` on an INDEXED column — a foreign key
   * like an `articleId`, an `orderId`, a `userId` — is answered from that
   * index, so the rows arrive in the index's order instead. Two rows inserted
   * first/second can therefore come back second/first.
   *
   * So any caller that depends on the order of the rows must pass `orderBy`
   * explicitly — a list a spec asserts (`tagList[0]`, `items[0]`, a `[0]`/`[1]`
   * index anywhere), a join table whose insertion order IS the client's data, a
   * "newest first" feed. `orderBy: [{ field: 'createdAt', direction: 'desc' }]`
   * is the usual one. Never assume the order rows were inserted in, and never
   * assume the order an earlier call happened to return. When the table has no
   * column that encodes the intended order, store one — a `position` integer
   * written from the array index (see `@molecule/api-resource-article`'s
   * `article_tags` and `@molecule/api-resource-order`'s `order_items`).
   */
  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>
}

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

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
}

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

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
}

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

Functions

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.

getPool()

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

function getPool(): DatabasePool

Returns: The bonded database pool.

hasPool()

Checks whether a database pool is currently bonded.

function hasPool(): boolean

Returns: true if a database pool 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 — a plain object with no .get(), .all() or .run() on it (those are better-sqlite3 driver methods; rows[0] is the "one row" here).

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.

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.

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.

getStore()

Retrieves the bonded DataStore, throwing if none is configured.

function getStore(): DataStore

Returns: The bonded DataStore implementation.

hasStore()

Checks whether a DataStore is currently bonded.

function hasStore(): boolean

Returns: true if a DataStore is bonded.

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.

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.

findMany(table, options)

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

The order of the rows is unspecified unless options.orderBy is given. A where on an INDEXED column (an articleId, an orderId, a userId) is answered from that index, so the rows come back in the index's order — not the order they were inserted in, and not the order an earlier call returned. Pass orderBy for every list whose order the caller depends on: a feed (orderBy: [{ field: 'createdAt', direction: 'desc' }]), a list a spec asserts an index of (tagList[0], items[0]), a join table whose insertion order is the client's data. See DataStore.findMany for the full note.

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, in the order options.orderBy asks for (an unspecified order when it is omitted).

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.

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.

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.

getActiveTransaction()

The transaction opened by the enclosing withTransaction() call, if any. Bond pools call this from query() and send the statement to the active transaction when it is one they opened.

function getActiveTransaction(): DatabaseTransaction | undefined

Returns: The active transaction, or undefined outside withTransaction().

withTransaction(fn)

Runs fn inside one database transaction: commits when it resolves, rolls back and rethrows when it throws. Every database call made while fn runs joins the transaction — create/findMany/updateById/deleteById, raw query(), and the resource packages built on them — so all-or-nothing work needs no separate connection and no raw SQL:

await withTransaction(async () => {
  const order = await create('orders', { userId, total })
  await updateById('products', productId, { stock: stock - 1 })
})

A nested withTransaction() joins the outer one. Calls made by unrelated concurrent requests do not join it.

function withTransaction(fn: () => Promise<T>): Promise<T>

• fn — The work to run atomically.

Returns: What fn resolves to.

Available Providers

Provider	Package
Cloudflare D1	@molecule/api-database-d1
MySQL	@molecule/api-database-mysql
PGlite	@molecule/api-database-pglite
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.
• A list's order is unspecified unless you pass orderBy. findMany returns rows in whatever order the database's plan produces: a where on an INDEXED column (an articleId, an orderId, a userId) is answered from that index, so the rows come back in the INDEX's order — NOT the order they were inserted. Pass orderBy: [{ field: 'createdAt', direction: 'desc' }] for anything whose order matters (a feed, a list a spec asserts an index of — tagList[0], items[0]). If no column encodes the intended order, store one: a position integer written from the array index (see @molecule/api-resource-article's article_tags, @molecule/api-resource-order's order_items).
• 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. What query() RESOLVES to is one plain object, { rows, rowCount, fields? }, with no .get(), .all() or .run() on it either — read (await query(sql, values)).rows, and rowCount for how many rows an INSERT/UPDATE/DELETE changed. TypeError: result.get is not a function is that mistake: better-sqlite3's db.prepare(…).get()/.all() have no equivalent here, so a "fetch one row" is rows[0] (possibly undefined).
• Atomic multi-write = withTransaction(async () => { … }). 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), wrap them: await withTransaction(async () => { await create('orders', o); await updateById('products', id, { stock }) }). Every call inside — the CRUD helpers, query(), and the resource packages built on them — joins the one transaction; it commits when the callback resolves and rolls back when it throws. Never call pool.transaction() and then the CRUD helpers: those run outside it (Postgres/MySQL: not atomic; SQLite: they wait behind it and fail after 30 s). A single-row update is already atomic via updateById. D1 has no interactive transactions, so withTransaction throws there.
• 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.