Relationships and Eager/Lazy Loading

The hardest problem in database abstraction is not storing data. It is navigating relationships. A blog application has Users who write Posts that have Comments from other Users. An e-commerce application has Products in Categories, purchased through Orders containing OrderItems. Every real application is a graph of interconnected entities.

SQL solves this with joins -- powerful but verbose. ORMs solve it with lazy loading -- convenient but riddled with N+1 query problems. FlinDB takes a different approach: explicit relationship declarations, automatic indexing, and controlled loading strategies that make the right thing easy and the wrong thing hard.

Session 164 was where relationships became real. Ten features, thirteen tests, nine hundred and seventy-two lines of Rust. By the end, FlinDB could load related entities eagerly or lazily, query through relationships, find inverse references, and auto-index foreign keys -- all without a single JOIN keyword.

Declaring Relationships

In FlinDB, relationships are declared as typed fields in entity definitions:

flinentity User {
    name: text
    email: text
}

entity Post {
    title: text
    body: text
    author: User           // One-to-many: each Post has one author
}

entity Tag {
    name: text
}

entity Article {
    title: text
    tags: [Tag]            // Many-to-many: each Article has multiple Tags
}

The author: User declaration creates a reference from Post to User. ZeroCore stores this as an entity ID internally, but the FLIN developer works with entity objects directly:

flinuser = User { name: "Thales" }
save user

post = Post { title: "Hello", body: "...", author: user }
save post

// Access the relationship
post.author.name    // "Thales"

No foreign key column. No join table. No user_id INTEGER REFERENCES users(id). The relationship is declared in the entity definition and used naturally in code.

Eager Loading with .with()

The most common performance problem with ORM-style relationship handling is the N+1 query problem. You load N posts, then for each post you load the author -- resulting in 1 + N database queries. FlinDB solves this with explicit eager loading:

rust// Load posts with their authors in a single pass
let results = db.query("Post")
    .with("author")
    .execute()?;

The .with("author") call tells the query builder to resolve the author reference for every Post in the result set. Instead of N separate lookups, ZeroCore resolves all references in a batch after the initial query:

rust// In execute_query(), after collecting results:
if !self.eager_load_fields.is_empty() {
    for entity in &mut results {
        for field_name in &self.eager_load_fields {
            if let Some(Value::EntityRef(ref_type, ref_id)) = entity.fields.get(field_name) {
                if let Ok(Some(referenced)) = db.find_by_id_internal(ref_type, *ref_id) {
                    entity.resolved_refs.insert(field_name.clone(), referenced);
                }
            }
        }
    }
}

Multiple relationships can be loaded simultaneously:

rustdb.query("Post")
    .with("author")
    .with("category")
    .execute()?;

And the .with_all() method loads every EntityRef field in the schema:

rustdb.query("Post")
    .with_all()
    .execute()?;

This resolves author, category, and any other reference fields -- useful when you need the complete entity graph for a detail view.

Reference Queries

Querying through relationships is one of FlinDB's most powerful features. The .where_ref() method filters entities by their referenced entity:

rust// Find all posts by a specific author
let user_posts = db.query("Post")
    .where_ref("author", user_id)
    .execute()?;

Under the hood, .where_ref() is a semantic wrapper around .where_eq() that operates on the reference field's stored ID:

rustpub fn where_ref(mut self, field: &str, ref_id: u64) -> Self {
    self.conditions.push(QueryCondition::Eq {
        field: field.to_string(),
        value: Value::EntityRef("_".to_string(), ref_id),
    });
    self
}

Because reference fields are automatically indexed (more on this below), reference queries benefit from O(1) index lookups. Finding all posts by a specific author is an index lookup, not a table scan.

Null reference filtering is also supported:

rust// Posts with an author assigned
db.query("Post")
    .where_ref_not_null("author")
    .execute()?;

// Posts without an author (drafts, maybe)
db.query("Post")
    .where_ref_is_null("author")
    .execute()?;

List Membership Queries

For many-to-many relationships (lists of references), FlinDB provides .where_list_contains():

flinentity Article {
    title: text
    tags: [Tag]
}

// Find articles that have a specific tag
tagged = Article.where_list_contains("tags", tag_id)

The Rust implementation checks whether the entity's list field contains the specified value:

rustpub fn where_list_contains(mut self, field: &str, value: Value) -> Self {
    self.conditions.push(QueryCondition::ListContains {
        field: field.to_string(),
        value,
    });
    self
}

During execution, the condition parses the list storage format and checks membership:

rustQueryCondition::ListContains { field, value } => {
    if let Some(stored) = entity.fields.get(field) {
        match stored {
            Value::Text(s) if s.starts_with("__LIST__:") => {
                let ids: Vec<&str> = s[9..].split(',').collect();
                // Check if value is in the list
            }
            _ => false,
        }
    } else {
        false
    }
}

Lists are stored as encoded text internally (__LIST__:1,2,3 for integer lists, __LIST_STR__:admin,editor for string lists). This encoding allows lists to be stored as a single field value while remaining queryable.

Inverse Queries

Sometimes you need to navigate a relationship in reverse. Given a User, find all Posts that reference that User. FlinDB provides three inverse query methods:

rust// Find all entities that reference a specific entity
let posts = db.find_referencing("Post", "author", user_id)?;

// Check if any references exist (boolean)
if db.has_references("Post", "author", user_id)? {
    // Cannot delete user -- they have posts
}

// Count references
let post_count = db.count_references("Post", "author", user_id)?;

These methods are essential for cascade operations and referential integrity. Before deleting a User, FlinDB checks has_references() for every entity type that could reference Users. If any references exist and the ON DELETE behavior is RESTRICT, the deletion is blocked.

The find_referencing() implementation uses the index on the reference field for efficiency:

rustpub fn find_referencing(
    &self,
    entity_type: &str,
    ref_field: &str,
    ref_id: u64,
) -> DatabaseResult<Vec<EntityInstance>> {
    // Uses index on ref_field for O(1) lookup
    self.query(entity_type)
        .where_ref(ref_field, ref_id)
        .execute()
}

Automatic Reference Indexing

One of Session 164's most impactful features was automatic indexing of entity reference fields. When a schema is registered with a reference field, ZeroCore automatically adds that field to the indexed fields list:

rust// During schema registration
for field_def in &schema.fields {
    if matches!(field_def.field_type, FieldType::EntityRef(_)) {
        if !schema.indexed_fields.contains(&field_def.name) {
            schema.indexed_fields.push(field_def.name.clone());
        }
    }
}

This is a critical optimization that requires zero developer effort. Without it, every reference query would be a full table scan. With it, Post.where(author == user) is an O(1) index lookup.

The automatic indexing was verified with a dedicated test:

rust#[test]
fn test_ref_field_auto_indexed() {
    let db = ZeroCore::new();
    let schema = EntitySchema::new("Post")
        .field("title", FieldType::String)
        .field("author", FieldType::EntityRef("User".to_string()));
    db.register_schema("Post", schema);

    // Verify that "author" was automatically added to indexed_fields
    let registered = db.get_schema("Post").unwrap();
    assert!(registered.indexed_fields.contains(&"author".to_string()));
}

Batch List Resolution

For many-to-many relationships, resolving an entire list of references is a common operation. FlinDB provides resolve_list_references() for batch resolution:

rustpub fn resolve_list_references(
    &self,
    entity_type: &str,
    entity_id: u64,
    field_name: &str,
    target_type: &str,
) -> DatabaseResult<Vec<EntityInstance>> {
    let entity = self.find_by_id(entity_type, entity_id)?;

    if let Some(Value::Text(list_str)) = entity.fields.get(field_name) {
        if list_str.starts_with("__LIST__:") {
            let ids: Vec<u64> = list_str[9..]
                .split(',')
                .filter_map(|s| s.parse().ok())
                .collect();

            let mut results = Vec::new();
            for id in ids {
                if let Ok(target) = self.find_by_id(target_type, id) {
                    results.push(target);
                }
            }
            return Ok(results);
        }
    }

    Ok(vec![])
}

This resolves all references in a list field to their full entity instances. For an Article with tags: [Tag] containing three tag IDs, resolve_list_references() returns three complete Tag entities.

The Loading Strategy Decision

FlinDB does not have implicit lazy loading. This is a deliberate design choice.

In ORMs like ActiveRecord or Hibernate, accessing a relationship property triggers an implicit database query. This is convenient but dangerous. A template that renders a list of posts, each showing the author name, silently executes N+1 queries. The developer does not see the queries. The performance degradation is invisible until the application slows to a crawl under load.

FlinDB requires explicit loading decisions:

• No .with() call: Reference fields contain the raw ID. Accessing post.author returns the reference ID, not the full entity. No implicit query.
• With .with("author") call: Reference fields are resolved to full entities. Accessing post.author returns the complete User entity.
• With .with_all() call: All reference fields are resolved. Every relationship is loaded.

This forces the developer to think about their data access pattern at query time. It is slightly less convenient than lazy loading, but it eliminates an entire category of performance bugs. You always know exactly how many database lookups a query will perform, because you specified them explicitly.

The Thirteen Tests

Session 164 added thirteen tests covering every relationship feature:

1. test_query_with_eager_loading -- basic .with() usage
2. test_query_with_multiple_relations -- multiple .with() calls
3. test_where_ref_basic -- reference filtering
4. test_where_list_contains -- integer list membership
5. test_where_list_contains_string -- string list membership
6. test_resolve_list_references -- batch list resolution
7. test_ref_field_auto_indexed -- automatic indexing verification
8. test_where_ref_uses_index -- index utilization for reference queries
9. test_find_referencing -- inverse query
10. test_has_references -- boolean reference check
11. test_count_references -- reference counting
12. test_where_ref_null_and_not_null -- null reference filtering
13. test_with_all -- load all relations

The total after Session 164: 2,133 tests (1,527 library + 606 integration). Nine hundred and seventy-two lines of code, all in zerocore.rs. This was one of the densest sessions in FlinDB's development -- nearly a thousand lines of relationship logic in a single sitting.

Beyond Simple Relationships

Session 164 brought relationships from 25% to 60% completion. The remaining work -- nested eager loading (.with("author.company")), cascade behavior through relationships, and relationship constraints -- would be addressed in subsequent sessions.

But the foundation was solid. Entities could reference other entities. References were automatically indexed. Queries could filter, load, and count through relationships. And the N+1 problem was solved by design -- not with a clever optimization, but with an API that makes implicit queries impossible.

This is Part 7 of the "How We Built FlinDB" series, documenting how we built a complete embedded database engine for the FLIN programming language.

Series Navigation: - [060] Aggregations and Analytics - [061] Index Utilization: Making Queries Fast - [062] Relationships and Eager/Lazy Loading (you are here) - [063] Transactions and Continuous Backup - [064] Graph Queries and Semantic Search