Skip to main content

SQL table vs Rell entity

We start by looking at how to define an entity and what the corresponding SQL table will look like. An entity is represented by a SQL table and is defined using the keyword entity. Each attribute represents a column of the SQL table.

entity my_entity {
    key my_key: text;
    index my_index: text;
    mutable my_mutable: text;
    my_immutable: text;
}

It's possible to add the keywords key, index, or mutable to each attribute. The key annotation adds a unique index on the column, while index adds a non-unique index. The mutable annotation informs Rell that the property can be changed, but it does not directly affect the database. Let's examine what an entity looks like in the database.

entity my_entity {
    key my_key: text;
    index my_index: text;
    mutable my_mutable: text;
    my_immutable: text;
}

First, we observe that a column rowid has been added as the primary key, which Rell uses to reference each entity. Next, my_key receives a unique constraint, implicitly indicating that it has a UNIQUE INDEX in the database. The attribute my_index becomes a column with an index. Both the mutable and immutable columns are equal in the database.

Foreign keys​

In a relational database, having several tables and references between them is common. This is enforced by adding foreign keys to a column. In Rell, this is done by referencing another entity as an attribute. Let's consider a simple example: one table representing a street address and one representing a house.

entity street {
  key address: text;
}

entity house {
  index street;
  number: integer;
  number_of_rooms: integer;
  number_of_floors: integer;
  floor_area: integer;
}

For each street address, we can have several houses. As seen here, referencing the street entity from the house entity corresponds to a bigint value in the house table, with a foreign key constraint on the street table rowid. When declaring the foreign key, it's also possible to set another name, specifying the reference as type: index my_street: street;.

It's also possible to add composite indexes. By specifying key attribute1, attribute2; or index attribute1, attribute2;, we can index based on multiple columns, depending on whether this combination must be unique. For example, we know that there can only be one house with a specific number, so we make the following change to the entity:

entity house {
  index street;
  number: integer;
  key street, number;
  number_of_rooms: integer;
  number_of_floors: integer;
  floor_area: integer;
}

This will correspond to the following SQL query:

create table "c0.house"(
  "rowid" bigint not null,
  "street" bigint not null,
  "number" bigint not null,
  "number_of_rooms" bigint not null,
  "number_of_floors" bigint not null,
  "floor_area" bigint not null,
  constraint "PK_c0.house"
  primary key ("rowid"),
  constraint "c0.house_street_FK"
  foreign key ("street")
  references "c0.street" ("rowid"),
  constraint "K_c0.house_0"
  unique (
      "street",
      "number"
    )
);
create index "IDX_c0.house_0" on "c0.house"("street");

A unique constraint was added with a combination of street and number columns.

For a deeper understanding of managing relationships between entities, check out our Understand relationships in Rell course.

tip

Define only a few entities. A complex database model will likely result in many joins, making the dapp slower.

Practical example

Create a new dapp and change to its directory:

chr create-rell-dapp my_app --template=plain && cd my_app

In the src directory, delete the main.rell file and create a new directory called main.

In the main directory, create the entities.rell file and insert the following code:

src/main/entities.rell
entity street {
  key address: text;
}

entity house {
  index street;
  number: integer;
  key street, number;
  number_of_rooms: integer;
  number_of_floors: integer;
  floor_area: integer;
}

Create a new file called operations.rell in the src/main directory and insert the following operation:

src/main/operations.rell
operation create_street(address: text) {
    create street( address );
}

To interact with the database, we must run the node locally:

chr node start

To wipe the database before startup, you can use chr node start --wipe.

When you make subsequent changes to your Rell files while the node is running, you must update your node to apply them:

chr node update

For more information, refer to the Chromia CLI Node Documentation.

Afterwards, we can invoke our operations to create new records using the Chromia CLI tool:

chr tx create_street "Drottninggatan"
chr tx create_street "Kungsgatan"

The output of these operations shows two transactions that were successfully posted and confirmed:

`transaction with rid B15128E212BAB67337E902C3A29182D57259E409B180DD50AB6415C0BB7ADE8B was posted CONFIRMED`
`transaction with rid F2C28DFA0596FB9EE6281994EC620EB7D8996E10F93F756598CBA6672C0B1780 was posted CONFIRMED`

Add the query below to the src/main/queries.rell file to request all available streets stored in the database:

src/main/queries.rell
query get_all_streets() = street @* { } ( id = .rowid, .address );

You can invoke the get_all_streets query using the chr command in the CLI:

chr query get_all_streets

The result of this operation is as follows:

result
[
  [
    "address": "Drottninggatan",
    "id": 1
  ],
  [
    "address": "Kungsgatan",
    "id": 2
  ]
]

The following operation creates a house record, which links to the street record in another table:

src/main/operations.rell
operation create_house(street_id: rowid, number: integer, number_of_rooms: integer, number_of_floors: integer, floor_area: integer) {
    val street = street @ { .rowid == street_id };
    create house( street, number, number_of_rooms, number_of_floors, floor_area );
}

Below, we invoke the create_house operation to create three house records:

chr tx create_house 1 23 3 2 80
chr tx create_house 2 24 12 2 500
chr tx create_house 2 30 15 3 700

Add the get_all_houses query to the queries.rell file in the src/main/ folder:

src/main/queries.rell
query get_all_houses() = house @* { } ( $.to_struct() );

You can invoke the get_all_houses query in the CLI:

chr query get_all_houses

The result of this query is an array of arrays containing details about the house records:

result
[
  [
    "floor_area": 80,
    "number": 23,
    "number_of_floors": 2,
    "number_of_rooms": 3,
    "street": 1
  ],
  [
    "floor_area": 500,
    "number": 24,
    "number_of_floors": 2,
    "number_of_rooms": 12,
    "street": 2
  ],
  [
    "floor_area": 700,
    "number": 30,
    "number_of_floors": 3,
    "number_of_rooms": 15,
    "street": 2
  ]
]