Skip to main content
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

​
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: The return values of similarity functions (SqlExpression<"number">) 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:
FunctionPostgreSQL functionPurpose
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
These functions return SqlExpression<"number"> and can be used within select. The pg_trgm extension must be installed.

​
Practical Examples

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
// }

​
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