- the code is not reusable outside of a database setting. So not cacheable.
- the code is not reusable accross different storage layers. So not portable.
- the code may needs updating if the schema change, you can't abstract that
- changing the logic means a db migration
- testing the code requires a DB
- tooling support to check that code si limited to SQL tooling, which is very weak, especially for code completion, refactor and debugging.
That's a lot of constraints for just making the application logic a lot simpler.
- it is cached, in the database’s memory, where the cache can be invalidated automatically. It is better to cache views than data anyway.
- it is portable to every platform postgres runs, which in practice means it will run everywhere. Portability between databases is overrated because it rarely happens in practice.
- the access control logic evolves together with the schema, guaranteeing they have an exact correspondence. This is a good thing.
- integration tests should involve a live database
- have you looked at jetbrains datagrip?
Since most popular product in the JS world are product to avoid writing Javascript (webpack+babel, typescript, coffeescript, jsx, etc), and plv8 supports non of that nor does it support standard unit tests framework like Jest, I'm not convinced.
To make things even worst, remote debugging is not supported anymore: https://github.com/plv8/plv8/issues/131#issuecomment-2377111...
If I had to do something that feels too complex, I'd write some interfaces in TypeScript to represent the tuples, then implement the function in TS, and compile it into plain JS in order to deploy it to plv8.
The cache may not be for the data in the data base, but for something else (task queue, calculation, user session, pre-rendering, etc). You effectively split your cache into several systems.
> it is portable to every platform postgres runs, which in practice means it will run everywhere. Portability between databases is overrated because it rarely happens in practice.
As I mentioned, for a project it doesn't matter. For a lib it does.
> the access control logic evolves together with the schema, guaranteeing they have an exact correspondence. This is a good thing.
Not always. Your access control logic could be evolving with your model abstraction layer, which frees you to make changes to the underlying implementation without having to change the control logic every time. It's also a good for your unit tests, as they are not linked to something with a side effect.
> integration tests should involve a live database
Integration tests are slow. You can run them as you develop.
> Have you looked at jetbrains datagrip?
It's fantastic. If you are willing to pay the price of it for and force it on your entire team. If you are on an open source project, will your expect that from all your contributors ?
High barrier of entry, with no modularity. Linters, formatters, debuggers, auto-importers, they all depend on that one graphical commercial product that is not integrated with your regular IDE and other tools.
Not to say it's not a good editor if you do write a lot of SQL, as JetBrains products are always worth their price.
In my 23-and-a-bit years of web development I've literally never changed the database engine on a project. Maybe that happens on other people's projects, but it's not something I consider important or even useful really. The notion that you can swap out your database for a different one without changing the application code to take advantage of the db you're moving to is ludicrous. Of course your database code isn't portable.
Websites that used MySQL and Myisam tables with raw SQL statements written as strings in PHP for the first decade of the web is one of the reasons why so many web developers still don't use things like transactions, stored procedures, views, etc. That's a bit of a tragedy. The web would be much better today if everyone had been using Postgres's features from the beginning.
I've literally never changed the database engine on a project
Then there is the case where you don't write a project, but a lib. Or the case where you extract such a lib from a project. In that case, you limit the use case of your lib to your database. Because if you don't change database during your project life time, the users of your lib may start a new project with a different database.
This is why Django ORM allowed such a vibrant ecosystem: not because it allows changing the data base of one project (although it's nice to have and I used it several times), but also because it allow so called "django pluggable apps" to be database independant.