JavaScript in your Postgres(postgres.heroku.com)
postgres.heroku.com
JavaScript in your Postgres
https://postgres.heroku.com/blog/past/2013/6/5/javascript_in_your_postgres/
7 comments
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.
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?
I have written quite a bit on my blog (http://ledgersmbdev.blogspot.com) on how to avoid sproc hell when porting a lot of data access logic to the database. LedgerSMB more or less is moving to a model where all non-service-locator queries (which look up stored procedures) are stored procedures and we believe we have solved most of these problems.
There are significant problems with stored procedures if they aren't used right. VARIADIC stored procedures help, but they don't entirely solve the problem. Fortunately PostgreSQL offers some rich tools for solving some of these problems. For example we make extensive use of named arguments.
But there is a second use here as well, and this is to extend what you can do inside a SQL query and when you combine this with JSON types, you can do quite a lot.
For example, suppose you do something like:
SELECT extract(myjsonfld, 'property1') FROM mytable WHERE id = 123;
There is no extract function in PostgreSQL 9.1-2 (there will be in 9.3 but not sure what the semantics will be), so you could write such a function in Javascript, such that it takes a JSON and string input and outputs a string or JSON object depending on what you want.
This allows you to extend the capabilities of SQL to do whatever you need.
There are significant problems with stored procedures if they aren't used right. VARIADIC stored procedures help, but they don't entirely solve the problem. Fortunately PostgreSQL offers some rich tools for solving some of these problems. For example we make extensive use of named arguments.
But there is a second use here as well, and this is to extend what you can do inside a SQL query and when you combine this with JSON types, you can do quite a lot.
For example, suppose you do something like:
SELECT extract(myjsonfld, 'property1') FROM mytable WHERE id = 123;
There is no extract function in PostgreSQL 9.1-2 (there will be in 9.3 but not sure what the semantics will be), so you could write such a function in Javascript, such that it takes a JSON and string input and outputs a string or JSON object depending on what you want.
This allows you to extend the capabilities of SQL to do whatever you need.
(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.
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.
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).
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).
> 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?
Because it doesn't have anything to do with "Stored Procedure hell". Having one more choice of procedural languages (of which there are already many available for Postgres) doesn't do anything to force you to adopt bad practices as to when you use procedural code in the database.
Because it doesn't have anything to do with "Stored Procedure hell". Having one more choice of procedural languages (of which there are already many available for Postgres) doesn't do anything to force you to adopt bad practices as to when you use procedural code in the database.
The thing I hated most about stored procedures was all the logic and dynamic SQL that creeped in.
The example in this link and what a lot of us hope to use it for is schema-less data storage.
I'm not going to put any logic into my v8 functions except for accessing data.
The example in this link and what a lot of us hope to use it for is schema-less data storage.
I'm not going to put any logic into my v8 functions except for accessing data.
JavaScript is a real language.
I've never seen IMMUTABLE used to describe a function before... Wouldn't PURE (a la Rust) be less confusing?
fyi, I believe that the 'pure' keyword has been removed in recent releases of Rust.
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...
Can't find the original thread, but you'll find [1] interesting.
1: https://mail.mozilla.org/pipermail/rust-dev/2013-January/002...
1: https://mail.mozilla.org/pipermail/rust-dev/2013-January/002...
Wow that was very interesting indeed, thanks.
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?
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 [email protected] and let us know which ones you'd like and why.
In the future we may support additional languages and if there's particular ones please feel free to drop us a line at [email protected] and let us know which ones you'd like and why.
Thanks for the response. OK, I buy the security issue. My Javascript skills - once pretty good - are bit rusty so Ill have to brush up on it. But I do have an ides for a project and will download postgres later today.
PL/Lua can be installed in either trusted and untrusted versions.
http://pllua.projects.pgfoundry.org/
http://pllua.projects.pgfoundry.org/
any chance of supporting fdw(foreign data wrappers)?
We're very excited to see FDWs evolve. In the future theres some chance we will support them, but no immediate timeline available.
postgres 9.3 is bringing writes to FDW and this will be really interesting
Except that around 1999 it was already possible to write stored procedures in Perl on Oracle. Eventually it was replaced by Java.
SQL Server allows for .NET stored procedures, so you could already use something like JScript.NET.
I fail to see what you mean by thinking out of the box.
SQL Server allows for .NET stored procedures, so you could already use something like JScript.NET.
I fail to see what you mean by thinking out of the box.
Well, at least they're thinking outside MySQL's box.
That's true of pretty much any database.
Procedural language handlers exist in the core distribution for Perl, Python, Tcl, and PostgreSQL's own procedural language, PL/pgSQL.
Third party handlers exist, off the top of my head, for JavaScript (obviously; see TFA), R, Ruby, Scheme and shell. That list probably isn't remotely exhaustive.
Third party handlers exist, off the top of my head, for JavaScript (obviously; see TFA), R, Ruby, Scheme and shell. That list probably isn't remotely exhaustive.
Java and C too.
To be honest, though, I've never really had much of a use for any language other than pl/pgsql and occasionally pl/r; I try to keep anything too complicated outside of the database.
To be honest, though, I've never really had much of a use for any language other than pl/pgsql and occasionally pl/r; I try to keep anything too complicated outside of the database.
The languages are, I guess, most useful for people who design their database as a datastore with an API, used by multiple independent clients (a common setup in enterprises). The API typically consists of stored procedures, and normal queries are disallowed for most usernames.
Being able to implement such an API in $DECENT_LANGUAGE and not pl/pgsql sounds like an enormous win.
Being able to implement such an API in $DECENT_LANGUAGE and not pl/pgsql sounds like an enormous win.
> postgres is really thinking outside the SQL box and I love it. JSON support was great and Javascript fits in with it.
Postgres is awesome, but the JavaScript support from PL/V8 is a third-party extension, not a core database feature.
Postgres is awesome, but the JavaScript support from PL/V8 is a third-party extension, not a core database feature.
Postgres is an extensible database. That's the beauty of the project.
I don't think you'd say "well, Ruby is awesome but web application development support comes from a third-party project, not the standard library."
I don't think you'd say "well, Ruby is awesome but web application development support comes from a third-party project, not the standard library."
> Postgres is an extensible database. That's the beauty of the project.
I agree, I just thought from the reference to JSON (which is a core database feature) that PL/v8 was being incorrectly attributed the postgres team directly.
> I don't think you'd say "well, Ruby is awesome but web application development support comes from a third-party project, not the standard library."
I might if someone pointed to Rails with a comment that implied it was a credit to the Ruby team and a next step to a stdlib feature.
I agree, I just thought from the reference to JSON (which is a core database feature) that PL/v8 was being incorrectly attributed the postgres team directly.
> I don't think you'd say "well, Ruby is awesome but web application development support comes from a third-party project, not the standard library."
I might if someone pointed to Rails with a comment that implied it was a credit to the Ruby team and a next step to a stdlib feature.
That's fair enough. It's worth noting that Hitoshi Harada, the original author, is a long time contributor to the core project.
Python has been available since v8.4: http://www.postgresql.org/docs/9.2/static/plpython.html.
Javascript is like a virus.
Does anybody else think "Yo dawg, I heard you like..." belongs in that title somewhere?
can someone give another usage for v8 other than json?
At Socialtext, we're working on https://npmjs.org/package/plv8x that installs npm packages into Pg, and maps Node methods directly into Pg functions.
This provides a safe and modular alternative to PL/PgSQL and PL/Perl, and lets us re-use client side models & validators in the database.
https://npmjs.org/package/pgrest builds on this work and offers a subset of MongoLab REST API for an existing Pg database, with the eventual aim of serving JSON APIs directly from the database, cutting out the middleware altogether.
We're also looking at adding Firebase-like ACLs into the mix.
Currently there's just the NPM doc'n and a bilingual presentation at https://speakerdeck.com/audreyt/pgrest-node-dot-js-in-the-da... —— documentation will appear on http://pgre.st/ as soon as the API solidifies.
This provides a safe and modular alternative to PL/PgSQL and PL/Perl, and lets us re-use client side models & validators in the database.
https://npmjs.org/package/pgrest builds on this work and offers a subset of MongoLab REST API for an existing Pg database, with the eventual aim of serving JSON APIs directly from the database, cutting out the middleware altogether.
We're also looking at adding Firebase-like ACLs into the mix.
Currently there's just the NPM doc'n and a bilingual presentation at https://speakerdeck.com/audreyt/pgrest-node-dot-js-in-the-da... —— documentation will appear on http://pgre.st/ as soon as the API solidifies.
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).
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).
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.
Bonus fun: IBM DB2 has a PL/SQL front end that compiles to their own bytecode.
Sure, it lets you use JavaScript at every level, from the client side (via in-browser JS) to the server (application side) via Node.js to the backend database (via PL/v8.)
So you don't need different languages for client components, server components, and stored procs / db functions.
So you don't need different languages for client components, server components, and stored procs / db functions.
The author of PLV8 is also continuing to enrich the functionality with things such as this early github project which aims to add the mongo API and functionality in Postgres - https://github.com/umitanuki/mongres
is there integration like this with elasticsearch? that would be awesome.
There is https://github.com/elasticsearch/elasticsearch-lang-javascri...
Though, it is based on Rhino, not v8 – and it's not sandboxed.
Though, it is based on Rhino, not v8 – and it's not sandboxed.
Chris Travers from the LedgerSMB project has done a lot of thinking about this topic on his blog.
http://ledgersmbdev.blogspot.com/
They use PL/pgSQL to provide a bunch of service interfaces, then have Perl that provides automatic service discovery and object generation. Basically ORM from the inside out.
http://ledgersmbdev.blogspot.com/
They use PL/pgSQL to provide a bunch of service interfaces, then have Perl that provides automatic service discovery and object generation. Basically ORM from the inside out.