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

61–70 of 210 posts

Re: PostgREST – REST API from any PostgreSQL database

#61

Post by alfonsodev »

fica wrote: post #9

Would be cool to put Kong [1] on top of the API to handle JWT or CORS [2] out of the box. [1] https://github.com/mashape/kong [2] http://getkong.org/plugins/

I think that would a good separation of concerns, I didn't know Kong, but it seems that is more specialised tool supporting Oauth2, Rate limit, Ip filtering ..etc via plugins. I would like to see both tools running in Docker and working together. I started this public gist to explore this solution https://gist.github.com/alfonsodev/6a6c66b4074248ed9702 feel free to comment and collaborate there.

Re: PostgREST – REST API from any PostgreSQL database

#62

Post by ilitirit »

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…

I find it strange when people adhere to these extremely dogmatic ideas about stored procedures. They appear to be either on one end of the scale or the other, ie. they either put all their logic in to Stored Procedures, or refuse to use them at all.

Of course, the reality is that people who use them reasonably do exist and are probably in the majority. You just rarely hear them talk about it because I suspect that they hold the same views about Stored Procedures, Constraints and any other DBMS feature as they do with any other software development tool ie. Use the right one for the job.

Re: PostgREST – REST API from any PostgreSQL database

#63

Post by kitd »

batou wrote: post #51
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 s…

Sorry but databases are just a hole to put your shit in when you want it out of memory.

You've got to be joking, right?

Data is an enterprise's single biggest asset. A robust, consistent and performant store is vital. SPs can be written as garbage like any other logic, but in the right hands they are a perfectly valid tool for providing useful access to complex data.

Re: PostgREST – REST API from any PostgreSQL database

#64

Post by wisty »

msane wrote: post #8
pilif wrote: post #5

Earlier quoted context omitted.

>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.…

Correct answer. The demo is doing everything right, parent seems confused. Browsers also don't have any special regard for '.json' in the path. The path can be anything; the path doesn't suggest anything about content-type or caching.

Yes, but if / returns a html file, and /.json returns the json, it's impossible for a browser to display the json by accident (no matter what the header is).

And yes, the header seems correct, Checking, that's the API demo, the GUI is separate. Thus the confusion.

Yeah, headers are the right way to do it, but a different path is also the right way to do it, and extensions like .json can make that simpler in some cases. Or /api/ prefixes.

Re: PostgREST – REST API from any PostgreSQL database

#65

Post by 3pt14159 »

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…

You are speaking from ignorance with the voice of authority.

I worked on a rails app that handled a billion requests per day. The problem isn't performance of the web framework, those are easy to load balance and split into C or cache when you need it. The problem is scaling your database, keeping your data secure, and iterating to meet business goals with a growing codebase and infrastructure. A mess of stored procedures would restrain you from doing all three.

And I know, I worked on a codebase in 1999 that did this because of the "performance gains". It ended up bricking the project due to inability to iterate.

Re: PostgREST – REST API from any PostgreSQL database

#66

Post by LGBT_2000 »

batou wrote: post #51
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 s…

1) Makes no sense whatsoever; stored procedures are typically going to be much faster than the equivalent mix of application-level code written in a scripting language that needs to communicate with a database and is likely vastly slower than PL/pgSQL or PL/SQL. The hardware licensing costs having nothing to do with stored procedures and apply just as much to anything else (there's free as an beer options and costly proprietary ones in either case).

2) This is not a reasonable objection, I could replace "loaded" with "compiled" and your non-argument would make just as much sense. The alternative does not make the "complexity" go away, it just distributes across multiple languages in your application and database.

3) No.

4) Another non-argument against stored procedures. For example, suppose I have a table "time_series(series_id INT, tstamp TIMESTAMP, val NUMERIC)". A common need would be to accumulate all points (tstamp, val) associated with a series_id. Following your logic, you either end up with tons of con the application side sending similar variations of a query that looks like "SELECT tstamp, val FROM time_series WHERE series_id = $x ORDER BY tstamp" or you create one application-level module that acts as an abstraction around a query like that. In the first case, you're doing massive duplication. In the second case, you've essentially made a stored procedure that is distributed across your database and application and all the issues you raised of having to write something to talk to it apply just the same.

5) Again, no. DBs are precisely to place to deal with issues like this as they have means for dealing with things like foreign tables. The application-level alternative just means re-inventing it all yourself and you're probably going to make a lot more mistakes and write a lot more code that way.

6) Non-argument (applies just as well to Rails, Python, Linux, etc.)

Re: PostgREST – REST API from any PostgreSQL database

#67

Post by glogla »

ilitirit wrote: post #62
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…

I find it strange when people adhere to these extremely dogmatic ideas about stored procedures. They appear to be either on one end of the scale or the other, ie. they either put all their logic in to Stored Procedures, or refuse to use them at all. Of course, the reality is that people who use them reasonably do exist and are probably in the majority. You just rarely hear them talk about it because I suspect that th…

Yeah. Lot of the time, for a CRUD app, the who layer between rendering and data storage doesn't really do much besides validations, and those can be in database, so whole middle layer can be unnecessary.

Sometimes, the logic has to be in database, because it is single point of truth and because many application servers are hard to synchronize with regards to "you get max 3 attempts at login" or "you have to have enough balance to do bank transfer".

Sometimes, lot of your logic is in database, because database can do a lot of things in really fast and practical way, like aggregation and reporting and various data exploration tasks.

Sometimes, the database is just dumb store of object oriented data.

It depends on the application.

Re: PostgREST – REST API from any PostgreSQL database

#68

Post by glogla »

bni wrote: post #60
adamors wrote: post #58

Earlier quoted context omitted.

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.

Yeah. I suspect that lot of hate of logic in DB is because of bad Oracle setups many years ago, kind of like lot of people think SQL is useless because MySQL is.

Re: PostgREST – REST API from any PostgreSQL database

#69

Post by glogla »

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 can do that using SQL views. That means you can provide consistent api of your choosing no matter how the original tables look like. Postgrest docs also describe how you should use this feature to version you API.

Re: PostgREST – REST API from any PostgreSQL database

#70

Post by spdionis »

batou wrote: post #51
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 s…

You had me until your last sentence. Database are very important to (and very good at) store your data. And data is important (duh). All the issues you described above are related to working with the data, which should not happen in the database.
Post reply on HN