Connecting to the database

Examples use the node client.

Install node plugin using npm install jsqldb-client in terminal.

To see how to create and manage users and databases, see Database Administration

Querying a database

Let’s say there is a database on the server called ‘mydb’.
Queries can be sent to the database in one of 3 forms.
See below for the 3 forms, for the same query, which retrieves a value from the database called ‘customers’. All queries are asynchronous and use JavaScript async/await.

The function .r() in forms 2 and 3, is the run/render/return function. (.t() is the equivalent of .r(), but will run the query as a transaction) It is actually also implicitly added to form one when it is await-ed, but can also be explicitly specified, for this example, that would be

Note that await is required for data all retrieval queries, therefore they have to be called from an async function, for example

Queries are not executed until they are called with await. This allows queries to be declared without being executed, and assigned to variables, which can be executed later.

The context placeholder, $

Query forms 2 and 3 can appended to form 1, with the help of the context variable, $.
The following are all equivalent:

Query Return Formats

Sometimes it is convenient to receive results as a parsed object, other times as a json string. Also sometimes only data is required efficiently, and other times an inspection of the data including circular references and functions are needed. JSqldb supports this through different request functions.
Consider the following data, which contains a circular reference as well as a function:

There are 3 query result modes, Data only (r), Filled string (rf) and Complete (rc). Additionally there are two formats, object and string.

See the below table for the formats

Request function Description Query example result
.r() Data only object await db.obj1.r() { “a”:1, “b”:”b” } as a parsed object
.rs() Data only json string await db.obj1.rs() { “a”:1, “b”:”b” } as a JSON string
.rf() Filled object await db.obj1.rf() { “a”:1, “b”:”b”, “c” : “function() { return 3; }”, “d” : “$0” } as a parsed object
.rfs() Filled json string await db.obj1.rfs() { “a”:1, “b”:”b”, “c” : “function() { return 3; }”, “d”:”$0″ } as a JSON string
.rc() Complete object await db.obj1.rc() Complete js object, including executable function and circular reference. The result of eval($0={ “a”:1, “b”:”b”, “c” : function() { return 3; } };$0.d=$0;$0;)
.rcs() Complete js string await db.obj1.rcs() JS string: { “a”:1, “b”:”b”, “c” : function() { return 3; } };$0.d=$0;

JSON can not represent circular references or functions, therefore the for r() omit these, and replaces undefined with null because JSON does not support undefined. This is also the most efficient query form. The filled form is useful for inspection, where the omitted circular references and functions are replaced by strings in a valid JSON format. The complete form returns javascript that will reconstruct the complete object as in the db. This is supported in string form in all drivers, but only the node js driver supports this in object form. All the forms can be executed as transactions by replacing r with t (e.g. t(), ts(), tf(), tfs(), tc(), tcs())

Limitations of form 1 queries

Note the some clients may not support Form 1 queries, but could only support Form 3. (These clients will also not support the above combination with form 1). Form 1 is natural for the node client, because of the common language.

Furthermore, complex queries may not fit into form 1, for example queries combining more than one phrase, where would the implicit .r() function go? There can only be one .r() function for one query.


Therefore more complex queries may only be viable in form 2 or 3

Inserting / updating / assigment

Assignment of database values is possible in all 3 query forms. However, form 1 has a caveat. See the following (valid) statement:

Note that there is no await. This is because it’s not possible to directly add await to assignments in javascript. The assignment query is simply asynchronously submitted to the database. How then will we know when and if and when the query completed successfully? For this reason, it is recommended to use form 2 or 3, that allow assignments to be directly awaited. Form 1 does allow assignments to be indirectly awaited though. See the below assignments in all 3 forms.

The function .aok() waits until all assignments are done, or throws and error if any assignments have failed since last .aok() call. It is recommended that this be called after all assignments of form 1.

Assigning one object to another

See the following assignment examples:

Note that assignment 2 is not equivalent to assignment 3. In assignment 2, obj2 will be a reference to the same object in the database as obj1, whereas obj3 will contain a clone copy of obj1. This is because assignment 3 is actually 2 queries, firstly “await conn.mydb.obj1” will be evaluated to { val: 2 }, and the result will be assigned to obj3 (conn.mydb.obj3 = { val: 2 }).

Now:

Deleting from the database

Deletion works like normal javascript objects

Setting a value to undefined will also delete the value. (Setting it to null will not delete it, the value will be javascript null)

Unreferenced objects will automatically be deleted / garbage collected by JSqldb. (continuing from object assignment example)

If a deleted or garbage collected object has references the other objects, they will also be recursively deleted if there are no other remaining references to them.

Transactions

In order to run a query as an ACID transaction, simply run the query using .t() instead of using .r().
Transactions apply to the whole query, and everything in the query, including functions and their contents. All changes made will be applied in one atomic commit. JSqldb uses optimistic concurrency. A transaction may fail if another query modifies something that the transaction has read or written while a transaction is in progress and before it has committed. If the query does not complete successfully, the changes are not committed. In order to abort or roll back a transaction, throw an exception. The below demonstrates an example transaction. If the query completes successfully, the transaction will have been committed.

Direct assignments of the form

are always implicitly run as transactions to ensure that the assignment can not partially complete.