Cloesce ORM

Alpha Note: The ORM is subject to change as new features are added.

During the hydration step of the Cloesce runtime, all of a Models data is fetched from it’s various defined sources (D1, KV, R2) and combined into a single object instance. This unified object can then be used seamlessly within your application code.

Luckily, Cloesce doesn’t keep this functionality to itself, it is made available through the Orm class in the cloesce/backend package.

Getting and Listing Models

Cloesce provides two basic methods to select a Model from D1, KV and R2:

import { Orm } from "cloesce/backend";
import { User } from "@data"

const orm = Orm.fromEnv(env);
const user = await orm.get(User, {
    id: 1,
    keyParams: {
        myParam: "value"
    },
    includeTree: User.withFriends
});
// => User | undefined

const users = await orm.list(User, User.withFriends);
// => User[]

Note that the get method requires the primary key of the Model to be passed in, along with any key parameters needed to construct KV or R2 keys.

The list method simply takes an optional Include Tree to specify which navigation properties to include. This means that the list method cannot be used with Models that require key parameters for KV or R2 properties (try using prefix queries instead).

Select, Map and Hydrate

Typically, when using a relational database, you require more advanced filtering capabilities. Instead of creating a DSL for querying models (such as LINQ) or advanced libraries like Drizzle, Cloesce takes a stance that when you need to do a SQL query– write it in SQL.

However, the logic of LEFT JOINing related tables based on navigation properties can be tedious and error prone. Additionally, some way to turn the flat result set of a SQL query into JSON objects, and some way to turn those JSON objects into fully fledged Model instances with KV and R2 properties populated is needed.

The select method generates the appropriate SQL query to fetch the desired data from D1, generating joins for navigation properties based on the provided Include Tree. It also aliases the selected columns to match the object graph structure, which is useful for filtering.

Let’s create a simple set of Models to demonstrate this:

@Model()
export class Boss {
    id: Integer;
    persons: Person[];

    static readonly withAll: IncludeTree<Boss> = {
        persons: {
            dogs: {},
            cats: {}
        }
    };
}

@Model()
export class Person {
    id: Integer;
    bossId: Integer;
    dogs: Dog[];
    cats: Cat[];
}

@Model()
export class Dog {
    id: Integer;
    personId: Integer;
    Person: Person | undefined;
}

@Model()
export class Cat {
    id: Integer;
    personId: Integer;
    Person: Person | undefined;
}

Using the select ORM method with the Boss.withAll Include Tree will generate the following SQL:

SELECT 
    "Boss"."id" AS "id",
    "Person_1"."id" AS "persons.id",
    "Person_1"."bossId" AS "persons.bossId",
    "Dog_2"."id" AS "persons.dogs.id",
    "Dog_2"."personId" AS "persons.dogs.personId",
    "Cat_3"."id" AS "persons.cats.id",
    "Cat_3"."personId" AS "persons.cats.personId"
FROM "Boss"
LEFT JOIN "Person" AS "Person_1" 
    ON "Boss"."id" = "Person_1"."bossId"
LEFT JOIN "Dog" AS "Dog_2" 
    ON "Person_1"."id" = "Dog_2"."personId"
LEFT JOIN "Cat" AS "Cat_3" 
    ON "Person_1"."id" = "Cat_3"."personId"

Utilizing the aliased results in a CTE expression allows for easy filtering based on navigation properties:

const selectSql = Orm.select(User, {
    includeTree: Boss.withAll
});

const query = `
    WITH BossCte AS (
        ${selectSql}
    )
    SELECT * FROM BossCte WHERE
        [persons.dogs.id] = 5
    AND
        [persons.cat.id] = 10
    AND
        [persons.id] = 15
`;

This SQL can be executed on a D1 instance, and the results passed to the map method to convert the flat result set into JSON objects:

const results = await d1.prepare(query).all();
const bosses = Orm.map(Boss, results, Boss.withAll);
// => Boss[]

Finally, the hydrate method can be used to take these JSON objects and convert them into fully fledged Model instances, with KV and R2 properties fetched and populated:

const orm = Orm.fromEnv(env);
const hydratedBosses = await orm.hydrate(Boss, {
    base: bosses,
    keyParams: {...},
    includeTree: Boss.withAll
});
// => Boss[]

Note: Orm.map requires the input results to be in the exact aliased format generated by Orm.select. Mixing and matching with other SQL queries may fail.

Saving a Model

Cloesce combines posting and editing a Model into a single method upsert. Upsert is capable of creating or inserting complex object graphs including D1 and KV properties. R2 properties are not supported for upsert since they typically involve large binary data that is better handled separately.

import { Orm } from "cloesce/backend";
import { User } from "@data"
const orm = Orm.fromEnv(env);
const result = await orm.upsert(User, {
    // id: 1, Assume User.id is an integer, we can auto-increment it
    name: "New User",
    friends: [
        {
            // Again assume Friend.id is an integer
            name: "Friend 1"
        },
        {
            id: 1, // Existing Friend
            name: "My Best Friend" // Update existing Friend name
        },
    ]
}, User.withFriends);

Upsert would then return the newly created User instance, complete with assigned primary keys and any navigation properties specified in the Include Tree, along with the newly created Friends.