tessl/npm-hibernatets

A TypeScript ORM library inspired by Hibernate for working with MySQL, MariaDB, and PostgreSQL databases using decorators and async/await patterns

Overview

Eval results

Files

SQL Conditions

Name: tessl/npm-hibernatets
Author: tessl

Fluent SQL condition builder for complex queries and filtering with type-safe parameter binding. The SqlCondition class provides a chainable API for constructing SQL WHERE clauses with automatic parameter handling and column validation.

Capabilities

SqlCondition Class

Core SQL condition builder with fluent API for constructing complex WHERE clauses.

/**
 * Fluent SQL condition builder for complex queries
 */
class SqlCondition {
  /** Predefined condition for "TRUE=TRUE" to match all records */
  static readonly ALL: SqlCondition;

  /**
   * Add column reference to the condition
   * @param columnName - Name of the database column
   * @returns SqlCondition instance for chaining
   */
  column(columnName: string): SqlCondition;

  /**
   * Add less-than operator (<)
   * @returns SqlCondition instance for chaining
   */
  smaller(): SqlCondition;

  /**
   * Add greater-than operator (>)
   * @returns SqlCondition instance for chaining
   */
  greater(): SqlCondition;

  /**
   * Add equals comparison (=)
   * @param value - Value to compare against (will be parameterized)
   * @returns SqlCondition instance for chaining
   */
  equals(value: SqlParameter): SqlCondition;

  /**
   * Add AND condition with nested logic
   * @param cb - Callback function to build nested condition
   * @returns SqlCondition instance for chaining
   */
  and(cb: (condition: SqlCondition) => SqlCondition): SqlCondition;

  /**
   * Add parameter value to the condition
   * @param value - Parameter value or Date object
   * @returns SqlCondition instance for chaining
   */
  param(value: SqlParameter | Date): SqlCondition;

  /**
   * Add current timestamp function (UNIX_TIMESTAMP(NOW(3))*1000)
   */
  now(): void;

  /**
   * Build final SQL string with parameter substitution
   * @param params - Array of parameters for substitution
   * @returns Generated SQL WHERE clause string
   */
  build(params: Array<SqlParameter> | null): string;

  /**
   * Validate column references against entity class
   * @param classRef - Entity class to validate columns against
   */
  checkColumns(classRef: any): void;
}

/** Type for SQL parameters */
type SqlParameter = string | number;

Usage Examples:

import { SqlCondition, load } from "hibernatets";

// Simple equality condition
const activeUsers = await load(User,
  new SqlCondition().column("active").equals(true)
);

// Greater than condition
const adultUsers = await load(User,
  new SqlCondition().column("age").greater().param(18)
);

// Less than condition with date
const recentPosts = await load(Post,
  new SqlCondition()
    .column("createdAt")
    .greater()
    .param(new Date("2023-01-01"))
);

// Using predefined ALL condition
const allUsers = await load(User, SqlCondition.ALL);

// Current timestamp comparison
const condition = new SqlCondition()
  .column("lastLoginAt")
  .greater();
condition.now();
const currentActiveUsers = await load(User, condition);

Complex Conditions

Building complex SQL conditions with AND logic and nested expressions.

/**
 * Add AND condition with nested logic
 * @param cb - Callback function that receives a new SqlCondition instance
 * @returns SqlCondition instance for chaining
 */
and(cb: (condition: SqlCondition) => SqlCondition): SqlCondition;

Usage Examples:

import { SqlCondition, load } from "hibernatets";

// AND condition with multiple criteria
const activeAdultUsers = await load(User,
  new SqlCondition()
    .column("active")
    .equals(true)
    .and(c => c.column("age").greater().param(18))
);

// Complex nested conditions
const eligibleUsers = await load(User,
  new SqlCondition()
    .column("verified")
    .equals(true)
    .and(c => c
      .column("createdAt")
      .smaller()
      .param(new Date("2023-12-31"))
    )
    .and(c => c
      .column("loginCount")
      .greater()
      .param(5)
    )
);

// Multiple AND conditions
const premiumActiveUsers = await load(User,
  new SqlCondition()
    .column("subscriptionType")
    .equals("premium")
    .and(c => c.column("active").equals(true))
    .and(c => c.column("paymentStatus").equals("current"))
    .and(c => c
      .column("lastPaymentDate")
      .greater()
      .param(new Date(Date.now() - 30 * 24 * 60 * 60 * 1000))
    )
);

Parameter Handling

Safe parameter binding and value handling in SQL conditions.

Usage Examples:

import { SqlCondition, load } from "hibernatets";

// String parameters
const usersByName = await load(User,
  new SqlCondition().column("name").equals("Alice")
);

// Numeric parameters
const userById = await load(User,
  new SqlCondition().column("id").equals(123)
);

// Date parameters
const recentUsers = await load(User,
  new SqlCondition()
    .column("registrationDate")
    .greater()
    .param(new Date("2023-01-01"))
);

// Dynamic parameters
function findUsersByAgeRange(minAge: number, maxAge: number) {
  return load(User,
    new SqlCondition()
      .column("age")
      .greater()
      .param(minAge)
      .and(c => c.column("age").smaller().param(maxAge))
  );
}

// Boolean parameters
const verifiedUsers = await load(User,
  new SqlCondition().column("isVerified").equals(true)
);

Column Validation

Validate column references against entity definitions to catch errors early.

Usage Examples:

import { SqlCondition } from "hibernatets";

// Validate columns before using in queries
const condition = new SqlCondition()
  .column("name")
  .equals("Alice")
  .and(c => c.column("age").greater().param(21));

// Check if columns exist in User entity
condition.checkColumns(User);

// This will throw an error if columns don't exist
try {
  const invalidCondition = new SqlCondition()
    .column("nonExistentColumn")
    .equals("value");

  invalidCondition.checkColumns(User);
} catch (error) {
  console.error("Invalid column reference:", error.message);
}

Advanced Query Patterns

Complex querying patterns and best practices for SQL condition building.

import { SqlCondition, load } from "hibernatets";

// Search functionality
async function searchUsers(searchTerm: string) {
  return await load(User,
    new SqlCondition()
      .column("name")
      .equals(`%${searchTerm}%`)
      .and(c => c.column("active").equals(true))
  );
}

// Date range queries
async function getUsersInDateRange(startDate: Date, endDate: Date) {
  return await load(User,
    new SqlCondition()
      .column("createdAt")
      .greater()
      .param(startDate)
      .and(c => c
        .column("createdAt")
        .smaller()
        .param(endDate)
      )
  );
}

// Status-based filtering
async function getUsersByStatus(statuses: string[]) {
  // For multiple values, you might need to build OR conditions
  // This example shows one approach using individual AND conditions
  let condition = new SqlCondition().column("status").equals(statuses[0]);

  // Note: This creates AND logic, for OR logic you'd need database-specific SQL
  for (let i = 1; i < statuses.length; i++) {
    condition = condition.and(c => c.column("status").equals(statuses[i]));
  }

  return await load(User, condition);
}

// Conditional query building
function buildUserFilter(options: {
  active?: boolean;
  minAge?: number;
  maxAge?: number;
  verified?: boolean;
  searchTerm?: string;
}) {
  let condition = SqlCondition.ALL;

  if (options.active !== undefined) {
    condition = new SqlCondition()
      .column("active")
      .equals(options.active);
  }

  if (options.minAge !== undefined) {
    condition = condition.and(c => c
      .column("age")
      .greater()
      .param(options.minAge!)
    );
  }

  if (options.maxAge !== undefined) {
    condition = condition.and(c => c
      .column("age")
      .smaller()
      .param(options.maxAge!)
    );
  }

  if (options.verified !== undefined) {
    condition = condition.and(c => c
      .column("verified")
      .equals(options.verified!)
    );
  }

  if (options.searchTerm) {
    condition = condition.and(c => c
      .column("name")
      .equals(`%${options.searchTerm}%`)
    );
  }

  return condition;
}

// Usage of conditional filter
const filteredUsers = await load(User, buildUserFilter({
  active: true,
  minAge: 18,
  verified: true,
  searchTerm: "John"
}));

Integration with Deep Loading

Using SQL conditions with relationship deep loading for complex data retrieval.

import { SqlCondition, load } from "hibernatets";

// Deep loading with filtered relationships
const usersWithRecentPosts = await load(User, SqlCondition.ALL, [], {
  deep: {
    posts: new SqlCondition()
      .column("published")
      .equals(true)
      .and(c => c
        .column("createdAt")
        .greater()
        .param(new Date(Date.now() - 7 * 24 * 60 * 60 * 1000))
      )
  }
});

// Multiple relationship filters
const activeUsersWithData = await load(User,
  new SqlCondition().column("active").equals(true),
  [],
  {
    deep: {
      posts: new SqlCondition()
        .column("status")
        .equals("published"),
      profile: new SqlCondition()
        .column("visibility")
        .equals("public"),
      comments: new SqlCondition()
        .column("approved")
        .equals(true)
        .and(c => c
          .column("createdAt")
          .greater()
          .param(new Date(Date.now() - 30 * 24 * 60 * 60 * 1000))
        )
    }
  }
);

SQL Generation and Debugging

Understanding how SQL conditions are built and debugging query generation.

import { SqlCondition } from "hibernatets";

// Build and inspect generated SQL
const condition = new SqlCondition()
  .column("active")
  .equals(true)
  .and(c => c.column("age").greater().param(21));

// Generate SQL string (usually done internally)
const sql = condition.build([true, 21]);
console.log("Generated SQL:", sql);
// Output: "active = ? AND age > ?"

// Complex condition SQL generation
const complexCondition = new SqlCondition()
  .column("status")
  .equals("active")
  .and(c => {
    c.column("lastLogin").greater();
    c.now();
    return c;
  })
  .and(c => c
    .column("credits")
    .greater()
    .param(100)
  );

// This would generate something like:
// "status = ? AND lastLogin > NOW() AND credits > ?"

Install with Tessl CLI