tessl/npm-objection

An SQL-friendly ORM for Node.js built on Knex.js with powerful query building, relationship handling, and JSON Schema validation

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Overview

Eval results

Files

Expression Builders

Name: tessl/npm-objection
Author: tessl

Query expression builders for raw SQL, column references, values, and functions.

Capabilities

Raw Expression Builder

Create raw SQL expressions with parameter binding.

/**
 * Create a raw SQL expression
 * @param sql - SQL string with optional parameter placeholders
 * @param bindings - Values to bind to placeholders
 * @returns RawBuilder instance
 */
function raw(sql: string, ...bindings: any[]): RawBuilder;

interface RawBuilder {
  /** Add alias to raw expression */
  as(alias: string): RawBuilder;
}

Usage Examples:

const { raw } = require('objection');

// Basic raw expression
const people = await Person.query()
  .select(raw('count(*) as total'))
  .groupBy('department');

// Raw with bindings
const people = await Person.query()
  .where(raw('age > ?', 18))
  .orderBy(raw('random()'));

// Raw in select with alias
const results = await Person.query()
  .select('firstName', 'lastName')
  .select(raw('age * 2').as('doubleAge'));

// Raw in joins
const results = await Person.query()
  .join('pets', raw('persons.id = pets.owner_id AND pets.species = ?', 'dog'));

// Complex raw expressions
const stats = await Person.query()
  .select(raw(`
    CASE 
      WHEN age < 18 THEN 'minor'
      WHEN age < 65 THEN 'adult'
      ELSE 'senior'
    END as ageGroup
  `))
  .groupBy(raw('ageGroup'));

Reference Builder

Create column references for queries.

/**
 * Create a column reference
 * @param expression - Column reference expression
 * @returns ReferenceBuilder instance
 */
function ref(expression: string): ReferenceBuilder;

interface ReferenceBuilder {
  /** Specify table for the reference */
  from(tableReference: string): ReferenceBuilder;
  
  /** Add alias to reference */
  as(alias: string): ReferenceBuilder;
  
  /** Cast reference to text type */
  castText(): ReferenceBuilder;
  
  /** Cast reference to integer type */
  castInt(): ReferenceBuilder;
  
  /** Cast reference to big integer type */
  castBigInt(): ReferenceBuilder;
  
  /** Cast reference to float type */
  castFloat(): ReferenceBuilder;
  
  /** Cast reference to decimal type */
  castDecimal(): ReferenceBuilder;
  
  /** Cast reference to real type */
  castReal(): ReferenceBuilder;
  
  /** Cast reference to boolean type */
  castBool(): ReferenceBuilder;
  
  /** Cast reference to JSON type */
  castJson(): ReferenceBuilder;
  
  /** Cast reference to array type */
  castArray(): ReferenceBuilder;
  
  /** Cast reference to custom SQL type */
  castType(sqlType: string): ReferenceBuilder;
  
  /** Alias for castType */
  castTo(sqlType: string): ReferenceBuilder;
}

Usage Examples:

const { ref } = require('objection');

// Basic column reference
const people = await Person.query()
  .where(ref('age'), '>', 18);

// Reference with table specification
const results = await Person.query()
  .join('pets', 'persons.id', 'pets.ownerId')
  .where(ref('persons.age'), '>', ref('pets.age'));

// Reference from specific table
const results = await Person.query()
  .join('pets', 'persons.id', 'pets.ownerId')
  .where(ref('age').from('persons'), '>', 25);

// Reference with type casting
const results = await Person.query()
  .select(ref('metadata').castJson().as('metadataJson'))
  .where(ref('id').castText(), 'like', '123%');

// Reference in order by
const people = await Person.query()
  .orderBy(ref('lastName').castText());

// Reference in complex expressions
const people = await Person.query()
  .where(ref('firstName'), ref('preferredName'))
  .orWhere(ref('age').castInt(), '>', ref('retirementAge').castInt());

Value Builder

Create parameterized values for queries.

/**
 * Create a parameterized value
 * @param value - Value to parameterize
 * @returns ValueBuilder instance
 */
function val(value: any | any[] | object | object[]): ValueBuilder;

interface ValueBuilder {
  /** Add alias to value */
  as(alias: string): ValueBuilder;
  
  /** Cast value to text type */
  castText(): ValueBuilder;
  
  /** Cast value to integer type */
  castInt(): ValueBuilder;
  
  /** Cast value to big integer type */
  castBigInt(): ValueBuilder;
  
  /** Cast value to float type */
  castFloat(): ValueBuilder;
  
  /** Cast value to decimal type */
  castDecimal(): ValueBuilder;
  
  /** Cast value to real type */
  castReal(): ValueBuilder;
  
  /** Cast value to boolean type */
  castBool(): ValueBuilder;
  
  /** Cast value to JSON type */
  castJson(): ValueBuilder;
  
  /** Cast value to array type */
  castArray(): ValueBuilder;
  
  /** Cast value to custom SQL type */
  castType(sqlType: string): ValueBuilder;
  
  /** Alias for castType */
  castTo(sqlType: string): ValueBuilder;
}

Usage Examples:

const { val } = require('objection');

// Basic value
const people = await Person.query()
  .where('status', val('active'));

// Value with type casting
const people = await Person.query()
  .where('age', '>', val('18').castInt());

// JSON value
const metadata = { department: 'engineering', level: 'senior' };
const people = await Person.query()
  .where('metadata', '@>', val(metadata).castJson());

// Array value
const tags = ['javascript', 'nodejs', 'objection'];
const people = await Person.query()
  .where('skills', '&&', val(tags).castArray());

// Value in select
const people = await Person.query()
  .select('firstName', 'lastName')
  .select(val('employee').as('type'));

// Value comparison
const people = await Person.query()
  .where(ref('salary'), '<', val(50000).castInt());

Function Builder

Create SQL function calls.

/**
 * Create a SQL function call
 * @param functionName - Name of the SQL function
 * @param args - Function arguments
 * @returns FunctionBuilder instance
 */
function fn(functionName: string, ...args: any[]): FunctionBuilder;

interface FunctionBuilder {
  /** Add alias to function call */
  as(alias: string): FunctionBuilder;
  
  /** Cast function result to text type */
  castText(): FunctionBuilder;
  
  /** Cast function result to integer type */
  castInt(): FunctionBuilder;
  
  /** Cast function result to big integer type */
  castBigInt(): FunctionBuilder;
  
  /** Cast function result to float type */
  castFloat(): FunctionBuilder;
  
  /** Cast function result to decimal type */
  castDecimal(): FunctionBuilder;
  
  /** Cast function result to real type */
  castReal(): FunctionBuilder;
  
  /** Cast function result to boolean type */
  castBool(): FunctionBuilder;
  
  /** Cast function result to JSON type */
  castJson(): FunctionBuilder;
  
  /** Cast function result to array type */
  castArray(): FunctionBuilder;
  
  /** Cast function result to custom SQL type */
  castType(sqlType: string): FunctionBuilder;
  
  /** Alias for castType */
  castTo(sqlType: string): FunctionBuilder;
  
  // Built-in function shortcuts
  /** Get current timestamp with optional precision */
  static now(precision?: number): FunctionBuilder;
  
  /** COALESCE function */
  static coalesce(...args: any[]): FunctionBuilder;
  
  /** CONCAT function */
  static concat(...args: any[]): FunctionBuilder;
  
  /** SUM function */
  static sum(column: string): FunctionBuilder;
  
  /** AVG function */
  static avg(column: string): FunctionBuilder;
  
  /** MIN function */
  static min(column: string): FunctionBuilder;
  
  /** MAX function */
  static max(column: string): FunctionBuilder;
  
  /** COUNT function */
  static count(column?: string): FunctionBuilder;
  
  /** UPPER function */
  static upper(column: string): FunctionBuilder;
  
  /** LOWER function */
  static lower(column: string): FunctionBuilder;
}

Usage Examples:

const { fn } = require('objection');

// Basic function call
const people = await Person.query()
  .select(fn('upper', 'firstName').as('upperFirstName'));

// Function with multiple arguments
const people = await Person.query()
  .select(fn('concat', 'firstName', ' ', 'lastName').as('fullName'));

// Function in where clause
const people = await Person.query()
  .where(fn('length', 'firstName'), '>', 5);

// Built-in function shortcuts
const stats = await Person.query()
  .select(
    fn.count().as('totalPeople'),
    fn.avg('age').as('averageAge'),
    fn.min('age').as('minAge'),
    fn.max('age').as('maxAge')
  );

// Function with type casting
const people = await Person.query()
  .select(fn('extract', 'year', 'birthDate').castInt().as('birthYear'));

// COALESCE function
const people = await Person.query()
  .select(fn.coalesce('nickname', 'firstName').as('displayName'));

// Current timestamp
const people = await Person.query()
  .insert({
    firstName: 'John',
    lastName: 'Doe',
    createdAt: fn.now()
  });

// Complex function calls
const people = await Person.query()
  .select(
    'id',
    fn('case')
      .when(ref('age'), '<', 18).then('Minor')
      .when(ref('age'), '<', 65).then('Adult')
      .else('Senior')
      .as('ageGroup')
  );

Expression Combination

Combine different expression builders for complex queries.

Usage Examples:

const { raw, ref, val, fn } = require('objection');

// Combining different expression types
const results = await Person.query()
  .select(
    'id',
    fn('concat', ref('firstName'), val(' '), ref('lastName')).as('fullName'),
    raw('age * ?', 2).as('doubleAge')
  )
  .where(ref('department'), val('engineering'))
  .where(fn('lower', ref('status')), val('active'))
  .orderBy(raw('random()'));

// Complex where conditions
const people = await Person.query()
  .where(
    fn('date_part', val('year'), ref('birthDate')),
    '=',
    fn('date_part', val('year'), fn.now())
  );

// JSON operations with expressions
const people = await Person.query()
  .where(
    raw("metadata->>'department'"),
    val('engineering')
  )
  .where(
    fn('jsonb_array_length', ref('tags')),
    '>',
    val(3).castInt()
  );

Type Casting

All expression builders support type casting for database compatibility.

Usage Examples:

// PostgreSQL specific casting
const results = await Person.query()
  .select(ref('id').castText())
  .where(val('123').castInt(), ref('age').castInt())
  .where(fn('array_length', ref('tags'), val(1)).castInt(), '>', val(0));

// JSON casting
const people = await Person.query()
  .where(
    ref('metadata').castJson(),
    '@>',
    val({ active: true }).castJson()
  );

// Custom type casting
const people = await Person.query()
  .select(ref('created_at').castType('date').as('creationDate'));

Types

interface RawBuilder {
  as(alias: string): RawBuilder;
}

interface ReferenceBuilder {
  from(tableReference: string): ReferenceBuilder;
  as(alias: string): ReferenceBuilder;
  castText(): ReferenceBuilder;
  castInt(): ReferenceBuilder;
  castBigInt(): ReferenceBuilder;
  castFloat(): ReferenceBuilder;
  castDecimal(): ReferenceBuilder;
  castReal(): ReferenceBuilder;
  castBool(): ReferenceBuilder;
  castJson(): ReferenceBuilder;
  castArray(): ReferenceBuilder;
  castType(sqlType: string): ReferenceBuilder;
  castTo(sqlType: string): ReferenceBuilder;
}

interface ValueBuilder {
  as(alias: string): ValueBuilder;
  castText(): ValueBuilder;
  castInt(): ValueBuilder;
  castBigInt(): ValueBuilder;
  castFloat(): ValueBuilder;
  castDecimal(): ValueBuilder;
  castReal(): ValueBuilder;
  castBool(): ValueBuilder;
  castJson(): ValueBuilder;
  castArray(): ValueBuilder;
  castType(sqlType: string): ValueBuilder;
  castTo(sqlType: string): ValueBuilder;
}

interface FunctionBuilder {
  as(alias: string): FunctionBuilder;
  castText(): FunctionBuilder;
  castInt(): FunctionBuilder;
  castBigInt(): FunctionBuilder;
  castFloat(): FunctionBuilder;
  castDecimal(): FunctionBuilder;
  castReal(): FunctionBuilder;
  castBool(): FunctionBuilder;
  castJson(): FunctionBuilder;
  castArray(): FunctionBuilder;
  castType(sqlType: string): FunctionBuilder;
  castTo(sqlType: string): FunctionBuilder;
}

type Expression<T = any> = 
  | T 
  | RawBuilder 
  | ReferenceBuilder 
  | ValueBuilder 
  | FunctionBuilder 
  | QueryBuilder;

Install with Tessl CLI