# Comparing SQL tables and Rell entities · Mastering Rell · Learn Chromia

> 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…

Source: https://chromia.com/learn/rell-masterclass/entities/

Mastering RellLesson 2 of 8

1. [01Mastering Rell](https://chromia.com/learn/rell-masterclass/introduction/)
2. [02Comparing SQL tables and Rell entities](https://chromia.com/learn/rell-masterclass/entities/)
3. [03Code optimization example](https://chromia.com/learn/rell-masterclass/example/)
4. [04Keys and indexes](https://chromia.com/learn/rell-masterclass/indexes/)
5. [05INNER JOIN statement](https://chromia.com/learn/rell-masterclass/join/)
6. [06SELECT statement](https://chromia.com/learn/rell-masterclass/select/)
7. [07Subqueries](https://chromia.com/learn/rell-masterclass/sub-queries/)
8. [08UPDATE statement](https://chromia.com/learn/rell-masterclass/update/)

[Learn](https://chromia.com/learn/)[Mastering Rell](https://chromia.com/learn/rell-masterclass/)

02 / 08

# 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.

rell

```
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:

rell

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

sql

```
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:

rell

```
entity street {
  key address: text;
}

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

sql

```
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:

rell

```
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:

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](https://chromia.com/learn/relationships-course/introduction/) course.

tip

Keep the number of entities small. A complex data model tends to require many joins, and those joins slow down your dapp.

## Practical example

Create a new dapp, then move into its directory:

shell

```nolines
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:

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

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

src/main/operations.rell

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

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

shell

```nolines
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:*

shell

```nolines
chr node update
```

For details, see the [Chromia CLI node documentation](https://docs.chromia.com/build/cli/commands/node/).

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

shell

```nolines
chr tx create_street "Drottninggatan"
```

shell

```nolines
chr tx create_street "Kungsgatan"
```

The output confirms that both transactions were posted and confirmed:

shell

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

shell

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

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

src/main/queries.rell

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

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

shell

```nolines
chr query get_all_streets
```

It returns:

result

```
[
  [
    "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:

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

The following commands call `create_house` three times, creating three `house` records:

shell

```nolines
chr tx create_house 1 23 3 2 80
```

shell

```nolines
chr tx create_house 2 24 12 2 500
```

shell

```nolines
chr tx create_house 2 30 15 3 700
```

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

src/main/queries.rell

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

Run `get_all_houses` from the CLI:

shell

```nolines
chr query get_all_houses
```

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

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
  ]
]
```

Written for this site from Chromia’s public course. Code samples are the course’s own.

[← Previous**Mastering Rell**](https://chromia.com/learn/rell-masterclass/introduction/)[Next →**Code optimization example**](https://chromia.com/learn/rell-masterclass/example/)

On this page

* [Foreign keys](#foreign-keys)