What happens in the database when reading

What this is about

Many frameworks first load a whole object and then cut away what the client does not want. CDMS does it the other way round: the response is evaluated before the database query, and the query reads only the columns you requested.

Three terms that appear on this page:

From the response to the SQL query

On the left is the response, on the right what CDMS makes of it (simplified):

flowchart LR
    subgraph R["response"]
        direction TB
        r1["firstname"]
        r2["lastname"]
        r3["{ field: company, response: [companyname] }"]
    end
    subgraph S["SQL for employee"]
        direction TB
        s1["SELECT e.id, e._createdOn, e._updatedOn, type,<br/>e.firstname, e.lastname,<br/>c.id, type of c"]
        s2["FROM employee e<br/>LEFT JOIN company c ON e.company_id = c.id"]
        s3["WHERE e.id = '7f3…' AND row filters"]
        s1 --> s2 --> s3
    end
    subgraph T["SQL for company"]
        direction TB
        t1["SELECT c.id, c._createdOn, c._updatedOn, type,<br/>c.companyname"]
        t2["FROM company c<br/>WHERE c.id = 'a1…' AND row filters"]
        t1 --> t2
    end
    R ==> S
    S ==>|"company.id"| T

You can see three things here:

1. Only requested columns. From employee, CDMS reads firstname and lastname, plus always id, _createdOn, _updatedOn and the type. The query does not touch other columns.
2. The reference comes via LEFT JOIN, but only with id and type. This way CDMS knows which object company points to without reading its columns.
3. The expanded reference is a read of its own. With the id, CDMS reads the company through its own model. Its permissions, its row filters and its read hooks apply, exactly as with a direct read.

The three steps of a query

Every query, including reading a single object, runs in three steps:

The values come back as a flat list with names like company.id. CDMS turns this back into a nested structure: { "company": { "id": … } }.

When it gets expensive

A single query is fast thanks to the projection. What makes it expensive is the number of queries. Every expanded reference and every expanded list is a query of its own, and that per object that contains it.

Subtypes of abstract models

If a field is not in the parent model but in a subtype, CDMS connects the subtype’s table via the shared id. For an abstract list, CDMS first searches the parent model for IDs and types only, and then reads all matches of each subtype with one query (id IN (…)). See Reading through abstract types.

Decision table

Rules of thumb

Where to go next

• Field selection in detail: Field selection with response
• Lists inside objects: Expanding references and lists
• Filters in nested lists: Filters in nested lists