@sabl/db-api v0.2.0
version: 0.2.0
| tag: v0.2.0
| commit: d55dcb13f
@sabl/db-api
db api is a simple, context-aware pattern for interacting with relational databases. It was first demonstrated in the golang standard library database/sql
package. This package makes the same pattern available in TypeScript and JavaScript projects.
This implementation builds on the following generic packages which describe connection pooling and transaction lifecycles, respectively:
Package | Pattern docs |
---|---|
@sabl/storage-pool | storage pool |
@sabl/txn | txn |
For more detail on the db api pattern, see sabl / patterns / db-api.
Basic Pooling and Transactions
As in the generic @sabl/storage-pool
package, this package provides pool, connection, and transaction types which represent the generic concerns of connection pooling and transaction lifecycles.
In most situations, clients should use the exec
, query
, and queryRow
APIs directly on an DbPool
. This ensures connections are returned to the pool as quickly as possible. Only use DbConn
directly if it is important to maintain session state such as variables, temporary tables, or other settings between subsequent queries.
The relational-specific query
and exec
APIs are described in detail below.
DbTxn
import { Txn } from '@sabl/txn';
import { StorageTxn } from '@sabl/storage-pool';
interface DbTxn implements DbApi, StorageTxn, Txn {
// StorageTxn, Txn:
commit(ctx: IContext): Promise<void>;
rollback(ctx: IContext): Promise<void>;
// DbApi:
exec(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Result>;
queryRow(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Row | null>;
query(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Rows>;
}
DbTxn
represents an active relational database transaction.
It is based on the Tx
struct from the golang database/sql
package, and is a composition of
- The generic
StorageTxn
interface from@sabl/txn
- The generic
Txn
interface from@sabl/txn
- The relational-specific
DbApi
.
DbConn
import { Transactable } from '@sabl/txn';
import { StorageConn } from '@sabl/storage-pool';
interface DbConn implements DbApi, StorageConn, Transactable<DbTxn> {
// StorageConn, Transactable:
beginTxn(ctx: IContext, opts?: TxnOptions): Promise<DbTxn>;
// StorageConn:
close(): Promise<void>;
// DbApi:
exec(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Result>;
queryRow(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Row | null>;
query(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Rows>;
}
DbConn
represents an open connection to a relational database that remains active until it is explicitly closed. See also storage pool pattern.
It is based on the Conn
struct from the golang database/sql
package, and is a composition of:
- The generic
StorageConn
interface from@sabl/storage-pool
- The
Transactable
interface from@sabl/txn
, - The relational-specific
DbApi
.
DbPool
interface DbPool
implements DbApi, StoragePool, Transactable<DbTxn> {
// StoragePool, Transactable:
beginTxn(ctx: IContext, opts?: TxnOptions): Promise<DbTxn>;
// StoragePool
conn(ctx: IContext): Promise<DbConn>;
close(): Promise<void>;
// DbApi:
exec(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Result>;
queryRow(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Row | null>;
query(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Rows>;
}
DbPool
represents a pool of database connections.
It is based on the DB
struct from the golang database/sql
package, and is a composition of
- The generic
StoragePool
interface from@sabl/storage-pool
- The
Transactable
interface from@sabl/txn
, - The relational-specific
DbApi
.
Shared SQL API
This package also includes a common interface that describes interacting with a relational database regardless of whether the context is a pool, a connection, or a transaction.
DbApi
interface DbApi {
exec(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Result>;
queryRow(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Row | null>;
query(ctx: IContext, sql: string, ...params: ParamValue[]): Promise<Rows>;
}
DbApi is the core interface which represents the basic operations of interacting with a relational database. It is based on the shared APIs on the DB
, Conn
, Txn
, and Stmt
types in the golang database/sql
package.
All three of its methods accept a context, a literal SQL statement, and an ordered list of parameters. Named parameters are also supported using the NamedParam
type, which can be provided in any order to a an underlying implementation that supports named parameters.
method | description |
---|---|
exec | Execute a statement which is not expected to return any rows, and resolve as soon as the database reports the number of rows affected. If executed directly on a Pool, waits until the connection has been closed and returned to the pool. |
queryRow | Retrieves a single row. Resolves when the first row is received, or when the response is concluded indicating there are no rows. If executed directly on a Pool, waits until the connection has been closed and returned to the pool. |
query | Opens a cursor for a result set that may contain multiple rows. Resolves as soon as the list of fields is received, possibly before any row data is received. |
Row
Row is a simple interface that allows fetching field values either by name or by ordinal.
interface Row {
/** Retrieve a value by name */
[key: string | symbol]: unknown;
/** Retrieve a value by zero-based index */
[index: number]: unknown;
}
Row is also exported as a class with several static utility methods:
class Row {
static fromObject(data: PlainObject, cols: string[]): Row;
static fromArray(data: unknown[], cols: string[]): Row;
static toObject(row: Row): PlainObject;
static toArray(row: Row): unknown[];
static clone(row: Row): Row;
}
method | description |
---|---|
fromObject | Wraps a plain object source row, using the provided array of column names to implement field lookup by index. |
fromArray | Wraps a plain array source row, using the provided array of column names to implement field lookup by name. |
toObject | Copies the values from an existing row to a plain object. |
toArray | Copies the values from an existing row to a plain array of values. |
clone | Copies the data from an existing row to a new object that also implements the Row interface. |
Constructing rows
Platform-specific client libraries usually return rows as either a plain object keyed by the field names, or as a plain array of field values. Both can be wrapped as a Row
using the static Row.fromObject
and Row.fromArray
methods, which also require an array of field names to map between field names and indexes.
Both methods use efficient implementations leveraging the Proxy
class, which do not copy the underlying field values.
// Wrapping a plain object as a Row:
const data = { id: 11, code: 'orange', label: 'Orange' };
const row = Row.fromObject(data, ['id', 'code', 'label']);
console.log(row[0]) // 11
console.log(row.id) // 11
// Wrapping a plain array as a Row:
const data = [ 11, 'orange', 'Orange' ];
const row = Row.fromArray(data, ['id', 'code', 'label']);
console.log(row[0]) // 11
console.log(row.id) // 11
Volatility
Row
describes an interface, which could be implemented directly by a volatile cursor whose internal state changes as a query is advanced or closed. Clients should not store Row
references obtained directly from query
or queryRow
APIs. If a client wishes to retain the data from a Row
in a generic form, it should be copied using any of the static methods Row.toObject
, Row.toArray
, or Row.clone
.
Rows
export interface Rows extends AsyncIterable<Row> {
close(): Promise<void>;
next(): Promise<boolean>;
get columns(): string[];
get columnTypes(): ColumnInfo[];
get row(): Row;
get err(): Error | null;
}
Rows
represents a set of Rows returned by a query
, essentially a cursor. Clients can manually iterate over the result set using next()
, row
, and close()
, or can automatically advance, retrieve, and close a row set using for await...of
.
Advancing with next()
and closing with close()
are both asynchronous operations. Retrieving the column names, column types, current row, or current error are all synchronous operations using the properties columns
, columnTypes
, row
, and err
, respectively. Implementations of query
should not resolve until the field information has been received from the server, so that column
and columnTypes
can be safely accessed.
method | description |
---|---|
close | Closes the row set. Row sets must be closed to ensure the underlying cursor is released, but will be closed automatically if next() resolves to false or if the rows are iterated using for await...of . All implementations of close must be idempotent, so calling close multiple times is not a problem. |
next | Advance to the next (or first) row. Resolves true if a row is available, or false if the end of the result set has been reached. Also automatically closes the cursor if there are no more rows. |
columns | Retrieve an array of string column names. |
columnTypes | Retrieve an array of column descriptors. |
row | Get the current row. Always null before calling next() for the first time. May be volatile implementation whose values change when the row set is advanced or closed. |
err | The last error, if any, encountered while using the cursor. |
Closing a Rows
Only the client knows when it is done working with a cursor. Therefore rows must be closed by the client to ensure the cursor is released.
All implementations of Rows
should automatically close the row set in two situations:
- When a call to
next()
resolves tofalse
, indicating there are no more rows - Within a
finally
block in the implementation of an async iterator method
For clients either of the following are safe patterns:
Using
next()
androw
directlyIf manually iterating using
next()
androw
, client code should always usetry...finally
and callrows.close()
in the finally block:const rows = await mydb.query(ctx, 'select * from my_table'); try { while(await rows.next()) { const current row = rows.row; // .. do stuff .. } } finally { await rows.close() }
Note that in this situation
close
may be called twice -- once automatically by the final call tonext()
if the entire result set is iterated, and once in the explicit call toclose()
in thefinally
block. This is not a problem, as all implementations ofclose()
must be idempotent.Using
for await...of
Implementations of the async iterator for a
Rows
should internally use thetry...finally
pattern illustrated in option 1 above. The mechanics of async iteration then guarantee thatclose()
will be called even if the loop is aborted due to an error or an explicitbreak
orreturn
.const rows = await mydb.query(ctx, 'select * from my_table'); // Guarantees row set is closed when iteration completes or // is canceled due to error, return, or break for await (const row of rows) { // .. do stuff }
Differences from Go
Many of the APIs in this package are patterned on the golang database/sql
package. That go package implements many shared mechanics, such as connection pooling, directly in its core Db
, Conn
, Rows
, and Txn
types, which are defined as concrete structs
that cannot be inherited or implemented. Instead, database client authors must implement a fairly complex and low-level set of related interfaces defined in the database/sql/driver
package.
This is not a good fit for the Node ecosystem, where most relational database API packages implement higher-level APIs more similar to the public APIs of the Db
and Conn
types. Therefore in this library we opt to define top-level APIs such DbTxn
, DbConn
, DbPool
, and Rows
as interfaces.
Adapter authors need only create thin wrappers around existing APIs in a target database client package. This often includes work to adapt legacy callback- and events-based processes into the Promise-based APIs in this package.