typeorm-store Functionality Enhancements and ON CONFLICT Support for Store Operations

[izzetemredemir]

When using partitioned tables, the save and upsert methods result in ON CONFLICT errors. This is particularly problematic because these methods are critical for efficiently managing database entities with TypeORM. My temporary solution was to modify the insert method in the Store class to use save instead of insert to bypass the ON CONFLICT issue.

Proposed Enhancement

I propose an enhancement to the upsert method to allow for optional specification of unique constraint fields. This would provide greater flexibility and compatibility with partitioned tables, where unique constraints might not be as straightforward as with non-partitioned tables.

Suggested Changes

• Modify the upsert method to accept an optional parameter that specifies which fields should be considered for the unique constraint during the upsert operation. This would be particularly useful for tables with complex unique constraints or partitioning.
• Ensure that the save, upsert, and potentially insert methods are compatible with partitioned tables and do not result in ON CONFLICT errors.

Code Reference

The issue and proposed changes pertain to the Store class within the Squid SDK TypeORM store module, specifically around the implementation of the save and upsert methods. Here is the relevant code section: Store.ts#L143

Conclusion

Adapting the Store class to better support partitioned tables and offering flexibility in defining unique constraints for upsert operations would significantly enhance the utility and robustness of the Squid SDK when dealing with complex database schemas. I believe this enhancement will benefit many users who rely on the Squid SDK for their TypeORM-based projects.

[izzetemredemir]

Problem Reproduction Steps

To replicate the issue with ON CONFLICT errors when using partitioned tables in PostgreSQL through the TypeORM store, follow these steps:

1. Initial Upsert Operation:
   In your processor's main.ts, perform an upsert operation using the ctx.store.upsert method. For example:

   await ctx.store.upsert(ethUniv2EthusdcSwaps);

2. Create a Partition on the Existing Table:
   Next, create a partition for the table that corresponds to the entity ethUniv2EthusdcSwaps in PostgreSQL. This involves defining a partitioning strategy based on your specific requirements (e.g., range, list) and executing the appropriate SQL commands to partition the table.

3. Restart the Indexer:
   After partitioning the table, restart the indexer. Upon attempting to perform upsert operations again as part of its normal processing, TypeORM is expected to throw an ON CONFLICT error, demonstrating the issue with handling upsert operations on partitioned tables.

Expected Behavior

The upsert operation should successfully insert or update records in the partitioned table without throwing ON CONFLICT errors, respecting the unique constraints defined across partitions.

Actual Behavior

TypeORM throws an ON CONFLICT error upon restarting the indexer and attempting to upsert records into a partitioned table, indicating a lack of support or improper handling of partitioned tables during upsert operations.