Diesel ORM

While Queryable is the standard trait for Diesel's query builder, you must use QueryableByName when executing raw SQL via the sql_query function. Because raw SQL doesn't provide the same type-safety guarantees as the query builder, Diesel maps results by column name rather than index.

To implement QueryableByName, you must provide type information for the struct fields using one of two methods:

1. Table Annotation: Add #[diesel(table_name = my_table)] to the struct. Diesel will then attempt to bind fields to the types defined in that table's schema.
2. Field Annotation: If not using table_name, every field must be explicitly annotated with #[diesel(sql_type = ColumnTypeHere)].

If you combine both, sql_type annotations will override the types found in the table_name schema.

Nested Structs: You can use #[diesel(embed)] on a field if that field is another struct that also implements QueryableByName.

// Example of QueryableByName with table mapping and field overrides
#[derive(Debug, QueryableByName)]
#[diesel(table_name = users)]
pub struct UserName {
    pub first_name: String,
    pub last_name: String,
    #[diesel(sql_type = Text)] // Overrides or provides type for a non-column field
    pub full_name: String,
}

#[derive(Debug, QueryableByName)]
#[diesel(table_name = posts)]
pub struct PostsWithUserName {
    #[diesel(embed)]
    pub user_name: UserName,
    pub title: String,
    pub body: String,
}

// Usage with sql_query
let joined = sql_query("SELECT ... FROM users INNER JOIN posts ...")
    .load::<PostsWithUserName>(&connection);

When building service layers, it is a best practice to create distinct types for different database operations to ensure type safety and encapsulation:

• Service (Read): Uses Queryable and Selectable. Represents the full record as it exists in the database.
• CreateService (Insert): Uses Insertable. Contains the fields required for a new record. Often identical to the Service type but without primary keys if they are auto-generated.
• UpdateService (Update): Uses AsChangeset. Every field is wrapped in an Option<T>. Diesel treats Some(value) as a field to be updated and None as a field to be ignored.

Implementing these methods directly on the Service struct encapsulates database logic and provides a single point of maintenance when the schema changes.

#[derive(Debug, Clone, Queryable, Selectable)]
#[diesel(table_name = crate::schema::smdb::service, primary_key(service_id))]
pub struct Service {
    pub service_id: i32,
    pub name: String,
    // ...
}

#[derive(Debug, Clone, Queryable, Insertable)]
#[diesel(table_name = crate::schema::smdb::service, primary_key(service_id))]
pub struct CreateService {
    pub service_id: i32,
    // ...
}

#[derive(Debug, Clone, Queryable, Insertable, AsChangeset)]
#[diesel(table_name = crate::schema::smdb::service)]
pub struct UpdateService {
    pub name: Option<String>,
    pub version: Option<i32>,
    // ...
}

The Identifiable trait allows a struct to be uniquely identified by its primary key, which is required for certain operations like table associations and updates using diesel::update(&model).

Key Behaviors

• Primary Key Detection: By default, it assumes the primary key is a column named id.
• id() Method: Implementing this trait provides an .id() method on your model instance that returns the value of the record's primary key.
• Table Mapping: It assumes the struct name is the singular form of the table name. If they differ, use #[diesel(table_name = some_table_name)].

Customizing Primary Keys

If your primary key is not named id or is a composite key, use the #[primary_key(...)] attribute:

• Single field: #[primary_key(some_field_name)]
• Composite key: #[primary_key(field_a, field_b)]

#[derive(Identifiable, Queryable)]
#[diesel(table_name = users)]
#[diesel(primary_key(id))] // Optional if name is 'id'
pub struct User {
    pub id: i32,
    pub first_name: String,
    pub last_name: String,
    pub email: String,
}

The AsChangeset trait allows you to easily update multiple fields at once using a struct, similar to how Insertable works for insertions. This is particularly useful for updating large amounts of deserialized data.

Handling Nullable Fields

When working with nullable columns (represented as Option<T> in Rust), AsChangeset provides three ways to handle updates:

1. Ignore the field: If the field is None, the column is not included in the UPDATE statement. This is the default behavior.
2. Set to NULL: If you want to explicitly set a column to NULL, you can use the #[diesel(treat_none_as_null = true)] attribute on the struct. In this mode, a None value results in a NULL in the database.
3. The Double Option Pattern: To support both 'ignore' and 'set to NULL' in a single struct, use Option<Option<T>>:
   • None $\rightarrow$ Field is ignored.
   • Some(None) $\rightarrow$ Field is set to NULL in the database.
   • Some(Some(value)) $\rightarrow$ Field is updated to value.

Configuration

• Primary Key: AsChangeset automatically ignores the primary key field to prevent accidental changes. If your primary key is not named id, you must specify it with #[diesel(primary_key(your_key))].
• Table Mapping: Use #[diesel(table_name = your_table)] to link the struct to a specific table.

Diesel uses the Associations trait to manage relationships between database tables. Relationships are uni-directional and focus on the child to parent relationship (e.g., a Post belongs to a User).

To implement an association:

1. On the child struct: Use #[derive(Associations)].
2. Reference the parent: Use the #[diesel(belongs_to(ParentStruct))] attribute on the child struct.
3. Requirements: Both the parent and child structs must implement the Identifiable trait.
4. Foreign Keys: By default, Diesel expects the foreign key column on the child to be named parent_id (e.g., user_id for a User parent). If you use a custom foreign key, specify it in the attribute: #[diesel(belongs_to(ParentStruct, foreign_key = my_custom_key))].

// Child struct setup
#[derive(Identifiable, Associations, Queryable)]
#[diesel(belongs_to(User))]
pub struct Post {
    pub id: i32,
    pub user_id: i32, // Default pattern: parent_id
    pub title: String,
    pub content: String,
}

// Parent struct setup
#[derive(Identifiable, Queryable)]
pub struct User {
    pub id: i32,
    pub first_name: String,
    pub last_name: String,
    pub email: Option<String>,
}

When Diesel generates schema.rs for tables containing custom Postgres types, it does not recreate the full structure of the custom type. Instead, it generates a zero-sized SQL type struct with the appropriate annotations (e.g., #[diesel(postgres_type(name = "...", schema = "..."))]).

In the generated diesel::table! macro, the column type for an array of custom types will appear as Array<Nullable<CustomType>>. You must then create a matching Rust struct in your own model module to map the database data to Rust types.

// @generated automatically by Diesel CLI.

pub mod smdb {
    pub mod sql_types {
        #[derive(diesel::query_builder::QueryId, Clone, diesel::sql_types::SqlType)]
        #[diesel(postgres_type(name = "service_endpoint", schema = "smdb"))]
        pub struct ServiceEndpoint;
    }

    diesel::table! {
        use diesel::sql_types::*;
        use super::sql_types::ServiceEndpoint;

        smdb.service (service_id) {
            service_id -> Int4,
            name -> Text,
            version -> Int4,
            online -> Bool,
            description -> Text,
            health_check_uri -> Text,
            base_uri -> Text,
            dependencies -> Array<Nullable<Int4>>,
            endpoints -> Array<Nullable<ServiceEndpoint>>,
        }
    }
}