sql & sequelize support

[musicformellons]

I am a bit puzzled by the following:

1. As my app has returning users with account etc I suppose I do need a database for authorization rules etc.
2. I really would prefer to use postgres (with sequelize) instead of mongodb. I saw some code examples in the docs for postgres & sequelize but it feels a bit like this is not really supported (yet) and I find it hard to judge whether I could get it up and running with these pointers. I guess my question boils down to: are these given examples enough to be up and running with postgres and sequelize, or are they just boilerplate examples on which more work is needed before you can use it?

Maybe you could also explain which features then would still be missing compared to the mongodb & mongoose solution.

[stalniy]

Integration with database should be quite easy because the only thing you need to do is to convert CASL rules into database query. sequelize uses Mongo-like syntax to construct SQL query. So, in terms of fetching all records according to ACL rules from the single table shouldn't be a big deal and examples from documentation should cover majority of such cases.

So, when you define permissions for an entity based on its own fields, all should be good.
But CASL doesn't provide a solution if you need to define permissions for one entity based on fields of another entity and it doesn't matter which database you use. So, for such cases you will need to do some manual stuff

Lets consider an example:
What if we want to allow all users who has admin word in email to update posts. Then I'd define rules as this:

can('update', 'Post', { 'author.email': { $regex: "admin" } })

In order to retrieve all posts which such user can update we need to create query like this:

SELECT *
FROM posts as p
  INNER JOIN users as u ON u.id = p.author_id
WHERE u.email LIKE "%admin%"

And now your rulesToQuery function should be able to detect that author.email is a field of another entity and you need to do inner join

[stalniy]

I don’t plan to create a core package for sequelize as it looks like that project is dying slowly. Instead I plan to add support for objection.js or/and mikroORM

[musicformellons]

Mmmh, what makes you think sequelize is dying?

[stalniy]

Probably I used to loud word “dying”). This is just my opinion after working with sequelize for half a year

this is the list of what I don’t like in sequelize:

• no query builder
• raw option just passes generated query to driver, and returns result without proper grouping
• I had a lot of discussion in my team about using classes for models or not (because documentation didn’t have info about classes but now it has)
• sequelize try to query database with inefficient generated query (e.g., when you have join, limit and offset, it produces query which doesn’t use indexes without any warning or using a better strategy)
• it’s hard to extend sequelize. There is no something like plugins in mongoose, only hooks.
• in order to insert a graph of objects into db, you need to pass a graph of includes

So, for standard use cases it works good, as well as mentioned libraries. But on the other hand objection and mikro-orm allows to work with non standard use cases easier (because both have query builder and more solid codebase)

[stalniy]

Some time ago I commented in sequelize repo on replacement of string based operators with symbol based

It’s ridiculous, I said that Symbols don't fix the problem, they agreed and closed the issue.

[ujwal-setlur]

@stalniy any status on mikroOrm support? I am using mikro-orm and would love this support. Happy to help or test!

[stalniy]

No progress for now. But have some thoughts:

1. The best way to start is to integrate with knex
2. MikroORM supports knex, so it should work with knex integration out of the box (or with minor configuration)
3. In knex, we can’t check whether a field is a json field or a foreign key ref (usually we can do this only on ORM level), that’s why we need to introduce extra syntax in conditions that emphasize field as a foreign field ref (to support joins).
4. To fulfill 3, I’ll need to send a PR in sift. So, this extra syntax in 3 can be processed on casl side (by passing getter option, for example)

[stalniy]

So, to start we need a function rulesToKnexQuery(ability, action, subject, options). options object should contain a property isRelation: (prop, subjectType, subject) => boolean. This function
returns true or false if prop is a relation to another entity. prop can be a string (e.g., name) or string with dot notation (e.g., author.email). If function return true we need to innerJoin relation and add conditions to where clause, if false then this is a local field and just add condition to where clause.

The result of this rulesToKnexQuery is a knex QueryBuilder that fetch records in a single request. If we do function like rulesToKnexQuery than it will be very easy to add integration for objection.js and mikroORM.js and any knex based ORM.

Afterwards we can even implement something similar for sequelize but in this case the result of the function will be an object, which we can pass to findAll because sequelize doesn't have query builder

@ujwal-setlur if you want to try to do this, your help is very welcome!

[ujwal-setlur]

@stalniy I will try to get to this in the next week or two!

[otaviosoares]

I want to help too. I intend to use it with objection.js. @stalniy do you plan to have rulesToKnexQuery at @casl/ability/extra as well?

[stalniy]

As a separate package. @casl/knex

[stalniy]

I’ll create a folder for it and prepare the infrastructure code

update: it would be awesome if We could create this together :)

[otaviosoares]

Sure, let me know when you want to chat.

[stalniy]

Sure, let me know when you want to chat.

@otaviosoares sent you an email with instructions and created infra code for casl-knex in feat/knex branch

[stalniy]

Waiting for the feedback from @crcn in crcn/sift.js#202

[stalniy]

Yes, it’s possible. Also you can manually plug @ucast/sql to get some semi-basic functionality to transform casl rules to sql

[lambrohan]

@stalniy Do you have any examples on how can it be done properly?

[stalniy]

It’s not battle tested yet. Don’t have enough time to do this. That’s why there is no docs

[JaneJeon]

For those of us using Objection.js, please see https://github.com/janeJeon/objection-authorize, I’ve been using and building it for the past few years, and it’s battle tested, and it supports full isomorphism!

[stalniy]

Guys, I proud to say CASL is now officially supports Prisma. So, anyone who uses prisma as ORM can try to use @casl/prisma and provide feedback

[natepelzel]

Hey, thanks for all the hard work on this project. I am very interested in the MicroORM integration, and think your idea to handle it via a custom repository makes a lot of sense.

Have you began development on it yet? Is there anything I can do to assist?

[stalniy]

Hi @natepelzel

Use @ucast/sql@alpha version to test integration with mikro-orm. there is a subpackage @ucast/sql/mikro-orm which exposes this functionality.

There are some edge cases in objection.js integration which might be the same for mikro-orm, check this issues:

• table name "xxx" specified more than once - objection ucast#17
• fix(objection): table name 'xxx' specified more than once ucast#18

[jassdv]

is there any update regarding Objection support?
@casl/sql/objection ?
would that be available any time soon?
or something I can start with ?

[stalniy]

@JaneJeon this is not true. I’ve never said that. Despite the fact there is no update on the work it doesn’t mean that you can use this thread to promote your library.

Please, let’s keep this issue focused on the actual title.

update: one more comment like that and you will be blocked

[raymondsze]

Its good to see if there is official library support typeorm as typeorm itself and nestjs are getting quite popular.

[kasvith]

Is there any docs about objection integration?

[dberardo-com]

is there any plan to support sequelize as well on top of prisma ? or would you recommend using prisma only with casl while keeping all rest of code with sequelize ? could this actually work this way (mixed approach)?

[philipimperato]

I don’t plan to create a core package for sequelize as it looks like that project is dying slowly. Instead I plan to add support for objection.js or/and mikroORM

This did not age well, haha!

[dberardo-com]

and do you know if it is possible to use prisma and sequelize in the same project? basically using prisma only for the policy classes and stuff required by casl while keeping sequelize for all the rest ?

[miguelHM63]

Hi, I'm trying to implement casl in my node express project with sequelize (using postgres as db), but it's difficult for me to add the conditions part so that the user can only process their records and other cases, has anyone been able to implement it? or what is the best guide to apply the conditions in the casl rules

[raikusy]

Did anyone implement CASL with drizzle + postgres? Would appreciate if someone can provide any example or guide.

[hitesh-pathak]

CASL v6 docs are not clear on how to use this with say TypeORM, I get that there is no official integration. But from the docs it looks like you can make it work at least for some basic queries. Would like to get any pointers.

I am using TypeORM & MySQL. Would consider migrating to Prisma but there are some issues with Prisma, like it doesn't provide specialized support for tree entities like TypeORM.

[josesilveiraa07]

Any updates for that? I'd love to use it with TypeORM, It's the only blocking issue for me.

[jd1378]

maybe knex support ?
edit: saw it being mentioned above already
the issue seems stale though

[stalniy]

Look at @ucast/sql latest alpha version. This is where I stopped and currently I don’t have time to work on new features in open source

[jd1378]

Thanks for the work you have done so far, these stuff takes a lot of precious time and I understand that well as I have other open source projects myself, and I wish you the best of luck

It would be great if you could do one more small favor and give me an overview of steps i need to take to make it work with create ability, as it seems ambiguous to me how i can make something like createMongoAbility out of it
If you don't have the time for this favor, that is fine and all

[ygrishajev]

@jd1378 this can be a good starting point: https://gist.github.com/ygrishajev/9ef01444fdb5c386c43b6611400c0fc6
which is based on some findings in this thread above

[jd1378]

thanks a lot @ygrishajev for the gist
though I still have no idea how to use it to create something like createMongoAbility, and the code for that looks complex

[ygrishajev]

oh, right, confused with possibility of usage with knex

[jd1378]

I guess I misunderstood the createMongoAbility all along because I had small issues with using it properly
I'm not sure if I understand it correctly now, but after reading the code for it to create one for Sql, I came into conclusion that it is not needed at all, as createMongoAbility simply converts a Mongo-like query into a JS runtime check for the ability's can/cannot.

sorry for bothering you all with this notification, just wanted to save someone else's time if they happen to wonder about the same thing coming across this :)