[audreyt]

The main motivation is to use the same set of npm-managed modules for backend and frontend model+logic.

A secondary motivation is using two familiar APIs — MongoLab and Firebase — to access existing PostgreSQL databases.

(We're using this in production at Socialtext and g0v.tw.)

[JonnieCache]

>A secondary motivation is using two familiar APIs — MongoLab and Firebase — to access existing PostgreSQL databases.

Is there really a significant population of developers now to whom these are more familiar query languages than SQL?

Honest question.

[audreyt]

For backend programming, SQL is certainly the most familiar language.

We don't generally send SQL over the wire, though. :-)

That is to say, front-end programmers usually work with a middleware (or backend-as-a-service) layer that translates REST/JS API requests into backend storage, and PgREST simply implements this layer with Postgres itself.

[JonnieCache]

How are these protocols different from the rails style JSON-over-REST I've been writing unchanged since 5 years ago? The kind of thing public web APIs for the likes of soundcloud expose?

EDIT: I did some research: there is no difference. It's just plain ol' rails-style REST. This project is basically taking your thin sinatra/express/flask API layer and pushing it into the database itself, for reasons I am as yet unable to ascertain.

[audreyt]

Excellent question! The REST API part should be familiar to any Rails programmer.

The main difference is that back-end models, validation rules, triggers and views are coded in DB level via stored procedures written in Node.js-compatible modules, so it's enforced for both SQL- and HTTP-speaking clients.

As you pointed out, this is simply an instant JSON-over-REST API server on top of existing Pg databases, and is not intended to replace the need for traditional frameworks with server-side templating.

[JonnieCache]

Ah, so I guess I could stop avoiding traditional SQL features like triggers for fear of having code floating around outside my main app codebase, and it would generally make everything more centralised. I wouldn't have to worry about my workers having access to the correct model code and so on. Interesting.

EDIT: how easy do you think it would be to do the authentication etc outside, at the level of the nginx proxy?

[audreyt]

For authentication (authn) it's quite easy, and in production we do have a separate authn daemon.

For authorization (authz) it's IMHO a bit better to handle it in the DB level, similar with Firebase's ACL lists.