Function and Operator Keywords¶
This page provides a comprehensive mapping between SQL keywords and their corresponding methods in ts-sql-query. It helps identify which SQL functions, operators, and expressions are supported, and how to express them fluently in TypeScript using the library’s API.
| SQL Keyword | ts-sql-query Equivalent | Notes | Link |
|---|---|---|---|
| value + x (Addition) | value .add(x) |
Arithmetic addition | Value expressions API |
| value - x (Subtraction) | value .substract(x) |
Arithmetic subtraction | Value expressions API |
| value * x (Multiplication) | value .multiply(x) |
Arithmetic multiplication | Value expressions API |
| value / x (Division) | value .divide(x) |
Arithmetic division | Value expressions API |
| value % x (Modulo) | value .modulo(x) |
Modulo operation | Value expressions API |
| value || x (String concatenation) | value .concat(x) |
String concatenation | Value expressions API |
| value < x | value .lessThan(...) |
Strictly less than | Value expressions API |
| value > x | value .greaterThan(...) |
Strictly greater than | Value expressions API |
| value <= x | value .lessOrEquals(...) |
Less than or equal | Value expressions API |
| value >= x | value .greaterOrEquals(...) |
Greater than or equal | Value expressions API |
| value BETWEEN a AND b | value .between(a, b) |
Inclusive range check | Value expressions API |
| value NOT BETWEEN a AND b | value .notBetween(a, b) |
Range exclusion | Value expressions API |
| value = x (Equals) | value .equals(value) |
Equality operator | Value expressions API |
| value <> x (Not equals) | value .notEquals(value) |
Inequality operator | Value expressions API |
| value != x (Not equals) | value .notEquals(value) |
Inequality operator | Value expressions API |
| value AND x | value .and(x) |
Logical AND | Value expressions API |
| value OR x | value .or(x) |
Logical OR | Value expressions API |
| value IS x | value .is(x) |
Safe equality for null comparisons | Value expressions API |
| value IS NOT x | value .isNot(value) |
Safe inequality for null comparisons | Value expressions API |
| value IN (...) | value .in([...]) |
Membership in a set | Value expressions API |
| value NOT IN (...) | value .notIn([...]) |
Not in set | Value expressions API |
| value IN (SELECT ...) | value .in(select) |
Membership in subquery result | Value expressions API |
| value NOT IN (SELECT ...) | value .notIn(select) |
Not in subquery result | Value expressions API |
| value IN (...) (variadic) | value .inN(...) |
Alternative to .in([...]) |
Value expressions API |
| value NOT IN (...) (variadic) | value .notInN(...) |
Alternative to .notIn([...]) |
Value expressions API |
| value IS NULL | value .isNull() |
Checks if value is NULL | Value expressions API |
| value IS NOT NULL | value .isNotNull() |
Checks if value is NOT NULL | Value expressions API |
| ABS(value) | value .abs() |
Absolute value | Value expressions API |
| ACOS(value) | value .acos() |
Arc cosine | Value expressions API |
| ASIN(value) | value .asin() |
Arc sine | Value expressions API |
| ATAN(value) | value .atan() |
Arc tangent | Value expressions API |
| ATAN2(y, x) | value .atan2(x) |
Arc tangent of y/x | Value expressions API |
| ATN2(y, x) | value .atan2(x) |
Arc tangent of y/x | Value expressions API |
| AVG(x) | connection .average(x) |
Aggregate: returns average | Connection API |
| AVG(DISTINCT x) | connection .averageDistinct(x) |
Aggregate: returns distinct average | Connection API |
| CASE ... WHEN ... THEN ... ELSE ... END | Not supported yet. Use a custom SQL fragment. | SQL conditional expression using CASE statement | SQL Fragment |
| CBRT(value) | value .cbrt() |
Cube root | Value expressions API |
| CEIL(value) | value .ceil() |
Round up | Value expressions API |
| CEILING(value) | value .ceil() |
Round up | Value expressions API |
| CHAR_LENGTH(value) | value .length() |
String length | Value expressions API |
| COALESCE(value, x) | value .valueWhenNull(x) |
Fallback value if NULL (standard SQL) | Value expressions API |
| CONCAT(value, x) | value .concat(x) |
String concatenation | Value expressions API |
| COS(value) | value .cos() |
Cosine | Value expressions API |
| COT(value) | value .cot() |
Cotangent | Value expressions API |
| COUNT(*) | connection .countAll() |
Aggregate: count all rows | Connection API |
| COUNT(x) | connection .count(x) |
Aggregate: count non-null rows | Connection API |
| COUNT(DISTINCT x) | connection .countDistinct(x) |
Aggregate: count distinct values | Connection API |
| CURRENT_DATE | connection .currentDate() |
Current date | Connection API |
| CURRENT_TIME | connection .currentTime() |
Current time | Connection API |
| CURRENT_TIMESTAMP | connection .currentDateTime() |
Current date and time | Connection API |
| CURRENT_TIMESTAMP | connection .currentTimestamp() |
Same as .currentDateTime() |
Connection API |
| CURRVAL(sequenceName) | connection .sequence(sequenceName).currentValue() |
Retrieves next value from a named sequence | Connection API |
| DEFAULT | connection .default() |
Default column value | Connection API |
| EXP(value) | value .exp() |
Exponential (e^x) | Value expressions API |
| EXTRACT(DAY FROM value) | value .getDate() |
Extract day of month | Value expressions API |
| EXTRACT(DOW FROM value) | value .getDay() |
Extract day of week (Sunday=0 like JavaScript) | Value expressions API |
| EXTRACT(EPOCH FROM value) | value .getTime() |
Extract timestamp in epoch milliseconds | Value expressions API |
| EXTRACT(HOUR FROM value) | value .getHours() |
Extract hour | Value expressions API |
| EXTRACT(MINUTE FROM value) | value .getMinutes() |
Extract minute | Value expressions API |
| EXTRACT(MILLISECOND FROM value) | value .getSeconds() |
Extract milliseconds | Value expressions API |
| EXTRACT(MONTH FROM value) | value .getMonth() |
Extract month (0-based like JavaScript) | Value expressions API |
| EXTRACT(SECOND FROM value) | value .getSeconds() |
Extract second | Value expressions API |
| EXTRACT(YEAR FROM value) | value .getFullYear() |
Extract year | Value expressions API |
| FLOOR(value) | value .floor() |
Round down | Value expressions API |
| GETDATE() | connection .currentDateTime() |
Current date and time | Connection API |
| GETDATE() | connection .currentTimestamp() |
Same as .currentDateTime() |
Connection API |
| GREATEST(value, x) | value .minValue(x) |
Ensures the value is at least x by returning the greater of the two values. | Value expressions API |
| CURRENT_TIMESTAMP | connection .currentTimestamp() |
Same as .currentDateTime() |
Connection API |
| EXISTS(x) | connection .exists(x) |
Returns true if subquery returns any row | Connection API |
| NOT EXISTS(x) | connection .notExists(x) |
Returns true if subquery returns no rows | Connection API |
| FALSE | connection .false() |
SQL constant for false | Connection API |
| GROUP_CONCAT(x, separator) | connection .stringConcat(x, separator) |
Aggregate string with separator | Connection API |
| GROUP_CONCAT(DISTINCT x, separator) | connection .stringConcatDistinct(x, separator) |
Aggregate distinct string concat | Connection API |
| IFNULL(value, x) | value .valueWhenNull(x) |
Fallback value if NULL (MySQL, SQLite variant) | Value expressions API |
| ISNULL(value, x) | value .valueWhenNull(x) |
Fallback value if NULL (standard SQL) | Value expressions API |
| LEAST(value, x) | value .maxValue(x) |
Ensures the value is at most x by returning the smaller of the two values. | Value expressions API |
| LENGTH(value) | value .length() |
String length | Value expressions API |
| LEN(value) | value .length() |
String length | Value expressions API |
| LN(value) | value .ln() |
Natural log | Value expressions API |
| LOG10(value) | value .log10() |
Base-10 log | Value expressions API |
| LOG(base, value) | value .logn(x) |
Log base-n | Value expressions API |
| LOWER(value) | value .toLowerCase() |
Lowercase string | Value expressions API |
| LTRIM(value) | value .trimLeft() |
Remove leading spaces | Value expressions API |
| MAX(x) | connection .max(x) |
Aggregate: returns max value | Connection API |
| MIN(x) | connection .min(x) |
Aggregate: returns min value | Connection API |
| MOD(value, x) | value .modulo(x) |
Modulo operation | Value expressions API |
| NEXTVAL(sequenceName) | connection .sequence(sequenceName).nextValue() |
Retrieves next value from a sequence | Connection API |
| NULLIF(value, x) | value .nullIfValue(x) |
Returns NULL if value equals x | Value expressions API |
| NOT value | value .negate() |
Negation | Value expressions API |
| PI() | connection .pi() |
Math constant π | Connection API |
| POWER(value, x) | value .power(x) |
Exponentiation | Value expressions API |
| RAND() | connection .random() |
Random number generator | Connection API |
| RANDOM() | connection .random() |
Random number generator | Connection API |
| REPLACE(value, x, y) | value .replaceAll(x, y) |
Replace all matching substrings | Value expressions API |
| REVERSE(value) | value .reverse() |
Reverse string | Value expressions API |
| ROUND(value) | value .round() |
Round nearest | Value expressions API |
| ROUND(value, x) | value .roundn(x) |
Round with precision | Value expressions API |
| RTRIM(value) | value .trimRight() |
Remove trailing spaces | Value expressions API |
| SIGN(value) | value .sign() |
Sign of value | Value expressions API |
| SIN(value) | value .sin() |
Sine | Value expressions API |
| SQRT(value) | value .sqrt() |
Square root | Value expressions API |
| STRING_AGG(x, separator) | connection .stringConcat(x, separator) |
Aggregate string with separator | Connection API |
| STRING_AGG(DISTINCT x, separator) | connection .stringConcatDistinct(x, separator) |
Aggregate distinct string concat | Connection API |
| SUBSTR(value, start, len) | value .substr(start, len) |
Substring by start and length | Value expressions API |
| SUBSTRING(value FROM start FOR len) | value .substring(start, end) |
Substring with specific range (varies by DB) | Value expressions API |
| SUM(x) | connection .sum(x) |
Aggregate: returns the sum of values | Connection API |
| SUM(DISTINCT x) | connection .sumDistinct(x) |
Aggregate: returns the sum of distinct values | Connection API |
| TAN(value) | value .tan() |
Tangent | Value expressions API |
| TRIM(value) | value .trim() |
Remove leading and trailing spaces | Value expressions API |
| TRUE | connection .true() |
SQL constant for true | Connection API |
| UPPER(value) | value .toUpperCase() |
Uppercase string | Value expressions API |