CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/npm-sql

SQL query builder for Node.js supporting multiple dialects with fluent, chainable API

—

Pending
Overview
Eval results
Files

functions.mddocs/

SQL Functions

This document covers built-in SQL functions for aggregation, string manipulation, date operations, mathematical calculations, and database-specific functions available through the SQL builder.

Functions Module

The functions module provides access to standard SQL functions and allows creation of custom function calls.

// Access standard functions
sql.functions: StandardFunctions;

// Create custom function calls
sql.function(functionName: string): (...args: any[]) => FunctionCall;
sql.functionCallCreator(functionName: string): (...args: any[]) => FunctionCall;

// Function call interface
interface FunctionCall {
  toQuery(): QueryResult;
  toNode(): FunctionCallNode;
  as(alias: string): ColumnNode;
}

// Get functions programmatically
function getFunctions(functionNames: string | string[]): object | function;
function getStandardFunctions(): StandardFunctions;

Standard Functions Interface

interface StandardFunctions {
  // Aggregate functions
  AVG(column: Column): FunctionCall;
  COUNT(column?: Column): FunctionCall;
  DISTINCT(column: Column): FunctionCall;
  MAX(column: Column): FunctionCall;
  MIN(column: Column): FunctionCall;
  SUM(column: Column): FunctionCall;
  
  // String functions
  ABS(value: any): FunctionCall;
  COALESCE(...values: any[]): FunctionCall;
  LEFT(string: any, length: number): FunctionCall;
  LENGTH(string: any): FunctionCall;
  LOWER(string: any): FunctionCall;
  LTRIM(string: any): FunctionCall;
  RANDOM(): FunctionCall;
  RIGHT(string: any, length: number): FunctionCall;
  ROUND(number: any, precision?: number): FunctionCall;
  RTRIM(string: any): FunctionCall;
  SUBSTR(string: any, start: number, length?: number): FunctionCall;
  TRIM(string: any): FunctionCall;
  UPPER(string: any): FunctionCall;
  
  // Date functions
  YEAR(date: any): FunctionCall;
  MONTH(date: any): FunctionCall;
  DAY(date: any): FunctionCall;
  HOUR(date: any): FunctionCall;
  CURRENT_TIMESTAMP(): FunctionCall;
  
  // PostgreSQL-specific functions
  HSTORE(...pairs: any[]): FunctionCall;
  TS_RANK(vector: any, query: any): FunctionCall;
  TS_RANK_CD(vector: any, query: any): FunctionCall;
  PLAINTO_TSQUERY(config: any, text: any): FunctionCall;
  TO_TSQUERY(config: any, text: any): FunctionCall;
  TO_TSVECTOR(config: any, document: any): FunctionCall;
  SETWEIGHT(vector: any, weight: string): FunctionCall;
}

Aggregate Functions

COUNT

COUNT(): FunctionCall;              // COUNT(*)
COUNT(column: Column): FunctionCall; // COUNT(column)

Usage examples:

// Count all rows
const countAllQuery = user
  .select(sql.functions.COUNT().as('total_users'))
  .toQuery();

// Count specific column
const countEmailQuery = user
  .select(sql.functions.COUNT(user.email).as('users_with_email'))
  .toQuery();

// Count with GROUP BY
const countByDeptQuery = user
  .select(
    user.department,
    sql.functions.COUNT(user.id).as('user_count')
  )
  .group(user.department)
  .toQuery();

// Count distinct
const distinctCountQuery = user
  .select(sql.functions.COUNT(sql.functions.DISTINCT(user.department)).as('dept_count'))
  .toQuery();

SUM, AVG, MIN, MAX

SUM(column: Column): FunctionCall;
AVG(column: Column): FunctionCall;
MIN(column: Column): FunctionCall;
MAX(column: Column): FunctionCall;

Usage examples:

// Sales statistics
const salesStatsQuery = order
  .select(
    sql.functions.SUM(order.amount).as('total_sales'),
    sql.functions.AVG(order.amount).as('avg_order_value'),
    sql.functions.MIN(order.amount).as('min_order'),
    sql.functions.MAX(order.amount).as('max_order'),
    sql.functions.COUNT(order.id).as('order_count')
  )
  .toQuery();

// Monthly aggregates
const monthlyStatsQuery = order
  .select(
    sql.functions.MONTH(order.created_at).as('month'),
    sql.functions.SUM(order.amount).as('monthly_total'),
    sql.functions.AVG(order.amount).as('monthly_avg')
  )
  .group(sql.functions.MONTH(order.created_at))
  .toQuery();

String Functions

Case Conversion

UPPER(string: any): FunctionCall;
LOWER(string: any): FunctionCall;

Usage examples:

// Case conversion
const caseQuery = user
  .select(
    user.id,
    sql.functions.UPPER(user.name).as('name_upper'),
    sql.functions.LOWER(user.email).as('email_lower')
  )
  .toQuery();

// Case-insensitive search with UPPER
const searchQuery = user
  .select()
  .where(sql.functions.UPPER(user.name).like(sql.functions.UPPER('%john%')))
  .toQuery();

String Manipulation

LENGTH(string: any): FunctionCall;
SUBSTR(string: any, start: number, length?: number): FunctionCall;
LEFT(string: any, length: number): FunctionCall;
RIGHT(string: any, length: number): FunctionCall;
TRIM(string: any): FunctionCall;
LTRIM(string: any): FunctionCall;
RTRIM(string: any): FunctionCall;

Usage examples:

// String length and substring
const stringOpsQuery = user
  .select(
    user.name,
    sql.functions.LENGTH(user.name).as('name_length'),
    sql.functions.SUBSTR(user.name, 1, 10).as('name_short'),
    sql.functions.LEFT(user.email, 5).as('email_prefix'),
    sql.functions.RIGHT(user.phone, 4).as('phone_last_four')
  )
  .toQuery();

// String trimming
const trimQuery = user
  .select(
    user.id,
    sql.functions.TRIM(user.name).as('name_trimmed'),
    sql.functions.LTRIM(user.description).as('desc_left_trimmed')
  )
  .toQuery();

// Filter by string length
const lengthFilterQuery = user
  .select()
  .where(sql.functions.LENGTH(user.password).gte(8))
  .toQuery();

COALESCE

COALESCE(...values: any[]): FunctionCall;

Usage examples:

// Handle null values
const coalesceQuery = user
  .select(
    user.id,
    sql.functions.COALESCE(user.nick_name, user.first_name, 'Anonymous').as('display_name'),
    sql.functions.COALESCE(user.phone, user.email, 'No contact').as('contact_info')
  )
  .toQuery();

Mathematical Functions

ABS and ROUND

ABS(value: any): FunctionCall;
ROUND(number: any, precision?: number): FunctionCall;
RANDOM(): FunctionCall;

Usage examples:

// Mathematical operations
const mathQuery = transaction
  .select(
    transaction.id,
    transaction.amount,
    sql.functions.ABS(transaction.amount).as('abs_amount'),
    sql.functions.ROUND(transaction.amount, 2).as('rounded_amount')
  )
  .toQuery();

// Random sampling
const randomSampleQuery = user
  .select()
  .order(sql.functions.RANDOM())
  .limit(10)
  .toQuery();

// Financial calculations
const financialQuery = account
  .select(
    account.id,
    sql.functions.ROUND(account.balance * 1.05, 2).as('balance_with_interest'),
    sql.functions.ABS(account.balance - account.credit_limit).as('available_credit')
  )
  .toQuery();

Date Functions

Date Extraction

YEAR(date: any): FunctionCall;
MONTH(date: any): FunctionCall;
DAY(date: any): FunctionCall;
HOUR(date: any): FunctionCall;
CURRENT_TIMESTAMP(): FunctionCall;

Usage examples:

// Date extraction
const dateQuery = order
  .select(
    order.id,
    order.created_at,
    sql.functions.YEAR(order.created_at).as('order_year'),
    sql.functions.MONTH(order.created_at).as('order_month'),
    sql.functions.DAY(order.created_at).as('order_day'),
    sql.functions.HOUR(order.created_at).as('order_hour')
  )
  .toQuery();

// Current timestamp
const timestampQuery = user
  .update({ last_login: sql.functions.CURRENT_TIMESTAMP() })
  .where(user.id.equals(1))
  .toQuery();

// Date grouping and aggregation
const monthlySalesQuery = order
  .select(
    sql.functions.YEAR(order.created_at).as('year'),
    sql.functions.MONTH(order.created_at).as('month'),
    sql.functions.SUM(order.amount).as('monthly_total')
  )
  .group(
    sql.functions.YEAR(order.created_at),
    sql.functions.MONTH(order.created_at)
  )
  .order(
    sql.functions.YEAR(order.created_at).desc,
    sql.functions.MONTH(order.created_at).desc
  )
  .toQuery();

PostgreSQL-Specific Functions

HSTORE Functions

HSTORE(...pairs: any[]): FunctionCall;

Usage examples:

// Create HSTORE from key-value pairs
const hstoreQuery = user
  .update({
    metadata: sql.functions.HSTORE('last_login', new Date(), 'ip_address', '192.168.1.1')
  })
  .where(user.id.equals(1))
  .toQuery();

Text Search Functions

TS_RANK(vector: any, query: any): FunctionCall;
TS_RANK_CD(vector: any, query: any): FunctionCall;
PLAINTO_TSQUERY(config: any, text: any): FunctionCall;
TO_TSQUERY(config: any, text: any): FunctionCall;
TO_TSVECTOR(config: any, document: any): FunctionCall;
SETWEIGHT(vector: any, weight: string): FunctionCall;

Usage examples:

// Full-text search setup
const searchSetupQuery = document
  .update({
    search_vector: sql.functions.TO_TSVECTOR('english', document.title.concat(' ').concat(document.content))
  })
  .toQuery();

// Full-text search query
const searchQuery = document
  .select(
    document.id,
    document.title,
    sql.functions.TS_RANK(
      document.search_vector,
      sql.functions.PLAINTO_TSQUERY('english', 'search terms')
    ).as('rank')
  )
  .where(
    document.search_vector.match(
      sql.functions.PLAINTO_TSQUERY('english', 'search terms')
    )
  )
  .order(
    sql.functions.TS_RANK(
      document.search_vector,
      sql.functions.PLAINTO_TSQUERY('english', 'search terms')
    ).desc
  )
  .toQuery();

// Advanced text search with weights
const weightedSearchQuery = document
  .select(
    document.id,
    document.title,
    sql.functions.TS_RANK_CD(
      sql.functions.SETWEIGHT(sql.functions.TO_TSVECTOR('english', document.title), 'A')
        .concat(sql.functions.SETWEIGHT(sql.functions.TO_TSVECTOR('english', document.content), 'B')),
      sql.functions.TO_TSQUERY('english', 'search & terms')
    ).as('rank')
  )
  .toQuery();

Custom Functions

Creating Custom Function Calls

// Create single function
sql.function(functionName: string): (...args: any[]) => FunctionCall;

// Create function call creator
sql.functionCallCreator(functionName: string): (...args: any[]) => FunctionCall;

// Create multiple functions
getFunctions(functionNames: string[]): { [name: string]: (...args: any[]) => FunctionCall };

Usage examples:

// Custom function call
const customFunction = sql.function('CUSTOM_FUNC');
const customQuery = user
  .select(
    user.id,
    customFunction(user.data, 'parameter').as('custom_result')
  )
  .toQuery();

// Database-specific functions
const postgresArrayAgg = sql.function('ARRAY_AGG');
const arrayAggQuery = user
  .select(
    user.department,
    postgresArrayAgg(user.name).as('user_names')
  )
  .group(user.department)
  .toQuery();

// JSON functions (PostgreSQL)
const jsonFunctions = sql.function('JSON_BUILD_OBJECT');
const jsonQuery = user
  .select(
    user.id,
    jsonFunctions('name', user.name, 'email', user.email, 'age', user.age).as('user_json')
  )
  .toQuery();

// Window functions
const windowFunction = sql.function('ROW_NUMBER');
const windowQuery = user
  .select(
    user.id,
    user.name,
    user.salary,
    windowFunction().over().partitionBy(user.department).orderBy(user.salary.desc).as('salary_rank')
  )
  .toQuery();

Function Composition

// Combine multiple functions
const complexQuery = order
  .select(
    order.id,
    sql.functions.ROUND(
      sql.functions.AVG(order.amount), 
      2
    ).as('avg_amount_rounded'),
    sql.functions.UPPER(
      sql.functions.SUBSTR(order.status, 1, 3)
    ).as('status_code')
  )
  .group(order.customer_id)
  .toQuery();

// Nested function calls
const nestedQuery = user
  .select(
    user.id,
    sql.functions.LENGTH(
      sql.functions.TRIM(
        sql.functions.UPPER(user.name)
      )
    ).as('clean_name_length')
  )
  .toQuery();

Dialect-Specific Function Support

MySQL-Specific Functions

// MySQL functions
const mysqlFunctions = {
  CONCAT: sql.function('CONCAT'),
  DATE_FORMAT: sql.function('DATE_FORMAT'),
  SUBSTRING_INDEX: sql.function('SUBSTRING_INDEX'),
  GROUP_CONCAT: sql.function('GROUP_CONCAT')
};

const mysqlQuery = user
  .select(
    user.id,
    mysqlFunctions.CONCAT(user.first_name, ' ', user.last_name).as('full_name'),
    mysqlFunctions.DATE_FORMAT(user.created_at, '%Y-%m-%d').as('created_date')
  )
  .toQuery();

SQL Server-Specific Functions

// SQL Server functions
const sqlServerFunctions = {
  ISNULL: sql.function('ISNULL'),
  DATEPART: sql.function('DATEPART'),
  CHARINDEX: sql.function('CHARINDEX')
};

const sqlServerQuery = user
  .select(
    user.id,
    sqlServerFunctions.ISNULL(user.middle_name, '').as('middle_name'),
    sqlServerFunctions.DATEPART('year', user.created_at).as('created_year')
  )
  .toQuery();

Oracle-Specific Functions

// Oracle functions
const oracleFunctions = {
  NVL: sql.function('NVL'),
  DECODE: sql.function('DECODE'),
  EXTRACT: sql.function('EXTRACT')
};

const oracleQuery = user
  .select(
    user.id,
    oracleFunctions.NVL(user.nick_name, user.first_name).as('display_name'),
    oracleFunctions.EXTRACT('YEAR FROM', user.created_at).as('created_year')
  )
  .toQuery();

Function Usage Patterns

Conditional Functions

// Use functions in WHERE clauses
const functionalFilterQuery = user
  .select()
  .where(sql.functions.LENGTH(user.password).gte(8))
  .where(sql.functions.UPPER(user.status).equals('ACTIVE'))
  .toQuery();

// Use functions in HAVING clauses
const havingFunctionQuery = order
  .select(
    order.customer_id,
    sql.functions.COUNT(order.id).as('order_count'),
    sql.functions.AVG(order.amount).as('avg_amount')
  )
  .group(order.customer_id)
  .having(sql.functions.COUNT(order.id).gt(5))
  .having(sql.functions.AVG(order.amount).gt(100))
  .toQuery();

Functions in ORDER BY

// Order by function results
const functionOrderQuery = user
  .select()
  .order(
    sql.functions.LENGTH(user.name).desc,
    sql.functions.UPPER(user.last_name).asc
  )
  .toQuery();

Functions with Aggregation

// Complex aggregation with functions
const complexAggQuery = sales
  .select(
    sql.functions.YEAR(sales.date).as('year'),
    sql.functions.MONTH(sales.date).as('month'),
    sql.functions.COUNT(sales.id).as('transaction_count'),
    sql.functions.SUM(sales.amount).as('total_sales'),
    sql.functions.ROUND(sql.functions.AVG(sales.amount), 2).as('avg_sale'),
    sql.functions.MIN(sales.amount).as('min_sale'),
    sql.functions.MAX(sales.amount).as('max_sale')
  )
  .group(
    sql.functions.YEAR(sales.date),
    sql.functions.MONTH(sales.date)
  )
  .order(
    sql.functions.YEAR(sales.date).desc,
    sql.functions.MONTH(sales.date).desc
  )
  .toQuery();

Install with Tessl CLI

npx tessl i tessl/npm-sql

docs

column-operations.md

dialect-support.md

functions.md

index.md

query-building.md

table-operations.md

tile.json