No, you're seeing a surge in interest for SQLite because people like relational databases, but the n-tier architecture is sometimes not the right solution for the problems people have. And again: many of your arguments have been applied to MySQL, but nobody can with a straight face say it's not a "real" backend database.
(To a first approximation ~nobody is interested in SQLite because it lacks correctness or rigid typing features; what's interesting about SQLite is not what was interesting about schemaless databases, but rather the ability to ship backend apps without a separate database tier.)
Again: I think you need to snap out of the idea that n-tier architectures are axiomatically optimal for all backend applications. They often are! But not all the time.
I think that most applications are written for their database. Their database defines their application.
If you write your application on a flimsy database then your application becomes equally flimsy. All of your business constraints become flimsy because your source-of-truth (the database) is flimsy.
This was the kind of thing people used to say about MySQL before Meta made those arguments look silly, and so they've moved to SQLite as a new target. I like Postgres fine, but it's just a tool, like many others.
Of course, once you come to that realization, then you realize that it is all one in the same and that there isn't any magic going on, which then realizes that business constraints can go anywhere in your application and be written by anyone.
I suspect what you are really trying to say is that you trust Hipp more than you trust yourself to get the constraints right. Indeed, if you screw it up you're in for a world of hurt, so you are right to be cautious. But, if you have more trust in a random stranger who has no care for your data than you do yourself to implement it for you, perhaps you shouldn't be writing any code at all? Software development certainly isn't for everyone.
Au contraire, SQLite makes it very easy to write extensive automated testing for your application, since you can spin up in-memory DBs per test with minimal overhead. This makes your application much more robust.
(To a first approximation ~nobody is interested in SQLite because it lacks correctness or rigid typing features; what's interesting about SQLite is not what was interesting about schemaless databases, but rather the ability to ship backend apps without a separate database tier.)
Again: I think you need to snap out of the idea that n-tier architectures are axiomatically optimal for all backend applications. They often are! But not all the time.