Testing Business Logic in PostgreSQL: Scenario Trees and Savepoints

Why It Matters

If you use PostgreSQL as the core database for a fintech product, crypto wallet, or payment service, you've probably faced the challenge: business logic grows branching scenarios, and testing each one becomes increasingly complex. Traditional approaches force you to rebuild the database state for every test — slow and painful. Alexey Evlampiev proposes an elegant solution: represent scenarios as a tree and use savepoints so each branch inherits the shared history instead of rebuilding it from scratch.

Step 1: Represent Scenarios as a Tree

Instead of writing separate test scripts for each case, express the branching of your business logic as a directory structure. Each directory is a scenario, and nested directories are its sub-scenarios. This gives you a clear map of all possible execution paths.

Step 2: Use Savepoints for History Inheritance

The key idea is to walk the tree using savepoints. When you enter a branch, you set a savepoint; if the branch fails, you roll back to it without losing changes made in parent branches. This allows each branch to "inherit" the shared history rather than rebuilding it.

Step 3: Commit Only Before COMMIT

Another important point: do not commit changes inside branches. Instead, work within a transaction and issue COMMIT only after all checks pass. This ensures atomicity and simplifies rollback on errors.

Step 4: Automate Tree Traversal

Write a script that recursively walks the scenario tree, executing SQL scripts from each directory. For each branch, create a savepoint, run checks, and depending on the result either roll back or proceed to the next. This covers all combinations without duplicating code.

Practical Implications

For fintech projects where every operation is a complex sequence of checks (balance, limits, fees), this approach saves development hours and reduces the risk of missing a rare scenario. You get a clean, maintainable test structure that easily extends when adding new functionality.

FAQ

Question: Do I need special libraries?
Answer: No, standard PostgreSQL features (savepoints, transactions) and any scripting language for file system traversal are sufficient.

Question: Is this method suitable for large databases?
Answer: Yes, especially if you have many branches. But note that each savepoint adds a small overhead, so for very deep trees you should optimize the number of checks.

Question: Can this approach be used outside testing?
Answer: Yes, the concept is also useful for data migrations and ETL processes where you need to handle different input variants.

Conclusion

The scenario-tree method with savepoints is a practical way to bring order to testing complex PostgreSQL business logic. It saves time, simplifies maintenance, and makes your tests more reliable. If you work with fintech or crypto payments, where every detail matters, it's worth adopting.

Sources

• https://vvka-141.github.io/pgmi/articles/test-every-branch-before-commit/