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

query-building.mddocs/

Query Building

This document covers comprehensive query construction with method chaining, joins, subqueries, and advanced SQL features for building complex database queries.

Query Class

The Query class provides a fluent API for building SQL statements through method chaining.

class Query {
  // Core query building
  select(...columns: any[]): Query;
  from(...tables: any[]): Query;
  where(...conditions: any[]): Query;
  and(condition: any): Query;
  or(condition: any): Query;
  
  // Joins
  join(table: Table): JoinQuery;
  leftJoin(table: Table): JoinQuery;
  
  // Ordering and grouping
  order(...criteria: any[]): Query;
  group(...columns: any[]): Query;
  having(...conditions: any[]): Query;
  
  // Limiting results
  limit(count: number): Query;
  offset(count: number): Query;
  
  // Query modifiers
  distinct(): Query;
  distinctOn(...columns: any[]): Query;
  
  // Data modification
  insert(data: object | object[]): Query;
  replace(data: object | object[]): Query;
  update(data: object): Query;
  delete(conditions?: any): Query;
  
  // Advanced features
  returning(...columns: any[]): Query;
  onDuplicate(action: object): Query;
  onConflict(action: ConflictAction): Query;
  forUpdate(): Query;
  forShare(): Query;
  
  // Output methods
  toQuery(): QueryResult;
  toNamedQuery(name: string, dialect?: string): NamedQueryResult;
  
  // Subquery operations (for subqueries only)
  star(): Column;
  exists(): BinaryExpression;
  notExists(): BinaryExpression;
  
  // Utility
  parameter(value: any): Query;
}

interface QueryResult {
  text: string;
  values: any[];
}

interface NamedQueryResult extends QueryResult {
  name: string;
}

SELECT Queries

Basic SELECT

// Select all columns
const allColumnsQuery = user.select().toQuery();
console.log(allColumnsQuery.text); // SELECT "user".* FROM "user"

// Select specific columns
const specificQuery = user.select(user.id, user.name).toQuery();
console.log(specificQuery.text); // SELECT "user"."id", "user"."name" FROM "user"

// Select with aliases
const aliasQuery = user.select(
  user.id,
  user.name.as('full_name'),
  user.email.as('contact_email')
).toQuery();

SELECT with Expressions

// Select with computed columns
const computedQuery = user.select(
  user.id,
  user.name,
  sql.functions.UPPER(user.name).as('upper_name'),
  sql.functions.COUNT(user.id).as('user_count')
).toQuery();

// Select with constants
const constantQuery = user.select(
  user.id,
  user.name,
  sql.constant('active').as('status')
).toQuery();

WHERE Clauses

Basic Conditions

// Single condition
query.where(condition: any): Query;

// Multiple conditions (AND)
query.where(condition1, condition2, ...): Query;

// Explicit AND/OR
query.and(condition: any): Query;
query.or(condition: any): Query;

Usage examples:

// Simple WHERE
const simpleWhere = user
  .select()
  .where(user.age.gt(18))
  .toQuery();

// Multiple conditions with AND
const multipleAnd = user
  .select()
  .where(
    user.age.gt(18),
    user.active.equals(true),
    user.email.isNotNull()
  )
  .toQuery();

// Complex conditions with AND/OR
const complexWhere = user
  .select()
  .where(user.age.gt(18))
  .and(user.active.equals(true))
  .or(user.role.equals('admin'))
  .toQuery();

// Grouped conditions
const groupedWhere = user
  .select()
  .where(
    user.name.equals('John').and(user.age.gt(25))
  )
  .or(
    user.name.equals('Jane').and(user.age.gt(30))
  )
  .toQuery();

JOINs

Basic Joins

interface JoinQuery {
  on(condition: any): Query;
  using(...columns: string[]): Query;
}

// Join types
table.join(other: Table): JoinQuery;        // INNER JOIN
table.leftJoin(other: Table): JoinQuery;    // LEFT JOIN
table.rightJoin(other: Table): JoinQuery;   // RIGHT JOIN (dialect-dependent)
table.fullJoin(other: Table): JoinQuery;    // FULL JOIN (dialect-dependent)

Usage examples:

// Inner join
const innerJoin = user
  .select(user.name, post.title)
  .from(user.join(post).on(user.id.equals(post.userId)))
  .toQuery();

// Left join with multiple conditions
const leftJoin = user
  .select(user.name, post.title)
  .from(
    user.leftJoin(post).on(
      user.id.equals(post.userId).and(post.published.equals(true))
    )
  )
  .toQuery();

// Multiple joins
const multiJoin = user
  .select(user.name, post.title, category.name.as('category'))
  .from(
    user
      .join(post).on(user.id.equals(post.userId))
      .join(category).on(post.categoryId.equals(category.id))
  )
  .toQuery();

// Self join with aliases
const friends = user.as('friends');
const selfJoin = user
  .select(user.name, friends.name.as('friend_name'))
  .from(
    user
      .join(friendship).on(user.id.equals(friendship.userId))
      .join(friends).on(friendship.friendId.equals(friends.id))
  )
  .toQuery();

Auto-Join

// Automatic join based on foreign key relationships
const autoJoin = user.joinTo(post);

ORDER BY and GROUP BY

ORDER BY

query.order(...criteria: any[]): Query;

// Order criteria can be:
// - Column with .asc or .desc
// - Column (defaults to ASC)
// - Raw SQL string

Usage examples:

// Single column ordering
const singleOrder = user
  .select()
  .order(user.name.asc)
  .toQuery();

// Multiple column ordering
const multiOrder = user
  .select()
  .order(user.name.asc, user.age.desc, user.id)
  .toQuery();

// Ordering with expressions
const expressionOrder = user
  .select()
  .order(sql.functions.LOWER(user.name).asc)
  .toQuery();

GROUP BY and HAVING

query.group(...columns: any[]): Query;
query.having(...conditions: any[]): Query;

Usage examples:

// Group by with aggregation
const groupQuery = user
  .select(user.department, sql.functions.COUNT(user.id).as('user_count'))
  .group(user.department)
  .toQuery();

// Group by with HAVING
const havingQuery = user
  .select(user.department, sql.functions.COUNT(user.id).as('user_count'))
  .group(user.department)
  .having(sql.functions.COUNT(user.id).gt(5))
  .toQuery();

// Multiple grouping columns
const multiGroupQuery = post
  .select(
    post.userId,
    post.category,
    sql.functions.COUNT(post.id).as('post_count')
  )
  .group(post.userId, post.category)
  .toQuery();

DISTINCT and LIMIT/OFFSET

DISTINCT

query.distinct(): Query;
query.distinctOn(...columns: any[]): Query;  // PostgreSQL specific

Usage examples:

// Distinct results
const distinctQuery = user
  .select(user.department)
  .distinct()
  .toQuery();

// Distinct on specific columns (PostgreSQL)
const distinctOnQuery = user
  .select(user.id, user.name, user.email)
  .distinctOn(user.email)
  .order(user.email, user.id)
  .toQuery();

LIMIT and OFFSET

query.limit(count: number): Query;
query.offset(count: number): Query;

Usage examples:

// Pagination
const paginatedQuery = user
  .select()
  .order(user.id)
  .limit(20)
  .offset(40)  // Skip first 40 records
  .toQuery();

// Top N records
const topQuery = user
  .select()
  .order(user.created_at.desc)
  .limit(10)
  .toQuery();

Subqueries

Creating Subqueries

table.subQuery(alias?: string): SubQuery;

interface SubQuery {
  select(...columns: any[]): SubQuery;
  where(...conditions: any[]): SubQuery;
  from(...tables: any[]): SubQuery;
  group(...columns: any[]): SubQuery;
  order(...criteria: any[]): SubQuery;
  limit(count: number): SubQuery;
  offset(count: number): SubQuery;
  exists(): BinaryExpression;
  notExists(): BinaryExpression;
  star(): Column;
}

Usage examples:

// Subquery in WHERE clause
const activeUsers = user.subQuery('active_users')
  .select(user.id)
  .where(user.active.equals(true));

const postsWithActiveUsers = post
  .select()
  .where(post.userId.in(activeUsers))
  .toQuery();

// EXISTS subquery
const usersWithPosts = user
  .select()
  .where(
    post.subQuery()
      .select(post.id)
      .where(post.userId.equals(user.id))
      .exists()
  )
  .toQuery();

// Subquery in FROM clause
const avgAgeByDept = user.subQuery('dept_avg')
  .select(
    user.department,
    sql.functions.AVG(user.age).as('avg_age')
  )
  .group(user.department);

const aboveAvgUsers = user
  .select(user.name, user.age, user.department)
  .from(
    user.join(avgAgeByDept).on(user.department.equals(avgAgeByDept.department))
  )
  .where(user.age.gt(avgAgeByDept.avg_age))
  .toQuery();

Advanced Query Features

RETURNING Clause

query.returning(...columns: any[]): Query;

Usage (PostgreSQL specific):

// INSERT with RETURNING
const insertReturning = user
  .insert({ name: 'John', email: 'john@example.com' })
  .returning(user.id, user.created_at)
  .toQuery();

// UPDATE with RETURNING
const updateReturning = user
  .update({ name: 'John Smith' })
  .where(user.id.equals(1))
  .returning(user.id, user.name, user.updated_at)
  .toQuery();

ON DUPLICATE KEY UPDATE

query.onDuplicate(action: object): Query;  // MySQL specific

Usage:

const onDuplicateQuery = user
  .insert({ id: 1, name: 'John', email: 'john@example.com' })
  .onDuplicate({ name: 'John Updated' })
  .toQuery();

ON CONFLICT (UPSERT)

query.onConflict(action: ConflictAction): Query;  // PostgreSQL specific

interface ConflictAction {
  target?: string | string[];
  action?: 'NOTHING' | object;
  where?: any;
}

Usage:

// ON CONFLICT DO NOTHING
const conflictNothing = user
  .insert({ name: 'John', email: 'john@example.com' })
  .onConflict({ target: 'email', action: 'NOTHING' })
  .toQuery();

// ON CONFLICT DO UPDATE
const conflictUpdate = user
  .insert({ name: 'John', email: 'john@example.com' })
  .onConflict({
    target: 'email',
    action: { name: 'John Updated' }
  })
  .toQuery();

Row Locking

query.forUpdate(): Query;
query.forShare(): Query;  // PostgreSQL specific

Usage:

// SELECT FOR UPDATE
const forUpdateQuery = user
  .select()
  .where(user.id.equals(1))
  .forUpdate()
  .toQuery();

// SELECT FOR SHARE
const forShareQuery = user
  .select()
  .where(user.id.equals(1))
  .forShare()
  .toQuery();

Query Execution and Output

Query Result

interface QueryResult {
  text: string;    // SQL query string with parameter placeholders
  values: any[];   // Parameter values array
}

interface NamedQueryResult extends QueryResult {
  name: string;    // Query name for identification
}

Usage:

// Get parameterized query
const query = user
  .select()
  .where(user.name.equals('John'))
  .toQuery();

console.log(query.text);   // SELECT "user".* FROM "user" WHERE ("user"."name" = $1)
console.log(query.values); // ['John']

// Named query
const namedQuery = user
  .select()
  .toNamedQuery('user.findAll');

console.log(namedQuery.name); // 'user.findAll'

Parameters

query.parameter(value: any): Query;

Usage:

// Explicit parameter
const paramQuery = user
  .select()
  .where(user.created_at.gt(sql.parameter(new Date())))
  .toQuery();

Query Composition and Reusability

Reusable Query Fragments

// Define reusable conditions
const activeCondition = user.active.equals(true);
const adultCondition = user.age.gte(18);

// Compose queries
const activeAdults = user
  .select()
  .where(activeCondition.and(adultCondition))
  .toQuery();

// Reusable joins
const userPostJoin = user.join(post).on(user.id.equals(post.userId));

const userPostsQuery = user
  .select(user.name, post.title)
  .from(userPostJoin)
  .toQuery();

Query Templates

// Create query builder function
function buildUserQuery(filters = {}) {
  let query = user.select();
  
  if (filters.active !== undefined) {
    query = query.where(user.active.equals(filters.active));
  }
  
  if (filters.minAge) {
    query = query.where(user.age.gte(filters.minAge));
  }
  
  if (filters.department) {
    query = query.where(user.department.equals(filters.department));
  }
  
  return query;
}

// Use template
const adminUsers = buildUserQuery({ 
  active: true, 
  minAge: 21, 
  department: 'admin' 
}).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