tessl/npm-postgres

Fastest full featured PostgreSQL client for Node.js and Deno with tagged template literals, transactions, streaming, and logical replication support

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Overview

Eval results

Files

Transactions

Name: tessl/npm-postgres
Author: tessl

Complete transaction support including nested transactions via savepoints and two-phase commit preparation.

Capabilities

Basic Transactions

Execute multiple queries within a single database transaction with automatic rollback on errors.

/**
 * Execute function within a transaction
 * @param fn - Function to execute within transaction scope
 * @returns Promise resolving to function result
 */
begin<T>(
  fn: (sql: TransactionSql) => T | Promise<T>
): Promise<T>;

/**
 * Execute function within a transaction with options
 * @param options - Transaction options (isolation level, etc.)
 * @param fn - Function to execute within transaction scope
 * @returns Promise resolving to function result
 */
begin<T>(
  options: string,
  fn: (sql: TransactionSql) => T | Promise<T>
): Promise<T>;

interface TransactionSql extends Sql {
  savepoint<T>(fn: (sql: TransactionSql) => T | Promise<T>): Promise<T>;
  savepoint<T>(name: string, fn: (sql: TransactionSql) => T | Promise<T>): Promise<T>;
  prepare(name: string): void;
}

Usage Examples:

// Basic transaction
const result = await sql.begin(async (sql) => {
  const user = await sql`
    INSERT INTO users (name, email) 
    VALUES (${name}, ${email}) 
    RETURNING id
  `;
  
  await sql`
    INSERT INTO user_profiles (user_id, bio) 
    VALUES (${user[0].id}, ${bio})
  `;
  
  return user[0];
});

// Transaction with options
const result = await sql.begin("ISOLATION LEVEL SERIALIZABLE", async (sql) => {
  const balance = await sql`
    SELECT balance FROM accounts WHERE id = ${accountId}
  `;
  
  if (balance[0].balance < amount) {
    throw new Error("Insufficient funds");
  }
  
  await sql`
    UPDATE accounts SET balance = balance - ${amount} WHERE id = ${accountId}
  `;
  
  return balance[0].balance - amount;
});

// Multiple operations
const result = await sql.begin(async (sql) => {
  // All these operations will be in the same transaction
  const order = await sql`
    INSERT INTO orders (user_id, total) 
    VALUES (${userId}, ${total}) 
    RETURNING id
  `;
  
  for (const item of items) {
    await sql`
      INSERT INTO order_items (order_id, product_id, quantity, price)
      VALUES (${order[0].id}, ${item.productId}, ${item.quantity}, ${item.price})
    `;
  }
  
  await sql`
    UPDATE products 
    SET stock = stock - ${item.quantity} 
    WHERE id = ${item.productId}
  `;
  
  return order[0];
});

Savepoints (Nested Transactions)

Create nested transaction scopes with savepoints for granular rollback control.

/**
 * Create an unnamed savepoint within transaction
 * @param fn - Function to execute within savepoint scope
 * @returns Promise resolving to function result
 */
savepoint<T>(
  fn: (sql: TransactionSql) => T | Promise<T>
): Promise<T>;

/**
 * Create a named savepoint within transaction
 * @param name - Savepoint name for debugging
 * @param fn - Function to execute within savepoint scope
 * @returns Promise resolving to function result
 */
savepoint<T>(
  name: string,
  fn: (sql: TransactionSql) => T | Promise<T>
): Promise<T>;

Usage Examples:

const result = await sql.begin(async (sql) => {
  // Main transaction operations
  const user = await sql`
    INSERT INTO users (name, email) 
    VALUES (${name}, ${email}) 
    RETURNING id
  `;
  
  // Nested transaction with savepoint
  try {
    await sql.savepoint("user_profile", async (sql) => {
      await sql`
        INSERT INTO user_profiles (user_id, bio) 
        VALUES (${user[0].id}, ${bio})
      `;
      
      // This might fail, but won't rollback the user insert
      await sql`
        INSERT INTO user_settings (user_id, theme) 
        VALUES (${user[0].id}, ${theme})
      `;
    });
  } catch (error) {
    console.log("Profile creation failed, but user was created");
  }
  
  return user[0];
});

// Multiple savepoints
const result = await sql.begin(async (sql) => {
  const order = await sql`
    INSERT INTO orders (user_id) VALUES (${userId}) RETURNING id
  `;
  
  for (const item of items) {
    try {
      await sql.savepoint(async (sql) => {
        // Try to add item to order
        await sql`
          INSERT INTO order_items (order_id, product_id, quantity)
          VALUES (${order[0].id}, ${item.id}, ${item.quantity})
        `;
        
        // Update stock
        await sql`
          UPDATE products 
          SET stock = stock - ${item.quantity} 
          WHERE id = ${item.id} AND stock >= ${item.quantity}
        `;
      });
    } catch (error) {
      console.log(`Failed to add item ${item.id}, continuing with other items`);
    }
  }
  
  return order[0];
});

Two-Phase Commit

Prepare transactions for two-phase commit scenarios across multiple databases.

/**
 * Prepare transaction for two-phase commit
 * @param name - Transaction identifier for prepare
 */
prepare(name: string): void;

Usage Examples:

// Prepare transaction for two-phase commit
const result = await sql.begin(async (sql) => {
  await sql`
    INSERT INTO orders (user_id, total) 
    VALUES (${userId}, ${total})
  `;
  
  await sql`
    UPDATE inventory 
    SET quantity = quantity - ${orderQuantity} 
    WHERE product_id = ${productId}
  `;
  
  // Prepare for two-phase commit instead of committing
  sql.prepare("order_transaction_123");
  
  return "prepared";
});

// Later, commit or rollback the prepared transaction
await sql`COMMIT PREPARED 'order_transaction_123'`;
// or
await sql`ROLLBACK PREPARED 'order_transaction_123'`;

Error Handling in Transactions

Understanding how errors are handled within transactions and savepoints.

Usage Examples:

try {
  const result = await sql.begin(async (sql) => {
    const user = await sql`
      INSERT INTO users (name, email) 
      VALUES (${name}, ${email}) 
      RETURNING id
    `;
    
    // This will cause transaction to rollback if it fails
    await sql`
      INSERT INTO user_profiles (user_id, bio) 
      VALUES (${user[0].id}, ${bio})
    `;
    
    return user[0];
  });
} catch (error) {
  // Both user and profile inserts will be rolled back
  console.error("Transaction failed:", error);
}

// Handling errors in savepoints
const result = await sql.begin(async (sql) => {
  const user = await sql`
    INSERT INTO users (name, email) 
    VALUES (${name}, ${email}) 
    RETURNING id
  `;
  
  // Try optional operations in savepoint
  try {
    await sql.savepoint(async (sql) => {
      await sql`
        INSERT INTO user_profiles (user_id, bio) 
        VALUES (${user[0].id}, ${bio})
      `;
      
      // If this fails, only the savepoint is rolled back
      await sql`
        INSERT INTO user_preferences (user_id, theme)
        VALUES (${user[0].id}, ${theme})
      `;
    });
  } catch (error) {
    console.log("Optional operations failed, but user was created");
  }
  
  return user[0]; // User will still be created
});

Transaction Isolation Levels

Configure transaction isolation levels for concurrent access control.

Usage Examples:

// Serializable isolation
await sql.begin("ISOLATION LEVEL SERIALIZABLE", async (sql) => {
  // Highest isolation level - prevents all phenomena
  const result = await sql`SELECT * FROM sensitive_data WHERE id = ${id}`;
  await sql`UPDATE sensitive_data SET value = ${newValue} WHERE id = ${id}`;
  return result;
});

// Repeatable read isolation  
await sql.begin("ISOLATION LEVEL REPEATABLE READ", async (sql) => {
  // Prevents dirty and non-repeatable reads
  const balance1 = await sql`SELECT balance FROM accounts WHERE id = ${accountId}`;
  // ... other operations ...
  const balance2 = await sql`SELECT balance FROM accounts WHERE id = ${accountId}`;
  // balance1 and balance2 will be the same
});

// Read committed (default)
await sql.begin("ISOLATION LEVEL READ COMMITTED", async (sql) => {
  // Default level - prevents dirty reads only
  const result = await sql`SELECT * FROM data WHERE updated_at > ${date}`;
  return result;
});

// Read uncommitted
await sql.begin("ISOLATION LEVEL READ UNCOMMITTED", async (sql) => {
  // Lowest level - allows all phenomena (rarely used)
  const result = await sql`SELECT COUNT(*) FROM large_table`;
  return result;
});

Transaction Best Practices

Performance Considerations:

// Keep transactions short
await sql.begin(async (sql) => {
  // Do database operations quickly
  const result = await sql`UPDATE users SET last_login = NOW() WHERE id = ${id}`;
  // Don't do heavy computation here
  return result;
});

// Batch operations when possible
await sql.begin(async (sql) => {
  // Insert multiple records in one operation
  await sql`
    INSERT INTO logs (user_id, action, timestamp)
    VALUES ${sql(logEntries.map(entry => [entry.userId, entry.action, entry.timestamp]))}
  `;
});

// Use savepoints for optional operations
await sql.begin(async (sql) => {
  // Critical operation
  const order = await sql`
    INSERT INTO orders (user_id, total) VALUES (${userId}, ${total}) 
    RETURNING id
  `;
  
  // Optional operations that shouldn't fail the transaction
  try {
    await sql.savepoint(async (sql) => {
      await sql`INSERT INTO audit_log (action, order_id) VALUES ('created', ${order[0].id})`;
      await sql`UPDATE user_stats SET orders_count = orders_count + 1 WHERE user_id = ${userId}`;
    });
  } catch (error) {
    console.warn("Audit operations failed, but order was created");
  }
  
  return order[0];
});

Install with Tessl CLI