Comparing SQL tables and Rell entities

Every entity you declare with the entity keyword maps to a SQL table, and each of its attributes becomes a column in that table. The examples below show how a declaration translates into SQL.

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

You can mark any attribute with key, index, or mutable. key puts a unique index on the column, and index puts a non-unique index on it. mutable tells Rell the value may be modified later. It has no direct effect on the database. Here is how an entity appears in the database:

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

create table "c0.my_entity"(  "rowid" bigint not null,  "my_key" text not null,  "my_index" text not null,  "my_mutable" text not null,  "my_immutable" text not null,  constraint "PK_c0.my_entity"  primary key ("rowid"),  constraint "K_c0.my_entity_0"  unique ("my_key"));create index "IDX_c0.my_entity_0" on "c0.my_entity"("my_index");

Rell has added a rowid column as the primary key. It uses this column to reference each entity. The my_key column has a unique constraint, which means the database implicitly gives it a UNIQUE INDEX. my_index is a column with an index on it. The mutable and immutable columns are identical at the database level.

Foreign keys

Relational databases usually contain multiple tables that reference each other, and foreign keys on columns enforce those references. In Rell, you create a foreign key by using another entity as an attribute type. The following example uses two tables, one for street addresses and one for houses:

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

create table "c0.street"(  "rowid" bigint not null,  "address" text not null,  constraint "PK_c0.street"  primary key ("rowid"),  constraint "K_c0.street_0"  unique ("address"));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"));create index "IDX_c0.house_0" on "c0.house"("street");

A single street address can have many houses. When the house entity references the street entity, the house table gets a bigint column with a foreign key constraint pointing to the rowid of the street table. You can also give the reference a different name and specify the entity as its type, for example index my_street: street;.

Indexes can also span several columns. Write key attribute1, attribute2; if the combination must be unique, or index attribute1, attribute2; if it does not need to be. A street can have only one house with a given number, so the entity changes as follows:

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

This change produces the following SQL:

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");

The result is a unique constraint on the combination of the street and number columns.

To learn more about modeling relationships between entities, see the Relationships in Rell explained course.

Practical example

Create a new dapp, then move into its directory:

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

Delete the main.rell file from the src directory, then create a new directory named main there.

Inside main, create a file named entities.rell with this code:

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;}

Next, create operations.rell in src/main and add this operation:

operation create_street(address: text) {    create street( address );}

You need a local node running before you can work with the database:

chr node start

If you want the database wiped at startup, run chr node start --wipe.

Any edits you make to your Rell files while the node is running take effect only after you update the node:

chr node update

For details, see the Chromia CLI node documentation.

With the node running, use the Chromia CLI to call the operations and create records:

chr tx create_street "Drottninggatan"

chr tx create_street "Kungsgatan"

The output confirms that both transactions were posted and confirmed:

`transaction with rid B15128E212BAB67337E902C3A29182D57259E409B180DD50AB6415C0BB7ADE8B was posted CONFIRMED`

`transaction with rid F2C28DFA0596FB9EE6281994EC620EB7D8996E10F93F756598CBA6672C0B1780 was posted CONFIRMED`

To fetch every street stored in the database, add this query to src/main/queries.rell:

query get_all_streets() = street @* { } ( id = .rowid, .address );

Run the get_all_streets query from the CLI with the chr command:

chr query get_all_streets

It returns:

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

The next operation creates a house record that links to a street record in the other table:

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 );}

The following commands call create_house three times, creating 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

Now add the get_all_houses query to queries.rell in the src/main/ folder:

query get_all_houses() = house @* { } ( $.to_struct() );

Run get_all_houses from the CLI:

chr query get_all_houses

The query returns an array of arrays, each holding the details of one house record:

[  [    "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  ]]