Database Agent

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

Connecting external databases with built-in drivers. Currently the following databases are supported:

• MySQL
• MS SQL Server
• PostgreSQL
• Oracle Database

Availability

• ✓ Generic logic
• ✗ Aggregator logic

info

This agent requires agent configuration. See tutorial for details.

Acquire Database Client

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

Returns a DatabaseClient object based on provided agent configuration name, which connects to a pre-defined database with confidential information. The client will establish a connection to the database until you release it.

note

The configuration name is the reference field set in Studio or name field set in CLI config files.

Throws an error if the configuration reference cannot be found.

Example

JavaScript

const dbClient = await DatabaseAgent.acquire("my-db-configuration");

TypeScript

const dbClient: DatabaseClient = await DatabaseAgent.acquire(
    "my-db-configuration",
);

Database Client

Release Connection

async dbClient.release(): Promise<void>

Example

await dbClient?.release();

See Error Handling about how to deal of expected database errors and release the client in any situations.

Select Query

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

Execute a SQL query with prepared statement.

Parameter	Type	Description
rawSql	string	SQL prepared statement
params (optional)	any	Values to be inserted by order into the prepared statement (default: empty array [])

Example: select query in MySQL

const dbClient = await DatabaseAgent.acquire("my-db-configuration");

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

const resp = await dbClient?.query(
    "SELECT * FROM table1 WHERE col_1 = ? AND col_2 = ?;",
    [value1, value2], // the placeholders "?" will be replaced by values
);

const rows = resp?.rows;

// iterate through the rows
rows.forEach((row) => {
    const value_1 = row.col_1;
    const value_2 = row.col_2;
    // ...
});

await dbClient?.release(); // release database when done

warning

Always Use Prepared Statement!

You can, of course, put parameters directly in the SQL statement string as such:

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

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

However this make the query vulnerable to SQL injection attack, especially if the parameters are provided by users. If the parameters contain malicious SQL scripts, they will not be executed in prepared statements.

Hence, you should always use prepared statements and seperate the parameters. Also the placeholder of prepared statement differs in databases:

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

QueryResults

resp is type of Database.QueryResults with the following fields:

Field	Type	Description
columns	Database.QueryResultColumn[], which is { name: string; type: string; }[]	Column name and type
rows	{ [key: string]: any }[]	Column fields and value

For example, a column with name ColName can be accessed as rows[index].ColName.

note

PostgreSQL client returns lower-cased column names so it will be rows[index].colname instead.

warning

If you read very large amount of data (for example, several dozen gigabytes) the data process may fail due to memory issues. Please consult FST Network for the actual upper limit.

Action Query

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

Execute a add, update or delete action with prepared statement. Does not return anything.

Parameter	Type	Description
rawSql	string	SQL prepared statement
params (optional)	any	Values to be inserted by order into the prepared statement (default: empty array [])

Example: action query in MySQL

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

Database Transaction

async beginTransaction(): Promise<DatabaseClient>
async commitTransaction(): Promise<void>
async rollbackTransaction(): Promise<void>

For executing multiple SQL, you can mark them as a transaction and apply changes together.

After beginTransaction(), any SQL execution queries will only be written when commitTransaction() is called. To cancel the transaction before commit, use rollbackTransaction().

warning

rollbackTransaction() will throw an SQL error if commitTransaction() is already executed.

note

For advanced users, you can also execute SQL statement SET TRANSACTION ISOLATION LEVEL (with dbClient?.execute()) to set the transaction isolation level (default is Read Committed).

Example

const dbClient = await DatabaseAgent.acquire("my-db-configuration");

try {
    // begin transaction
    await dbClient?.beginTransaction();

    // a series of SQL transactions:

    // dbClient?.execute(...)

    // dbClient?.execute(...)

    // ...

    // commit all actions (changes become permanent)
    await dbClient?.commitTransaction();
} catch (e) {
    // roll back transaction in case of error
    try {
        // query error
        await dbClient?.rollbackTransaction();
    } catch (e) {
        // error occurred after commit
    }
} finally {
    await dbClient?.release();
}

Example (multiple database clients)

const dbClient_1 = await DatabaseAgent.acquire("my-db-configuration-1");
const dbClient_2 = await DatabaseAgent.acquire("my-db-configuration-2");

try {
    // begin transaction
    await dbClient_1?.beginTransaction();
    await dbClient_2?.beginTransaction();

    // a series of SQL transactions:

    // dbClient_1?.execute(...)

    // dbClient_2?.execute(...)

    // ...

    // commit all actions (changes become permanent)
    await dbClient_1?.commitTransaction();
    await dbClient_2?.commitTransaction();
} catch (e) {
    // roll back transaction in case of error
    try {
        // query error
        await dbClient_1?.rollbackTransaction();
    } catch (e) {
        // error occurred after commit
    }
    try {
        // query error
        await dbClient_2?.rollbackTransaction();
    } catch (e) {
        // error occurred after commit
    }
} finally {
    await dbClient_1?.release();
    await dbClient_2?.release();
}