PostGreSQL Management

While Klave provides access to a tamper-proof NoSQL key-value datastore Ledger APIs, there are many use-cases that need a connection to a relational database outside of Klave hosting. To that intent, we manage a dedicated service hosted in an enclave that manages PostGreSQL connection, queries and executes transactions on your behalf.

Using PostGreSQL Interface from Applications

The Klave SDK exposes 4 different routes to interact with a PostGreSQL database hosted outside of Klave.

The set of operations is the following one:

Class	Operation	Parameters	Returns	Behavior
`PostGreSQL`	`connectionString`	host, dbname, user, password	uri	Builds the connection_string to be provided in the route connectionOpen.
`PostGreSQL`	`connectionOpen`	connection_string	handle	This route returns the handle to the database. It's mandatory to open a connection before any query or execution.
`PostGreSQL`	`sqlQuery`	connection_handle, query	value	Returns a string value which is the result of your specific query.
`PostGreSQL`	`sqlExec`	connection_handle, command	value	Returns a string code indicating the completion of your command.

Routes connectionOpen, sqlQuery and sqlExec have to be called within the context of a query (@query), as the result isn't deterministic.

Always run the connectionOpen route first to open an handle whose lifetime will be limited to the context of the query.

In AssemblyScript, these different routes can be accessed through the PostGreSQL keyword. For example:

import { PostGreSQL } from '@klave/sdk';
 
@json
export class OutputMessage {
    success!: boolean;
    message!: string;
}
 
@json
export class DBInputDetails {
    host!: string;
    dbname!: string;
    user!: string;
    password!: string;
}
 
@json
export class QueryInput {
    query!: string;
    dbInputDetails!: DBInputDetails;
}
 
/**
* @query
*/
export function sql_query(input: QueryInput): void {
 
    let uri: string = "";
    if (input.dbInputDetails.host && input.dbInputDetails.dbname
        && input.dbInputDetails.user && input.dbInputDetails.password ) {
            uri = PostGreSQL.connectionString(input.dbInputDetails.host, input.dbInputDetails.dbname, input.dbInputDetails.user, input.dbInputDetails.password);
    }
    else {
        Notifier.sendJson<OutputMessage>({
            success: false,
            message: "Wrong database input details"
        });
        return
    }
 
    let opaqueHandleRes = PostGreSQL.connectionOpen(uri);
    if (opaqueHandleRes.err) {
        Notifier.sendJson<OutputMessage>({
            success: false,
            message: "Connection issue: " + opaqueHandleRes.err!.message
        });
        return
    }
 
    let queryRes = PostGreSQL.sqlQuery(opaqueHandleRes.data!, input.query);
    if (queryRes.err) {
        Notifier.sendJson<OutputMessage>({
            success: false,
            message: "Query issue: " + queryRes.err!.message
        });
        return
    }
 
    Notifier.sendJson<OutputMessage>({
        success: true,
        message: queryRes.data!
    });
}

Output of a query is an object with fields definition and resultset arrays. You will find an example as defined below:

{
  "fields": [
    { "name": "id", "type": 3, "size": 18446744073709551615, "scale": 0, "nullable":false, "description":null },
    { "name": "name", "type": 12, "size": 104, "scale": 0, "nullable":true, "description":null },
    { "name": "price", "type": 15, "size": 655366, "scale": 0, "nullable":true, "description":null }
  ],
  "resultset": [
    [1, "Laptop Pro", 1200.00],
    [2, "Mouse", 25.50]
    // ... more rows
  ]
}

PostGreSQL Management

Using PostGreSQL Interface from Applications

On this page