ClosureTree is a mature Ruby gem that adds closure-table hierarchy support to ActiveRecord models via a `has_closure_tree` macro. It maintains a separate `_hierarchies` table storing ancestor/descendant pairs with generation depth, enabling O(1) SELECTs for ancestors, descendants, siblings, leaves, and roots. The gem handles the full lifecycle: node creation inserts a self-referencing row plus copies of the parent's ancestor rows (2 INSERTs), reparenting rebuilds the affected hierarchy, and destruction cleans up references with configurable dependent behavior (nullify, adopt, destroy, delete_all). It supports STI, UUID primary keys, deterministic numeric ordering with preorder traversal, scoped multi-tenant trees, PostgreSQL schema-qualified tables, and multi-database setups (PostgreSQL/MySQL/SQLite simultaneously). Concurrency safety is provided through the with_advisory_lock gem with customizable lock names and per-instance lock granularity for multi-tenant use cases. The architecture is notably clean: a `ClosureTree::Support` instance (`_ct`) encapsulates all implementation details to avoid polluting the host model's namespace, and focused mixins (Finders, HashTree, Digraphs, DeterministicOrdering, HierarchyMaintenance) are composed by the `has_closure_tree` entry point. The code demonstrates sophisticated cross-database SQL engineering — MySQL uses user variables for gap-free sibling reordering, PostgreSQL uses window functions, and Arel query builders handle adapter-specific quoting. The test suite is exceptional: it runs against Ruby 3.4/4.0, Rails 7.2 through edge, and all three database engines, with concurrency tests, query-count assertions, and shared example suites for both integer and UUID primary keys. The gem is actively maintained with release-please automation, semantic versioning, and a detailed changelog spanning over a decade of development.
sqlalchemy_mptt is a Python library that provides SQLAlchemy mixins for implementing Modified Preorder Tree Traversal (MPTT) with SQLAlchemy models. It automatically maintains tree structure through SQLAlchemy event listeners, handling insert, update, and delete operations while preserving tree integrity. The library supports multi-tree scenarios, various primary key types, and provides methods for tree traversal, drilldown operations, and path-to-root queries. The architecture centers around the BaseNestedSets mixin class that adds tree-specific columns and methods to SQLAlchemy models, complemented by a TreesManager that handles SQLAlchemy events to automatically maintain lft, rgt, level, and tree_id attributes. The library demonstrates strong engineering practices with a compatibility layer for SQLAlchemy versions, comprehensive test suite, and active maintenance with recent support for SQLAlchemy 2.0 and modern Python versions.