Subsets are predefined query templates for retrieving Entity data in various forms.
Subset Overview
Flexible Retrieval
Data formats for each situationA, P, SS, etc.
Type Safety
Compile-time validationAutomatic type inference
Relation Loading
Integrated JOIN and Loader1:N, N:M support
Performance Optimization
Select only needed fieldsSolve N+1 problem
Why Subsets are Needed
Problem: Limitations of Single Queries
Each API requires different data formats:
// ❌ 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
Solution: Unify with Subsets
// ✅ 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 }
Subset Components
1. SubsetQuery - Base Query
Field selection defined in Entity:
{
"subsets": {
"A": ["id", "username", "email", "role"],
"P": ["id", "username", "employee.department.name"],
"SS": ["id", "username"]
}
}
2. LoaderQuery - Relation Loading
Loading 1:N, N:M relation data:
// 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"
}
}
}
Subset Naming Conventions
Common Subset name patterns:
| Subset | Meaning | Usage Example |
|---|
| A | All | Admin page detail |
| P | Profile | User profile view |
| L | List | List API, table rows |
| SS | Summary | Dropdowns, brief info |
| C | Card | Card UI components |
Subset names can be freely defined. Use consistent conventions within your team.
Practical Examples
User List API
// 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,
}));
}
User Profile API
// 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,
};
}
Subset vs Raw Puri
Using Subset (Recommended)
// ✅ Using Subset
const user = await UserModel.findById(1, ["P"]);
// - Type safe
// - Code reuse
// - Easy maintenance
Using Raw Puri
// ⚠️ 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
When to use Raw Puri?
- Complex queries not expressible with Subsets
- Special cases requiring performance optimization
- One-time data migrations
For typical CRUD, always use Subsets. Next Steps