
JavaScript in your Postgres - jacobian
https://postgres.heroku.com/blog/past/2013/6/5/javascript_in_your_postgres/
======
audreyt
In addition to JavaScript, the plv8 module also supports CoffeeScript and
LiveScript ( <http://livescript.net/> ) procedures, with "CREATE EXTENSION
plcoffee" and "CREATE EXTENSION plls" respectively.

------
gfodor
As someone who lived through the Stored Procedure hell of the late 90's and
early 00's, can someone explain to me why this shouldn't scare the bejeezus
out of me?

~~~
hgimenez
(I work on Heroku Postgres)

During the afformentioned "Sproc Hell", we were putting application logic in
the database. Of course, this made perfect sense: it was secure because of
bound params and strict typing, it was fast because it avoided several trips
to the database for multi query operations and even for single query
statements, query plans were precomputed and cached by the DB. You were also
able to tweak application logic without deploying code, which was likely a
clumsy process involving more than one team and various manual steps. This is
all bollocks, as we've learned many scars and gray hairs later.

Now, the proposal here is entirely different. While yes, you are creating a
function in your database, you are doing it to access data in a JSON
structure, per the OP. Because in Postgres you can create an index on the
result of any expression, including a function, you can now create indexes on
functions that parse and access data your JSON docs. And it's fast.

~~~
einhverfr
I don't think it was all bollocks. It was just due to the fact that sproc
interfaces sucked. Also development of quality sprocs is qualitatively
_different_ than upper level app code (among other things, you want a single
large query front and center to the extent possible), and so if you write
stored procedures the way you write application code _they will suck._

Now, what we do with LedgerSMB is build our stored procedures as basically
named queries, inspired by web services (both SOAP and REST have been
inspirations there). The procedures are intended to be relatively discoverable
at runtime, with the aggressive attempts to use what infrastructure exists for
this purpose that REST gives for HTTP.

Stored procedures are not a problem. They allow you to encapsulate a database
behind an API, and the desire to do that is a major point of Martin Fowler's
NoSQL advocacy (arguing for doing this for NoSQL dbs).

------
alexatkeplar
I've never seen IMMUTABLE used to describe a function before... Wouldn't PURE
(a la Rust) be less confusing?

~~~
portmanteaufu
fyi, I believe that the 'pure' keyword has been removed in recent releases of
Rust.

~~~
alexatkeplar
Oh really! Is there a discussion somewhere as to why? It seemed (from the
outside) like a neat idea for any language which supports mutable as well as
immutable variables...

~~~
mercurial
Can't find the original thread, but you'll find [1] interesting.

1: [https://mail.mozilla.org/pipermail/rust-
dev/2013-January/002...](https://mail.mozilla.org/pipermail/rust-
dev/2013-January/002903.html)

~~~
alexatkeplar
Wow that was very interesting indeed, thanks.

------
greenlakejake
postgres is really thinking outside the SQL box and I love it. JSON support
was great and Javascript fits in with it. Now how about support for other
languages like Python/Ruby/Lua?

~~~
craigkerstiens
PM of Heroku Postgres here. Part of choosing JavaScript and in particular V8
was that its a fully sandboxed language. Other languages while also very
powerful can have various security risks that come along with them.

In the future we may support additional languages and if there's particular
ones please feel free to drop us a line at postgres@heroku.com and let us know
which ones you'd like and why.

~~~
Goranek
any chance of supporting fdw(foreign data wrappers)?

~~~
craigkerstiens
We're very excited to see FDWs evolve. In the future theres some chance we
will support them, but no immediate timeline available.

~~~
Goranek
postgres 9.3 is bringing writes to FDW and this will be really interesting

------
rheide
Javascript is like a virus.

------
TallGuyShort
Does anybody else think "Yo dawg, I heard you like..." belongs in that title
somewhere?

------
Goranek
can someone give another usage for v8 other than json?

~~~
mhd
Some setups are pretty heavy on stored procedures. Not too long ago I had a
job interview at a company who avoided ORMs and did every complex SQL
manipulation that way. So on the client side it was pretty specific what they
wanted and instead of writing weird call chains (or mixing code with dozens of
lines of SQL), you just did that in the database itself. Don't think they used
JavaScript, but if you're already using it in lots of other places, it seems
like a natural way to go.

Now I'm actually for polyglot programming and don't think you should do
everything with just one tool (especially if its JavaScript), but for stored
procedures you're not really losing a lot. This _is_ scripting, after all. And
the less said about the DB-"native" PLs, the better. I've still got nightmares
about inappropriate use cases of PL/SQL (and hey, I actually _like_ Ada).

~~~
jacques_chester
PL/SQL only looks like Ada, it's not very featuresome.

Bonus fun: IBM DB2 has a PL/SQL front end that compiles to their own bytecode.

