Querying

/**
 * Find all records matching the query
 * @param options - Query options
 * @returns Promise resolving to array of model instances
 */
static findAll(options?: FindOptions): Promise<Model[]>;

/**
 * Find one record matching the query
 * @param options - Query options
 * @returns Promise resolving to model instance or null
 */
static findOne(options?: FindOptions): Promise<Model | null>;

/**
 * Find record by primary key
 * @param identifier - Primary key value
 * @param options - Query options
 * @returns Promise resolving to model instance or null
 */
static findByPk(identifier: Identifier, options?: Omit<FindOptions, 'where'>): Promise<Model | null>;

/**
 * Find and count all records
 * @param options - Query options
 * @returns Promise resolving to object with rows and count
 */
static findAndCountAll(options?: FindOptions): Promise<{ rows: Model[]; count: number }>;

interface FindOptions {
  /** WHERE clause conditions */
  where?: WhereOptions;
  /** Attributes to select */
  attributes?: FindAttributeOptions;
  /** Include related models */
  include?: Includeable | Includeable[];
  /** ORDER BY clause */
  order?: Order;
  /** GROUP BY clause */
  group?: GroupOption;
  /** HAVING clause */
  having?: WhereOptions;
  /** LIMIT clause */
  limit?: number;
  /** OFFSET clause */
  offset?: number;
  /** Subquery options */
  subQuery?: boolean;
  /** Include paranoid (soft-deleted) records */
  paranoid?: boolean;
  /** Use raw queries */
  raw?: boolean;
  /** Transaction */
  transaction?: Transaction;
  /** Query logging */
  logging?: boolean | ((sql: string, timing?: number) => void);
}

type Identifier = string | number | Buffer;
type Order = string | Fn | Col | [string | Fn | Col, string] | [string | Fn | Col, string, string];
type GroupOption = string | Fn | Col | (string | Fn | Col)[];

import { Op } from "sequelize";

// Find all users
const users = await User.findAll();

// Find with WHERE conditions
const activeUsers = await User.findAll({
  where: {
    isActive: true,
    age: {
      [Op.gte]: 18
    }
  }
});

// Find with complex conditions
const users = await User.findAll({
  where: {
    [Op.or]: [
      { firstName: 'John' },
      { lastName: 'Doe' }
    ],
    email: {
      [Op.like]: '%@company.com'
    }
  }
});

// Find with specific attributes
const users = await User.findAll({
  attributes: ['id', 'firstName', 'email']
});

// Find with ordering and pagination
const users = await User.findAll({
  order: [['createdAt', 'DESC']],
  limit: 10,
  offset: 20
});

// Find by primary key
const user = await User.findByPk(123);

// Find one with conditions
const user = await User.findOne({
  where: { email: 'user@example.com' }
});

// Find and count
const result = await User.findAndCountAll({
  where: { isActive: true },
  limit: 10
});
console.log(`Found ${result.count} users, showing ${result.rows.length}`);

/**
 * Build new model instance without saving
 * @param values - Attribute values
 * @param options - Build options
 * @returns New model instance
 */
static build(values?: CreationAttributes, options?: BuildOptions): Model;

/**
 * Create and save new record
 * @param values - Attribute values
 * @param options - Create options
 * @returns Promise resolving to created model instance
 */
static create(values?: CreationAttributes, options?: CreateOptions): Promise<Model>;

/**
 * Create multiple records in bulk
 * @param records - Array of attribute objects
 * @param options - Bulk create options
 * @returns Promise resolving to array of created instances
 */
static bulkCreate(records: CreationAttributes[], options?: BulkCreateOptions): Promise<Model[]>;

/**
 * Find existing record or build new one (without saving)
 * @param options - Find or build options
 * @returns Promise resolving to [instance, created] tuple
 */
static findOrBuild(options: FindOrBuildOptions): Promise<[Model, boolean]>;

/**
 * Find existing record or create new one
 * @param options - Find or create options
 * @returns Promise resolving to [instance, created] tuple
 */
static findOrCreate(options: FindOrCreateOptions): Promise<[Model, boolean]>;

interface CreateOptions {
  /** Fields to include in INSERT */
  fields?: string[];
  /** Skip validation */
  validate?: boolean;
  /** Include hooks */
  hooks?: boolean;
  /** Transaction */
  transaction?: Transaction;
  /** Query logging */
  logging?: boolean | ((sql: string, timing?: number) => void);
}

interface BulkCreateOptions extends CreateOptions {
  /** Skip duplicate errors */
  ignoreDuplicates?: boolean;
  /** Update on duplicate key */
  updateOnDuplicate?: string[];
  /** Return created instances */
  returning?: boolean | string[];
}

interface FindOrBuildOptions {
  /** WHERE clause for finding record */
  where: WhereOptions;
  /** Default values for building new instance */
  defaults?: CreationAttributes;
  /** Transaction */
  transaction?: Transaction;
}

// Build instance without saving
const user = User.build({
  firstName: 'John',
  lastName: 'Doe',
  email: 'john@example.com'
});
await user.save();

// Create and save in one step
const user = await User.create({
  firstName: 'Jane',
  lastName: 'Smith',
  email: 'jane@example.com'
});

// Bulk create multiple records
const users = await User.bulkCreate([
  { firstName: 'Alice', email: 'alice@example.com' },
  { firstName: 'Bob', email: 'bob@example.com' },
  { firstName: 'Charlie', email: 'charlie@example.com' }
]);

// Find or build (without saving)
const [user, built] = await User.findOrBuild({
  where: { email: 'user@example.com' },
  defaults: {
    firstName: 'New',
    lastName: 'User'
  }
});

if (built) {
  console.log('Built new user instance (not saved)');
  await user.save(); // Manual save required
} else {
  console.log('Found existing user');
}

// Find or create
const [user2, created] = await User.findOrCreate({
  where: { email: 'user2@example.com' },
  defaults: {
    firstName: 'New',
    lastName: 'User'
  }
});

if (created) {
  console.log('Created new user');
} else {
  console.log('Found existing user');
}

/**
 * Update multiple records
 * @param values - Values to update
 * @param options - Update options
 * @returns Promise resolving to [affectedCount, affectedRows]
 */
static update(values: Partial<Attributes>, options: UpdateOptions): Promise<[number, Model[]]>;

/**
 * Update or create record (upsert)
 * @param values - Values to insert or update
 * @param options - Upsert options
 * @returns Promise resolving to [instance, created] tuple
 */
static upsert(values: CreationAttributes, options?: UpsertOptions): Promise<[Model, boolean]>;

/**
 * Increment numeric fields
 * @param fields - Fields to increment
 * @param options - Increment options
 * @returns Promise resolving to updated instances
 */
static increment(fields: string | string[] | { [key: string]: number }, options: IncrementDecrementOptions): Promise<Model[]>;

/**
 * Decrement numeric fields
 * @param fields - Fields to decrement
 * @param options - Decrement options
 * @returns Promise resolving to updated instances
 */
static decrement(fields: string | string[] | { [key: string]: number }, options: IncrementDecrementOptions): Promise<Model[]>;

interface UpdateOptions {
  /** WHERE clause for records to update */
  where: WhereOptions;
  /** Include paranoid records */
  paranoid?: boolean;
  /** Fields to update */
  fields?: string[];
  /** Skip validation */
  validate?: boolean;
  /** Include hooks */
  hooks?: boolean;
  /** Return updated instances */
  returning?: boolean | string[];
  /** Transaction */
  transaction?: Transaction;
}

// Update multiple records
const [affectedCount] = await User.update(
  { isActive: false },
  { where: { lastLoginAt: { [Op.lt]: new Date('2023-01-01') } } }
);

// Upsert (insert or update)
const [user, created] = await User.upsert({
  id: 1,
  firstName: 'John',
  email: 'john@example.com'
});

// Increment fields
await User.increment(['loginCount'], {
  where: { id: 1 }
});

// Increment with custom amounts
await User.increment({
  loginCount: 1,
  score: 10
}, {
  where: { id: 1 }
});

/**
 * Delete records from database
 * @param options - Destroy options
 * @returns Promise resolving to number of deleted records
 */
static destroy(options: DestroyOptions): Promise<number>;

/**
 * Restore soft-deleted records
 * @param options - Restore options
 * @returns Promise resolving when records are restored
 */
static restore(options: RestoreOptions): Promise<void>;

/**
 * Truncate table (delete all records)
 * @param options - Truncate options
 * @returns Promise resolving when table is truncated
 */
static truncate(options?: TruncateOptions): Promise<void>;

interface DestroyOptions {
  /** WHERE clause for records to delete */
  where?: WhereOptions;
  /** Force delete (ignore paranoid) */
  force?: boolean;
  /** Include hooks */
  hooks?: boolean;
  /** Truncate instead of delete */
  truncate?: boolean;
  /** CASCADE delete */
  cascade?: boolean;
  /** Transaction */
  transaction?: Transaction;
}

interface RestoreOptions {
  /** WHERE clause for records to restore */
  where?: WhereOptions;
  /** Include hooks */
  hooks?: boolean;
  /** Transaction */
  transaction?: Transaction;
}

// Delete records matching condition
const deletedCount = await User.destroy({
  where: {
    isActive: false,
    lastLoginAt: { [Op.lt]: new Date('2022-01-01') }
  }
});

// Force delete (ignore paranoid mode)
await User.destroy({
  where: { id: 1 },
  force: true
});

// Restore soft-deleted records
await User.restore({
  where: { id: 1 }
});

// Truncate table
await User.truncate();

/**
 * Count records
 * @param options - Count options
 * @returns Promise resolving to count
 */
static count(options?: CountOptions): Promise<number>;

/**
 * Find maximum value
 * @param field - Field to find max
 * @param options - Aggregate options
 * @returns Promise resolving to maximum value
 */
static max(field: string, options?: AggregateOptions): Promise<any>;

/**
 * Find minimum value
 * @param field - Field to find min
 * @param options - Aggregate options
 * @returns Promise resolving to minimum value
 */
static min(field: string, options?: AggregateOptions): Promise<any>;

/**
 * Sum values
 * @param field - Field to sum
 * @param options - Aggregate options
 * @returns Promise resolving to sum
 */
static sum(field: string, options?: AggregateOptions): Promise<number>;

interface CountOptions {
  /** WHERE clause */
  where?: WhereOptions;
  /** Include related models */
  include?: Includeable | Includeable[];
  /** Count distinct values */
  distinct?: boolean;
  /** Field to count */
  col?: string;
}

interface AggregateOptions {
  /** WHERE clause */
  where?: WhereOptions;
  /** Include related models */
  include?: Includeable | Includeable[];
  /** Data type for result */
  dataType?: DataType;
}

// Count all users
const userCount = await User.count();

// Count with conditions
const activeUserCount = await User.count({
  where: { isActive: true }
});

// Count distinct values
const uniqueEmails = await User.count({
  col: 'email',
  distinct: true
});

// Aggregate functions
const maxAge = await User.max('age');
const minAge = await User.min('age');
const totalSalary = await User.sum('salary', {
  where: { department: 'Engineering' }
});

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

interface QueryOptions {
  /** Query type */
  type?: QueryTypes;
  /** Bind parameters */
  bind?: { [key: string]: any };
  /** Replacement parameters */
  replacements?: { [key: string]: any };
  /** Model to map results to */
  model?: typeof Model;
  /** Map to instances */
  mapToModel?: boolean;
  /** Include raw results */
  raw?: boolean;
  /** Transaction */
  transaction?: Transaction;
}

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

// Raw query with model mapping
const users = await sequelize.query(
  'SELECT * FROM users WHERE department = ?',
  {
    replacements: ['Engineering'],
    model: User,
    mapToModel: true
  }
);