select - 🌲 Sonamu

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",
  display_label: Puri.concatExpressions("Sonamu", " ", "User"),
  email_upper: Puri.upper("users.email"),
  email_lower: Puri.lower("users.email"),
});

Puri.concatExpressions() adds PostgreSQL text casts to bound literal parameters, so literal-only expressions like the example above are runnable. Legacy Puri.concat() binds literals as untyped parameters and does not treat column-like strings as columns. A standalone literal-only Puri.concat() expression is therefore not runnable on PostgreSQL. Use Puri.concatExpressions() to combine string SQL expressions with literal text.

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("EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM users.birth_date)"),

  // Return boolean
  is_adult: Puri.rawBoolean("EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM 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

pg_trgm Similarity Functions

Calculates similarity scores provided by PostgreSQL’s pg_trgm extension in SELECT. After filtering candidates with whereFuzzy, these functions can be used to compute per-column weighted scores.

const results = await puri
  .table("items")
  .whereFuzzy("items.search_text", query)
  .select({
    id: "items.id",
    title_ko: "items.title_ko",
    title_en: "items.title_en",

    // word similarity — how similar the query is to a substring of the column (0~1)
    title_score: Puri.wordSimilarity("items.title_ko", query),

    // similarity — overall similarity between two strings (0~1)
    full_score: Puri.similarity("items.title_ko", query),

    // strict word similarity — stricter substring matching than word similarity (0~1)
    strict_score: Puri.strictWordSimilarity("items.title_ko", query),
  })
  .orderBy("title_score", "desc");

// Types are automatically inferred
results[0].title_score; // number
results[0].full_score; // number
results[0].strict_score; // number

Weighted score example: For a plain column or non-null string expression, similarity functions return SqlExpression<"number">. These expressions can be combined with rawNumber to compute per-column weighted scores.

const results = await puri
  .table("items")
  .whereFuzzy("items.search_text", query)
  .select({
    id: "items.id",
    title_ko: "items.title_ko",

    // Individual similarity scores (for weighted scoring)
    ko_score: Puri.wordSimilarity("items.title_ko", query),
    en_score: Puri.wordSimilarity("items.title_en", query),
  })
  .orderBy("ko_score", "desc");

Function comparison:

Function	PostgreSQL function	Purpose
`Puri.wordSimilarity(column, query)`	`word_similarity(query, column)`	Substring similarity between query and column
`Puri.similarity(column, query)`	`similarity(column, query)`	Overall string similarity
`Puri.strictWordSimilarity(column, query)`	`strict_word_similarity(query, column)`	Strict substring similarity

The result preserves the input expression’s nullability. jsonText() produces a nullable string expression; passing it to a similarity helper produces SqlExpression<"number", true> and selects as number | null; a plain column or non-null expression produces SqlExpression<"number"> and selects as number. The pg_trgm extension must be installed.

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 from typed string expressions
    full_info: Puri.concatExpressions(
      Puri.upper("users.name"),
      " (",
      Puri.lower("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("EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM 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

​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

​pg_trgm Similarity 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

pg_trgm Similarity Functions

Practical Examples

Type Safety

Cautions

1. select vs appendSelect

2. Table Prefix Required

3. Specify Raw SQL Types

Next Steps