Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgREST – REST API from any PostgreSQL database

github.com

Post reply on HN
Print view View original

51–60 of 210 posts

Re: PostgREST – REST API from any PostgreSQL database

#51

Post by batou »

benkant wrote: post #12

This is good work and if I ever did web development, it would be like this. Why people in the web world don't use stored procedures and constraints is a mystery to me. That this approach is seen as novel is in itself fascinating. It's like all those web framework inventors didn't read past chapter 2 of their database manuals. So they wrote a whole pile of code that forces you to add semantics in another language else…

That is quite frankly horse poop.

We use stored procedures. Not by choice; this is a legacy we're stuck with. It is nothing but an unadulterated disaster of a technology regardless of what you use it for. I'm talking 45,000 stored procedures here. 2000 tables. TiB of data. 50,000 requests/second across SOAP/web/desktop etc. It's hell.

Problems with stored procedures:

1) Performance. More code running in the hard to scale-out black box. You're just hanging yourself with hardware and/or license costs in the long run.

2) Maintenance. All database objects are stateful i.e. they have to be loaded to work. The sheer complexity of managing that on top of the table state results in massive cost. Add to that tooling, version control costs as well. Have you tried merging a stored procedure in a language which has no compiler and very loose verification?

3) Orthoganality. Nothing inside the relational model matches the logical concepts in your application. Think of transactions, domain modelling etc.

4) Duplication. You still have to write something in the application to talk to every single one of those stored procedures and map it to and from parameters and back to collections.

5) Transaction scoping. Do you know how expensive it is to introduce distributed transactions? Well it's a ton more cash when EVERYTHING is inside that black box.

6) Lock in. Your stored procedures aren't portable. Good luck trying to shift vendor in the future when you hit a brick wall.

Now I know it's popular to bash on Rails and I wouldn't use it personally but there are people using the same model on top of other platforms, like us.

Sorry but databases are just a hole to put your shit in when you want it out of memory. If you start investing too much in all of the specific functionality you're hanging yourself.

Re: PostgREST – REST API from any PostgreSQL database

#53

Post by greghinch »

adamors wrote: post #43
chx wrote: post #18

Earlier quoted context omitted.

Yes. HN is a bubble. There are ~700 PHP questions on SO a day and ~150 node.js. This is just one pair of numbers, you can mine your own whatever you like but you'll realize there are massive amounts of "web developers" with a ... low amount knowledge.

Thinking most web developers are still shipping PHP 5.3 apps on shared hosts is also a very outdated view.

Globally, it's not.

Re: PostgREST – REST API from any PostgreSQL database

#54

Post by batou »

glogla wrote: post #49
adamors wrote: post #44

Earlier quoted context omitted.

For one, stored procedures are hard to test, debug, maintain and check into source control. But don't let that get in your way of ignorantly generalising about web developers.

Stored procedures may be all of those things, but they don't have to - it's just that most of the time, developers don't really care, so they have fancy versioning, deployment and continuous integration for all their code, except for stored procedures. Here is interesting talk about database migrations and stored procedures and unit tests: http://www.pgcon.org/2013/schedule/events/615.en.html Also, DB procedures are…

Hmm no.

Avoid state at all costs. Stored procedures are stateful. Schema and migrations is pain enough already.

Write me a check constraint that validates an email address being put in a varchar column and reports back a sensible message which can be bound to an entry field with metadata about the error.

Write me a constraint and key arrangement which is unique across two and three columns in separate groups.

No. You're wrong.

Re: PostgREST – REST API from any PostgreSQL database

#56

Post by markbernard »

pilif wrote: post #5
wisty wrote: post #4

Example is broken. It's returning a JSON doc, so if you leave it then return, some browsers will just return the cached JSON (as text). Should add some header to say that it's JSON, or add a .json file extension for the main page data. Very interesting project though.

>Should add some header to say that it's JSON, or add a .json file extension for the main page data. The server sends `Content-Type: application/json` and provides no header related to caching. Browsers that do anything but fetching the resource again are not spec compliant. Also, the only browser to ever look at the extension of a file in the URL was IE ( https://msdn.microsoft.com/en-us/library/ms775147(v=vs.85).a.…

Are you sure about that? Cache-Control, Expires? If you don't change the URL IE will cache the response whether you like it or not. 2 ways to handle this are to generate a random number to append as a parameter to change the URL. The other way is to have your web service to tell the browser not to cache with response headers. I have had IE do this to me and made all my web services send back "Cache-Control: no-cache" to prevent IE caching.

Re: PostgREST – REST API from any PostgreSQL database

#57

Post by cies »

gizmodo59 wrote: post #55

With Data Virtualization providers like Denodo you can create a REST web service with any relational database very easily.. https://community.denodo.com/tutorials/browse/dataservices/2...

I'd be interested to see a benchmark. PostgREST is fast! And it is also a piece of software that tries to "do one thing really well". It deploys as a binary, which is also a big plus compared to these "first install this list of dependencies at these ranges of versions before using our product".

Last thing: is Denodo open source? It is not listed at "why use Denodo", so I guess not...

Re: PostgREST – REST API from any PostgreSQL database

#58

Post by adamors »

bni wrote: post #48
adamors wrote: post #44

Earlier quoted context omitted.

For one, stored procedures are hard to test, debug, maintain and check into source control. But don't let that get in your way of ignorantly generalising about web developers.

"hard to test, debug, maintain and check into source control." Why? I have never had problem with any of these. SPs is just imperative code like any other imperative code.

Well, you cannot isolate the code, you cannot unit test it, you cannot use a debugger, cannot set breakpoint, have stack traces etc. You are tied to the database at all times.

In my experience, every stored procedure that is larger than 2-3 lines is a headache.

Re: PostgREST – REST API from any PostgreSQL database

#59

Post by cies »

bni wrote: post #52

What about when changes are made to the schema, wont the API just be changed in that case? Wont this lock you in with very hard coupling between your db schema and public REST API?

You could always write a custom API using any one of the many libraries/frameworks fit to do so.

If you want to use Haskell (easy binary deploys, fast and strict type safety for cheaper maintenance, less bugs, easy refactoring) you could have a look at servant[1].

[1]: https://github.com/haskell-servant/servant

Re: PostgREST – REST API from any PostgreSQL database

#60

Post by bni »

adamors wrote: post #58
bni wrote: post #48

Earlier quoted context omitted.

"hard to test, debug, maintain and check into source control." Why? I have never had problem with any of these. SPs is just imperative code like any other imperative code.

Well, you cannot isolate the code, you cannot unit test it, you cannot use a debugger, cannot set breakpoint, have stack traces etc. You are tied to the database at all times. In my experience, every stored procedure that is larger than 2-3 lines is a headache.

All of those are no problem with the right tools. Any database IDE can debug SPs. You can unit test SPs like any other code just use a testrunner.
Post reply on HN