Skip to main content
Puri is a type-safe query builder that lets you write SQL queries safely in TypeScript. This document explains the basic usage of SELECT, INSERT, UPDATE, and DELETE.

​
Getting Started with Queries

SELECT

Query dataselect, selectAll, first

INSERT

Add datainsert, upsert, returning

UPDATE

Modify dataupdate, increment, decrement

DELETE

Delete datadelete, truncate

​
SELECT - Querying Data

​
Basic SELECT

const users = await db.table("users").select({
  id: "id",
  name: "username",
  email: "email",
});
// Result: { id: number; name: string; email: string; }[]
The select method uses an object form to select columns. Keys are result field names, values are actual table column names.

​
Column Aliases

const posts = await db.table("posts").select({
  postId: "id",              // Using alias
  postTitle: "title",
  authorName: "author_name", // snake_case → camelCase
  createdDate: "created_at",
});

// Result type is automatically inferred
const firstPost = posts[0];
console.log(firstPost.postId);      // number
console.log(firstPost.postTitle);   // string

​
appendSelect - Add Columns

You can add more columns to an already selected set. 
const query = db.table("users").select({
  id: "id",
  name: "username",
});

const users = await query.appendSelect({
  email: "email",
  role: "role",
});

// Result: { id, name, email, role }

​
WHERE - Filtering Conditions

​
Basic WHERE

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

​
Multiple Conditions (AND)

const users = await db
  .table("users")
  .select({ id: "id", name: "username" })
  .where("role", "admin")
  .where("is_active", true)
  .where("age", ">=", 18);

// SQL: WHERE role = 'admin' AND is_active = true AND age >= 18

​
OR Conditions

const users = await db
  .table("users")
  .select({ id: "id", name: "username" })
  .where("role", "admin")
  .orWhere("role", "moderator");

// SQL: WHERE role = 'admin' OR role = 'moderator'
orWhere is a simple OR condition. For complex condition groups, use whereGroup. See Advanced Patterns for details.

​
IN / NOT IN

const users = await db
  .table("users")
  .select({ id: "id" })
  .whereIn("role", ["admin", "moderator"]);

const users = await db
  .table("users")
  .select({ id: "id", name: "username" })
  .where("username", "like", "%john%");

// SQL: WHERE username LIKE '%john%'

​
INSERT - Adding Data

​
Insert Single Record

const result = await db.table("users").insert({
  username: "john",
  email: "john@example.com",
  password: "hashed_password",
  role: "normal",
});

// result: number (number of inserted records)

​
Get Inserted Data with RETURNING

const inserted = await db
  .table("users")
  .insert({
    username: "john",
    email: "john@example.com",
    password: "hashed_password",
    role: "normal",
  })
  .returning({ id: "id", name: "username" });

console.log(inserted);
// [{ id: 1, name: "john" }]

​
Insert Multiple Records

const result = await db.table("users").insert([
  {
    username: "john",
    email: "john@example.com",
    password: "hash1",
    role: "normal",
  },
  {
    username: "jane",
    email: "jane@example.com",
    password: "hash2",
    role: "normal",
  },
]);

​
UPDATE - Modifying Data

​
Basic UPDATE

const count = await db
  .table("users")
  .where("id", 1)
  .update({
    username: "updated_name",
    updated_at: new Date(),
  });

console.log(`${count} rows updated`);
WHERE clause required: UPDATE must always be used with a WHERE condition. Modifying all data without conditions may cause errors.

​
increment / decrement

You can increase/decrease numeric columns. 
await db
  .table("posts")
  .where("id", 1)
  .increment("view_count", 1);

// SQL: UPDATE posts SET view_count = view_count + 1 WHERE id = 1

​
Update Multiple Columns

await db
  .table("users")
  .where("id", 1)
  .update({
    username: "new_name",
    email: "new@example.com",
    updated_at: new Date(),
  });

​
DELETE - Deleting Data

​
Basic DELETE

const count = await db
  .table("users")
  .where("id", 1)
  .delete();

console.log(`${count} rows deleted`);
WHERE clause required: DELETE must also be used with a WHERE condition.

​
Delete Multiple Records

const count = await db
  .table("users")
  .whereIn("status", ["deleted", "banned"])
  .delete();

​
LIMIT & OFFSET - Pagination

​
LIMIT - Restrict Result Count

const users = await db
  .table("users")
  .select({ id: "id", name: "username" })
  .limit(10);

// Query at most 10 records

​
OFFSET - Skip Records

const users = await db
  .table("users")
  .select({ id: "id", name: "username" })
  .limit(10)
  .offset(20);

// Skip 20 and get next 10 (records 21-30)

​
Pagination Example

function getUsers(page: number, pageSize: number) {
  return db
    .table("users")
    .select({ id: "id", name: "username" })
    .limit(pageSize)
    .offset((page - 1) * pageSize);
}

// Page 1 (1-10)
await getUsers(1, 10);

// Page 2 (11-20)
await getUsers(2, 10);

​
ORDER BY - Sorting

​
Basic Sorting

const users = await db
  .table("users")
  .select({ id: "id", name: "username" })
  .orderBy("created_at", "asc");

​
Multiple Column Sorting

const users = await db
  .table("users")
  .select({ id: "id", name: "username", age: "age" })
  .orderBy("age", "desc")      // Primary: age descending
  .orderBy("created_at", "asc"); // Secondary: creation date ascending

​
first() - Query Single Result

first() returns only the first result. 
const user = await db
  .table("users")
  .select({ id: "id", name: "username" })
  .where("email", "john@example.com")
  .first();

if (user) {
  console.log(user.name); // string
} else {
  console.log("User not found");
}

// Type: { id: number; name: string; } | undefined
first() returns undefined if no result. Always check for existence.

​
pluck() - Extract Single Column

Get only values from a specific column as an array. 
const userIds = await db
  .table("users")
  .where("role", "admin")
  .pluck("id");

// [1, 2, 3, 4, 5]
// Type: number[]

​
count() - Count Records

Quickly query record count. 
const count = await db
  .table("users")
  .where("role", "admin")
  .count();

console.log(`Total admins: ${count}`);
// Type: number
count() is a simple count method, not an aggregate function. For complex aggregations, see Aggregations.

​
Practical Examples

​
User List Query API

async findUsers(params: {
  role?: string;
  search?: string;
  page: number;
  pageSize: number;
}) {
  const { role, search, page, pageSize } = params;
  
  let query = this.getPuri("r")
    .table("users")
    .select({
      id: "id",
      username: "username",
      email: "email",
      role: "role",
      createdAt: "created_at",
    });
  
  // Add conditions
  if (role) {
    query = query.where("role", role);
  }
  
  if (search) {
    query = query.where("username", "like", `%${search}%`);
  }
  
  // Pagination
  const users = await query
    .orderBy("created_at", "desc")
    .limit(pageSize)
    .offset((page - 1) * pageSize);
  
  // Total count
  const total = await this.getPuri("r")
    .table("users")
    .where("role", role)
    .count();
  
  return { users, total };
}

​
Create Post API

async createPost(data: {
  title: string;
  content: string;
  userId: number;
}) {
  const inserted = await this.getPuri("w")
    .table("posts")
    .insert({
      title: data.title,
      content: data.content,
      user_id: data.userId,
      status: "draft",
      created_at: new Date(),
    })
    .returning({
      id: "id",
      title: "title",
      createdAt: "created_at",
    });
  
  return inserted[0];
}

​
Increment View Count

async incrementViewCount(postId: number) {
  await this.getPuri("w")
    .table("posts")
    .where("id", postId)
    .increment("view_count", 1);
}

​
Query Debugging

​
debug() - Output SQL

const users = await db
  .table("users")
  .select({ id: "id" })
  .where("role", "admin")
  .debug(); // Output SQL to console

// Output:
// SELECT "users"."id" AS `id` FROM "users" WHERE "role" = 'admin'

​
rawQuery() - Get Knex QueryBuilder

Access the internal Knex query builder. 
const knexQuery = db
  .table("users")
  .select({ id: "id" })
  .rawQuery();

console.log(knexQuery.toQuery());

​
Next Steps

Joins

Joining tables

Aggregations

Using aggregate functions

Type Safety

Understanding type safety

Advanced Patterns

Learning advanced patterns