Converting CASL conditions to MikroORM query conditions - need guidance on best practices

[only-issues]

Hi CASL team and community! 👋

I'm working on integrating CASL with MikroORM and while I've seen discussion #949 about Collections handling, my main challenge is with converting CASL's MongoDB-style conditions into MikroORM query conditions.

The core problem

CASL generates conditions using MongoDB query syntax (which is great!), but when I need to apply these conditions to actual database queries through MikroORM, I'm struggling with the translation layer.

Example scenario:

// CASL rule
can('read', 'Post', { 
  author: { id: { $in: [1, 2, 3] } },
  status: { $ne: 'draft' },
  $or: [
    { publishedAt: { $lte: new Date() } },
    { featured: true }
  ]
});

// How do I convert this to MikroORM QueryBuilder conditions?
const posts = await em.createQueryBuilder(Post, 'p')
  .where(/* ??? what goes here ??? */)
  .getMany();

What I've tried

1. Manual operator mapping: Started creating a mapping function for each MongoDB operator ($in, $ne, $or, etc.) but it's getting complex quickly and I'm worried about edge cases.

2. Looking into @ucast/sql: I saw mentions of this in issue Request for TypeORM #347 for TypeORM, but I can't find clear documentation on how to use it with MikroORM specifically.

3. Serialization approach: Converting everything to JSON first, but this defeats the purpose of having database-level filtering.

Specific challenges I'm facing

• Nested object conditions: How to handle conditions on relations like author.profile.country
• Complex logical operators: Properly translating $and, $or, $not combinations
• Array operators: Converting $in, $nin, $elemMatch to work with MikroORM's syntax
• Performance implications: Ensuring the translation doesn't create inefficient queries

What I'm looking for

1. Recommended approach: Is there a standard/recommended way to bridge CASL conditions with SQL-based ORMs like MikroORM?

2. Existing solutions: Has anyone built or knows of a helper library for this specific translation?

3. @ucast/sql guidance: If @ucast/sql is the way to go, could someone provide guidance on integrating it specifically with MikroORM?

4. Best practices: What's the most maintainable approach for handling this conversion layer?

Code example of what I need

Ideally, I'm looking for something like:

// Hypothetical helper function
const ability = defineAbility((can) => {
  can('read', 'Post', { status: { $ne: 'draft' } });
});

const queryBuilder = em.createQueryBuilder(Post, 'p');
const filteredQuery = applyCaslConditions(queryBuilder, ability, 'read', 'Post');
const posts = await filteredQuery.getMany();

Context

• Framework: NestJS with TypeScript
• Database: PostgreSQL
• ORM: MikroORM v5+
• Use case: Multi-tenant application with complex authorization rules

I really appreciate CASL's approach to authorization and would love to make this work smoothly with MikroORM. Any guidance, code examples, or pointers to existing solutions would be incredibly helpful!

Thanks for your time and for building such a great library! 🙏

---

Note: This is separate from the Collections issue discussed in #949 - I'm specifically focused on the conditions mapping challenge.