Basic Queries - 🌲 Sonamu

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 data select, selectAll, first

INSERT

Add data insert, upsert, returning

UPDATE

Modify data update, increment, decrement

DELETE

Delete data delete

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; }[]

const users = await db.table("users").selectAll();
// Result: User[]

const user = await db.table("users").select({ id: "id", name: "username" }).where("id", 1).first();
// Result: { id: number; name: string; } | undefined

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");

const users = await db.table("users").select({ id: "id", age: "age" }).where("age", ">=", 18);
// Supported: =, !=, <, <=, >, >=

const users = await db.table("users").select({ id: "id" }).where("deleted_at", "!=", null);

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" })
  .whereNotIn("status", ["deleted", "banned"]);

LIKE Search

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", "username"]);

console.log(inserted);
// [{ id: 1, username: "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

await db
  .table("users")
  .where("id", 1)
  .decrement("credit", 100);

// SQL: UPDATE users SET credit = credit - 100 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");

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

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[]

const emails = await db
  .table("users")
  .select({ userId: "id", userEmail: "email" })
  .where("role", "admin")
  .pluck("email");

// ["admin1@test.com", "admin2@test.com"]
// Type: string[]

count - Count Records

To query record count, use the Puri.count() static method inside select.

const result = await db
  .table("users")
  .where("role", "admin")
  .select({ total: Puri.count() })
  .first();

console.log(`Total admins: ${result.total}`);
// Type: number

Puri.count() is a static method used inside the SELECT clause. For more 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)
    .select({ total: Puri.count() })
    .first();

  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", "title", "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

​Getting Started with Queries

SELECT

INSERT

UPDATE

DELETE

​SELECT - Querying Data

​Basic SELECT

​Column Aliases

​appendSelect - Add Columns

​WHERE - Filtering Conditions

​Basic WHERE

​Multiple Conditions (AND)

​OR Conditions

​IN / NOT IN

​LIKE Search

​INSERT - Adding Data

​Insert Single Record

​Get Inserted Data with RETURNING

​Insert Multiple Records

​UPDATE - Modifying Data

​Basic UPDATE

​increment / decrement

​Update Multiple Columns

​DELETE - Deleting Data

​Basic DELETE

​Delete Multiple Records

​LIMIT & OFFSET - Pagination

​LIMIT - Restrict Result Count

​OFFSET - Skip Records

​Pagination Example

​ORDER BY - Sorting

​Basic Sorting

​Multiple Column Sorting

​first() - Query Single Result

​pluck() - Extract Single Column

​count - Count Records

​Practical Examples

​User List Query API

​Create Post API

​Increment View Count

​Query Debugging

​debug() - Output SQL

​rawQuery() - Get Knex QueryBuilder

​Next Steps

Joins

Aggregations

Type Safety

Advanced Patterns

Getting Started with Queries

SELECT - Querying Data

Basic SELECT

Column Aliases

appendSelect - Add Columns

WHERE - Filtering Conditions

Basic WHERE

Multiple Conditions (AND)

OR Conditions

IN / NOT IN

LIKE Search

INSERT - Adding Data

Insert Single Record

Get Inserted Data with RETURNING

Insert Multiple Records

UPDATE - Modifying Data

Basic UPDATE

increment / decrement

Update Multiple Columns

DELETE - Deleting Data

Basic DELETE

Delete Multiple Records

LIMIT & OFFSET - Pagination

LIMIT - Restrict Result Count

OFFSET - Skip Records

Pagination Example

ORDER BY - Sorting

Basic Sorting

Multiple Column Sorting

first() - Query Single Result

pluck() - Extract Single Column

count - Count Records

Practical Examples

User List Query API

Create Post API

Increment View Count

Query Debugging

debug() - Output SQL

rawQuery() - Get Knex QueryBuilder

Next Steps