Seriously. That "Downsides: Code leakage" point is nonsensical.
```
CREATE OR REPLACE VIEW active_customer AS
SELECT *
FROM customer
WHERE
deleted_at IS NULL
OR deleted_at <= NOW()
;
```
There, I fixed it.
Just use `active_customer` instead of `customer ... deleted_at IS NULL`.
In fact, since the deleted_at column is a timestamp, the original "leakage" query:
```
SELECT *
FROM customer
WHERE id = @id
AND deleted_at IS NULL;
```
is actually broken. A non-null `deleted_at` timestamp that's in the future implies the record hasn't been deleted yet, right?
I've often had junior devs assert that views are some kind of code smell, but these sorts of "canned query/filter that you want to apply very very often" seem like the perfect use case for a view to me. It's DRY, and the fact that your standard "query" is in the database" means you can change it more readily than trying to make sure you hit all the points it might be embedded in the application code.
> I feel like a lot of developers underutilize the capabilities of the massively advanced database engines they code against
Early-ish in the JDBC days a senior dev I was working with at the time (as a junior dev myself) made a pretty good case that "the database is part of the application" that's always stuck with me. Full database independence via software level abstractions is a pretty silly goal outside of library code. If you have a service that makes extensive use of the database, don't throw away the database features in the interest of some abstract "we could swap out oracle with mysql without changing anything" objective. If you want it to be generic, use the SQL standard, but don't be afraid to have a few db-specific bits in the app code if that's a subsystem you might replace once a decade or something.
I blame the DBA/Dev divide for a lot of this. A lot of the impedance between these layers is social/procedural. If you can change the DB as easily as the code, there's a lot less fear of using the right tool for the specific job.