Blog

Building advanced internal business applications with Retool

Maxime Topolov
Maxime Topolov
June 21, 2024
 
Building advanced internal business applications with Retool

Challenges of managing complex workflows across multiple Retool apps

When developing large-scale solutions in Retool, architects often encounter limitations with single-app designs. Complex workflows spanning multiple business domains necessitate breaking functionality into separate apps. However, this introduces challenges around data sharing, state management, and synchronization.

A key issue is maintaining a consistent state across apps. Passing data between apps often requires workarounds like storing intermediate results in temporary tables or localStorage. This approach is error-prone and difficult to maintain as the system grows.

Another challenge is replicating common UI patterns and business logic across apps. Without a modular architecture, developers end up duplicating code, leading to inconsistencies and maintenance headaches.

Strategies for sharing data and state between apps

1. Query Library: Create reusable queries in the Query Library to encapsulate complex data operations. This promotes consistency and reduces duplication across apps.

2. Custom Components: Build reusable UI components that can be shared across apps. This is particularly useful for complex visualizations or domain-specific interfaces.

3. Workflows: Use Retool Workflows to orchestrate multi-step processes spanning multiple apps. Workflows can handle asynchronous operations and complex branching logic.

4. URL Parameters: Pass state between apps using URL parameters. This allows for deep linking and maintaining context when navigating between apps.

The thread at https://community.retool.com/t/specific-links-prod-vs-stg/20668 discusses strategies for managing environment-specific URLs for apps, which is crucial when implementing multi-app architectures.

Techniques for synchronizing data updates across apps

Maintaining data consistency across apps requires careful consideration of update patterns:

1. Event-driven architecture: Use Retool's event system to trigger updates across apps when data changes. This can be implemented using custom events and listeners.

2. Polling: For less time-sensitive updates, implement polling mechanisms to periodically check for changes.

3. WebSockets: For real-time updates, consider implementing a WebSocket connection to push changes to connected clients.

Case study: Architecting a large-scale enterprise solution with Retool

Consider a large e-commerce platform built on Retool. The system is divided into several apps:

1. Order Management
2. Inventory Control
3. Customer Service
4. Analytics Dashboard

To ensure scalability and maintainability:

1. Implement a shared authentication module using custom components and the Query Library.
2. Use Retool Workflows to handle complex order processing logic spanning multiple apps.
3. Develop a centralized state management system using a combination of the Query Library and custom events.
4. Create reusable UI components for common elements like product displays and order forms.
5. Implement a WebSocket-based real-time update system for inventory levels and order statuses.

Performance of Retool-based applications

As Retool applications grow in complexity and handle larger datasets, query optimization becomes crucial for maintaining performance and responsiveness.

Common Performance Bottlenecks in Retool Queries

One of the most common performance issues in Retool applications stems from the inefficient handling of large datasets. Creating numerous queries to simulate pivot table functionality can significantly slow down an application.  Another frequent bottleneck occurs when applications make multiple API calls in sequence, especially when dealing with external services with rate limits or high latency, as when trying to send hundreds of WhatsApp notifications via Twilio, demonstrating the need for strategies to handle bulk operations more efficiently.

Leverage SQL Aggregation Functions

Instead of relying on client-side processing, utilize SQL's built-in aggregation functions to reduce the amount of data transferred. For example:


SELECT users.id,
      users.first_name || ' ' || users.last_name AS full_name,
      string_agg(stories.title, ',') AS titles
FROM users
INNER JOIN writers ON users.id = writers.user_id
INNER JOIN stories ON writers.story_id = stories.id
GROUP BY 1, 2

This approach consolidates data at the database level, reducing the load on Retool's front end.

Use Efficient JOIN Operations

When dealing with related data, prefer efficient JOIN operations over multiple separate queries. A well-structured JOIN can retrieve all necessary data in a single query:


SELECT users.id,
      users.first_name || ' ' || users.last_name AS full_name,
      stories.title
FROM users
INNER JOIN writers ON users.id = writers.user_id
INNER JOIN stories ON writers.story_id = stories.id


Leveraging JavaScript Transformers for Efficient Data Processing

JavaScript transformers in Retool offer powerful capabilities for post-processing query results. Here are some advanced techniques:

Data Reshaping:
Use reducers to efficiently reshape data structures. For example, to group stories by author:

return data.reduce((hash, value) => {
 const {story_name, ...rest} = value;
 if(hash[value.id]){
   hash[value.id].story_names.push(story_name);
 } else {
   hash[value.id] = {...rest, story_names: [story_name]};
 }
 return hash;
}, {});


Parallel Processing:
For operations involving multiple asynchronous calls, use Promise.all to execute them concurrently:

const promises = arr.map(item => {
 return query.trigger({ additionalScope: { id: item } });
});
return Promise.all(promises);

Implementing Caching Mechanisms for Improved Query Performance

Use Temporary State Variables:
Store frequently accessed data in temporary state variables to avoid redundant API calls or database queries.

Implement Client-Side Caching:
For data that doesn't change frequently, consider using browser localStorage to cache results, reducing server load and improving response times.

Best Practices for Handling Large Datasets in Retool Applications

1. Pagination:
Implement pagination for large datasets to load data in smaller chunks. This reduces initial load times and improves overall application responsiveness.

2. Lazy Loading:
Use lazy loading techniques to defer the loading of non-critical data until it's needed. This can be particularly useful in complex dashboard applications.

3. Debouncing and Throttling:
When dealing with user inputs that trigger queries (e.g., search functionality), implement debouncing or throttling to limit the frequency of API calls.

4. Optimize Data Transfer:
Only request the necessary fields from your data source. Avoid fetching entire records if only specific columns are needed for display or processing.

5. Use Efficient Data Structures:
Choose appropriate data structures for your use case. For example, when dealing with lookups, consider using objects instead of arrays for faster access times.

Common issues with Retool applications

Data Manipulation and Display

Issue: Users frequently struggle with manipulating and displaying data, especially when dealing with complex data structures or nested JSON.

Solution: Utilize JavaScript transformers and lodash functions to reshape data. For nested structures, use methods like `formatDataAsArray()` or custom functions to flatten and map data appropriately.

Why it occurs: Retool's visual components sometimes have limitations in handling complex data structures out-of-the-box, requiring additional data manipulation.

Query Execution and Performance

Issue: Problems with query timeouts, especially when dealing with large datasets or multiple queries.

Solution: Optimize queries, use pagination, and consider using Retool Workflows for long-running or complex operations. For multiple queries, use Promise.all() in JavaScript queries to run them concurrently.

Why it occurs: Retool has built-in timeout limits to prevent resource overuse, which can be hit with inefficient queries or large data volumes.

Component Interactions and State Management

Issue: Difficulties in managing state across components, especially in complex apps with many interdependent elements.

Solution: Use Temporary State variables effectively, leverage the `retoolContext` object, and create custom event handlers to manage data flow between components.

Why it occurs: As apps grow in complexity, managing state and component interactions becomes more challenging without a clear strategy.

Custom Authentication and API Integration

Issue: Challenges in setting up custom authentication flows or integrating with complex APIs.

Solution: Utilize Retool's custom auth options, leverage the Query Library for reusable API calls, and use JavaScript queries for more complex API interactions.

Why it occurs: Each API has unique authentication requirements, and Retool's built-in options don't always cover all scenarios.

UI Customization and Responsive Design

Issue: Limitations in customizing UI components or creating responsive layouts.

Solution: Use custom CSS where possible, leverage container components for layout control, and consider custom components for highly specific UI needs.

Why it occurs: Retool prioritizes rapid development over extensive customization, leading to some limitations in fine-grained UI control.

Working with Date/Time Data

Issue: Challenges in formatting, manipulating, and displaying date/time data consistently.

Solution: Use the built-in moment.js library for date manipulations, and leverage JavaScript transformers for complex date-related operations.

Why it occurs: Date/time handling is inherently complex, and different data sources may provide dates in varying formats.

Handling Many-to-Many Relationships

Issue: Difficulty in displaying and managing many-to-many relationships in tables and forms.

Solution: Use custom queries to join data appropriately, leverage JavaScript transformers to reshape data, and consider using nested components or custom UI elements for complex relationships.

Why it occurs: Retool's default components are optimized for simpler data structures, requiring additional work for complex relationships.

Share this post
 
Business Apps
ERP
Software Engineering
Low-code
Maxime Topolov
Maxime Topolov
CEO

You can also read

API
Performance
Content
SEO
Data
Consumer App
Software Engineering
On-premises
Mobile Dev
ERP
E-commerce
Recruiting
Cloud
Content Migration
AI
Frontend
CMS
Headless
Backend
Low-code
Business Apps
Conversional AI
Education
Media and Publishing
Healthcare
Financial services
Large corporate
Start-Up