4 ms·

But that also means that: - the code is not reusable outside of a database setting. So not cacheable. - the code is not reusable accross different storage lay

But that also means that: - 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.

Joeri 7y ago
I agree, but I also see the benefits from the other side, so I’ll play devil’s advocate: - 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?
- sscarduzio 7y ago
  The tooling is just fine for me, DataGrip or not: I wrote a ton of PostgreSQL triggers and utility functions in Javascript thanks to the excellent plv8 (free) extension. Development and unit tests for these functions is as easy as plain Javascript.
  - BiteCode_dev 7y ago
    > Development and unit tests for these functions is as easy as plain Javascript. 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-237711101 https://github.com/plv8/plv8/issues/131#issuecomment-2377111...
    - sscarduzio 7y ago
      I personally this time around, I found no need to even use a framework for unit testing a single javascript function. 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.
- BiteCode_dev 7y ago
  > it is cached, in the database’s memory, where the cache can be invalidated automatically. It is better to cache views than data anyway. 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.
remus 7y ago
It also means you need a per-user database connection which isn't feasible in many applications as the recommended number of concurrent connections is typically quite low.
- chatmasta 7y ago
  Not necessarily. You can SET ROLE at beginning of transaction and DISCARD afterward. This is what postgraphile and postgrest do, for example.
  - steve-chavez 7y ago
    postgrest actually does a `SET LOCAL ROLE`(transaction scoped, so no need to DISCARD), but yes, that's the gist of it.
  - remus 7y ago
    Oh cool, I didn't know that. Good to learn!
- devit 7y ago
  You can just change the user for every query.
- BenjieGillam 7y ago
  This absolutely isn't required; I think you might be getting confused with RBAC rather than RLS. Handy infosheet: https://learn.graphile.org/docs/PostgreSQL_Row_Level_Security_Infosheet.pdf https://learn.graphile.org/docs/PostgreSQL_Row_Level_Securit...
onion2k 7y ago
the code is not reusable accross different storage layers. So not portable. 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.
- bernawil 7y ago
  I've literally never changed the database engine on a project And some of us do it several times a day because we deploy to prod with Postgres but run testing and CI with SQLite.
  - Matrixik 7y ago
    Me too, until I found how fast Postgres is starting in Docker and for different languages there are now libraries helping you writing tests using Docker containers.
  - patrec 7y ago
    Well, you're doing it wrong. It's easy and fast to run tests against real postgres (takes a fraction of a second to spin up the DB for your tests if you do it right).
- Deadron 7y ago
  I have never seen anybody migrate out of PostgreSQL, but I HAVE seen people migrate out of Oracle, and DB2 because they are so expensive its worth the cost to migrate.
- BiteCode_dev 7y ago
  If you write a project, you probably won't change the database. But you may add new data layer services such as a cache backend, a search engine with, a secret server, a session store, a permission micro service, etc. They will all interact with permissions and change where you source the data from. 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.

Joeri 7y ago

I agree, but I also see the benefits from the other side, so I’ll play devil’s advocate: - 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?

sscarduzio 7y ago
The tooling is just fine for me, DataGrip or not: I wrote a ton of PostgreSQL triggers and utility functions in Javascript thanks to the excellent plv8 (free) extension. Development and unit tests for these functions is as easy as plain Javascript.
- BiteCode_dev 7y ago
  > Development and unit tests for these functions is as easy as plain Javascript. 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-237711101 https://github.com/plv8/plv8/issues/131#issuecomment-2377111...
  - sscarduzio 7y ago
    I personally this time around, I found no need to even use a framework for unit testing a single javascript function. 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.
BiteCode_dev 7y ago
> it is cached, in the database’s memory, where the cache can be invalidated automatically. It is better to cache views than data anyway. 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.

remus 7y ago

It also means you need a per-user database connection which isn't feasible in many applications as the recommended number of concurrent connections is typically quite low.

chatmasta 7y ago
Not necessarily. You can SET ROLE at beginning of transaction and DISCARD afterward. This is what postgraphile and postgrest do, for example.
- steve-chavez 7y ago
  postgrest actually does a `SET LOCAL ROLE`(transaction scoped, so no need to DISCARD), but yes, that's the gist of it.
- remus 7y ago
  Oh cool, I didn't know that. Good to learn!
devit 7y ago
You can just change the user for every query.
BenjieGillam 7y ago
This absolutely isn't required; I think you might be getting confused with RBAC rather than RLS. Handy infosheet: https://learn.graphile.org/docs/PostgreSQL_Row_Level_Security_Infosheet.pdf https://learn.graphile.org/docs/PostgreSQL_Row_Level_Securit...

onion2k 7y ago

the code is not reusable accross different storage layers. So not portable. 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.

bernawil 7y ago
I've literally never changed the database engine on a project And some of us do it several times a day because we deploy to prod with Postgres but run testing and CI with SQLite.
- Matrixik 7y ago
  Me too, until I found how fast Postgres is starting in Docker and for different languages there are now libraries helping you writing tests using Docker containers.
- patrec 7y ago
  Well, you're doing it wrong. It's easy and fast to run tests against real postgres (takes a fraction of a second to spin up the DB for your tests if you do it right).
Deadron 7y ago
I have never seen anybody migrate out of PostgreSQL, but I HAVE seen people migrate out of Oracle, and DB2 because they are so expensive its worth the cost to migrate.
BiteCode_dev 7y ago
If you write a project, you probably won't change the database. But you may add new data layer services such as a cache backend, a search engine with, a secret server, a session store, a permission micro service, etc. They will all interact with permissions and change where you source the data from. 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.