CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/npm-sequelize

Promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite, Microsoft SQL Server, Amazon Redshift and Snowflake's Data Cloud with solid transaction support, relations, eager and lazy loading, read replication and more

—

Pending
Overview
Eval results
Files

query-operators.mddocs/

Query Operators

Advanced query operators for complex WHERE clauses and raw SQL execution for custom database operations.

Capabilities

Comparison Operators

Basic comparison operators for WHERE clauses.

interface OpTypes {
  /** Equals (=) */
  eq: symbol;
  /** Not equals (!=) */
  ne: symbol;
  /** Greater than (>) */
  gt: symbol;
  /** Greater than or equal (>=) */
  gte: symbol;
  /** Less than (<) */
  lt: symbol;
  /** Less than or equal (<=) */
  lte: symbol;
  /** IS (for null checks) */
  is: symbol;
  /** NOT */
  not: symbol;
  /** BETWEEN */
  between: symbol;
  /** NOT BETWEEN */
  notBetween: symbol;
  /** IN */
  in: symbol;
  /** NOT IN */
  notIn: symbol;
}

const Op: OpTypes;

Usage Examples:

import { Op } from "sequelize";

// Equals
const users = await User.findAll({
  where: {
    age: { [Op.eq]: 25 }
    // Or simply: age: 25
  }
});

// Not equals
const users = await User.findAll({
  where: {
    status: { [Op.ne]: 'inactive' }
  }
});

// Greater than / Less than
const users = await User.findAll({
  where: {
    age: { [Op.gt]: 18 },
    salary: { [Op.lte]: 50000 }
  }
});

// Between
const users = await User.findAll({
  where: {
    age: { [Op.between]: [18, 65] },
    salary: { [Op.notBetween]: [30000, 40000] }
  }
});

// In / Not In
const users = await User.findAll({
  where: {
    role: { [Op.in]: ['admin', 'moderator'] },
    status: { [Op.notIn]: ['banned', 'suspended'] }
  }
});

// IS / NOT (for null values)
const users = await User.findAll({
  where: {
    deletedAt: { [Op.is]: null },
    profilePicture: { [Op.not]: null }
  }
});

String Operators

Operators for string pattern matching and text search.

interface OpTypes {
  /** LIKE */
  like: symbol;
  /** NOT LIKE */
  notLike: symbol;
  /** ILIKE (case insensitive, PostgreSQL) */
  iLike: symbol;
  /** NOT ILIKE */
  notILike: symbol;
  /** LIKE 'value%' */
  startsWith: symbol;
  /** LIKE '%value' */
  endsWith: symbol;
  /** LIKE '%value%' */
  substring: symbol;
  /** REGEXP (MySQL/PostgreSQL) */
  regexp: symbol;
  /** NOT REGEXP */
  notRegexp: symbol;
  /** ~* (case insensitive regexp, PostgreSQL) */
  iRegexp: symbol;
  /** !~* (case insensitive not regexp, PostgreSQL) */
  notIRegexp: symbol;
}

Usage Examples:

// LIKE patterns
const users = await User.findAll({
  where: {
    firstName: { [Op.like]: 'John%' },      // Starts with "John"
    lastName: { [Op.notLike]: '%test%' },   // Doesn't contain "test"
    email: { [Op.iLike]: '%@COMPANY.COM' }  // Case insensitive (PostgreSQL)
  }
});

// Convenience string operators
const users = await User.findAll({
  where: {
    firstName: { [Op.startsWith]: 'John' },     // LIKE 'John%'
    lastName: { [Op.endsWith]: 'son' },         // LIKE '%son'
    bio: { [Op.substring]: 'developer' }        // LIKE '%developer%'
  }
});

// Regular expressions
const users = await User.findAll({
  where: {
    phone: { [Op.regexp]: '^\\d{3}-\\d{3}-\\d{4}$' },  // MySQL/PostgreSQL
    username: { [Op.iRegexp]: '^[a-z]+$' }             // PostgreSQL only
  }
});

Logical Operators

Operators for combining multiple conditions.

interface OpTypes {
  /** AND */
  and: symbol;
  /** OR */
  or: symbol;
}

Usage Examples:

// AND (implicit by default)
const users = await User.findAll({
  where: {
    age: { [Op.gte]: 18 },
    isActive: true
    // Implicit AND between conditions
  }
});

// Explicit AND
const users = await User.findAll({
  where: {
    [Op.and]: [
      { age: { [Op.gte]: 18 } },
      { isActive: true }
    ]
  }
});

// OR
const users = await User.findAll({
  where: {
    [Op.or]: [
      { firstName: 'John' },
      { lastName: 'Doe' }
    ]
  }
});

// Complex combinations
const users = await User.findAll({
  where: {
    [Op.and]: [
      { isActive: true },
      {
        [Op.or]: [
          { role: 'admin' },
          { 
            [Op.and]: [
              { role: 'user' },
              { verified: true }
            ]
          }
        ]
      }
    ]
  }
});

Array Operators

Operators for array operations (PostgreSQL primarily).

interface OpTypes {
  /** @> (array contains) */
  contains: symbol;
  /** <@ (array contained by) */
  contained: symbol;
  /** && (array overlap) */
  overlap: symbol;
  /** ANY */
  any: symbol;
  /** ALL */
  all: symbol;
}

Usage Examples:

// PostgreSQL array operations
const users = await User.findAll({
  where: {
    tags: { [Op.contains]: ['developer', 'javascript'] },  // Array contains values
    skills: { [Op.contained]: ['js', 'node', 'react'] },   // Array is subset
    interests: { [Op.overlap]: ['music', 'sports'] }       // Arrays have common elements
  }
});

// ANY operator
const users = await User.findAll({
  where: {
    age: { [Op.gt]: { [Op.any]: [18, 21, 25] } }  // age > ANY(18,21,25)
  }
});

// ALL operator  
const users = await User.findAll({
  where: {
    score: { [Op.gt]: { [Op.all]: [80, 85, 90] } }  // score > ALL(80,85,90)
  }
});

Range Operators

Operators for range types (PostgreSQL).

interface OpTypes {
  /** -|- (adjacent ranges) */
  adjacent: symbol;
  /** << (strictly left of) */
  strictLeft: symbol;
  /** >> (strictly right of) */
  strictRight: symbol;
  /** &< (does not extend right of) */
  noExtendRight: symbol;
  /** &> (does not extend left of) */
  noExtendLeft: symbol;
}

Usage Example:

// PostgreSQL range operations
const events = await Event.findAll({
  where: {
    dateRange: { [Op.overlap]: '[2023-01-01,2023-12-31)' },
    timeSlot: { [Op.adjacent]: '[09:00,10:00)' }
  }
});

Special Operators

Special operators for advanced use cases.

interface OpTypes {
  /** Column reference */
  col: symbol;
  /** @@ (full text search, PostgreSQL) */
  match: symbol;
  /** VALUES clause */
  values: symbol;
  /** Internal placeholder */
  placeholder: symbol;
}

Usage Examples:

// Column references
const users = await User.findAll({
  where: {
    firstName: { [Op.col]: 'lastName' }  // WHERE firstName = lastName
  }
});

// Full text search (PostgreSQL)
const articles = await Article.findAll({
  where: {
    searchVector: { 
      [Op.match]: sequelize.fn('plainto_tsquery', 'javascript programming')
    }
  }
});

// VALUES clause
const users = await User.findAll({
  where: {
    id: { [Op.in]: { [Op.values]: [[1], [2], [3]] } }
  }
});

Raw SQL Utilities

Utilities for building custom SQL expressions.

/**
 * Create SQL function call
 * @param fn - Function name
 * @param args - Function arguments
 * @returns Fn instance
 */
fn(fn: string, ...args: any[]): Fn;

/**
 * Reference table column
 * @param col - Column name (can include table prefix)
 * @returns Col instance
 */
col(col: string): Col;

/**
 * Create raw SQL literal
 * @param val - Raw SQL string
 * @returns Literal instance
 */
literal(val: string): Literal;

/**
 * Type casting
 * @param val - Value to cast
 * @param type - Target type
 * @returns Cast instance
 */
cast(val: any, type: string): Cast;

/**
 * WHERE condition builder
 * @param attr - Attribute or expression
 * @param comparator - Comparison operator
 * @param logic - Value to compare against
 * @returns Where instance
 */
where(attr: any, comparator: any, logic?: any): Where;

/**
 * JSON path operations
 * @param conditionsOrPath - JSON path or conditions
 * @param value - Value to compare (if path provided)
 * @returns JSON query condition
 */
json(conditionsOrPath: string | object, value?: any): object;

Usage Examples:

import { fn, col, literal, cast, where, json } from "sequelize";

// SQL functions
const users = await User.findAll({
  where: {
    age: { [Op.gt]: fn('AVG', col('age')) }
  }
});

// Column references
const users = await User.findAll({
  where: where(col('user.created_at'), Op.gt, col('user.updated_at'))
});

// Raw SQL literals
const users = await User.findAll({
  where: {
    balance: { [Op.gt]: literal('(SELECT AVG(balance) FROM users)') }
  }
});

// Type casting
const users = await User.findAll({
  where: {
    score: { [Op.gt]: cast('85.5', 'INTEGER') }
  }
});

// JSON operations
const users = await User.findAll({
  where: {
    preferences: json('theme', 'dark'),
    metadata: json({ 'user.settings.notifications': true })
  }
});

// Complex example combining utilities
const users = await User.findAll({
  where: where(
    fn('DATE', col('created_at')),
    Op.eq,
    literal('CURRENT_DATE')
  ),
  attributes: [
    'id',
    'firstName',
    [fn('COUNT', col('posts.id')), 'postCount'],
    [cast(col('age'), 'TEXT'), 'ageString']
  ],
  include: [{
    model: Post,
    attributes: []
  }],
  group: ['user.id']
});

Raw Query Execution

Execute raw SQL queries directly.

/**
 * Execute raw SQL query
 * @param sql - SQL query string
 * @param options - Query execution options
 * @returns Promise resolving to query results
 */
query(sql: string, options?: QueryOptions): Promise<any>;

interface QueryOptions {
  /** Bind parameters (named placeholders) */
  bind?: { [key: string]: any };
  /** Replacement parameters (positional placeholders) */
  replacements?: { [key: string]: any } | any[];
  /** Query type */
  type?: QueryTypes;
  /** Model to map results to */
  model?: typeof Model;
  /** Map results to model instances */
  mapToModel?: boolean;
  /** Return raw results */
  raw?: boolean;
  /** Nest results */
  nest?: boolean;
  /** Plain objects instead of instances */
  plain?: boolean;
  /** Transaction */
  transaction?: Transaction;
  /** Query logging */
  logging?: boolean | ((sql: string, timing?: number) => void);
}

Usage Examples:

// Basic raw query
const results = await sequelize.query(
  'SELECT * FROM users WHERE age > 25',
  { type: QueryTypes.SELECT }
);

// With replacements
const results = await sequelize.query(
  'SELECT * FROM users WHERE age > :age AND city = :city',
  {
    replacements: { age: 25, city: 'New York' },
    type: QueryTypes.SELECT
  }
);

// With bind parameters (safer for repeated queries)
const results = await sequelize.query(
  'SELECT * FROM users WHERE age > $1 AND city = $2',
  {
    bind: [25, 'New York'],
    type: QueryTypes.SELECT
  }
);

// Map to model instances
const users = await sequelize.query(
  'SELECT * FROM users WHERE active = true',
  {
    model: User,
    mapToModel: true
  }
);

// Insert/Update/Delete operations
await sequelize.query(
  'UPDATE users SET last_login = NOW() WHERE id = :userId',
  {
    replacements: { userId: 123 },
    type: QueryTypes.UPDATE
  }
);

Install with Tessl CLI

npx tessl i tessl/npm-sequelize

docs

associations.md

data-types.md

database-connection.md

error-handling.md

hooks.md

index.md

model-definition.md

query-operators.md

querying.md

transactions.md

tile.json