Database Agent - JS/TS

Read and write an external database server.

Logic Type	Available
Generic logic	✅
Aggregator logic	❌

Database Agent Configuration

See: Agent Configuration

A Database Agent Configuration defines an external database server that will be allowed to be accessed from LOC runtime.

The Database Agent requires a configuration reference name so that it can access data on the database server. The reference name can be added to a logic while creating or editing a data process.

Import and Usage

JavaScript

import {
    DatabaseAgent,
} from "@fstnetwork/loc-logic-sdk";

export async function run(ctx) {
    const dbClient = await DatabaseAgent.acquire("db-config-ref");
    const result = await dbClient?.query("SELECT * FROM table1;", []);
}

export async function handleError(ctx, error) {
    // ... same
}

TypeScript

import {
    DatabaseAgent,
    GenericContext,
    RailwayError,
} from "@fstnetwork/loc-logic-sdk";

export async function run(ctx: GenericContext) {
    const dbClient = await DatabaseAgent.acquire("db-config-ref");
    const result = await dbClient?.query("SELECT * FROM table1;", []);
}

export async function handleError(ctx: GenericContext, error: RailwayError) {
    // ... same
}

Class Reference

Type

• DatabaseAgent

Method: Acquire Database Client

async acquire(configurationName: string): Promise<DatabaseClient>

Parameter	Description
configurationName	Database Agent Configuration reference name

Acquire a Database client using a configuration reference name. Throws an error if the configuration cannot be found.

Database Client

Type

• DatabaseClient

Importable from @fstnetwork/loc-logic-sdk

Method: Select Query

async query(rawSql: string, params: any[]): Promise<Database.QueryResults>

Parameter	Type	Description
rawSql	string	SQL prepared statement
params	any[]	Values to be inserted into the prepared statement

Execute a select query and returns a QueryResults object.

Always Use Prepared Statements

It is strongly recommended to query a database using prepared statements. For example, the following code works but is vulnerable to SQL injection attack:

// bad practice
const result = await dbClient.query(
    `SELECT * FROM table1 WHERE col_1 = ${value1} AND col_2 = ${value2};`,
    [],
);

Instead, seperate parameters into the params array:

const result = await dbClient.query(
    "SELECT * FROM table1 WHERE col_1 = ? AND col_2 = ?;",
    [value1, value2],
);

Prepared Statement Placeholders

Supported Database	Placeholder
MySQL/Oracle	?
Microsoft SQL Server	@P1, @P2...
PostgreSQL	$1, $2...

QueryResults

Results from select query.

Property	Type	Description
columns	Database.QueryResultColumn[]	Column descriptions
rows	Array<{ [key: string]: any }>	Columns names and row values

If a column in the result has the name of ColName, the value can be accessed as rows[index].ColName or rows[index]["ColName"].

info

PostgreSQL returns lowered-case column names, which makes it rows[index].colname.

Database.QueryResultColumn

Property	Type	Description
name	string	Column name
type	string	Column data type

Method: Action Query

async execute(rawSql: string, params: any[]): Promise<any>

Parameter	Type	Description
rawSql	string	SQL prepared statement
params	any[]	Values to be inserted into the prepared statement

Execute an action query (insert, update, delete, etc.). Returns the number of affected rows.

Method: Begin Transaction

async beginTransaction(): Promise<DatabaseClient>

Begin a transaction. All action queries will only take effect when commitTransaction() is called.

Modify Transaction Isolation Level

Advanced users can modify the transaction isolation level with the following SQL statement:

--MySQL, MS SQL Server
SET TRANSACTION ISOLATION LEVEL <LEVEL>

--PostgreSQL, Oracle
SET TRANSACTION <LEVEL>

The default level setting is READ COMMITTED.

Method: Commit Transaction

async commitTransaction(): Promise<void>

Commit a transaction.

Method: Rollback Transaction

async rollbackTransaction(): Promise<void>

Rollback a transaction. The database may throw an error if commitTransaction() is already called.

Method: Release Connection

async release(): Promise<void>

The LOC runtime will also try to close unreleased connection when the task completes execution.

Examples

Query From Table

const dbClient = await DatabaseAgent.acquire("db-config-ref");

const value1 = "value1";
const value2 = "value2";

const result = await dbClient?.query(
    "SELECT * FROM table1 WHERE col_1 = ? AND col_2 = ?;",
    [value1, value2],
);

const rows = result?.rows;

// iterate through rows
rows.forEach((row) => {
    const value1 = row.col_1;
    const value2 = row.col_2;
    // ...
});

await dbClient?.release();

Insert Data Into Table

const dbClient = await DatabaseAgent.acquire("db-config-ref");

const value1 = "value1";
const value2 = "value2";

await dbClient?.execute("INSERT INTO table1 (col_1, col_2) VALUES (?, ?);", [
    value1,
    value2,
]);

Error Handling with Transaction

const dbClient = await DatabaseAgent.acquire("db-config-ref");

try {
    await dbClient?.beginTransaction();

    // a series of SQL action queries to be completed

    await dbClient?.commitTransaction();
} catch (e) {
    try {
        await dbClient?.rollbackTransaction();
    } catch (e) {
        // error occurred after commit
    }
} finally {
    await dbClient?.release();
}