Advanced Patterns - 🌲 Sonamu

UpsertBuilder’s advanced features allow efficient handling of complex data structures.

Advanced Patterns Overview

Conditional Registration

Dynamic data creation if/else branching

Nested Relations

3+ level relations Deep hierarchies

Upsert Behavior

Update on duplicate onConflict usage

Composite Keys

Multiple field combinations UNIQUE constraints

Conditional Data Creation

Create Data Based on Role

async function createUserWithRole(data: {
  email: string;
  username: string;
  role: "admin" | "normal";
}) {
  return await db.transaction(async (trx) => {
    // 1. Register User
    const userRef = trx.ubRegister("users", {
      email: data.email,
      username: data.username,
      password: "password",
      role: data.role,
    });

    // 2. Conditional Profile creation
    if (data.role === "admin") {
      // Create AdminProfile only for Admin
      trx.ubRegister("admin_profiles", {
        user_id: userRef,
        permissions: "full_access",
      });
    } else {
      // Create regular Profile for Normal
      trx.ubRegister("profiles", {
        user_id: userRef,
        bio: "",
      });
    }

    // 3. Save
    const [userId] = await trx.ubUpsert("users");

    if (data.role === "admin") {
      await trx.ubUpsert("admin_profiles");
    } else {
      await trx.ubUpsert("profiles");
    }

    return userId;
  });
}

Optional Relations

async function createEmployee(data: {
  email: string;
  username: string;
  departmentId?: number; // Optional
  mentorEmail?: string; // Optional
}) {
  return await db.transaction(async (trx) => {
    // User
    const userRef = trx.ubRegister("users", {
      email: data.email,
      username: data.username,
      password: "pass",
      role: "normal",
    });

    // Find Mentor (if provided)
    let mentorId: number | null = null;
    if (data.mentorEmail) {
      const mentor = await trx
        .table("employees")
        .join("users", "employees.user_id", "users.id")
        .select({ emp_id: "employees.id" })
        .where("users.email", data.mentorEmail)
        .first();

      mentorId = mentor?.emp_id || null;
    }

    // Employee
    trx.ubRegister("employees", {
      user_id: userRef,
      department_id: data.departmentId || null,
      mentor_id: mentorId,
      employee_number: `E${Date.now()}`,
    });

    await trx.ubUpsert("users");
    const [empId] = await trx.ubUpsert("employees");

    return empId;
  });
}

Deep Hierarchical Structures

4-Level Relationship (Company → Dept → Team → Employee)

await db.transaction(async (trx) => {
  // 1. Company
  const companyRef = trx.ubRegister("companies", {
    name: "Tech Corp",
  });

  // 2. Department (under Company)
  const deptRef = trx.ubRegister("departments", {
    name: "Engineering",
    company_id: companyRef,
  });

  // 3. Team (under Department)
  const teamRef = trx.ubRegister("teams", {
    name: "Backend Team",
    department_id: deptRef,
  });

  // 4. User
  const userRef = trx.ubRegister("users", {
    email: "dev@test.com",
    username: "developer",
    password: "pass",
    role: "normal",
  });

  // 5. Employee (under Team)
  trx.ubRegister("employees", {
    user_id: userRef,
    team_id: teamRef,
    employee_number: "E001",
  });

  // 6. Save in order
  await trx.ubUpsert("companies");
  await trx.ubUpsert("departments");
  await trx.ubUpsert("teams");
  await trx.ubUpsert("users");
  await trx.ubUpsert("employees");
});

Upsert Behavior (ON CONFLICT)

Update on Duplicate

await db.transaction(async (trx) => {
  // If email already exists, update username
  trx.ubRegister(
    "users",
    {
      email: "john@test.com",
      username: "john_updated",
      password: "new_pass",
      role: "normal",
    },
    {
      onConflict: {
        columns: ["email"],
        update: ["username", "password"],
      },
    },
  );

  await trx.ubUpsert("users");
});

Composite UNIQUE Key

await db.transaction(async (trx) => {
  // If (project_id, employee_id) combination exists, update role
  trx.ubRegister(
    "projects__employees",
    {
      project_id: 1,
      employee_id: 5,
      role: "lead",
    },
    {
      onConflict: {
        columns: ["project_id", "employee_id"],
        update: ["role"],
      },
    },
  );

  await trx.ubUpsert("projects__employees");
});

DO NOTHING

await db.transaction(async (trx) => {
  // Ignore if duplicate (no error)
  trx.ubRegister(
    "users",
    {
      email: "john@test.com",
      username: "john",
      password: "pass",
      role: "normal",
    },
    {
      onConflict: {
        columns: ["email"],
        action: "nothing",
      },
    },
  );

  await trx.ubUpsert("users");
});

Complex ManyToMany

Junction Table with Additional Fields

await db.transaction(async (trx) => {
  const projectRef = trx.ubRegister("projects", {
    name: "Project Alpha",
    status: "in_progress",
  });

  const empRefs = ["John", "Jane"].map((name) => {
    const userRef = trx.ubRegister("users", {
      email: `${name.toLowerCase()}@test.com`,
      username: name,
      password: "pass",
      role: "normal",
    });

    return trx.ubRegister("employees", {
      user_id: userRef,
      employee_number: `E${name}`,
    });
  });

  // Junction table with role, assigned_at fields
  empRefs.forEach((empRef, i) => {
    trx.ubRegister("projects__employees", {
      project_id: projectRef,
      employee_id: empRef,
      role: i === 0 ? "lead" : "member",
      assigned_at: new Date(),
    });
  });

  await trx.ubUpsert("projects");
  await trx.ubUpsert("users");
  await trx.ubUpsert("employees");
  await trx.ubUpsert("projects__employees");
});

Dynamic Table Selection

Using Table Name as Variable

async function createRecordInTable(tableName: string, data: Record<string, any>) {
  return await db.transaction(async (trx) => {
    trx.ubRegister(tableName as any, data);
    const [id] = await trx.ubUpsert(tableName as any);
    return id;
  });
}

// Usage
await createRecordInTable("users", {
  email: "test@test.com",
  username: "test",
  password: "pass",
  role: "normal",
});

Preventing Circular References

Circular References Not Possible

// ❌ Error: Circular reference
await db.transaction(async (trx) => {
  const dept1Ref = trx.ubRegister("departments", {
    name: "Dept A",
    parent_id: dept2Ref, // ← References dept2Ref first
  });

  const dept2Ref = trx.ubRegister("departments", {
    name: "Dept B",
    parent_id: dept1Ref, // ← References dept1Ref first
  });

  // Cannot save (circular reference)
});

Solution: Two-Step Save

// ✅ Solution: Sequential save
await db.transaction(async (trx) => {
  // 1. Create first without parent_id
  trx.ubRegister("departments", {
    name: "Dept A",
    parent_id: null,
  });

  const [deptAId] = await trx.ubUpsert("departments");

  // 2. Set relationship with created ID
  await trx.table("departments").insert({
    name: "Dept B",
    parent_id: deptAId,
  });
});

Data Transformation and Validation

Transform Input Data

async function importUsers(csvData: any[]) {
  return await db.transaction(async (trx) => {
    for (const row of csvData) {
      // Data transformation
      const userData = {
        email: row.email.toLowerCase().trim(),
        username: row.username.trim(),
        password: hashPassword(row.password),
        role: row.role === "1" ? "admin" : "normal",
        is_verified: row.verified === "true",
      };

      // Validation
      if (!isValidEmail(userData.email)) {
        console.warn(`Invalid email: ${userData.email}`);
        continue;
      }

      trx.ubRegister("users", userData);
    }

    const userIds = await trx.ubUpsert("users");
    return { imported: userIds.length };
  });
}

Bulk Relationship Updates

Remove Existing + Add New Relationships

async function updateProjectMembers(projectId: number, newMemberIds: number[]) {
  return await db.transaction(async (trx) => {
    // 1. Remove all existing relationships
    await trx.table("projects__employees").where("project_id", projectId).delete();

    // 2. Register new relationships
    for (const empId of newMemberIds) {
      trx.ubRegister("projects__employees", {
        project_id: projectId,
        employee_id: empId,
        assigned_at: new Date(),
      });
    }

    // 3. Save
    await trx.ubUpsert("projects__employees");

    return { memberCount: newMemberIds.length };
  });
}

Practical Examples

Complex Data Migration

async function migrateFromLegacySystem(legacyData: {
  companies: Array<{ id: number; name: string }>;
  users: Array<{
    id: number;
    email: string;
    companyId: number;
  }>;
}) {
  return await db.transaction(async (trx) => {
    // 1. Legacy Company ID → UBRef mapping
    const companyRefMap = new Map<number, any>();

    for (const company of legacyData.companies) {
      const ref = trx.ubRegister("companies", {
        name: company.name,
        legacy_id: company.id,
      });
      companyRefMap.set(company.id, ref);
    }

    // 2. Migrate Users
    for (const user of legacyData.users) {
      const companyRef = companyRefMap.get(user.companyId);

      trx.ubRegister("users", {
        email: user.email,
        username: user.email.split("@")[0],
        password: "temp_password",
        role: "normal",
        legacy_id: user.id,
      });
    }

    // 3. Save
    const companyIds = await trx.ubUpsert("companies");
    const userIds = await trx.ubUpsert("users");

    return {
      companies: companyIds.length,
      users: userIds.length,
    };
  });
}

Department Reorganization

async function reorganizeDepartments(
  changes: Array<{
    departmentId: number;
    newParentId: number | null;
    newName?: string;
  }>,
) {
  return await db.transaction(async (trx) => {
    for (const change of changes) {
      await trx
        .table("departments")
        .where("id", change.departmentId)
        .update({
          parent_id: change.newParentId,
          ...(change.newName && { name: change.newName }),
        });
    }

    return { updated: changes.length };
  });
}

Complex Initial Data Creation

async function createInitialData() {
  await db.transaction(async (trx) => {
    // 1. System User
    const systemUserRef = trx.ubRegister("users", {
      email: "system@internal",
      username: "system",
      password: "n/a",
      role: "admin",
    });

    // 2. Default Company
    const companyRef = trx.ubRegister("companies", {
      name: "Default Company",
    });

    // 3. Departments
    const depts = ["Engineering", "Sales", "HR"];
    const deptRefs = depts.map((name) =>
      trx.ubRegister("departments", {
        name,
        company_id: companyRef,
      }),
    );

    // 4. Default Projects
    const projects = [
      { name: "Onboarding", status: "active" },
      { name: "Training", status: "active" },
    ];

    for (const project of projects) {
      trx.ubRegister("projects", project);
    }

    // 5. Save
    await trx.ubUpsert("users");
    await trx.ubUpsert("companies");
    await trx.ubUpsert("departments");
    await trx.ubUpsert("projects");

    console.log("✅ Initial data created");
  });
}

Error Handling Patterns

Retry After Rollback

async function createWithRetry<T>(
  fn: () => Promise<T>,
  maxRetries = 3
): Promise<T> {
  for (let i = 0; i < maxRetries; i++) {
    try {
      return await fn();
    } catch (error) {
      if (i === maxRetries - 1) throw error;

      console.warn(`Retry ${i + 1}/${maxRetries}:`, error.message);
      await new Promise((resolve) => setTimeout(resolve, 1000));
    }
  }
  throw new Error("Max retries exceeded");
}

// Usage
await createWithRetry(async () => {
  return await db.transaction(async (trx) => {
    trx.ubRegister("users", { ... });
    await trx.ubUpsert("users");
  });
});

Partial Success Handling

async function importWithPartialSuccess(data: any[]) {
  const results = {
    success: [] as number[],
    failed: [] as { data: any; error: string }[],
  };

  for (const item of data) {
    try {
      const id = await db.transaction(async (trx) => {
        trx.ubRegister("users", item);
        const [userId] = await trx.ubUpsert("users");
        return userId;
      });
      results.success.push(id);
    } catch (error) {
      results.failed.push({
        data: item,
        error: error.message,
      });
    }
  }

  return results;
}

Next Steps

Basic Usage

UpsertBuilder basics

Relational Data

Handle relationships with UBRef

Batch Operations

Bulk data registration

Transactions

Transaction detailed guide

​Advanced Patterns Overview

Conditional Registration

Nested Relations

Upsert Behavior

Composite Keys

​Conditional Data Creation

​Create Data Based on Role

​Optional Relations

​Deep Hierarchical Structures

​4-Level Relationship (Company → Dept → Team → Employee)

​Upsert Behavior (ON CONFLICT)

​Update on Duplicate

​Composite UNIQUE Key

​DO NOTHING

​Complex ManyToMany

​Junction Table with Additional Fields

​Dynamic Table Selection

​Using Table Name as Variable

​Preventing Circular References

​Circular References Not Possible

​Solution: Two-Step Save

​Data Transformation and Validation

​Transform Input Data

​Bulk Relationship Updates

​Remove Existing + Add New Relationships

​Practical Examples

​Complex Data Migration

​Department Reorganization

​Complex Initial Data Creation

​Error Handling Patterns

​Retry After Rollback

​Partial Success Handling

​Next Steps