Creating a table

Creating a new table is probably the most complicated aspect of the database. There are two important decisions that have to be made.

The first is the category of the table. We try to keep various data elements grouped together to make comprehension together, including the *Error class that encapsulates most error conditions. Examples of these are the accounts or groups. These categorys generally form a directed acicylic graph with the higher-order categories building on top of the lower ones; a good way of knowing this is to look if the error class includes another categories of errors.

The second is the table name. This has to be unique throughout the entire system:

• The table name is going to be the plural form (e.g., accounts)
• The table will be in the default public. schema
• The entity directory will be a singular lower_snake_case (account)
• The entity class will be the singular form in PascalCase (Account)

Between these two determines the paths of the files that are created:

• src/leicmin/src/data/<category>/<lower_snake_case>/mod.rs
• This will contains the logical view of the class
• src/leicmin/src/data/<category>/<lower_snake_case>/db.rs
• This will contains the SeaORM model
• src/leicmin/src/data/migrations/mYYYYMMDD_HHMMSS_name.rs
• This will contain the migration that creates the table

Steps

1. Define the type-safe identifier for the table
2. Add the logical entity class in mod.rs
3. Add the SeaORM Model, Entity, Column, Relation, and ActiveModelBehavior in db.rs

• For the most part, there will be a one-to-one mapping for every column in the entity struct as in the database struct
• The entity and migration must agree on every column's name, type, nullability, default, primary-key status, uniqueness, foreign-key target, and delete/update behavior

4. Export the module from the necessary parent mod.rs files and add any required re-export in src/leicmin/src/data/db.rs
5. Create the table in a new migration and register the migration in migrations/mod.rs.
6. Add required indexes, constraints, queries, services, and tests

Identifiers

Identifiers are UUID. We prefer the use of UUIDv7 for most identifiers but UUIDv4 will be used when the identifier needs to be harder to guess, such as session identifiers. These are implemented as typesafe identifiers, such as AccountId. A key part is the attribute leicmin_identifier. Omitting the optional version, or specifying v7, generates a new constructor using UUIDv7. Specifying v4 generates a new constructor using UUIDv4, while v8 generates no new constructor:

#[leicmin_identifier("account")]
#[derive(Clone, Copy, Deserialize, Eq, Hash, PartialEq)]
pub struct AccountId(Uuid);

#[leicmin_identifier("session", v4)]
#[derive(Clone, Copy, Deserialize, Eq, Hash, PartialEq)]
pub struct SessionId(Uuid);

For identifiers that need UUIDv4, use #[leicmin_identifier("session", v4)]. The v8 option is reserved for identifiers whose values are supplied by other code rather than generated by the identifier type.

This uses the leicmin-macros create the handle the boilerplate of type_safe_id which creates a TypeId with a unique prefix for the application, such as account_01kedf0ykkf67r67tztwedpx4s. These identifiers are also stored in JSON and serialized format but are bare UUID in the database.

Logical Operations

For the most part, creating a new entity class involves copying patterns from the existing ones. There are some general naming conventions:

• delete* means delete (soft or hard) the associated row
• ensure means retrieve, or insert if not in the database
• find* means retrieve an optional object, usually Result<Option<Self>, CategoryError>
• get* means retrieve a single entity
• insert* inserts the entity into the database, it is a &self method
• list* retrieve a Vec<Self> of the entities
• upsert* means either update or insert depending if the table is in the database

We use *_by_* to qualify operations that use a selection criteria for anything beyond the primary key column.

Physical Operations

The physical operations are basically SeaORM operations that are called from the logical method. In most cases, the logical version should be called but there are situations when going direct to the entity makes sense.

network_addresses example

NetworkAddressId type-safe identifier

The following example creates a network_addresses table with a typed NetworkAddressId using the netaddr_ prefix, a PostgreSQL inet value that accepts either IPv4 or IPv6, and an optional host name.

The typed identifier belongs in src/leicmin/src/data/audits/network_address_id.rs:

use leicmin_macros::leicmin_identifier;
use serde::Deserialize;
use uuid::Uuid;

#[leicmin_identifier("netaddr")]
#[derive(Clone, Copy, Deserialize, Eq, Hash, PartialEq)]
pub struct NetworkAddressId(Uuid);

Entity

For the logical model, we create NetworkAddress in src/leicmin/src/data/audits/network_address/mod.rs:

use std::net::IpAddr;

use crate::data::audits::NetworkAddressId;

pub struct NetworkAddress
{
    pub network_address_id: NetworkAddressId,
    pub address: IpAddr,
    pub host_name: Option<String>,
}

Database

In src/leicmin/src/data/audits/network_address/db.rs, we create the SeaORM components. This can use std::net::IpAddr for PostgreSQL's inet type and Option<String> makes host_name nullable for later processing:

use std::net::IpAddr;

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq)]
#[sea_orm(table_name = "network_addresses")]
pub struct Model
{
    #[sea_orm(primary_key, auto_increment = false)]
    pub network_address_id: NetworkAddressId,
    #[sea_orm(column_type = "Inet")]
    pub address: IpAddr,
    #[sea_orm(column_type = "String(StringLen::N(255))")]
    pub host_name: Option<String>,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation
{
}

impl ActiveModelBehavior for ActiveModel {}

Migration

Use just add-migration network_addresses to create the migration and register it in src/leicmin/src/data/migrations/mod.rs. Then, edit the migration to set up the columns that match the data model above:

#[derive(DeriveIden)]
enum NetworkAddresses
{
    Table,
    NetworkAddressId,
    Address,
    HostName,
}

manager
    .create_table(
        Table::create()
            .table(NetworkAddresses::Table)
            .if_not_exists()
            .col(
                ColumnDef::new(NetworkAddresses::NetworkAddressId)
                    .uuid()
                    .not_null(),
            )
            .col(
                ColumnDef::new(NetworkAddresses::Address)
                    .inet()
                    .not_null(),
            )
            .col(
                ColumnDef::new(NetworkAddresses::HostName)
                    .string_len(255),
            )
            .primary_key(
                Index::create().col(NetworkAddresses::NetworkAddressId),
            )
            .to_owned(),
    )
    .await?;

Seeded rows

If the table requires application-owned rows, seed them after all referenced rows exist. See seed data. Do not use a table migration to silently overwrite user-owned rows.