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 `open` method.
`PostGreSQL`	`open`	uri	`Connection`	This route returns the `Connection` to the database. It's mandatory to open a connection before any query or execution.
`Connection`	`query`	query	value	Returns a string value which is the result of your specific query.
`Connection`	`execute`	command	value	Returns a string code indicating the completion of your command.

Methods open, query and execute have to be called within the context of a query (@query), as the result isn't deterministic. Always run the open 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 connectionResult = PostGreSQL.open(uri);
    if (connectionResult.err) {
        Notifier.sendJson<OutputMessage>({
            success: false,
            message: "Connection issue: " + connectionResult.err!.message
        });
        return
    }

    let conn = connectionResult.data!;
    let queryRes = conn.query(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!
    });
}

The set of operations is the following one:

Class	Operation	Parameters	Returns	Behavior
`Connection`	`connection_string`	host, dbname, user, password	uri	A static method that builds the connection_string to be provided in the method `open`.
`Connection`	`open`	uri	`Connection`	A static factory method that returns a `Connection` to the database. It's mandatory to open a connection before any query or execution.
`Connection`	`query`	query	value	Returns a string value which is the result of the command.
`Connection`	`execute`	command	value	Returns a string code indicating the completion of your command.

Methods open, query and execute have to be called within the context of a query (@query), as the result isn't deterministic. Always run open first to open a connection whose lifetime will be limited to the context of the query.

use klave::{self, postgresql};
use serde::{Serialize, Deserialize};

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct DBInputDetails {
    pub host: String,
    pub dbname: String,
    pub user: String,
    pub password: String,
}

pub struct QueryInput {
    dbInputDetails: DBInputDetails,
    query: String
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Field {
    pub name: String,
    #[serde(rename = "type")] // "type" is a reserved keyword in Rust, so we rename it
    pub field_type: u32,
    pub size: u64,
    pub scale: u32,
    pub nullable: bool,
    pub description: Option<String>, // Use Option<String> for nullable fields
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct PostGreResponse<T> {
    pub fields: Vec<Field>,
    pub resultset: T, // Use Vec<Vec<Value>> for the varying resultset
}

fn sql_query(cmd: String) {
    let input: QueryInput = match serde_json::from_str(&cmd) {
        Ok(input) => input,
        Err(err) => {
            klave::notifier::send_string(&format!("Invalid input: {}", err));
            return;
        }
    };

    let client_connection : String = postgresql::Connection::connection_string(&input.db_input_details.host, &input.db_input_details.dbname,
            &input.db_input_details.user, &input.db_input_details.password);

    // Connect to the DB
    let conn = match postgresql::Connection::open(&client_connection) {
        Ok(res) => res,
        Err(err) => {
            klave::notifier::send_string(&format!("Failed to connect: {}", err));
            return;
        }
    };

    // Execute query
    let _ = match conn.query(&input.query) {
        Ok(res) => {
            let _ = klave::notifier::send_string(&res);
            return;
        },
        Err(err) => {
            klave::notifier::send_string(&format!("Failed to execute query: {}", err));
            return;
        }
    };
}

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