What are Subsets?

// ❌ Problem 1: List query - too much data
const users = await db.table("users").selectAll();
// → Fetches all columns (including password, bio, unnecessary info)

// ❌ Problem 2: Detail query - complex relation loading
const user = await db.table("users").where("id", 1).first();
const employee = await db.table("employees").where("user_id", user.id).first();
const department = await db.table("departments").where("id", employee.department_id).first();
// → N+1 problem, code duplication

// ❌ Problem 3: Lack of type safety
type UserList = {
  id: number;
  username: string;
  role: string;
}; // Manual type definition required

// ✅ Subset A: Full information
const admin = await UserModel.findById(1, ["A"]);
// Type: { id, created_at, email, username, role, bio, ... }

// ✅ Subset P: Profile + relations
const profile = await UserModel.findById(1, ["P"]);
// Type: { id, username, employee: { salary, department: { name } } }

// ✅ Subset SS: Summary information
const summary = await UserModel.findById(1, ["SS"]);
// Type: { id, username, role, last_login_at }

{
  "subsets": {
    "A": ["id", "username", "email", "role"],
    "P": ["id", "username", "employee.department.name"],
    "SS": ["id", "username"]
  }
}

// Loader definition (auto-generated)
const loaders = {
  employees: {
    as: "employees",
    refId: "department_id",
    qb: (qb, fromIds) =>
      qb
        .table("employees")
        .whereIn("department_id", fromIds)
        .select({ id: "id", name: "username" }),
  },
};

// Flat result (from DB)
{
  id: 1,
  username: "john",
  employee__department__name: "Engineering"
}

// ↓ Hydrate transformation

// Nested object (final result)
{
  id: 1,
  username: "john",
  employee: {
    department: {
      name: "Engineering"
    }
  }
}

// GET /users - User list
async list(params: UserListParams) {
  const users = await UserModel.findMany({
    listParams: params,
    subsetKey: "L",  // Use List Subset
  });

  // Type: { id: number; username: string; role: string; created_at: Date }[]
  return users.map(user => ({
    id: user.id,
    username: user.username,
    role: user.role,
    createdAt: user.created_at,
  }));
}

// GET /users/:id/profile
async getProfile(userId: number) {
  const user = await UserModel.findById(userId, ["P"]);

  if (!user) {
    throw new Error("User not found");
  }

  // Type: {
  //   id: number;
  //   username: string;
  //   employee: {
  //     salary: string;
  //     department: { name: string }
  //   }
  // }

  return {
    id: user.id,
    username: user.username,
    department: user.employee?.department?.name,
    salary: user.employee?.salary,
  };
}

// ✅ Using Subset
const user = await UserModel.findById(1, ["P"]);
// - Type safe
// - Code reuse
// - Easy maintenance

// ⚠️ Raw Puri (special cases only)
const user = await UserModel.getPuri("r")
  .table("users")
  .join("employees", "users.id", "employees.user_id")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .select({
    id: "users.id",
    username: "users.username",
    deptName: "departments.name",
  })
  .where("users.id", 1)
  .first();
// - Complex type inference
// - Code duplication
// - Use only for special queries not expressible with Subsets