@profoundlogic/dbconn

dbconn

Database connection management.

Provides a consistent plugin-based API for managing database connections, including connection creation, pooling, queries, and transactions.

Built-in Plugins

• ODBC, via odbc.
  • Requires native dependencies and ODBC driver for target DB. See odbc docs for details.
• IBM Db2 for i, via idb-connector.
  • Only available when installed on IBM i.
• Microsoft SQL Server, via tedious.

Plugin Interface

Database connections are created by passing a class that implements the IDBConnection interface to the exported createConnection and createPool functions, along with a configuration object that is specific to each plugin.

Each connection object has the following methods:

• open(): Opens the connection. Returns a Promise that resolves when the connection is opened.
• close(): Closes the connection. Returns a Promise that resolves when the connection is closed.
• validate(): This is used by the connection pool to validate connections when client code acquires a connection. Returns a Promise that resolves to a Boolean true/false value that indicates whether/not the connection is still valid.
• beginTransaction(): Begins a transaction. Returns a Promise that resovles when the transaction is ready to begin.
• query(sql :string, parameters? :DBColValue[]): Runs an SQL statement with optional parameters. Returns a Promise that resolves to the result set when the query completes.
• commit(): Commits a transaction. Returns a Promise that resolves when the transaction is committed.
• rollback(): Rolls back a transaction. Returns a Promise that resolves when the transaction is rolled back.

Parameter Values / SQL Syntax

SQL statement syntax depends on the underlying DB plugin, with the exception that all plugins use the character ? for parameter markers. For example:

select * from mytable where my col = ?

SQL parameter values can be either string, number, Date, or null, as needed.

Date/Time Values

All plugins handle date/time values as follows:

• All dates and times are interpreted as UTC.
• Date-only types (i.e. DATE) are input/output as strings in the format YYYY-MM-DD.
• Time-only types (i.e. TIME) are input/output as strings in the 24-hour format HH:MM:SS.
• Date/time types are input/ouptut as JavaScript Date instances.

Error Handling

All methods above reject for any errors with an instance of DBError that extends Error with the following additional properties:

• code: Error code
• state: Error state

Connection Pooling

The following connection pool options are available:

• min (required, 2): Minimum number of connections to keep in pool.
• max (required, 10): Maximum number of connections in pool.
• acquireTimeoutMillis (30000): Acquire promises are rejected after this many milliseconds if a resource cannot be acquired
• createTimeoutMillis (30000): Create operations are cancelled after this many milliseconds if a resource cannot be created
• destroyTimeoutMillis (5000): Destroy operations are awaited for at most this many milliseconds new resources will be created after this timeout
• idleTimeoutMillis (30000): Free resouces are destroyed after this many milliseconds
• reapIntervalMillis (1000): How often to check for idle resources to destroy
• createRetryIntervalMillis (200): How long to idle after failed create before trying again
• propagateCreateError (false): If true, when a create fails, the first pending acquire is rejected with the error. If this is false then create is retried until acquireTimeoutMillis milliseconds has passed.
• validateOnBorrow (true): If true, connections are validated by running a simple SQL statement at the time a request is made to acquire a connection from the pool. If a connection is found to be invalid, it is destroyed and removed from the pool and a new one is created. If false, connections will only be validated at acquire time if they have been manually invalidated via call to the pool's invalidate() or invalidAll() method.

Examples

Single connection to SQL Server, via tedious.

import { createConnection, plugins } from "dbconn";


// See tedious docs for available config options.
const config = {
  authentication: {
    type: "default",
    options: {
      userName: "user",
      password: "password"
    }
  },
  server: "myserver.myorg.com",
  options: {
    database: "mydb"
  }
};

const conn = await createConnection(plugins.TdsConnection, config);
const result = await conn.query("select * from my table");
console.log(result);
await conn.close();

Pooled connection to SQL Server, via tedious.

import { createPool, plugins } from "dbconn";

// See tedious docs for available config options.
const config = {
  authentication: {
    type: "default",
    options: {
      userName: "user",
      password: "password"
    }
  },
  server: "myserver.myorg.com",
  options: {
    database: "mydb"
  }
};

 // See above for available pool options.
const poolConfig = {
  min: 10,
  max: 10,
  propagateCreateError: true
};

const pool = createPool(plugins.TdsConnection, config, poolConfig);
const conn = await pool.acquire().promise;
const result = await conn.query("select * from my table");
console.log(result);
pool.release(conn);
await pool.destroy(); // Call when process needs to shut down.

Pooled connection to IBM i, via ODBC

import { createPool, plugins } from "dbconn";

// See ODBC driver docs for target DB for available connection string options.
const config = {
  connectionString: "Driver=IBM i Access ODBC Driver;SYSTEM=myserver;UID=user;PWD=password;"
};

 // See above for available pool options.
 const poolConfig = {
  min: 10,
  max: 10,
  propagateCreateError: true
};

const pool = createPool(plugins.OdbcConnection!, config, poolConfig);
const conn = await pool.acquire().promise;
const result = await conn.query("select * from my table");
console.log(result);
pool.release(conn);
await pool.destroy(); // Call when process needs to shut down.