When it comes to OLTP databases, Object-Relational Mappers (ORMs) are incredibly useful. They simplify database interactions, provide a layer of abstraction, and make it easier to manage complex data relationships. But what about OLAP databases, like ClickHouse? Do we need a different approach?
The short answer is yes. While ORMs are great for OLTP, they’re not suitable for OLAP databases. The semantics of core concepts are different, and trying to extend ORMs like Prisma or Drizzle to OLAP databases can be problematic.
Let’s explore why.
## The Problem with ORMs in OLAP
In OLTP, columns are nullable by default, whereas in OLAP, they’re not. `unique()` in OLTP means write-time enforcement, while in ClickHouse, it means eventual deduplication via a ReplacingMergeTree engine. Hiding these differences is dangerous and can lead to unexpected behavior.
## Principles for an OLAP-Native DX
So, what do we need from an ORM for OLAP databases? We propose that a better tool should:
– Borrow the best parts of ORMs (schemas-as-code, migrations).
– Promote OLAP-native semantics and defaults.
– Avoid hiding the power of the underlying SQL and its rich function library.
## Moose OLAP: An Open-Source Solution
We’ve built an open-source, MIT-licensed project called Moose OLAP to explore these ideas. It’s designed to provide an OLAP-native experience, with a focus on simplicity, flexibility, and performance.
## Join the Conversation
We’d love to hear your thoughts on this topic. Do you think ORMs need to be reimagined for OLAP databases? Share your opinions and experiences in the comments below!