Supporting cascades delete in convex?
As I was searching for the cascades keyword in the channel, I there was a discussion on the case
https://discord.com/channels/1019350475847499849/1067581483243544627/1067584500063817729
I am wondering if you guys have any plans / roadmap on supporting this and many other features that are commonly available in relational database. I know there is a no free lunch theorem, but I simply believe with convex or any fast evolving infrastructure of the future, we can have our cake and eat it too : )
4 Replies
Thanks for expressing the desire! We don’t have this in our short term roadmap, but it’s good to hear when folks ask for things.
Yeah what I would do is write a helper function to delete certain kinds of records, and implement your cascading deletes there. The reason this is inefficient in normal DBs is you're trying to get the absolute bare minimum of requests to the DB, and so you end up trying to pack a ton of logic into your SQL statement to do a ton of things at once. With Convex, your function is running close enough to the DB that you can actually write JS, which in some ways is more work, but is also way easier to debug and extend, since it's just functions and code, where you can put in print statements, etc.
I'm also trying to figure out the way to cascade delete records, e.g. the simplest case would be author and book tables, and if an author is deleted, all books that have a matching authorId would be deleted too
In my case I have multiple cascading levels though
I can do it with mutations that delete the author and then trigger mutations to delete the orphans
I could also do it with cron jobs that inspect the DB and clean up the orphans
I have the feeling that the first option would be ok, but I would like to know what is the "intended way" that has the "convex seal of approval", if any
I would do the first because at that time you already know the authorid you need to clean up and thus only have to find the relevant book records to delete.
With crons you have to look through the entire books table to find orphans which could become very inefficient/expensive.
On this topic. @Michal Srb recently wrote a library that handles all this and a lot more for you: https://stack.convex.dev/ents. It's a higher level data modeling and querying library built entirely on top of Convex. Think Prisma or Drizzle ORM for Convex.
It's definitely an "all or nothing" approach that can be appealing to many people. @ian is working on making many of these features incrementally adoptable as separate helpers.