• REL
• REL CONCEPTS
• Updating Data: Working with Base Relations

# Updating Data: Working With Base Relations

This concept guide discusses storing, updating, and deleting data in a RAI database using the Rel language. It explains how to interact with and change base relations, specifically through the use of the reserved relations insert and delete. This guide also covers how transactions update the database and then goes into the more advanced topic of reactive updates.

## Introduction

Most databases are not just static collections of data; they evolve over time.

Generally speaking, a database can change in two ways:

1. The data themselves are updated — changed, added, or deleted.
2. The rules that define derived relations are updated — changed, added, or deleted.

It is important to distinguish relations stored on disk, which are often raw data imported from other sources, from relations that are derived from these data via rules. RelationalAI uses the technical terms from the Datalog literature:

• Base relations are stored on disk or other nonvolatile media.
• Derived relations are defined by rules, computed on-demand, and are not persisted — though their definitions can be persisted, as installed code. These are analogous to views in other database systems.

Normally, base relations contain data from external sources that cannot otherwise be captured; they are the “raw data” as far as the database is concerned. By contrast, derived relations are defined and modified by applying rules to the base relations.

Base relations often contain data loaded from CSV files or other external sources.

## Simple Example:

You can directly create a small base relation using insert, described in more detail below:

// update

def insert[:parent] = {("James", "Harry"); ("Harry", "Lily")}
def output = parent

While parent(x, y) is a base and binary relation, the tuples of which are explicitly stored in the database, grandparent(x, y) might be a derived relation, derived from parent and defined by the rule:

// query

def grandparent(x, z) = parent(x, y) and parent(y, z) from y
def output = grandparent

Derived relations can change if the rules or the underlying base relations change.

Base relations only change when the underlying data change.

## Adding Base Relation Data: insert

To add data to a new or existing base relation, you can use the reserved insert relation. The first argument for insert should be a RelName, i.e., a specialized relation starting with : . Data are added to the right-hand side of the definition to create a relation. Other than the important side effect of updating base relations, these definitions are just like any other in Rel, and all the usual Rel constructs can be used.

For example, consider a balance base relation, where balance(x, y) says that account x has balance y. To add data to balance, you can execute this update query:

// update

def insert[:balance] = {("john", 20); ("jack", 30); ("jill", 60)}
def output = balance

This will add three pairs to the balance relation, creating the relation if it does not already exist.

🔎

This code block has issued a RAI SDK execute call using the readonly=false option. This corresponds to an update cell in the RAI notebooks. Execute calls that are not read-only are update transactions, which can change the state of the database, and you can mark them with the “update” label as shown in the code block above.

The insert relation is computed from the current state of the database, determining what gets added to the base relation.

It is even possible to query insert, as in the example below, but it is not itself persisted. The same is true for the delete relation discussed below.

When requested as a query output, these relations are computed from the new state of the database. See the Transactions section below. After the above, insert will have the tuples {(:balance, "john", 20) ; (:balance, "jack", 30) ; (:balance, "jill", 60)}.

In general, insert is defined as any other relation, so the right-hand side of the definition can be a relational expression, defined from other base and derived relations in the database. For example:

// update

def insert[:pairs](x, y) = range(1, 6, 1, x) and x % 2 = 0 and y = x * 2
def output:insert = insert
def output:pairs = pairs


The discussions above hold even if the transaction is read-only — the only difference is that the new database state will not be saved at the end. See the diagram in the Transactions section below.

Note that the readonly option in the query API call has no effect on derived relation definitions. To persist those, use the install_model API call. See the Installing Models concept guide or install cells in the RAI notebooks.

## Removing Base Relation Data: delete

Removing data from the database is analogous to adding them, except here you use the reserved delete relation instead of insert. For example, you can remove all balances lower than 50 with:

// update

def delete[:balance](x, y) = balance(x,y) and y < 50
def output = balance

This has removed the tuples ("jack", 30) and ("john", 20) from the balance relation. You can clear the entire balance relation with:

// update

def delete[:balance] = balance
def output = balance

In general, you can include def delete[:r] = r in a transaction to make sure that r is empty before any new inserts happen. This can produce a warning the first time, if r is not previously defined, but the warning can be ignored.

## Inserts and Deletes in the Same Transaction

Regardless of the order of the definitions, within a single transaction, described in more detail below, deletions are always executed before insertions.

When inserts and deletes are issued together in the same transaction, the deletes are executed first, irrespective of whether the inserts take precedence in the code.

The example below first initializes a unary base relation t:

// update

def insert[:t] = {1; 2}
def output:t = t

You will now add and remove data through an insert and a delete. Note: In the RelationalAI guides, blocks of Rel code are executed as a single transaction. Since they are in the same transaction, the order of these lines does not matter:

// update

def delete[:t] = {1; 2; 10; 20}
def insert[:t] = {1; 2; 3}
def output:t = t
def output:insert = insert
def output:delete = delete

As you can see, 1 and 2 are still in t.

Deleting elements that don’t exist is not an error. This is why you can include extra elements in delete, such as 10 and 20 in this example, that are not in the t relation in the first place.

### Updating Existing Values

In general, to update an existing value, the old value must be deleted — otherwise, the new value will simply be added, and the old and new values will coexist.

For example, suppose you give "jack" a balance of 60:

// update

def delete[:balance] = balance
def insert[:balance]["jack"] = 60
def output = balance

You can update the balance for "jack" with the following transaction, which also removes the old balance:

// update

def delete[:balance]("jack", x) = balance("jack", x)
def insert[:balance]("jack", new) = balance("jack", old) and new = old + 40 from old
def output = balance

If balance is meant to be a function, you can add an integrity constraint for it. A functional dependency on a relation, indicating that the last argument is uniquely determined by the values of the previous ones, can be specified with the built-in relation function:

// install

ic balance_is_a_function { function(balance) }

This IC will make sure that no customer ever has two different account balances — any transaction that violates it will fail. See the Integrity Constraints section for other examples of ICs relevant to data updates.

## Transactions

A database transaction is a unit of work that takes the database from one state S to a new state S_new. Transactions happen atomically, meaning that either the entire transaction succeeds, with no externally visible intermediate states and no interference from other transactions, or the database remains at S. In the case of a read-only transaction, the two states are the same.

Internally, a transaction can be divided into the following steps:

1. Installed sources are updated, if applicable, and are reverted if the transaction fails.
2. Deletes are applied, evaluating delete in state S.
3. Inserts are applied, evaluating insert in state S.
4. Integrity constraints are checked in the state S_new, as a result of steps 2 and 3.
5. If the integrity constraints fail, the entire transaction fails, and the database remains at state S.
6. Outputs, if any, are evaluated at state S_new.
7. Commit, if the transaction is not read-only. The database state is now S_new.

### Current and Updated States

Note that the body of an update refers to the “current” state of the database, before any of the updates are carried out. For example, consider a database where you initialize r to {1; 2} as follows:

// update

def delete[:r] = r // clear r
def delete[:t] = t // clear t
def insert[:r] = {1; 2}
def output:t = t
def output:r = r

Suppose you add some elements to r, but also set the new relation t to be equal to r:

// update

def insert[:r] = {3; 4; 5}
def insert[:t] = r
def output:insert = insert
def output:t = t
def output:r = r

As you can see, the updated r will contain {1; 2; 3; 4; 5}, but t will contain the previous value of r, that is, {1; 2}. This is because when evaluating def insert[:t] = r, the relation r still had the initial values. The current transaction has not been committed, and r has not been changed yet.

The situation is different when you evaluate the output relation. As you can see, insert[:t] in the output will contain the new value of r ({1; 2; 3; 4; 5}), rather than the old one. This means you cannot rely on the output value of insert and delete alone to see deltas. See the Change Tracking section for alternatives.

If you want t to get the latest values of r, you should execute def insert[:t] = r in a separate transaction, after r has been updated:

// update

def insert[:t] = r
def output:t = t
def output:r = r
🔎

In a non-read-only update transaction, insert and delete definitions are evaluated at the start of the transaction. Output relations are evaluated at the end of the transaction and reflect the updates — even if you ask for insert and delete.

### Concurrent Writing Transactions

A common situation that you may experience is trying to write transactions simultaneously to a certain database, such as when you are constantly updating a base relation by inserting new data.

In these cases where there may exist concurrent writes, one — or more — writing transactions will “lose” their current writing slot, i.e., a failed compare-and-swap (CAS) operation. These transactions will be aborted and rerun the writing operation, up to eight retries.

Note that the scope of conflict is the entire database you are working with.

You can verify that a certain transaction has been successful by querying the updated relation. You can also check it by inspecting the transaction log in the RAI Console.

CSV files are also loaded using insert. See the CSV Import how-to guide for details. Here’s a quick recap:

def insert[:my_csv_relation] = load_csv["/my/filename.csv"]
def insert[:my_json_relation] = load_json["/my/filename.json"]

Note that the above will add new data to any previously existing relation. You can fully replace previously loaded data like this:

def delete[:my_csv_relation] = my_csv_relation
def insert[:my_csv_relation] = load_csv["/my/updated_filename.csv"]

The loaded relation can be transformed before being persisted — for example, if only a subset of the external data is persisted. Here, only columns A and B are kept:

@inline
def schema_map[CSV](k, v) = CSV(:A, pos, k) and CSV(:B, pos, v) from pos
def insert[:my_csv_relation] = schema_map[load_csv["/my/filename.csv"]]

### Updating a Single Field in JSON Data

// update

def config[:data] = """{ "a": {"b": {"c": 1, "d": 2}} }"""
def output = myjson

You can update one of its fields as follows:

// update

def delete[:myjson][:a, :b, :c] = myjson[:a, :b, :c]
def insert[:myjson][:a, :b, :c] = 1234
def output = myjson

See the JSON Import and Export how-to guide for more on JSON data.

Installed update rules can be used to implement reactive database logic, where the values in the database are updated according to external events.

As an example, consider the balance base relation again. You can clear it and give "jack" and "jill" a balance of 100:

// update

def delete[:balance] = balance
def insert[:balance] = {("jack", 100); ("jill", 100)}
def output = balance

To transfer money from one account to another, on the condition that the balances do not become negative, you can install the following update logic:

// install

def transfer = {}
def insert[:balance] = {(p1, b1_new) ; (p2, b2_new) }
from
p1, p2, val,
b1_new, b2_new, b1_old, b2_old
where
transfer(p1, p2, val)
and val != 0
and balance(p1, b1_old)
and balance(p2, b2_old)
and b1_new = b1_old - val
and b2_new = b2_old + val
and b1_new >= 0
and b2_new >= 0

def delete[:balance](x, y) = balance(x, y) and insert(:balance, x, new) from new
🔎

The code block above triggered a RelationalAI.install API call, indicated by the install label above it. For more on installing sources, see the Installing Models concept guide.

If the derived relation transfer(p1, p2, val) holds a nonzero value val, the logic above “triggers”, transferring an amount val from account p1 to account p2, provided neither account falls below 0.

Note that even though you have not populated transfer, you are able to install logic in the database that will update the base relation balance if transfer is present.

You can do this even if transfer is not defined. The system will report that transfer is undefined, but the code will still be installed. To avoid this warning, you can add def transfer = {} to the code above. Note that the RAI query engine combines all defs when installing the logic above, so this has no effect when other values are present in the relation. Also, the order in which the definitions are written down is not relevant. See Rel Primer: Basic Syntax for more details.

If the relation transfer has values, the balance transfer happens. The transfer relation is known as the trigger in some formulations, since the update does not happen without it.

Try triggering a balance transfer of 40 from "jack" to "jill" by running:

// update

def transfer = ("jack", "jill", 40)
def output = balance

Since the installed code runs with every query, this tuple in the transfer relation caused the updates to balance to happen.

You can verify the balance updates by installing these integrity constraints:

// install

ic balance_check {
balance["jack"] + balance["jill"] = 200
}

ic nonnegative_balances(x, y) {
balance(x, y) implies y >= 0
}

Executing the same query again triggers another update to balance:

// update

def transfer=("jack", "jill", 40)
def output = balance

If you execute it one more time, balance will be unchanged, since there are not enough funds in the "jack" account:

// update

def transfer=("jack", "jill", 40)
def output = balance

The condition b1_new >= 0 prevented the insert from being triggered. Note that no error happened: The transaction succeeded, but no updates were made.

It is important to remember that installed insert or delete definitions are always executed with each transaction. This might lead to some confusing behavior, if you forget that the code is installed.

If you install an unconditional insert, as in insert[:p] = 1, that value will always be present in the relation and cannot be removed, unless you uninstall the insert. Note that an installed delete can possibly be overridden by an insert, since inserts are executed after deletes. An unconditional insert can only be undone by uninstalling or modifying the corresponding source code.

This behavior might be useful to ensure that certain elements of a relation can never be deleted.

The Console notebook lets you browse and edit installed sources.

If you’re using the API, you can run RelationalAI.list_source(<connection>) to see what’s installed in the database, and RelationalAI.delete_source(<connection>, <source_name>) to remove a particular source.

## Change Tracking

You can debug updates by using auxiliary base relations to see what was inserted and deleted. For example, you can install these rules for inserted and deleted:

// install

def delete[:inserted] = inserted
def delete[:deleted] = deleted
def insert[:inserted](p, x) = insert[p](x) and p != :inserted
def insert[:deleted](p, x) = delete[p](x) and p != :deleted

You can update t and check for inserted and deleted afterwards:

// update

def delete[:t] = t
def insert[:t] = {10; 11; 12}
def output:inserted = inserted
def output:deleted = deleted

Note that inserted and deleted are really supersets of what was changed in the relation. For more accuracy, you can use a snapshot, as described below.

### Snapshotting Relations

You can always snapshot a derived relation or base relation, saving it to disk. Currently, the relation t is {10; 11; 12} (see above). You can now snapshot and update it:

// update

def delete[:t_snapshot] = t_snapshot
def insert[:t_snapshot] = t
def insert[:t] = {100; 101}
def delete[:t] = t
def output = t_snapshot

Note that the relation is captured before the updates are applied. Later, you can compare the snapshot with the latest version of the relation. For example:

// query

def changed[:added](x...) = t(x...) and not t_snapshot(x...)
def changed[:deleted](x...) = t_snapshot(x...) and not(t(x...))
def output = changed

Note that this gives more accurate information about what has actually changed in t, compared to looking only at insert and delete.

## Integrity Constraints

Recall that integrity constraints are evaluated after all deletes and inserts are applied. If an update violates an integrity constraint, the violation is reported and the entire transaction fails. Integrity constraints can therefore ensure that properties of the database are maintained by any update. See the Integrity Constraints concept guide for more details.

For example, you can make sure that a relation contains only integers with:

ic p_int { forall(x: p(x) implies Int(x) ) }

You can ensure that a relation is never empty by installing the constraint:

ic p_nonempty { not empty(p) }

As the diagram in the Transactions section shows, integrity constraints are checked after all the updates are applied, and before the transaction commits. Thus, for example, the p_nonempty IC will not be triggered if, in the same transaction, you can delete all the elements of p and then insert a new one:

def delete[:p] = p
def insert[:p] = 1

You can disallow all inserts or deletes with this integrity constraint:

ic readonly_database { not exists(x...: insert(x...)) and not exists(x... : delete(x...))}

## Recursive Inserts and Deletes

The insert and delete relations can be defined recursively, to handle “cascading updates,” where an update can depend on previous ones. For example:

def insert[:ancestor](x, y) = parent(x, y)
def insert[:ancestor](x, y) = parent(x, t) and insert[:ancestor](t, y) from t

This populates an ancestor base relation, starting with the existing parent relation, and then recursively adding new pairs if the inserts imply it. See the Recursion concept guide for more details on recursion in Rel.

As another example, assume that you have several products. You have a binary relation part_of(x, y), where x is a component of y, and that you want to mark products as imported if one of their parts is imported. Given a new_import fact, you can then update:

def insert[:imported](x) = new_import(x)
def insert[:imported](y) = part_of(x, y) and insert[:imported](x) from x

Note that this only updates the imported relation for products connected to the new_import fact.

## Other Notes

### Listing Base Relations

The SDK APIs allow you to list all installed base relations. For example, see this section of the RelationalAI SDK for Julia.

Creating both derived relations and base relations with the same name is not allowed. For example, this will fail:

def insert[:r] = {1; 2}
def r = 3

Like derived relations, base relations can be overloaded by arity and type. If you insert different types or arities into the same relation name, overloaded versions of the relation will be created:

// update

def insert[:q] = 3.0
def insert[:q] = "a"
def insert[:q] = (1, "b")
def output = q

## Summary

Updating data in a RAI database using the Rel language depends on changing, adding, or deleting either the base relations — the raw data — or the rules that define derived relations. The reserved relations insert and delete can be used in base relations to add or remove data. They are also used to load and replace CSV and JSON files. Rel also allows you to implement reactive data logic, use auxiliary base relations to update bugs, and even recursively define insert and delete relations to handle updates that depend on previous transactions.