[dubswithus]

> Using views, stored procedures and other features lets you implement things like soft delete trivially, without it infecting all your application code.

That’s great but some of us actually like to write code. Especially Ruby on Rails where soft delete is a breeze if you don’t overthink it and build something the business doesn’t need.

[doctor_eval]

Well, less code means less bugs, but go nuts. I'm just saying that the database is part of the stack. You wouldn't avoid Ruby features "just in case we stop using Ruby" - so why would we avoid using database features? It's up to you how best to assemble the features from your stack.

There is a reason we avoided database features in the 90's, which was to avoid database vendor lock-in. This was almost entirely a financial decision - you didn't want to be at the mercy of a vendor who could 10x the license fees and put you out of business, so you needed to have leverage over the vendor, and you needed to be able to credibly say you could change databases -- and in fact this exact scenario actually happened to me.

But that kind of behaviour is not really needed any more, and there's no need to avoid the great database features that systems like PG provide. In my experience, using database primitives to implement features tends to perform much faster (10x - 100x) and more consistently than the equivalent implementations higher up the stack.

[Falkon1313]

The main reason was indeed vendor lock-in. Databases (like compilers) were hugely expensive back before open source got big.

But also some of the features were a bit flaky back then. I remember views in MySQL always caused problems - if you dumped and restored (ex: staging to dev), the special handling of permissions for views and such ("Access denied; you need the SUPER privilege for this operation") would break stuff. So we just didn't use them.^1

However, I'm still in favor of them, and think it's worth finding workarounds for things like that. They're not as flaky now, and people have found workarounds for the remaining flakiness. Nowadays the fear of using them is just tradition from 30 year old problems.

But now we have one or two generations of developers who were taught "Don't use the database features! 'Best practice' is to use it as a dumb datastore and re-implement them all in your application." But they don't know why, so wouldn't even know that those reasons are no longer applicable.

>^1 There exists a shortcoming with the current implementation of views. If a user is granted the basic privileges necessary to create a view (the CREATE VIEW and SELECT privileges), that user cannot call SHOW CREATE VIEW on that object unless the user is also granted the SHOW VIEW privilege.

>That shortcoming can lead to problems backing up a database with mysqldump, which may fail due to insufficient privileges. This problem is described in Bug #22062.

>The workaround to the problem is for the administrator to manually grant the SHOW VIEW privilege to users who are granted CREATE VIEW, since MySQL doesn't grant it implicitly when views are created.

> -- https://dev.mysql.com/doc/refman/8.0/en/view-restrictions.ht...

[dubswithus]

The use case for using the database here is overblown. I've never experienced of heard of anyone using a view for the users table. I suppose it may exist in the wild somewhere.

I wouldn't avoid Ruby features because the applications I develop are in Ruby and it would require a complete rewrite anyway. I avoid the database because there are development speed advantages to using Rails features. And everything works together nicely assuming you do things the Rails way.

https://github.com/rubysherpas/paranoia

> If a record has has_many associations defined AND those associations have dependent: :destroy set on them, then they will also be soft-deleted if acts_as_paranoid is set, otherwise the normal destroy will be called.

It's convenient.

[doctor_eval]

> I've never experienced of heard of anyone using a view for the users table

Well, now you have heard of people doing this.