Manipulating data in ThingsDB using procedures

Introduction

Manipulating data in a database often requires writing lots of queries. When the same data is required in multiple places, this will probably result in duplicate code. When these queries also need to be updated, it can be an “inconvenience” to say the least.

When using ThingsDB, you can make use of something called procedures. The official definition of a procedure is: “A named closure that is attached to a scope and available to use in an API call”. But simply put, it is just a function stored in ThingsDB that you can run as many times as you want, from any supported client. This not only saves you a lot of time, it also ensures a clearer codebase and a better performance. Another advantage of using procedures is that it prevents injection related security issues.

The goal of this blog is to give an introduction about procedures within ThingsDB and to demonstrate how to make use of them in an accessible way.

Note: ThingsDB supports queries as well, but using procedures is desirable in many cases.

Set up

Before getting into how to create and run procedures, we need to start and prepare the database ThingsDB. You can try out ThingsDB easily by creating a playground on: https://thingsdb.net/. So that is what we’ve done.

However it is also possible to set up ThingsDB manually. If you want to do this, please consult the ThingsDB documentation for more information: https://docs.thingsdb.net/v0/getting-started/.

Now we have a collection in ThingsDB that is called ‘Talus’ and we got an authentication token. We hold on to this token, because we will need this in the next HTTP request, using CURL with token authentication.

First, we will add a list to the root of the collection with the name pizzas. This is done like this:

// Add a new list called `pizzas` to the root of the collection 
.pizzas = [];

Second, we will create a type called Pizza. This is not mandatory, but it allows you to define the properties of a given type. Instances of this type that do not meet this definition will not be accepted.

// Create a new type called `Pizza`
set_type('Pizza', {
    name: 'str',
    price: 'float'
});

Now if we want to add a Pizza to the list pizzas, all we have to do is create a procedure for this and run it!

Creating our first procedure

Creating a procedure is very easy. All we have to do is call the function new_procedure, which is part of the Procedure API. This function requires two arguments. The first one is the name that you give to the new procedure and the second argument is the closure. A closure is an inline function, which you also find in other programming languages such as Javascript (Arrow function) or Python (Lambda function).

So let’s create our first procedure, which adds a Pizza to the list of pizzas:

// Create a new procedure called `add_pizza`
new_procedure('add_pizza', |name, price| {
    pizza = Pizza{
        name: name,
        price: price
    };
    .pizzas.push(pizza);
});

Note: Procedures can be created to the @thingsdb or a @collection scope. So make sure you target the correct scope, when creating procedures.

Now we just have to run it. Running a procedure is also part of the Procedure API. The run function can be used for this:

// Run `add_pizza` with a given name and price
run('add_pizza', 'Pepperoni', 7.99);

That’s it! Now you know how to create and run procedures.

If you would like to try out the previous steps combined using a CURL command. You can use the following example:

curl --location --request POST 'https://playground.thingsdb.net//Talus' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer <PLACE_YOUR_TOKEN_HERE>' \
--data-raw '{
    "type": "query",
    "code": ".pizzas = []; set_type(`Pizza`, {name: `str`, price:     `float`}); new_procedure(`add_pizza`, |name, price|{ pizza = Pizza{name: name, price: price}; .pizzas.push(pizza);}); wse(run(`add_pizza`, `Pepperoni`, 7.99));"
}'

Note: Stored closures which can potentially make changes to ThingsDB are called closures with side effects and must be wrapped with the wse(..) function. Since a procedure is a named closure that is attached to a scope, this also applies here.

There is also a direct entry point for running procedures instead of starting a procedure using the run(..) syntax. This can be used as follows:

curl --location --request POST 'https://playground.thingsdb.net//Talus' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer <PLACE_YOUR_TOKEN_HERE>' \
--data-raw '{
    "type": "run",
    "name": "add_pizza",
    "args": ["Pepperoni", 7.99]
}'

Additional procedures

Now that we have created our list of pizzas and a procedure to add one, we of course also want to retrieve or even modify this data. The way to do this is by simply creating more procedures. For any ThingsDB related action, a procedure can be created. You can create as many procedures as you want.

So to retrieve the list of pizzas, we could write the following procedure:

// Create a new procedure called `get_pizzas`
new_procedure('get_pizzas', || {
    .pizzas;
});

Or if we would like to update a property value of a certain pizza:

// Create a new procedure called `update_pizza_price`
new_procedure('update_pizza_price', |name, new_price| {
    pizza = .pizzas.find(|p| p.name == name);
    pizza.price = new_price;
    nil;
});

And lastly, if we want to remove a pizza:

// Create a new procedure called `remove_pizza`
new_procedure('remove_pizza', |name| {
    .pizzas.remove(|p| p.name == name);
    nil;
});

Tips and Tricks

In addition to being able to create new procedures, the Procedure API also offers a number of other useful functions. One of these functions is procedure_doc. This function returns the doc string for a given procedure. A doc string can be added to a procedure when creating one.

Here is an example, which shows how a doc string can be added to a procedure and then retrieved again:

// Create a new procedure called `count_pizzas`
new_procedure('count_pizzas', || {
"Returns the number of pizzas";
    .pizzas.len();
});
procedure_doc('count_pizzas');

Another useful function is has_procedure. With this function you can quickly determine if a procedure exists within the current scope.

An example:

has_procedure('count_pizzas');

Other use cases

Besides the fact that you can reuse these procedures, using them has even more advantages. First of all, they are faster than regular queries. This actually has to do with “stored closures”. A procedure uses a stored closure whose code / syntax has already been compiled and where possible variables have already been created. So there is already some work done that does not need to be redone. This can have major advantages in terms of scalability. Second, you can call these procedures from any project that you connect to ThingsDB. This can be very useful, for example, when sharing data between multiple apps or other systems.

Finally, as mentioned before, you can avoid code injection by making use of procedures. By using procedures you are more or less ‘forced’ to inject arguments. Variable injection is used to prevent injection issues with normal queries. So you may have to look at the “variable injection” documentation to get normal queries ‘safe’, but procedures by itself force using “arguments”. These “arguments” are parsed securely by ThingsDB and potentially executable code is not executed as such.

Conclusion

In this blog we have shown how accessible and simple it is to use procedures. We explained what its benefits are and some of its use cases. Now you have enough information to get started with procedures yourself!

If you want to learn more about ThingsDB or its procedures you can visit the official documentation page at https://docs.thingsdb.net.