PostgREST – REST API from any PostgreSQL database • Hacker News Board

Re: PostgREST – REST API from any PostgreSQL database

#151

Post by hippich » Wed, Jul 22, 2015, 4:47 PM UTC

arturventura wrote: post #14

"It provides a cleaner, more standards-compliant, faster API than you are likely to write from scratch." If you are using this as a web server persistence backend, I would agree with the first, more or less accept the second and reject the third. HTTP + JSON serialisation are way slower for that kind of job. If you are just exposing the database using only the Postgres, in that case is interesting, however, I have co…

I believe idea here is to put all the permissions into DB and let frontend code do all the business logic.

Re: PostgREST – REST API from any PostgreSQL database

#152

Post by fweespeech » Wed, Jul 22, 2015, 4:49 PM UTC

threeseed wrote: post #133

bkeroack wrote: post #116

Earlier quoted context omitted.

A mess is a mess, true, but some are easier to clean up than others. GP is correct. Methods for scaling/optimizing the application layer are clear and well-known. Scaling the data layer is a huge challenge. This is why the market is filled with snake oil databases promising linear scalability and perfect consistency/reliability, etc.

Scaling the data layer is a huge challenge. No doubt. But calling databases that are designed for solving these problems "snake oil" undermines the huge amount of work that serious engineers have invested in this. No one has ever promised linear scalability and perfect consistency/reliability. No one. Cassandra, HBase, CouchDB etc even MongoDB have built in scalability as a first order priority from day one and have…

MongoDB heavily implied the linear scalability, consistency, reliability bit in its early material [particularly in their marketing].

Its only in the past couple years they really started mentioning the fact it was "tunable consistency" blatantly rather rather than burying it in a couple places in the manual.

Re: PostgREST – REST API from any PostgreSQL database

#153

Post by dragonwriter » Wed, Jul 22, 2015, 4:49 PM UTC

batou wrote: post #54

glogla wrote: post #49

Earlier quoted context omitted.

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…

> Avoid state at all costs. Stored procedures are stateful.

Stored procedures are no more state than application code is.

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

What does "unique across two and three columns in separate groups" mean? I get that its something more complex than a simple multicolumn uniqueness constraint, but not what it is supposed to do.

I suspect that whatever it is can be done with PostgreSQL -- possibly using the (relatively) new exclusion constraints -- but I can't quite be sure without more clarity on what you mean.

Re: PostgREST – REST API from any PostgreSQL database

#154

Post by chrismarlow9 » Wed, Jul 22, 2015, 4:50 PM UTC

curiousjorge wrote: post #148

The comments are unbelievably negative considering the quality and the range of features this offers. This is extremely useful because I won't have to spend time writing out REST api in order to expose the Postgre data. Often a client just wants to access the data with REST api and to write an entire stack just to serve a few doesn't make sense. There's no expectation that this is going to serve a gazillion requests…

Best advice I ever got from any engineer that actually caused me to start completing projects was "complicate as necessary, not as desired..."

Re: PostgREST – REST API from any PostgreSQL database

#155

Post by scttnlsn » Wed, Jul 22, 2015, 4:50 PM UTC

caf wrote: post #45

jister wrote: post #32

I'm sorry but why would I go through HTTP to query data? Why can't I just hit the database directly without the overhead of HTTP? Does a cleaner and being more standards-compliant worth the overhead of passing through HTTP? And what happens when you start applying complex business rules that needs to scale? So many questions about this approach...

Presumably because browsers talk HTTP, but don't talk Postgres' native protocol.

Yeah, this seems great if you want to completely eliminate the middle tier and have a client application talk directly w/ the database. I'm curious why there's an entire query API via query string parameters. Why not just expose a single POST /query endpoint where you can send some SQL?

Re: PostgREST – REST API from any PostgreSQL database

#156

Post by framp » Wed, Jul 22, 2015, 4:52 PM UTC

kaikokaur wrote: post #149

framp wrote: post #81

Earlier quoted context omitted.

I don't think using stored procedures should be the focus of the project. PostgREST is great because it lets you kickstart a CRUD application with ease. I'm mainly a node.js developer nowadays and I'm using some frameworks to kickstart APIs for my clients - and then I jump in and add features. What I really want is a solution to build a API server which deals with authentication, exposing my models through REST and o…

You may want to see http://www.zazler.com/ or https://www.npmjs.com/package/zazler

I'm using hapi + sequelize or loopback actually. Zazler looks interesting, thanks for the link!

Re: PostgREST – REST API from any PostgreSQL database

#157

Post by jfarmer » Wed, Jul 22, 2015, 4:54 PM UTC

chrismarlow9 wrote: post #154

curiousjorge wrote: post #148

The comments are unbelievably negative considering the quality and the range of features this offers. This is extremely useful because I won't have to spend time writing out REST api in order to expose the Postgre data. Often a client just wants to access the data with REST api and to write an entire stack just to serve a few doesn't make sense. There's no expectation that this is going to serve a gazillion requests…

Best advice I ever got from any engineer that actually caused me to start completing projects was "complicate as necessary, not as desired..."

And unless an engineer is psychic, this usually results in better, more robust products. Bonus! :D

Re: PostgREST – REST API from any PostgreSQL database

#158

Post by FLUX-YOU » Wed, Jul 22, 2015, 4:56 PM UTC

abrookewood wrote: post #83

batou wrote: post #51

Earlier quoted context omitted.

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…

Some counter-points I've heard made: 1) Performance. Stored procedures are fast, meaning it will be longer before you need to scale out. 2) Security. If you only use stored procs, you're a lot less exposed to SQL injections etc. I don't really have a firm opinion either way, but it's not as clear cut as you are making out.

>If you only use stored procs, you're a lot less exposed to SQL injections etc.

How does that help you vs. prepared statements in any typical language?

I've seen SQL statements in SPs that are concatenated (|| in oracle) to varchar fields from a table and I thought that would be just as vulnerable?

Re: PostgREST – REST API from any PostgreSQL database

#159

Post by marknadal » Wed, Jul 22, 2015, 5:16 PM UTC

Wow, there is a lot of contention in this thread. So first off I want to say congratulations to the author of PostgREST. Getting 2k req/s out of a Heroku free tier is just awesome ontop of all the overhead convenience you provide. Great job, great documentation, all around looking fantastic. You deserve to be on HN homepage.

Second, I'm an author of a distributed database (VC backed, open-source), so I'd like to respond to some of opinions on databases voiced in this thread - particularly in the branched discussions. If you aren't interested in those responses, you can ignore the rest of my comment.

- "You'd have to pay me a million dollars a year to do web development." Don't worry, most webdev jobs are about a tenth of that. If inflation goes up even a little bit...

- "The problem is scaling your database", I can confirm that this is my experience as well. But there is a very specific reason for that. Most databases are designed to be Strongly Consistent (of the CAP Theorem) and thus use Master-Slave architecture. This ultimately requires having a centralized server to handle all your writes, and this becomes extraordinarily prone to failure. To solve this, I looked into Master-Master (or Peer-to-Peer / Decentralized) algorithms for my http://gunDB.io/ database. Point being, I'm siding with @3pt14159 in this thread.

- "Sorry but databases are just a hole to put your shit in when you want it out of memory", I write a database and... uh, I unfortunately kind of have to agree, probably at the cost of making fun of my own product. You see, the reason why is because most databases now a days are doing the same thing - they keep the active data set in memory and then have some fancy flush mechanism to a journal on disk and then do some cleanup/compression/reorganizing of the disk snapshot with some cool Fractal Tree or whatever. But it does not matter how well you optimize your Big O queries... if the data isn't in memory, it is going to be slow (to see why, zoom in on this photo http://i.imgur.com/X1Hi1.gif ). You just can't get the performance (or scale) without preloading things into RAM, so if your database doesn't do that... well what @batou said.

Overall, I urge you to listen to @3pt14159 and @batou. PostgreSQL is undeniably awesome, but please don't fanboy yourself into ignorance. Machines and systems have their limitations, and you can't get around them by throwing more black boxes at it - your app will still break and so will your fanboyness.

Re: PostgREST – REST API from any PostgreSQL database

#160

Post by AlisdairO » Wed, Jul 22, 2015, 5:21 PM UTC

threeseed wrote: post #133

bkeroack wrote: post #116

Earlier quoted context omitted.

A mess is a mess, true, but some are easier to clean up than others. GP is correct. Methods for scaling/optimizing the application layer are clear and well-known. Scaling the data layer is a huge challenge. This is why the market is filled with snake oil databases promising linear scalability and perfect consistency/reliability, etc.

Scaling the data layer is a huge challenge. No doubt. But calling databases that are designed for solving these problems "snake oil" undermines the huge amount of work that serious engineers have invested in this. No one has ever promised linear scalability and perfect consistency/reliability. No one. Cassandra, HBase, CouchDB etc even MongoDB have built in scalability as a first order priority from day one and have…

> It's always a shame to see HN act like you scale vertically and magically every problem is solved.

When this is seen (and IME it's a pretty minority opinion) I think it's there as a reaction to the massive overuse and hype regarding a lot of newer-gen DBs. There's absolutely no doubt that there are good uses for them, but those cases are pretty niche compared to the level of their uptake.