Optimizing Database Access with Loaders

What Are Loaders?

Loaders are specialized functions that dramatically optimize how your event handlers fetch data from the database. They provide a powerful mechanism to:

• Batch multiple database requests into single operations
• Cache database results in memory for instant access
• Reduce I/O operations, which are typically the primary performance bottleneck in indexing

By using loaders, you can reduce database roundtrips from thousands to just a handful, especially when processing large batches of events.

Why Use Loaders?

The Database I/O Problem

Consider this common pattern in event handlers:

// Without loaders: Inefficient database access
ERC20.Transfer.handler(async ({ event, context }) => {
  const sender = await context.Account.get(event.params.from);
  const receiver = await context.Account.get(event.params.to);

  // Process the transfer...
});

The Performance Challenge: If you're processing 5,000 transfer events, each with unique from and to addresses, this results in 10,000 total database roundtrips—one for each sender and receiver lookup (2 per event × 5,000 events). This creates a significant bottleneck that slows down your entire indexing process.

The External Calls Problem

To ensure consistent and reliable data, all handlers execute synchronously in on-chain order. This means external calls can dramatically increase processing time:

// Without loaders: Blocking external calls
ERC20.Transfer.handler(async ({ event, context }) => {
  const metadata = await fetch(
    `https://api.example.com/metadata/${event.params.from}`
  );

  // Process the transfer...
});

The Performance Challenge: If you're processing 5,000 transfer events, each with an external call, this results in 5,000 sequential external calls—each waiting for the previous one to complete. This can turn a fast indexing process into a slow, sequential crawl.

How Loaders Solve This

Loaders address these performance bottlenecks through intelligent optimization:

1. Collect all database and Effect requests before processing events
2. Batch similar requests into single I/O operations
3. Cache results in memory for efficient reuse

This approach reduces thousands of database calls to just a handful per batch, dramatically improving indexing performance. When combined with the Effect API, you can also parallelize external calls for even greater efficiency.

How to Implement Loaders

Basic Structure

Loaders use the handlerWithLoader pattern, which elegantly separates data loading from event processing:

ContractName.EventName.handlerWithLoader({
  // The loader function runs before event processing starts
  loader: async ({ event, context }) => {
    // Load all required data from the database
    // Return the data needed for event processing
  },

  // The handler function processes each event with pre-loaded data
  handler: async ({ event, context, loaderReturn }) => {
    // Process the event using the data returned by the loader
  },
});

Basic Example: Converting a Simple Handler

Let's convert our previous inefficient example to use loaders:

ERC20.Transfer.handlerWithLoader({
  loader: async ({ event, context }) => {
    // Load sender and receiver accounts efficiently
    const sender = await context.Account.get(event.params.from);
    const receiver = await context.Account.get(event.params.to);

    // Return the loaded data to the handler
    return {
      sender,
      receiver,
    };
  },

  handler: async ({ event, context, loaderReturn }) => {
    const { sender, receiver } = loaderReturn;

    // Process the transfer with the pre-loaded data
    // No database lookups needed here!
  },
});

How Batching Works

The batching process follows these three key steps:

1. Batch Creation: HyperIndex creates an ordered batch of events from the event buffer accumulated in memory

2. Preload Phase: All loader functions run concurrently for the entire batch. This is called the Preload phase.

   Note: During the preload phase, some entities being loaded may not exist yet since the handlers haven't been executed. This is expected behavior - the loader runs twice per event to ensure data consistency.

3. Sequential Processing: For each event in the batch, its loader runs a second time and then passes the result to the handler. This step is sequential to maintain order.

For our 5,000 transfer events example, this reduces database roundtrips from 10,000 total calls to just 2!

Advanced Loader Techniques

Optimizing for Concurrency

You can further optimize performance by requesting multiple entities concurrently:

ERC20.Transfer.handlerWithLoader({
  loader: async ({ event, context }) => {
    // Request sender and receiver concurrently for maximum efficiency
    const [sender, receiver] = await Promise.all([
      context.Account.get(event.params.from),
      context.Account.get(event.params.to),
    ]);

    return { sender, receiver };
  },

  handler: async ({ event, context, loaderReturn }) => {
    const { sender, receiver } = loaderReturn;
    // Process with pre-loaded data
  },
});

This approach can reduce the database roundtrips to just 1 for the entire batch of events!

Querying by Field Values

Loaders also support complex queries using the getWhere method, which allows you to retrieve arrays of entities based on field values:

ERC20.Approval.handlerWithLoader({
  loader: async ({ event, context }) => {
    // Find all approvals for this specific owner
    const currentOwnerApprovals = await context.Approval.getWhere.owner_id.eq(
      event.params.owner
    );

    return { currentOwnerApprovals };
  },

  handler: async ({ event, context, loaderReturn }) => {
    const { currentOwnerApprovals } = loaderReturn;

    // Process all the owner's approvals efficiently
    for (const approval of currentOwnerApprovals) {
      // Process each approval
    }
  },
});

This technique works with any entity field that:

• Is used in a relationship with the @derivedFrom directive
• Has an @index directive

Effect API experimental

The Effect API provides a powerful and convenient way to perform external calls from your handlers. It's especially effective when used with loaders:

• Automatic batching: Calls of the same kind are automatically batched together
• Intelligent memoization: Calls are memoized, so you don't need to worry about the handler function being called multiple times
• Deduplication: Calls with the same arguments are deduplicated to prevent overfetching
• Future enhancements: We're working on automatic retry logic and result persistence for indexer reruns 🏗️

To use the Effect API, you first need to define an effect using experimental_createEffect function from the envio package:

import { experimental_createEffect, S } from "envio";

export const getMetadata = experimental_createEffect(
  {
    name: "getMetadata",
    input: S.string,
    output: {
      description: S.string,
      value: S.bigint,
    },
  },
  async ({ input, context }) => {
    const response = await fetch(`https://api.example.com/metadata/${input}`);
    const data = await response.json();
    context.log.info(`Fetched metadata for ${input}`);
    return {
      description: data.description,
      value: data.value,
    };
  }
);

The first argument is an options object that describes the effect:

• name (required) - the name of the effect used for debugging and logging
• input (required) - the input type of the effect
• output (required) - the output type of the effect

The second argument is a function that will be called with the effect's input.

Note: For type definitions, you should use S from the envio package, which uses Sury library under the hood.

After defining an effect, you can use context.effect to call it from your handler, loader, or another effect.

The context.effect function accepts an effect as the first argument and the effect's input as the second argument:

ERC20.Transfer.handlerWithLoader({
  loader: async ({ event, context }) => {
    const metadata = await context.effect(getMetadata, event.params.from);
    return { metadata };
  },

  handler: async ({ event, context, loaderReturn }) => {
    const { metadata } = loaderReturn;
    // Process the event with the metadata
  },
});

This way for our problem of 5,000 transfer events, we will be able to parallelize all external calls instead of executing them one by one.

Viem Pro Tip

You can use viem or any other blockchain client inside your effect functions. When doing so, it's highly recommended to enable the batch option to group all effect calls into fewer RPC requests:

// Create a public client to interact with the blockchain
const client = createPublicClient({
  chain: mainnet,
  // Enable batching to group calls into fewer RPC requests
  transport: http(rpcUrl, { batch: true }),
});

// Get the contract instance for your contract
const lbtcContract = getContract({
  abi: erc20Abi,
  address: "0x8236a87084f8B84306f72007F36F2618A5634494",
  client: client,
});

// Effect to get the balance of a specific address at a specific block
export const getBalance = experimental_createEffect(
  {
    name: "getBalance",
    input: {
      address: S.string,
      blockNumber: S.optional(S.bigint),
    },
    output: S.bigint,
  },
  async ({ input, context }) => {
    try {
      // If blockNumber is provided, use it to get balance at that specific block
      const options = input.blockNumber
        ? { blockNumber: input.blockNumber }
        : undefined;
      const balance = await lbtcContract.read.balanceOf(
        [input.address as `0x${string}`],
        options
      );

      return balance;
    } catch (error) {
      context.log.error(`Error getting balance for ${input.address}: ${error}`);
      // Return 0 on error to prevent processing failures
      return BigInt(0);
    }
  }
);

Why Experimental?

The Effect API is currently marked as experimental, but we don't expect breaking changes in the future. This designation simply means we're actively iterating on the feature and may add new capabilities that could subtly change indexer behavior. We plan to remove the experimental tag soon, and your feedback is invaluable in this process!

Best Practices

When to Use Loaders

Loaders provide the most significant benefits when:

• Processing large batches of events that require similar database lookups
• Reading the same entities multiple times across different events
• Performing relationship queries that affect multiple entities
• Building high-performance indexers that need to handle millions of events

Performance Considerations

When using loaders, keep these performance factors in mind:

• Memory Usage: All loaded entities are stored in memory during batch processing
• Query Size: Very large getWhere queries might cause memory issues
• Complexity: Balance the benefits of batching against code complexity
• Batch Size: Larger batches provide better performance but use more memory

Rules of Thumb

Follow these guidelines for optimal loader usage:

1. Use loaders for large-scale indexing: Implement loaders if you're indexing more than 1 million events
2. Centralize database operations: Put all database operations in the loader function
3. Wrap external calls in effects: Use the Effect API for external calls and implement them in loaders
4. Keep handlers focused: Reserve handler functions for business logic, not data fetching
5. Optimize with concurrency: Use concurrent requests when loading multiple unrelated entities
6. Monitor memory usage: Be mindful of memory consumption with large batches

Understanding Double Run Behavior

Loaders are designed to run twice per event to ensure data consistency across the batch. This is intentional and expected behavior:

1. First Run (Preload Phase): All loaders run concurrently at the start of batch processing
2. Second Run (Event Processing): Each loader runs again sequentially before its corresponding handler

This double execution pattern ensures that:

• Entities created by earlier events in the batch are available to later events
• Data consistency is maintained across the entire batch
• The benefits of batching are preserved while ensuring accurate data access

ERC20.Transfer.handlerWithLoader({
  loader: async ({ event, context }) => {
    // This loader will run twice per event
    // First run: May not find the sender if it's created by an earlier event in this batch
    // Second run: Will find the sender if it was created by an earlier event
    const sender = await context.Account.getOrThrow(event.params.from);

    return {
      sender,
    };
  },

  handler: async ({ event, context, loaderReturn }) => {
    const { sender } = loaderReturn;
    // Process the event with the loaded sender data
  },
});

The indexer will only crash if the sender entity was actually not set in an event preceding the one being processed, which indicates a genuine data consistency issue.

Preload Phase Behavior

Starting from envio@2.23.0, the preload phase has been enhanced to prevent batch processing failures. During the first run (preload phase), the following operations are silently ignored:

• Thrown exceptions: Any errors thrown during the preload phase are caught and ignored
• Entity setting: Calls to context.Entity.set() and other entity operations are ignored during preload
• Logging: Calls to context.log.*() are ignored during preload

Only during the second run (actual event processing) are all operations fully enabled:

• Exceptions will crash the indexer if not handled
• Entity setting operations will persist to the database
• Logging will output to the console

This design ensures that the preload phase can safely attempt to load data that may not exist yet, while the actual processing phase handles all operations normally.

If you're using an earlier version of envio, we strongly recommend upgrading to the latest version with pnpm install envio@latest to take advantage of this improved preload phase behavior.

Going All-In with Loaders

Starting from envio@2.23.0, loaders support setting entities directly, making handlers optional. You can now process all your events entirely within the loader function:

ERC20.Transfer.handlerWithLoader({
  loader: async ({ event, context }) => {
    // Load existing data efficiently
    const [sender, receiver] = await Promise.all([
      context.Account.getOrThrow(event.params.from),
      context.Account.getOrThrow(event.params.to),
    ]);

    // Skip expensive operations during preload
    if (context.isPreload) {
      return;
    }

    // CPU-intensive calculations only happen once
    const complexCalculation = performExpensiveOperation(event.params.value); // Placeholder function for demonstration

    // Create or update sender account
    const senderAccount = context.Account.set({
      id: event.params.from,
      balance: sender.balance - event.params.value,
      computedValue: complexCalculation,
    });

    // Create or update receiver account
    const receiverAccount = context.Account.set({
      id: event.params.to,
      balance: receiver.balance + event.params.value,
    });

    // No need to return anything - all work is done in the loader
  },
  handler: async ({ event, context, loaderReturn }) => {
    // Handler can be empty - all logic is handled in the loader
  },
});

For power users, we recommend moving all event processing to the loader function to take advantage of this enhanced functionality. This approach will be the most compatible with future Envio versions.

If you need to skip the preload phase for CPU-intensive operations or to perform certain actions only once, you can use context.isPreload. This allows you to replicate the traditional handler behavior within a loader.

Note: While context.isPreload can be useful for bypassing double execution, it's recommended to use the Effect API for external calls instead, as it provides automatic batching and memoization benefits.

Future: Version 3.0 Unified Handler Behavior

In Envio V3, the separation between handlers and loaders will be removed entirely. All handlers will behave like loaders by default, running twice per event to ensure data consistency. This change will:

• Make many indexers faster by default without requiring explicit configuration
• Simplify the mental model (similar to React's double-render pattern)
• Provide the benefits of loaders without requiring explicit configuration

The double execution pattern should be familiar to JavaScript/React developers and will be thoroughly documented to help users understand this new behavior.

Summary

Loaders provide a powerful and efficient way to optimize database access in your Envio indexers by:

• Dramatically reducing database roundtrips from thousands to just a few per batch
• Automatically batching similar requests for maximum efficiency
• Caching results in memory for instant access during processing

By elegantly separating data loading from event processing, loaders enable you to build more efficient and performant indexers while maintaining clean, readable code. Whether you're processing thousands or millions of events, loaders can transform your indexing performance from slow and sequential to fast and parallel.