select

select is a method for specifying which fields to query. You can select fields in a type-safe manner and use SQL aggregate functions and expressions.

Basic Usage

Query All Fields

const users = await puri.table("users").selectAll();

// SELECT * FROM users

Select Specific Fields

const users = await puri.table("users").select({
  id: "users.id",
  email: "users.email",
  name: "users.name"
});

// SELECT users.id, users.email, users.name FROM users

Field Alias

const users = await puri.table("users").select({
  userId: "users.id",
  userName: "users.name"
});

// SELECT users.id as userId, users.name as userName FROM users

SQL Aggregate Functions

Puri provides type-safe SQL functions.

COUNT

const result = await puri.table("users")
  .select({
    total: Puri.count()  // COUNT(*)
  })
  .first();

console.log(result.total);  // number

// Count specific column
const result = await puri.table("posts")
  .select({
    published: Puri.count("posts.published")
  })
  .first();

SUM, AVG, MAX, MIN

const stats = await puri.table("orders")
  .select({
    total: Puri.sum("orders.amount"),
    average: Puri.avg("orders.amount"),
    max: Puri.max("orders.amount"),
    min: Puri.min("orders.amount")
  })
  .first();

console.log(stats.total);    // number
console.log(stats.average);  // number

String Functions

const users = await puri.table("users").select({
  id: "users.id",
  full_name: Puri.concat("users.first_name", "' '", "users.last_name"),
  email_upper: Puri.upper("users.email"),
  email_lower: Puri.lower("users.email")
});

Raw SQL Expressions

You can use complex SQL expressions.

Type-Specific Raw Functions

const result = await puri.table("users").select({
  id: "users.id",

  // Return string
  display_name: Puri.rawString("CONCAT(users.first_name, ' ', users.last_name)"),

  // Return number
  age: Puri.rawNumber("YEAR(CURRENT_DATE) - YEAR(users.birth_date)"),

  // Return boolean
  is_adult: Puri.rawBoolean("YEAR(CURRENT_DATE) - YEAR(users.birth_date) >= 18"),

  // Return date
  signup_date: Puri.rawDate("DATE(users.created_at)"),

  // Return string array
  tags: Puri.rawStringArray("ARRAY_AGG(tags.name)")
});

// Types are automatically inferred
result[0].display_name;  // string
result[0].age;           // number
result[0].is_adult;      // boolean
result[0].signup_date;   // Date
result[0].tags;          // string[]

Nested Object Selection

You can write SELECT statements with nested object structures.

const users = await puri.table("users")
  .leftJoin("posts", "users.id", "posts.user_id")
  .select({
    id: "users.id",
    email: "users.email",
    post: {
      id: "posts.id",
      title: "posts.title"
    }
  });

// Automatically flattened:
// SELECT
//   users.id,
//   users.email,
//   posts.id as post__id,
//   posts.title as post__title
// FROM users LEFT JOIN posts ...

// Results are hydrated and returned as nested objects
console.log(users[0].post.title);

appendSelect

Adds fields to existing SELECT.

let query = puri.table("users").select({
  id: "users.id",
  email: "users.email"
});

// Add fields
query = query.appendSelect({
  name: "users.name",
  created_at: "users.created_at"
});

// SELECT users.id, users.email, users.name, users.created_at FROM users

select() overwrites existing SELECT, while appendSelect() appends to it.

Full-Text Search Functions

ts_highlight (PostgreSQL)

Highlighting using PostgreSQL’s tsvector.

const posts = await puri.table("posts")
  .whereTsSearch("posts.content_tsv", "typescript")
  .select({
    id: "posts.id",
    title: Puri.tsHighlight("posts.title", "typescript"),
    content: Puri.tsHighlight("posts.content", "typescript", {
      startSel: "<mark>",
      stopSel: "</mark>",
      maxFragments: 3,
      maxWords: 50
    })
  });

Options:

parser: Query parser (default: "websearch_to_tsquery")
config: Text search configuration (default: "simple")
startSel: Start tag (default: "<b>")
stopSel: End tag (default: "</b>")
maxWords: Maximum number of words
minWords: Minimum number of words
maxFragments: Maximum number of fragments

ts_rank (PostgreSQL)

Calculates search relevance score.

const posts = await puri.table("posts")
  .whereTsSearch("posts.content_tsv", "typescript")
  .select({
    id: "posts.id",
    title: "posts.title",

    // Basic rank
    rank: Puri.tsRank("posts.content_tsv", "typescript"),

    // With weights
    weighted_rank: Puri.tsRank("posts.content_tsv", "typescript", {
      weights: [0.1, 0.2, 0.4, 1.0],  // D, C, B, A weights
      normalization: 1  // Length normalization
    }),

    // Cover Density rank
    rank_cd: Puri.tsRankCd("posts.content_tsv", "typescript")
  })
  .orderBy("rank", "desc");

Options:

parser: Query parser (default: "websearch_to_tsquery")
config: Text search configuration (default: "simple")
weights: [D, C, B, A] weight array
normalization: Normalization option (0-32)

PGroonga Functions

Full-text search using PGroonga extension.

const posts = await puri.table("posts")
  .whereSearch("posts.title", "타입스크립트")
  .select({
    id: "posts.id",

    // Single column highlighting
    title: Puri.highlight("posts.title", "타입스크립트"),

    // Multiple search terms
    content: Puri.highlight("posts.content", ["타입스크립트", "자바스크립트"]),

    // Multiple column highlighting (returns array)
    highlights: Puri.highlight(
      ["posts.title", "posts.content"],
      ["타입스크립트", "Node.js"]
    ),

    // Search score
    score: Puri.score()
  })
  .orderBy("score", "desc");

// Types are automatically inferred
console.log(posts[0].title);       // string
console.log(posts[0].highlights);  // string[]
console.log(posts[0].score);       // number

Practical Examples

User Statistics
Order Summary
Users + Posts
Search + Highlighting

const stats = await puri.table("users")
  .select({
    total_users: Puri.count(),
    active_users: Puri.rawNumber(
      "SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END)"
    ),
    avg_age: Puri.avg("users.age"),
    oldest: Puri.max("users.age"),
    youngest: Puri.min("users.age")
  })
  .first();

console.log(stats);
// {
//   total_users: 1000,
//   active_users: 850,
//   avg_age: 32.5,
//   oldest: 65,
//   youngest: 18
// }

const orderSummary = await puri.table("orders")
  .where("orders.status", "completed")
  .select({
    order_count: Puri.count(),
    total_amount: Puri.sum("orders.amount"),
    avg_amount: Puri.avg("orders.amount"),
    max_amount: Puri.max("orders.amount"),

    // Date format conversion
    first_order: Puri.rawDate("MIN(orders.created_at)"),
    last_order: Puri.rawDate("MAX(orders.created_at)")
  })
  .first();

const users = await puri.table("users")
  .leftJoin("posts", "users.id", "posts.user_id")
  .select({
    // User information
    id: "users.id",
    name: "users.name",
    email: "users.email",

    // Post information (nested)
    post: {
      id: "posts.id",
      title: "posts.title",
      views: "posts.views"
    },

    // Calculated field
    full_info: Puri.concat(
      "users.name",
      "' ('",
      "users.email",
      "')'"
    )
  });

// Returned as nested object
console.log(users[0].post.title);

const searchResults = await puri.table("posts")
  .whereTsSearch("posts.content_tsv", "typescript programming")
  .select({
    id: "posts.id",

    // Title highlighting
    title: Puri.tsHighlight("posts.title", "typescript programming", {
      startSel: "<mark>",
      stopSel: "</mark>"
    }),

    // Content excerpt + highlighting
    excerpt: Puri.tsHighlight("posts.content", "typescript programming", {
      startSel: "<mark>",
      stopSel: "</mark>",
      maxFragments: 2,
      maxWords: 30
    }),

    // Relevance score
    rank: Puri.tsRank("posts.content_tsv", "typescript programming", {
      weights: [0.1, 0.2, 0.4, 1.0]
    })
  })
  .orderBy("rank", "desc")
  .limit(20);

// Returns highlighted HTML
console.log(searchResults[0].title);
// "Introduction to <mark>TypeScript</mark> <mark>Programming</mark>"

Type Safety

SELECT return types are automatically inferred.

const result = await puri.table("users")
  .select({
    id: "users.id",
    email: "users.email",
    count: Puri.count(),
    upper_name: Puri.upper("users.name")
  })
  .first();

// Types are automatically inferred
result.id;         // number
result.email;      // string
result.count;      // number
result.upper_name; // string

result.unknown;    // ❌ Type error

Cautions

1. select vs appendSelect

// ❌ Bad: Previous select is overwritten
query.select({ id: "users.id" })
     .select({ name: "users.name" });  // id is lost!

// ✅ Good: Add with appendSelect
query.select({ id: "users.id" })
     .appendSelect({ name: "users.name" });

2. Table Prefix Required

// ❌ Wrong
puri.table("users").select({
  id: "id"  // Unclear which table's id
});

// ✅ Correct
puri.table("users").select({
  id: "users.id"  // Explicit table specification
});

3. Specify Raw SQL Types

// Specify return type when using raw SQL
const result = await puri.table("users").select({
  // ✅ Type specified
  age: Puri.rawNumber("YEAR(CURRENT_DATE) - YEAR(birth_date)"),

  // ❌ No type (any)
  unknown: puri.raw("SOME_COMPLEX_SQL")
});

Next Steps

where

Filter with conditions

join

Join tables

order-by

Sort results

advanced-methods

Advanced query methods

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

Basic Usage

Query All Fields

Select Specific Fields

Field Alias

SQL Aggregate Functions

COUNT

SUM, AVG, MAX, MIN

String Functions

Raw SQL Expressions

Type-Specific Raw Functions

Nested Object Selection

appendSelect

Full-Text Search Functions

ts_highlight (PostgreSQL)

ts_rank (PostgreSQL)

PGroonga Functions

Practical Examples

Type Safety

Cautions

1. select vs appendSelect

2. Table Prefix Required

3. Specify Raw SQL Types

Next Steps

where

join

order-by

advanced-methods

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

​Basic Usage

​Query All Fields

​Select Specific Fields

​Field Alias

​SQL Aggregate Functions

​COUNT

​SUM, AVG, MAX, MIN

​String Functions

​Raw SQL Expressions

​Type-Specific Raw Functions

​Nested Object Selection

​appendSelect

​Full-Text Search Functions

​ts_highlight (PostgreSQL)

​ts_rank (PostgreSQL)

​PGroonga Functions

​Practical Examples

​Type Safety

​Cautions

​1. select vs appendSelect

​2. Table Prefix Required

​3. Specify Raw SQL Types

​Next Steps

where

join

order-by

advanced-methods

Basic Usage

Query All Fields

Select Specific Fields

Field Alias

SQL Aggregate Functions

COUNT

SUM, AVG, MAX, MIN

String Functions

Raw SQL Expressions

Type-Specific Raw Functions

Nested Object Selection

appendSelect

Full-Text Search Functions

ts_highlight (PostgreSQL)

ts_rank (PostgreSQL)

PGroonga Functions

Practical Examples

Type Safety

Cautions

1. select vs appendSelect

2. Table Prefix Required

3. Specify Raw SQL Types

Next Steps