Skip to main content

One-to-one relationship

A one-to-one relationship exists when a single record in one table is associated with a single record in another table. For example, imagine a library system where each library has one unique address, and each address is linked to precisely one library. This relationship ensures that every library entry corresponds to one specific address.

Implementing a one-to-one relationship in a traditional relational database involves creating multiple tables and using foreign keys to establish references between them. This can get quite complex, especially when ensuring data integrity and uniqueness. Here’s an example of how you would set up a one-to-one relationship in SQL:

CREATE TABLE Address (
    id INT PRIMARY KEY,
    street VARCHAR(255),
    city VARCHAR(255),
    postalCode VARCHAR(10)
);

CREATE TABLE Library (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    address_id INT,
    FOREIGN KEY (address_id) REFERENCES Address(id)
);

In this example:

  • Address table: Contains the address details with a primary key ID to identify each address uniquely.
  • Library table: Contains library details and has a foreign key, address_id, which references the ID in the Address table. The UNIQUE constraint ensures that each address is linked to only one library.

Instead of manually dealing with foreign keys and constraints, Rell lets you directly reference entities.

First, create a new directory called library_management in your /src directory. Then, add a file named module.rell with the following code:

src/library_management/module.rell
module;

Create a new file entities.rell in the /src directory and add the following code:

src/library_management/entities.rell
entity library {
    library_name: text;     // Name of the library
    key address;    // One-to-one relationship with Address
}

entity address {
    street: text;         // Street address
    city: text;           // City name
    postal_code: text;    // Postal code
}

First, let’s create an operation to add a new library along with its address. This will involve creating both the Library and Address records and linking them together.

Create a new file operations.rell in the /src/library_management directory and add the following code:

/src/library_management/operations.rell
operation create_library(name: text, street: text, city: text, postal_code: text) {
    val library_address = create address(street, city, postal_code);
    create library(name, library_address);
}

This operation creates an instance of the address using the provided street, city, and postal code. Then, it creates a library using the provided name and the previously created address instance. Rell handles linking these records automatically.

For testing purposes, create the following queries to retrieve records. Add the following code to the queries.rell file:

src/library_management/queries.rell
query get_all_library() = library @* {} ( id = .rowid, .library_name, city = .address.city, street = .address.street, postal_code = .address.postal_code );

query get_library_by_name(library_name: text) = library @ { library_name };

Testing

Let's incorporate some tests to ensure that everything functions as intended. In Rell, we can use test modules to create our tests.

Begin by opening the src/test/my_library_test.rell file and add the test. First, create a library with an address, then using a query, return all existing libraries in our database and check their count:

src/test/my_library_test.rell
@test module;

import library_management.{ create_library, get_all_library };

function test_create_library() {
    // Create a new library with an address
    rell.test.tx()
        .op(create_library("Biblioteca Apostolica Vaticana", "Città del Vaticano", "Vatican", "00120"))
        .op(create_library("New York Public Library", "476 5th Ave", "New York", "10018"))
        .run();

    val all_libraries = get_all_library();
    assert_equals(all_libraries.size(), 2);
}

Next, run the following command in the terminal from the project folder to test the code:

chr test

The result of the successful test looks as follows:

====================Running unit tests====================
TEST: test.my_library_test:test_create_library
OK: test.my_library_test:test_create_library (5.010s)

------------------------------------------------------------
TEST RESULTS:

OK test.my_library_test:test_create_library

SUMMARY: 0 FAILED / 1 PASSED / 1 TOTAL (5.010s)

***** OK *****

Creating new records

In the following example, we use Chromia CLI to create new library records. Then, these records are queried in the examples provided in the next section.

To interact with the database, you must run the Chromia node locally. In case you are experimenting, we recommend running the node with the --wipe flag because it removes all currently stored records.

chr node start --wipe

To create a one-to-one relationship, we must generate two data tables, library and address. This can be achieved using the create_library operation provided above.

In the example below, we invoke the create_library operation twice to create two related records, passing the required arguments:

chr tx --await create_library "Biblioteca Apostolica Vaticana" "Città del Vaticano" "Vatican" '"001201"'
chr tx --await create_library "New York Public Library" "476 5th Ave" "New York" '"10018"'

The successful invocation must return the following:

transaction with rid 0CAADC0937030F67C78003E2F9C1CA15E8C5D0C280ACA295CB8CD747AA09FBD9 was posted CONFIRMED

Querying new records

To query new records, you can run the get_all_library operation in the Chromia CLI:

chr query get_all_library

The result of this query is:

[
  [
    "city": "Vatican",
    "id": 2,
    "library_name": "Biblioteca Apostolica Vaticana",
    "postal_code": "001201",
    "street": "Citta del Vaticano"
  ],
  [
    "city": "New York",
    "id": 4,
    "library_name": "New York Public Library",
    "postal_code": "10018",
    "street": "476 5th Ave"
  ]
]