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 keywords key, index, or mutable to each attribute. The key annotation will add a Unique index on the column, and index adds an index. The mutable annotation lets Rell know that the property can be changed, thus having no actual effect on the database. Let's see 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 see that a column rowid has been added as the primary key. Rell uses this to reference each entity. Then, we see that my_key gets a unique constraint, implicitly indicating that it has a UNIQUE INDEX in the database. The my_index attribute becomes a column that gets 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 we see 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, but specifying the reference as type: index my_street: street;.

It's also possible to add joined indexes. By specifying key attribute1, attribute2; or index attribute1, attribute2;, we can index based on more than one column depending on whether this combination must be unique. For example, we know that there can be only one house with a specific number, so we make the following change into 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 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");

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

tip

Don't define too many entities. A complex database model will likely result in many joins, making the dapp slower.