Type Safety - 🌲 Sonamu

Puri maximizes TypeScript’s type system to catch SQL query errors at compile time.

Type Safety Overview

Automatic Type Inference

Query result types auto-inferredCompile-time validation

Column Autocomplete

IDE autocomplete supportPrevent typos

JOIN Type Inference

Auto-expanded types after joinLEFT JOIN nullable

Raw Function Types

Raw SQL is also type-safeExplicit return types

Basic Type Inference

SELECT Type Inference

const users = await db
  .table("users")
  .select({
    id: "id",              // number
    name: "username",      // string
    email: "email",        // string
    isActive: "is_active", // boolean
    createdAt: "created_at", // Date
  });

// Types are automatically inferred
const first = users[0];
first.id;        // number
first.name;      // string
first.email;     // string
first.isActive;  // boolean
first.createdAt; // Date

// ❌ Compile error
first.nonExistent; // Error: Property 'nonExistent' does not exist

WHERE Type Validation

// ✅ Correct types
await db
  .table("users")
  .where("id", 1)              // number
  .where("username", "john")   // string
  .where("is_active", true);   // boolean

// ❌ Compile error
await db
  .table("users")
  .where("id", "not-a-number"); // Error: Type 'string' is not assignable to type 'number'

JOIN Type Extension

INNER JOIN

const results = await db
  .table("employees")
  .join("users", "employees.user_id", "users.id")
  .select({
    employeeId: "employees.id",  // number
    userName: "users.username",   // string
  });

// After join, columns from both tables are available
results[0].employeeId;  // number
results[0].userName;    // string

LEFT JOIN - nullable Types

const results = await db
  .table("employees")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .select({
    employeeId: "employees.id",      // number
    departmentName: "departments.name", // string | null (LEFT JOIN)
  });

// Columns from LEFT JOIN are nullable
const first = results[0];
first.employeeId;      // number
first.departmentName;  // string | null

// Null check required
if (first.departmentName) {
  console.log(first.departmentName.toUpperCase()); // ✅ OK
}

LEFT JOIN type safety:

INNER JOIN: Value always exists → T
LEFT JOIN: Value may not exist → T | null

TypeScript automatically enforces null checks.

Column Autocomplete

IDE Autocomplete

await db
  .table("users")
  .select({
    id: "users.", // ← IDE provides autocomplete
    // users.id
    // users.username
    // users.email
    // ...
  });

Prevent Invalid Columns

// ❌ Compile error
await db
  .table("users")
  .select({
    id: "users.nonexistent", // Error: Column does not exist
  });

Raw Function Type Safety

Explicit Type Specification

const results = await db
  .table("users")
  .select({
    id: "id",
    // Each Raw function specifies return type
    fullName: Puri.rawString("CONCAT(first_name, last_name)"),  // string
    age: Puri.rawNumber("EXTRACT(YEAR FROM AGE(birth_date))"),  // number
    isAdmin: Puri.rawBoolean("role = 'admin'"),                 // boolean
    tags: Puri.rawStringArray("string_to_array(tags, ',')"),    // string[]
  });

// Types are accurately inferred
results[0].fullName;  // string
results[0].age;       // number
results[0].isAdmin;   // boolean
results[0].tags;      // string[]

Aggregate Function Types

const stats = await db
  .table("employees")
  .select({
    departmentId: "department_id",  // number
    count: Puri.count("id"),         // number
    avgSalary: Puri.avg("salary"),   // number
    maxSalary: Puri.max("salary"),   // number
  })
  .groupBy("department_id");

stats[0].count;      // number
stats[0].avgSalary;  // number

first() Types

first() returns a single result or undefined.

const user = await db
  .table("users")
  .select({ id: "id", name: "username" })
  .where("id", 1)
  .first();

// Type: { id: number; name: string; } | undefined

if (user) {
  // ✅ Safe to use after null check
  console.log(user.name.toUpperCase());
} else {
  console.log("User not found");
}

// ❌ Compile error (using without null check)
console.log(user.name); // Error: Object is possibly 'undefined'

pluck() Types

// Array of single column
const ids = await db
  .table("users")
  .where("role", "admin")
  .pluck("id");

// Type: number[]
ids[0]; // number

const names = await db
  .table("users")
  .pluck("username");

// Type: string[]
names[0]; // string

Type-safe Parameters

Params Type Definition

// user.types.ts
export const UserListParams = z.object({
  role: z.enum(["admin", "normal"]).optional(),
  search: z.string().optional(),
  page: z.number().int().min(1),
  pageSize: z.number().int().min(1).max(100),
});
export type UserListParams = z.infer<typeof UserListParams>;

Using in API Methods

async findUsers(params: UserListParams) {
  let query = this.getPuri("r")
    .table("users")
    .select({
      id: "id",
      name: "username",
      role: "role",
    });

  // Type-safe condition additions
  if (params.role) {
    query = query.where("role", params.role);
  }

  if (params.search) {
    query = query.where("username", "like", `%${params.search}%`);
  }

  const users = await query
    .orderBy("created_at", "desc")
    .limit(params.pageSize)
    .offset((params.page - 1) * params.pageSize);

  return users;
}

Subquery Types

// Subquery result types are also safe
const adminUsers = db
  .table("users")
  .where("role", "admin")
  .select({
    id: "id",
    name: "username",
  });

// adminUsers type: Puri<Schema, { users: User }, { id: number; name: string; }>

const results = await db
  .table({ admins: adminUsers })
  .select({
    adminId: "admins.id",      // number
    adminName: "admins.name",  // string
  });

Practical Examples

interface SearchFilters {
  departmentId?: number;
  minSalary?: number;
  maxSalary?: number;
  isActive?: boolean;
  search?: string;
}

async searchEmployees(filters: SearchFilters) {
  let query = this.getPuri("r")
    .table("employees")
    .join("users", "employees.user_id", "users.id")
    .leftJoin("departments", "employees.department_id", "departments.id")
    .select({
      employeeId: "employees.id",
      userName: "users.username",
      salary: "employees.salary",
      departmentName: "departments.name", // string | null
      isActive: "users.is_active",
    });

  // All conditions are type-safe
  if (filters.departmentId !== undefined) {
    query = query.where("employees.department_id", filters.departmentId);
  }

  if (filters.minSalary !== undefined) {
    query = query.where("employees.salary", ">=", filters.minSalary);
  }

  if (filters.maxSalary !== undefined) {
    query = query.where("employees.salary", "<=", filters.maxSalary);
  }

  if (filters.isActive !== undefined) {
    query = query.where("users.is_active", filters.isActive);
  }

  if (filters.search) {
    query = query.where("users.username", "like", `%${filters.search}%`);
  }

  return await query.orderBy("employees.id", "asc");
}

Using Generics

interface PaginationParams {
  page: number;
  pageSize: number;
}

interface PaginatedResult<T> {
  data: T[];
  total: number;
  page: number;
  pageSize: number;
}

async paginate<T>(
  query: Puri<any, any, T>,
  params: PaginationParams
): Promise<PaginatedResult<T>> {
  const { page, pageSize } = params;

  // Total count
  const countQuery = query.clone();
  const total = await countQuery.count();

  // Page data
  const data = await query
    .limit(pageSize)
    .offset((page - 1) * pageSize);

  return {
    data,
    total,
    page,
    pageSize,
  };
}

// Usage
const userQuery = db.table("users").select({ id: "id", name: "username" });
const result = await this.paginate(userQuery, { page: 1, pageSize: 20 });
// result.data type: { id: number; name: string; }[]

Type Guards

Narrowing Result Types

interface AdminUser {
  id: number;
  username: string;
  role: "admin";
  permissions: string[];
}

interface NormalUser {
  id: number;
  username: string;
  role: "normal";
}

type User = AdminUser | NormalUser;

function isAdmin(user: User): user is AdminUser {
  return user.role === "admin";
}

const user = await db
  .table("users")
  .select({
    id: "id",
    username: "username",
    role: "role",
  })
  .where("id", 1)
  .first();

if (user && isAdmin(user)) {
  // TypeScript recognizes user as AdminUser
  console.log(user.permissions); // ✅ OK
}

Next Steps

Advanced Patterns

Learn advanced patterns

Basic Queries

Back to basic queries

Joins

Type safety in joins

Model

Using Puri in Models

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

​Type Safety Overview

Automatic Type Inference

Column Autocomplete

JOIN Type Inference

Raw Function Types

​Basic Type Inference

​SELECT Type Inference

​WHERE Type Validation

​JOIN Type Extension

​INNER JOIN

​LEFT JOIN - nullable Types

​Column Autocomplete

​IDE Autocomplete

​Prevent Invalid Columns

​Raw Function Type Safety

​Explicit Type Specification

​Aggregate Function Types

​first() Types

​pluck() Types

​Type-safe Parameters

​Params Type Definition

​Using in API Methods

​Subquery Types

​Practical Examples

​Type-safe Search Filter

​Using Generics

​Type Guards

​Narrowing Result Types

​Next Steps

Advanced Patterns

Basic Queries

Joins

Model

Type Safety Overview

Basic Type Inference

SELECT Type Inference

WHERE Type Validation

JOIN Type Extension

INNER JOIN

LEFT JOIN - nullable Types

Column Autocomplete

IDE Autocomplete

Prevent Invalid Columns

Raw Function Type Safety

Explicit Type Specification

Aggregate Function Types

first() Types

pluck() Types

Type-safe Parameters

Params Type Definition

Using in API Methods

Subquery Types

Practical Examples

Type-safe Search Filter

Using Generics

Type Guards

Narrowing Result Types

Next Steps