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

Transactions

Name: tessl/npm-sequelize
Author: tessl

Database transaction management with support for multiple isolation levels, savepoints, and automatic rollback on errors.

Capabilities

Creating Transactions

Methods for creating and managing database transactions.

/**
 * Create managed transaction with automatic handling
 * @param options - Transaction options
 * @param autoCallback - Function to execute within transaction
 * @returns Promise resolving to callback result
 */
transaction<T>(options: TransactionOptions, autoCallback: (t: Transaction) => PromiseLike<T>): Promise<T>;

/**
 * Create unmanaged transaction
 * @param options - Transaction options
 * @returns Promise resolving to Transaction instance
 */
transaction(options?: TransactionOptions): Promise<Transaction>;

interface TransactionOptions {
  /** Transaction isolation level */
  isolationLevel?: Transaction.ISOLATION_LEVELS;
  /** Transaction type */
  type?: Transaction.TYPES;
  /** Deferrable constraints */
  deferrable?: Deferrable;
  /** Read-only transaction */
  readOnly?: boolean;
  /** Auto-commit mode */
  autocommit?: boolean;
  /** Query logging */
  logging?: boolean | ((sql: string, timing?: number) => void);
}

Usage Examples:

// Managed transaction (recommended)
try {
  const result = await sequelize.transaction(async (t) => {
    const user = await User.create({
      firstName: 'John',
      lastName: 'Doe'
    }, { transaction: t });

    await Profile.create({
      userId: user.id,
      bio: 'Hello world'
    }, { transaction: t });

    return user;
  });
  // Transaction automatically committed
  console.log('User created:', result.id);
} catch (error) {
  // Transaction automatically rolled back
  console.error('Transaction failed:', error);
}

// Unmanaged transaction
const t = await sequelize.transaction();
try {
  const user = await User.create({
    firstName: 'Jane'
  }, { transaction: t });

  await Profile.create({
    userId: user.id,
    bio: 'Hello'
  }, { transaction: t });

  await t.commit();
} catch (error) {
  await t.rollback();
  throw error;
}

Transaction Control

Methods for controlling transaction lifecycle.

class Transaction {
  /**
   * Commit the transaction
   * @returns Promise that resolves when transaction is committed
   */
  commit(): Promise<void>;

  /**
   * Rollback the transaction
   * @returns Promise that resolves when transaction is rolled back
   */
  rollback(): Promise<void>;

  /**
   * Add callback to execute after successful commit
   * @param fn - Callback function
   */
  afterCommit(fn: (transaction: Transaction) => void): void;

  /**
   * Add callback to execute after rollback
   * @param fn - Callback function
   */
  afterRollback(fn: (transaction: Transaction) => void): void;
}

Usage Examples:

const t = await sequelize.transaction();

// Add hooks
t.afterCommit((transaction) => {
  console.log('Transaction committed successfully');
  // Send notification, clear cache, etc.
});

t.afterRollback((transaction) => {
  console.log('Transaction was rolled back');
  // Log error, cleanup resources, etc.
});

try {
  // Perform operations
  await User.create({ name: 'John' }, { transaction: t });
  await t.commit();
} catch (error) {
  await t.rollback();
}

Isolation Levels

Transaction isolation levels for controlling concurrent access.

namespace Transaction {
  enum ISOLATION_LEVELS {
    READ_UNCOMMITTED = 'READ UNCOMMITTED',
    READ_COMMITTED = 'READ COMMITTED',
    REPEATABLE_READ = 'REPEATABLE READ',
    SERIALIZABLE = 'SERIALIZABLE'
  }
}

Usage Examples:

// Different isolation levels
const t1 = await sequelize.transaction({
  isolationLevel: Transaction.ISOLATION_LEVELS.READ_COMMITTED
});

const t2 = await sequelize.transaction({
  isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE
});

// Using with managed transactions
await sequelize.transaction({
  isolationLevel: Transaction.ISOLATION_LEVELS.REPEATABLE_READ
}, async (t) => {
  // Operations within transaction
  const user = await User.findByPk(1, { transaction: t });
  user.balance += 100;
  await user.save({ transaction: t });
});

Transaction Types

Transaction types for different database behaviors.

namespace Transaction {
  enum TYPES {
    DEFERRED = 'DEFERRED',
    IMMEDIATE = 'IMMEDIATE', 
    EXCLUSIVE = 'EXCLUSIVE'
  }
}

Usage Example:

// SQLite transaction types
const t = await sequelize.transaction({
  type: Transaction.TYPES.IMMEDIATE
});

Deferrable Constraints

Control when constraints are checked within transactions.

interface Deferrable {
  INITIALLY_DEFERRED: symbol;
  INITIALLY_IMMEDIATE: symbol;
  NOT: symbol;
}

const Deferrable: Deferrable;

Usage Example:

// PostgreSQL deferrable constraints
const t = await sequelize.transaction({
  deferrable: Deferrable.INITIALLY_DEFERRED
});

// Constraints checked at transaction end
await User.create({ /* data */ }, { transaction: t });
await Profile.create({ /* data */ }, { transaction: t });
await t.commit(); // Constraints validated here

Savepoints

Create savepoints within transactions for partial rollbacks.

class Transaction {
  /**
   * Create a savepoint
   * @param name - Savepoint name
   * @returns Promise resolving when savepoint is created
   */
  createSavepoint(name: string): Promise<void>;

  /**
   * Rollback to savepoint
   * @param name - Savepoint name
   * @returns Promise resolving when rolled back to savepoint
   */
  rollbackToSavepoint(name: string): Promise<void>;

  /**
   * Release savepoint
   * @param name - Savepoint name
   * @returns Promise resolving when savepoint is released
   */
  releaseSavepoint(name: string): Promise<void>;
}

Usage Example:

await sequelize.transaction(async (t) => {
  // Create user
  const user = await User.create({ name: 'John' }, { transaction: t });

  // Create savepoint
  await t.createSavepoint('user_created');

  try {
    // Attempt risky operation
    await RiskyOperation.create({ userId: user.id }, { transaction: t });
  } catch (error) {
    // Rollback to savepoint, user creation preserved
    await t.rollbackToSavepoint('user_created');
    console.log('Risky operation failed, but user still created');
  }

  // Continue with transaction
  await Profile.create({ userId: user.id }, { transaction: t });
});

Transaction with Queries

Using transactions with various query operations.

// All query methods accept transaction option
interface QueryOptions {
  transaction?: Transaction;
}

Usage Examples:

await sequelize.transaction(async (t) => {
  // Create operations
  const user = await User.create({
    name: 'John'
  }, { transaction: t });

  const posts = await Post.bulkCreate([
    { title: 'Post 1', userId: user.id },
    { title: 'Post 2', userId: user.id }
  ], { transaction: t });

  // Update operations
  await User.update({
    lastActive: new Date()
  }, {
    where: { id: user.id },
    transaction: t
  });

  // Find operations
  const updatedUser = await User.findByPk(user.id, {
    include: [Post],
    transaction: t
  });

  // Delete operations
  await Post.destroy({
    where: { userId: user.id, status: 'draft' },
    transaction: t
  });

  // Raw queries
  await sequelize.query(
    'UPDATE users SET updated_at = NOW() WHERE id = :userId',
    {
      replacements: { userId: user.id },
      transaction: t
    }
  );
});

Concurrent Transaction Handling

Patterns for handling concurrent transactions and conflicts.

// Optimistic locking with version field
class User extends Model {}
User.init({
  name: DataTypes.STRING,
  version: {
    type: DataTypes.INTEGER,
    defaultValue: 0
  }
});

// Handle optimistic lock errors
try {
  await sequelize.transaction(async (t) => {
    const user = await User.findByPk(1, { transaction: t });
    user.name = 'Updated Name';
    user.version += 1;
    
    await user.save({
      transaction: t,
      where: { version: user.previous('version') } // Optimistic lock
    });
  });
} catch (error) {
  if (error instanceof OptimisticLockError) {
    console.log('Record was modified by another transaction');
    // Retry logic here
  }
}

Transaction Best Practices

Recommended patterns for transaction usage.

// Connection pooling considerations
const sequelize = new Sequelize(database, username, password, {
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
});

// Batch operations in transactions
async function createUsersInBatch(userData: any[]) {
  return await sequelize.transaction(async (t) => {
    const users = [];
    
    // Process in chunks to avoid long-running transactions
    for (let i = 0; i < userData.length; i += 100) {
      const chunk = userData.slice(i, i + 100);
      const chunkUsers = await User.bulkCreate(chunk, { transaction: t });
      users.push(...chunkUsers);
    }
    
    return users;
  });
}

// Error handling with specific rollback logic
async function transferFunds(fromUserId: number, toUserId: number, amount: number) {
  return await sequelize.transaction(async (t) => {
    const fromUser = await User.findByPk(fromUserId, {
      transaction: t,
      lock: Transaction.LOCK.UPDATE // Row-level locking
    });
    
    const toUser = await User.findByPk(toUserId, {
      transaction: t,
      lock: Transaction.LOCK.UPDATE
    });
    
    if (fromUser.balance < amount) {
      throw new Error('Insufficient funds');
    }
    
    fromUser.balance -= amount;
    toUser.balance += amount;
    
    await fromUser.save({ transaction: t });
    await toUser.save({ transaction: t });
    
    // Log transaction
    await TransactionLog.create({
      fromUserId,
      toUserId,
      amount,
      timestamp: new Date()
    }, { transaction: t });
    
    return { fromUser, toUser };
  });
}

Row-Level Locking

Transaction locking mechanisms for concurrent access control.

namespace Transaction {
  enum LOCK {
    UPDATE = 'UPDATE',
    SHARE = 'SHARE',
    KEY_SHARE = 'KEY SHARE',
    NO_KEY_UPDATE = 'NO KEY UPDATE'
  }
}

interface FindOptions {
  /** Row-level lock type */
  lock?: Transaction.LOCK | boolean;
  /** Skip locked rows */
  skipLocked?: boolean;
}

Usage Examples:

// Row-level locking for updates
await sequelize.transaction(async (t) => {
  // Lock user row for update
  const user = await User.findByPk(1, {
    lock: Transaction.LOCK.UPDATE,
    transaction: t
  });
  
  // Modify user (other transactions will wait)
  user.balance += 100;
  await user.save({ transaction: t });
});

// Shared lock for reading
const users = await User.findAll({
  where: { isActive: true },
  lock: Transaction.LOCK.SHARE,
  transaction: t
});

// Skip locked rows (PostgreSQL)
const availableUsers = await User.findAll({
  lock: Transaction.LOCK.UPDATE,
  skipLocked: true,
  transaction: t
});

Nested Transactions with Savepoints

Advanced savepoint management for complex transaction scenarios.

class Transaction {
  /**
   * Create a savepoint within the transaction
   * @param name - Savepoint name
   * @returns Promise resolving when savepoint is created
   */
  createSavepoint(name: string): Promise<void>;

  /**
   * Rollback to a specific savepoint
   * @param name - Savepoint name
   * @returns Promise resolving when rolled back to savepoint
   */
  rollbackToSavepoint(name: string): Promise<void>;

  /**
   * Release a savepoint (remove it)
   * @param name - Savepoint name
   * @returns Promise resolving when savepoint is released
   */
  releaseSavepoint(name: string): Promise<void>;
}

Usage Examples:

// Complex transaction with multiple savepoints
await sequelize.transaction(async (t) => {
  // Initial operations
  const user = await User.create({ name: 'John' }, { transaction: t });
  
  // First savepoint
  await t.createSavepoint('after_user_creation');
  
  try {
    // Risky operation 1
    await RiskyService.operation1(user.id, { transaction: t });
    
    // Second savepoint
    await t.createSavepoint('after_operation1');
    
    try {
      // Risky operation 2
      await RiskyService.operation2(user.id, { transaction: t });
      
      // Success - release savepoints
      await t.releaseSavepoint('after_operation1');
      await t.releaseSavepoint('after_user_creation');
      
    } catch (error) {
      // Rollback operation 2, keep operation 1
      console.log('Operation 2 failed, rolling back to after operation 1');
      await t.rollbackToSavepoint('after_operation1');
    }
    
  } catch (error) {
    // Rollback everything except user creation
    console.log('Operation 1 failed, rolling back to after user creation');
    await t.rollbackToSavepoint('after_user_creation');
  }
  
  // Continue with other operations...
  await Profile.create({ userId: user.id }, { transaction: t });
});

Transaction Callback Hooks

Advanced transaction lifecycle hooks.

class Transaction {
  /**
   * Add callback to execute after successful commit
   * @param fn - Callback function
   */
  afterCommit(fn: (transaction: Transaction) => void | Promise<void>): void;

  /**
   * Add callback to execute after rollback
   * @param fn - Callback function
   */
  afterRollback(fn: (transaction: Transaction) => void | Promise<void>): void;
}

Usage Examples:

// Transaction with lifecycle callbacks
const t = await sequelize.transaction();

// Setup callbacks
t.afterCommit(async (transaction) => {
  // Clear cache after successful commit
  await cache.clear(['users', 'posts']);
  
  // Send notifications
  await notificationService.sendUpdates();
  
  // Log successful transaction
  console.log('Transaction committed successfully');
});

t.afterRollback(async (transaction) => {
  // Log rollback
  console.log('Transaction was rolled back');
  
  // Send error notifications
  await notificationService.sendErrorAlert();
  
  // Cleanup any side effects
  await cleanupService.rollbackSideEffects();
});

try {
  // Perform operations
  const user = await User.create({ name: 'John' }, { transaction: t });
  const profile = await Profile.create({ userId: user.id }, { transaction: t });
  
  await t.commit(); // afterCommit callbacks will run
} catch (error) {
  await t.rollback(); // afterRollback callbacks will run
}

Transaction Context Management

Advanced patterns for transaction context management.

// Transaction context utilities
interface TransactionContext {
  /** Current transaction */
  transaction?: Transaction;
  /** User performing the operation */
  userId?: number;
  /** Request context */
  requestId?: string;
  /** Additional metadata */
  metadata?: any;
}

// Utility functions for transaction management
class TransactionManager {
  static async withTransaction<T>(
    operation: (context: TransactionContext) => Promise<T>,
    options?: TransactionOptions & { userId?: number; requestId?: string }
  ): Promise<T> {
    return await sequelize.transaction(async (t) => {
      const context: TransactionContext = {
        transaction: t,
        userId: options?.userId,
        requestId: options?.requestId || generateRequestId(),
        metadata: {}
      };
      
      return await operation(context);
    });
  }
  
  static async withSavepoint<T>(
    transaction: Transaction,
    savepointName: string,
    operation: () => Promise<T>
  ): Promise<T> {
    await transaction.createSavepoint(savepointName);
    
    try {
      const result = await operation();
      await transaction.releaseSavepoint(savepointName);
      return result;
    } catch (error) {
      await transaction.rollbackToSavepoint(savepointName);
      throw error;
    }
  }
}

Usage Examples:

// Use transaction manager for complex operations
const result = await TransactionManager.withTransaction(async (context) => {
  const { transaction, userId, requestId } = context;
  
  // Create user
  const user = await User.create({
    name: 'John',
    createdBy: userId
  }, { transaction });
  
  // Use savepoint for risky operation
  await TransactionManager.withSavepoint(transaction, 'before_profile', async () => {
    const profile = await Profile.create({
      userId: user.id,
      bio: 'User bio'
    }, { transaction });
    
    // This might fail, but user creation will be preserved
    await ExternalService.createProfile(profile.id);
  });
  
  // Log the operation
  await AuditLog.create({
    action: 'user_creation',
    userId: user.id,
    performedBy: userId,
    requestId,
    timestamp: new Date()
  }, { transaction });
  
  return user;
}, { 
  userId: 123, 
  requestId: 'req-456',
  isolationLevel: Transaction.ISOLATION_LEVELS.READ_COMMITTED 
});

Transaction Retry Pattern

Automatic retry logic for transaction conflicts.

// Transaction retry utility
class TransactionRetry {
  static async withRetry<T>(
    operation: () => Promise<T>,
    options: {
      maxRetries?: number;
      baseDelay?: number;
      maxDelay?: number;
      retryOnError?: (error: Error) => boolean;
    } = {}
  ): Promise<T> {
    const {
      maxRetries = 3,
      baseDelay = 100,
      maxDelay = 5000,
      retryOnError = (error) => 
        error.name === 'SequelizeOptimisticLockError' ||
        error.name === 'SequelizeTimeoutError' ||
        error.message.includes('deadlock')
    } = options;

    let attempt = 1;
    let delay = baseDelay;

    while (attempt <= maxRetries) {
      try {
        return await operation();
      } catch (error) {
        if (attempt === maxRetries || !retryOnError(error)) {
          throw error;
        }

        console.log(`Transaction failed on attempt ${attempt}, retrying in ${delay}ms...`);
        await new Promise(resolve => setTimeout(resolve, delay));
        
        attempt++;
        delay = Math.min(delay * 2, maxDelay); // Exponential backoff
      }
    }

    throw new Error('Max retries exceeded');
  }
}

Usage Examples:

// Retry transaction on conflicts
const user = await TransactionRetry.withRetry(async () => {
  return await sequelize.transaction(async (t) => {
    const user = await User.findByPk(1, {
      lock: Transaction.LOCK.UPDATE,
      transaction: t
    });
    
    if (!user) {
      throw new Error('User not found');
    }
    
    // This might cause optimistic lock errors in high concurrency
    user.balance += 100;
    user.version += 1; // Manual version increment
    
    await user.save({
      transaction: t,
      where: { version: user.previous('version') } // Optimistic locking
    });
    
    return user;
  });
}, {
  maxRetries: 5,
  baseDelay: 50,
  retryOnError: (error) => 
    error.name === 'SequelizeOptimisticLockError' ||
    error.message.includes('version')
});

Install with Tessl CLI