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

column-operations.mddocs/

Column Operations

This document covers rich column expressions including comparisons, mathematical operations, string functions, type casting, and all available column methods for building complex SQL expressions.

Column Class

The Column class represents database columns and provides methods for creating SQL expressions.

class Column {
  // Properties
  name: string;
  table: Table;
  alias: string;
  dataType: string;
  defaultValue: any;
  
  // Value and node operations
  value(value: any): Column;
  getValue(): any;
  toNode(): ColumnNode;
  as(alias: string): ColumnNode;
  toQuery(): QueryResult;
  
  // Aggregation functions
  count(alias?: string): Column;
  sum(alias?: string): Column;
  avg(alias?: string): Column;
  min(alias?: string): Column;
  max(alias?: string): Column;
  distinct(): Column;
  arrayAgg(alias?: string): Column;
  aggregate(alias: string, aggregator: string): Column;
  
  // Ordering
  asc: OrderByValueNode;
  ascending: OrderByValueNode;
  desc: OrderByValueNode; 
  descending: OrderByValueNode;
}

Comparison Operations

Equality and Inequality

column.equals(value: any): BinaryExpression;
column.equal(value: any): BinaryExpression;    // Alias for equals
column.notEquals(value: any): BinaryExpression;
column.notEqual(value: any): BinaryExpression; // Alias for notEquals

Usage examples:

// Basic equality
const equalQuery = user
  .select()
  .where(user.name.equals('John'))
  .toQuery();

// Not equal
const notEqualQuery = user
  .select()
  .where(user.status.notEquals('inactive'))
  .toQuery();

// Compare with another column
const columnCompareQuery = user
  .select()
  .where(user.created_at.equals(user.updated_at))
  .toQuery();

Relational Comparisons

column.gt(value: any): BinaryExpression;    // Greater than
column.gte(value: any): BinaryExpression;   // Greater than or equal
column.lt(value: any): BinaryExpression;    // Less than
column.lte(value: any): BinaryExpression;   // Less than or equal

Usage examples:

// Age comparisons
const adultUsers = user
  .select()
  .where(user.age.gte(18))
  .toQuery();

// Date comparisons
const recentPosts = post
  .select()
  .where(post.created_at.gt(new Date('2023-01-01')))
  .toQuery();

// Salary range
const salaryRangeQuery = employee
  .select()
  .where(
    employee.salary.gte(50000).and(employee.salary.lte(100000))
  )
  .toQuery();

Null Checks

column.isNull(): UnaryExpression;
column.isNotNull(): UnaryExpression;

Usage examples:

// Find users without email
const noEmailQuery = user
  .select()
  .where(user.email.isNull())
  .toQuery();

// Find users with phone numbers
const hasPhoneQuery = user
  .select()
  .where(user.phone.isNotNull())
  .toQuery();

String Operations

Pattern Matching

column.like(pattern: string): BinaryExpression;
column.notLike(pattern: string): BinaryExpression;
column.ilike(pattern: string): BinaryExpression;      // Case-insensitive LIKE (PostgreSQL)
column.notIlike(pattern: string): BinaryExpression;   // Case-insensitive NOT LIKE (PostgreSQL)
column.rlike(pattern: string): BinaryExpression;      // RLIKE (MySQL)

Usage examples:

// LIKE pattern matching
const likeQuery = user
  .select()
  .where(user.name.like('John%'))
  .toQuery();

// Case-insensitive search (PostgreSQL)
const ilikeQuery = user
  .select()
  .where(user.email.ilike('%@gmail.com'))
  .toQuery();

// NOT LIKE
const notLikeQuery = user
  .select()
  .where(user.email.notLike('%temp%'))
  .toQuery();

Regular Expressions

column.regex(pattern: string): BinaryExpression;      // ~ (PostgreSQL)
column.iregex(pattern: string): BinaryExpression;     // ~* (PostgreSQL)
column.notRegex(pattern: string): BinaryExpression;   // !~ (PostgreSQL)
column.notIregex(pattern: string): BinaryExpression;  // !~* (PostgreSQL)
column.regexp(pattern: string): BinaryExpression;     // REGEXP (MySQL)
column.match(pattern: string): BinaryExpression;      // @@ full-text search (PostgreSQL)

Usage examples:

// Regular expression matching (PostgreSQL)
const regexQuery = user
  .select()
  .where(user.phone.regex('^\\+1'))
  .toQuery();

// Case-insensitive regex (PostgreSQL)
const iregexQuery = user
  .select()
  .where(user.name.iregex('^(john|jane)'))
  .toQuery();

// MySQL REGEXP
const mysqlRegexQuery = user
  .select()
  .where(user.email.regexp('[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}'))
  .toQuery();

// Full-text search (PostgreSQL)
const fullTextQuery = document
  .select()
  .where(document.content_vector.match('search & terms'))
  .toQuery();

Set Operations

IN and NOT IN

column.in(values: any[]): BinaryExpression;
column.in(subquery: SubQuery): BinaryExpression;
column.notIn(values: any[]): BinaryExpression;
column.notIn(subquery: SubQuery): BinaryExpression;

Usage examples:

// IN with array
const inArrayQuery = user
  .select()
  .where(user.status.in(['active', 'pending', 'verified']))
  .toQuery();

// IN with subquery
const activeUserIds = user.subQuery()
  .select(user.id)
  .where(user.active.equals(true));

const postsFromActiveUsers = post
  .select()
  .where(post.userId.in(activeUserIds))
  .toQuery();

// NOT IN
const notInQuery = user
  .select()
  .where(user.role.notIn(['banned', 'suspended']))
  .toQuery();

BETWEEN

column.between(start: any, end: any): TernaryExpression;
column.notBetween(start: any, end: any): TernaryExpression;

Usage examples:

// Age range
const ageRangeQuery = user
  .select()
  .where(user.age.between(18, 65))
  .toQuery();

// Date range
const dateRangeQuery = post
  .select()
  .where(post.created_at.between('2023-01-01', '2023-12-31'))
  .toQuery();

// NOT BETWEEN
const notBetweenQuery = employee
  .select()
  .where(employee.salary.notBetween(30000, 40000))
  .toQuery();

Mathematical Operations

column.plus(value: any): BinaryExpression;      // Addition (+)
column.minus(value: any): BinaryExpression;     // Subtraction (-)
column.multiply(value: any): BinaryExpression;  // Multiplication (*)
column.divide(value: any): BinaryExpression;    // Division (/)
column.modulo(value: any): BinaryExpression;    // Modulo (%)

Usage examples:

// Calculate total with tax
const totalWithTaxQuery = order
  .select(
    order.id,
    order.subtotal,
    order.subtotal.multiply(1.08).as('total_with_tax')
  )
  .toQuery();

// Age in months
const ageInMonthsQuery = user
  .select(
    user.name,
    user.age.multiply(12).as('age_in_months')
  )
  .toQuery();

// Discount calculation
const discountQuery = product
  .select(
    product.name,
    product.price,
    product.price.minus(product.price.multiply(0.1)).as('discounted_price')
  )
  .toQuery();

Bitwise Operations

column.leftShift(value: any): BinaryExpression;   // << (left shift)
column.rightShift(value: any): BinaryExpression;  // >> (right shift)
column.bitwiseAnd(value: any): BinaryExpression;  // & (bitwise AND)
column.bitwiseOr(value: any): BinaryExpression;   // | (bitwise OR)
column.bitwiseXor(value: any): BinaryExpression;  // # (bitwise XOR)
column.bitwiseNot(value: any): BinaryExpression;  // ~ (bitwise NOT)

Usage examples:

// Permission checking with bitwise operations
const permissionQuery = user
  .select()
  .where(user.permissions.bitwiseAnd(4).gt(0))  // Check for specific permission bit
  .toQuery();

// Bitwise flags
const flagQuery = item
  .select()
  .where(item.flags.bitwiseOr(8).equals(item.flags))
  .toQuery();

String/JSON Operations

String Concatenation

column.concat(value: any): BinaryExpression;     // || (string concatenation)

Usage examples:

// Concatenate first and last name
const fullNameQuery = user
  .select(
    user.id,
    user.first_name.concat(' ').concat(user.last_name).as('full_name')
  )
  .toQuery();

JSON Operations (PostgreSQL)

column.key(key: string): BinaryExpression;       // -> (JSON key access)
column.keyText(key: string): BinaryExpression;   // ->> (JSON key access as text)
column.path(path: string[]): BinaryExpression;   // #> (JSON path access)
column.pathText(path: string[]): BinaryExpression; // #>> (JSON path access as text)

Usage examples:

// JSON key access
const jsonKeyQuery = user
  .select(
    user.id,
    user.metadata.key('preferences').as('user_preferences')
  )
  .toQuery();

// JSON key as text
const jsonTextQuery = user
  .select(
    user.id,
    user.profile.keyText('name').as('profile_name')
  )
  .toQuery();

// JSON path access
const jsonPathQuery = user
  .select(
    user.id,
    user.settings.path(['ui', 'theme']).as('theme_setting')
  )
  .toQuery();

Array/JSON Advanced Operations (PostgreSQL)

column.contains(value: any): BinaryExpression;      // @> (contains)
column.containedBy(value: any): BinaryExpression;   // <@ (contained by)
column.containsKey(key: string): BinaryExpression;  // ? (contains key)
column.overlap(value: any): BinaryExpression;       // && (overlaps)
column.at(index: number): Expression;               // Array element access
column.slice(start: number, end?: number): Expression; // Array slice

Usage examples:

// Array contains
const containsQuery = post
  .select()
  .where(post.tags.contains(['javascript', 'node']))
  .toQuery();

// JSON contains key
const hasKeyQuery = user
  .select()
  .where(user.metadata.containsKey('preferences'))
  .toQuery();

// Array overlap
const overlapQuery = user
  .select()
  .where(user.interests.overlap(['programming', 'music']))
  .toQuery();

// Array element access
const firstTagQuery = post
  .select(
    post.id,
    post.tags.at(0).as('first_tag')
  )
  .toQuery();

// Array slice
const firstThreeTagsQuery = post
  .select(
    post.id,
    post.tags.slice(0, 3).as('first_three_tags')
  )
  .toQuery();

Logical Operations

column.and(condition: any): BinaryExpression;
column.or(condition: any): BinaryExpression;

Usage examples:

// Complex logical conditions
const complexQuery = user
  .select()
  .where(
    user.active.equals(true)
      .and(user.verified.equals(true))
      .or(user.role.equals('admin'))
  )
  .toQuery();

Type Operations

Type Casting

column.cast(dataType: string): CastExpression;

Usage examples:

// Cast to different type
const castQuery = user
  .select(
    user.id,
    user.age.cast('VARCHAR').as('age_string'),
    user.created_at.cast('DATE').as('created_date')
  )
  .toQuery();

CASE Expressions

column.case(whenConditions: any[], thenValues: any[], elseValue?: any): CaseExpression;

Usage examples:

// Simple CASE expression
const caseQuery = user
  .select(
    user.name,
    user.age.case(
      [user.age.lt(18), user.age.between(18, 65)],
      ['Minor', 'Adult'],
      'Senior'
    ).as('age_category')
  )
  .toQuery();

// CASE with multiple conditions
const statusCaseQuery = order
  .select(
    order.id,
    order.status.case(
      [
        order.status.equals('pending'),
        order.status.equals('shipped'),
        order.status.equals('delivered')
      ],
      ['Processing', 'In Transit', 'Completed'],
      'Unknown'
    ).as('status_description')
  )
  .toQuery();

Aggregation Functions

column.count(alias?: string): Column;
column.sum(alias?: string): Column;
column.avg(alias?: string): Column;
column.min(alias?: string): Column;
column.max(alias?: string): Column;
column.distinct(): Column;
column.arrayAgg(alias?: string): Column;        // PostgreSQL
column.aggregate(alias: string, aggregator: string): Column;

Usage examples:

// Basic aggregations
const aggregateQuery = order
  .select(
    order.userId,
    order.amount.sum('total_amount'),
    order.amount.avg('avg_amount'),
    order.amount.min('min_amount'),
    order.amount.max('max_amount'),
    order.id.count('order_count')
  )
  .group(order.userId)
  .toQuery();

// Distinct count
const distinctQuery = user
  .select(
    user.department.distinct().count('unique_departments')
  )
  .toQuery();

// Array aggregation (PostgreSQL)
const arrayAggQuery = user
  .select(
    user.department,
    user.name.arrayAgg('user_names')
  )
  .group(user.department)
  .toQuery();

// Custom aggregation
const customAggQuery = product
  .select(
    product.category,
    product.price.aggregate('median_price', 'PERCENTILE_CONT(0.5)')
  )
  .group(product.category)
  .toQuery();

Ordering Operations

// Ordering properties
column.asc: OrderByValueNode;         // Ascending order
column.ascending: OrderByValueNode;   // Ascending order (alias)
column.desc: OrderByValueNode;        // Descending order
column.descending: OrderByValueNode;  // Descending order (alias)

Usage examples:

// Simple ordering
const orderedQuery = user
  .select()
  .order(user.name.asc, user.age.desc)
  .toQuery();

// Ordering with expressions
const expressionOrderQuery = user
  .select()
  .order(
    user.last_name.asc,
    user.first_name.asc,
    user.created_at.desc
  )
  .toQuery();

Column Value Assignment

column.value(value: any): Column;
column.getValue(): any;

Usage examples:

// Set column values for INSERT
const insertQuery = user
  .insert(
    user.name.value('John Doe'),
    user.email.value('john@example.com'),
    user.age.value(30)
  )
  .toQuery();

// Use in UPDATE
const updateQuery = user
  .update({
    name: 'Jane Doe',
    updated_at: user.updated_at.value(new Date())
  })
  .where(user.id.equals(1))
  .toQuery();

Column Aliases

column.as(alias: string): ColumnNode;

Usage examples:

// Column aliases in SELECT
const aliasQuery = user
  .select(
    user.id,
    user.first_name.as('firstName'),
    user.last_name.as('lastName'),
    user.email.as('emailAddress')
  )
  .toQuery();

// Aliases with expressions
const expressionAliasQuery = order
  .select(
    order.id,
    order.subtotal.multiply(1.08).as('totalWithTax'),
    order.created_at.cast('DATE').as('orderDate')
  )
  .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