Database

This document describes the Database object in the JavaScript environment for app serivices.

The library uses the main database connection of the app service. The config.json of the JavaScript app can be used to define tables. It is guaranteed that the database connection is already up when the app code is evaluated.

Table of content

Object Database
Functions insert
exec
transaction
escape
Object DatabaseTransaction
Functions begin
commit
rollback
insert
exec
Object DatabaseOperation
Functions oncomplete
onerror
Examples Using database transactions

Database

insert
Starts an SQL insert query.

Parameters

queryThe SQL query. Note that the library will append " RETURNING id;" to the string.

Return value

DatabaseOperationThe object representing the database operation.
Database.insert("INSERT INTO users (sip, domain) VALUES ('alice','example.com')")
    .oncomplete(function(id) { })
    .onerror(function(error, errorText, dbErrorCode) { });
exec
Starts a general SQL query.

Parameters

queryThe SQL query.

Return value

DatabaseOperationThe object representing the database operation.
Database.exec("SELECT * FROM users")
    .oncomplete(function(data) { })
    .onerror(function(error, errorText, dbErrorCode) { });
transaction
Creates a DatabaseTransaction object.

Return value

DatabaseTransactionThe created object.
var transaction = Database.transaction();
escape
Escapes a string, so it can be safely used inside SQL queries.

Parameters

string valueThe string value to be escaped.

Return value

stringThe escaped string value.
var escapedValue = Database.escape(value);

DatabaseTransaction

An object representing a database transaction. It can be created using Database.transaction.
In general transactions block the database connection for other operations. To avoid blocking the database connection permanently make sure that your code
begin
Starts the transaction, giving an optional lock statement.

Parameters

lockStatementAn optional lock statement that shall be used for the database transaction.

Return value

DatabaseOperationThe object representing the database query.
var transaction = Database.transaction();
transaction.begin("LOCK TABLE users IN ACCESS EXCLUSIVE MODE")
    .oncomplete(function() { })
    .onerror(function(error, errorText, dbErrorCode) { });
commit
Commits all operaions done in the context of the DatabaseTransaction and deletes the object.

Return value

DatabaseOperationThe object representing the database operation.
transaction.commit()
    .oncomplete(function() { })
    .onerror(function(error, errorText, dbErrorCode) { });
rollback
Rolls-back all operations previously done in the context of the DatabaseTransaction and deletes the object.

Return value

DatabaseOperationThe object representing the database operation.
transaction.rollback()
    .oncomplete(function() { })
    .onerror(function(error, errorText, dbErrorCode) { });
insert
Starts an SQL insert query.

Parameters

queryThe SQL query. Note that the library will append " RETURNING id;" to the string.

Return value

DatabaseOperationThe object representing the database operation.
transaction.insert("INSERT INTO users (sip, domain) VALUES ('alice','example.com')")
    .oncomplete(function(id) { })
    .onerror(function(error, errorText, dbErrorCode) { });
exec
Starts a general SQL query.

Parameters

queryThe SQL query.

Return value

DatabaseOperationThe object representing the database operation.
transaction.exec("SELECT * FROM users")
    .oncomplete(function(data) { })
    .onerror(function(error, errorText, dbErrorCode) { });

DatabaseOperation

An object that represents a gerneral database operation. It is returned by all functions of Database and DatabaseTransaction that trigger asynchronous operations. The object can be used to set callbacks for success or failure.
oncomplete
Sets a callback function that shall be called, when the operation is completed.

Parameters

function callback(...)Called when the operation is complete.
The callback has different parameters depending on the type of operation.
  • "id" for insert operations - the nummeric id of the created row.
  • "data" for exec operations - an array of objects representing the returned rows.
  • no parameters for the other operations

Return value

DatabaseOperationA reference to the object itself. Useful for method chaining.
Database.insert("INSERT INTO users (sip, domain) VALUES ('alice','example.com')")
    .oncomplete(function(id) { log("created id=" + id); })
    .onerror(function(error, errorText, dbErrorCode) {});
Database.exec("SELECT * FROM users")
    .oncomplete(function(data) { log("result=" + JSON.stringify(data, null, 4)); })
    .onerror(function(error, errorText, dbErrorCode) {});
transaction.begin("LOCK TABLE users IN ACCESS EXCLUSIVE MODE")
    .oncomplete(function() {})
    .onerror(function(error, errorText, dbErrorCode) {});
transaction.commit()
    .oncomplete(function() {})
    .onerror(function(error, errorText, dbErrorCode) {});
transaction.rollback()
    .oncomplete(function() {})
    .onerror(function(error, errorText, dbErrorCode) {});
onerror
Sets a callback function that shall be called, if the operation has failed.

Parameters

function callback(error, errorText, dbErrorCode) Called if the operation has failed.
error
A string representation of db_error_t.
errorText
A descriptive text that might be useful for debugging.
dbErrorCode
The error code returned by the underlying database server. The value is dependent on the used database server (for example postgreSQL) and its version.

Return value

DatabaseOperationA reference to the object itself. Useful for method chaining.
Database.insert("INSERT INTO users (sip, domain) VALUES ('alice','example.com')")
    .oncomplete(function(id) { log("created id=" + id); })
    .onerror(function(error, errorText, dbErrorCode) {});
Database.exec("SELECT * FROM users")
    .oncomplete(function(data) { log("result=" + JSON.stringify(data, null, 4)); })
    .onerror(function(error, errorText, dbErrorCode) {});
transaction.begin("LOCK TABLE users IN ACCESS EXCLUSIVE MODE")
    .oncomplete(function() {})
    .onerror(function(error, errorText, dbErrorCode) {});
transaction.commit()
    .oncomplete(function() {})
    .onerror(function(error, errorText, dbErrorCode) {});
transaction.rollback()
    .oncomplete(function() {})
    .onerror(function(error, errorText, dbErrorCode) {});

Example: Using database transactions

This examples demonstrates the usage of database transaction by implementing a money withdrawal from the balance of one user to the balance of another user. If one of the sub-operations fail, the entire transaction shall fail. Some of the sub-operations could be done with SQL only, like calculating the new balances. But for demonstrational purposes we will do it in the JavaScript code.

Definition of the used table in the config.json

{
    "database": {
        "init": [
            {

                "cmd": "table",
                "name": "users",
                "init": [
                    {
                        "cmd": "column",
                        "name": "id",
                        "type": "BIGSERIAL PRIMARY KEY NOT NULL"
                    },
                    {
                        "cmd": "column",
                        "name": "sip",
                        "type": "VARCHAR(128) UNIQUE NOT NULL"
                    },
                    {
                        "cmd": "column",
                        "name": "balance",
                        "type": "INTEGER"
                    }
                ]
            }
        ]
    }
}

Code example reading and writing bytea (hex) data type values.

/*
                          Table "public.binary_data"
 Column |  Type  | Collation | Nullable |                Default
--------+--------+-----------+----------+----------------------------------------
 id     | bigint |           | not null | nextval('files_data_id_seq'::regclass)
 data   | bytea  |           |          |
id   | data
-----+ --------
1    | \x313233
*/
Database.exec("SELECT data FROM binary_data;")
    .oncomplete(function (dataset) {
        var string = Encoding.binToString(dataset[0].data);
        log(string); // 123
    })
    .onerror(function (error, errorText, dbErrorCode) { });
var bin = Encoding.stringToBin("456");
var hexstring = Encoding.binToHex(bin); // 343536
Database.insert("INSERT INTO binary_data (data) VALUES ('\\x" + hexstring + "')") // \x343536
    .oncomplete(function (id) { })
    .onerror(function (error, errorText, dbErrorCode) { });

Code example using nested callback syntax.

function withdraw(userA, userB, amount, success, error) {
    var balanceA = 0;
    var balanceB = 0;
    var transaction = Database.transaction();
  
    transaction.begin("LOCK TABLE users IN ACCESS EXCLUSIVE MODE").onerror(error).oncomplete(function() {
        transaction.exec("SELECT balance FROM users WHERE sip='" + Database.escape(userA) + "'").onerror(rollback).oncomplete(function(data) {
            if (data.length == 0 || data[0].balance < amount) rollback();
            else {
                balanceA = data[0].balance;
                transaction.exec("SELECT balance FROM users WHERE sip='" + Database.escape(userB) + "'").onerror(rollback).oncomplete(function(data) {
                    if (data.length == 0) rollback();
                    else {
                        balanceB = data[0].balance;
                        balanceA -= amount;
                        balanceB += amount;
                        transaction.exec("UPDATE users SET balance=" + balanceA + " WHERE sip='" + Database.escape(userA) + "'").onerror(rollback).oncomplete(function() {
                            transaction.exec("UPDATE users SET balance=" + balanceB + " WHERE sip='" + Database.escape(userB) + "'").onerror(rollback).oncomplete(function() {
                                transaction.commit().oncomplete(success).onerror(error);
                            });
                        });
                    }
                });
            }
        });
    });

    function rollback() {
        transaction.rollback().oncomplete(error).onerror(error);
    }
}

Code example using a state machine pattern.

function withdraw(userA, userB, amount, success, error) {
    var balanceA = 0;
    var balanceB = 0;
    var transaction = Database.transaction();

    transaction.begin("LOCK TABLE users IN ACCESS EXCLUSIVE MODE").onerror(error).oncomplete(state1);

    function state1() {
        transaction.exec("SELECT balance FROM users WHERE sip='" + Database.escape(userA) + "'").onerror(rollback).oncomplete(state2);
    }

    function state2(data) {
        if (data.length > 0 && data[0].balance >= amount) {
            balanceA = data[0].balance;
            transaction.exec("SELECT balance FROM users WHERE sip='" + Database.escape(userB) + "'").onerror(rollback).oncomplete(state3);
        }
        else rollback();
    }

    function state3(data) {
        if (data.length > 0) {
            balanceB = data[0].balance;
            balanceA -= amount;
            balanceB += amount;
            transaction.exec("UPDATE users SET balance=" + balanceA + " WHERE sip='" + Database.escape(userA) + "'").onerror(rollback).oncomplete(state4);
        }
        else rollback();
    }

    function state4() {
        transaction.exec("UPDATE users SET balance=" + balanceB + " WHERE sip='" + Database.escape(userB) + "'").onerror(rollback).oncomplete(state5);
    }

    function state5() {
        transaction.commit().oncomplete(success).onerror(error);
    }

    function rollback() {
        transaction.rollback().oncomplete(error).onerror(error);
    }
}