7 ms·
JavaScript in your Postgres
- greenlakejake 13y agopostgres 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?
- smilliken 13y agoPython has been available since v8.4: http://www.postgresql.org/docs/9.2/static/plpython.html http://www.postgresql.org/docs/9.2/static/plpython.html.
- craigkerstiens 13y agoPM 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 13y agoany chance of supporting fdw(foreign data wrappers)?
- craigkerstiens 13y agoWe're very excited to see FDWs evolve. In the future theres some chance we will support them, but no immediate timeline available.
- Goranek 13y agopostgres 9.3 is bringing writes to FDW and this will be really interesting
- greenlakejake 13y agoThanks 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.
- jacques_chester 13y agoPL/Lua can be installed in either trusted and untrusted versions. http://pllua.projects.pgfoundry.org/ http://pllua.projects.pgfoundry.org/
- dragonwriter 13y ago> 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.
- pvh 13y agoPostgres 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."
- dragonwriter 13y ago> 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.
- pvh 13y agoThat's fair enough. It's worth noting that Hitoshi Harada, the original author, is a long time contributor to the core project.
- rosser 13y agoProcedural 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.
- petepete 13y agoJava 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.
- skrebbel 13y agoThe 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.
- pjmlp 13y agoExcept 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.
- skrebbel 13y agoWell, at least they're thinking outside MySQL's box.
- brokenparser 13y agoThat's true of pretty much any database.
- Goranek 13y agocan someone give another usage for v8 other than json?
- dragonwriter 13y agoSure, 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.
- craigkerstiens 13y agoThe 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 https://github.com/umitanuki/mongres
- Goranek 13y agois there integration like this with elasticsearch? that would be awesome.
- brasetvik 13y agoThere is https://github.com/elasticsearch/elasticsearch-lang-javascript https://github.com/elasticsearch/elasticsearch-lang-javascri... Though, it is based on Rhino, not v8 – and it's not sandboxed.
- audreyt 13y agoAt Socialtext, we're working on https://npmjs.org/package/plv8x 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 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-database https://speakerdeck.com/audreyt/pgrest-node-dot-js-in-the-da... —— documentation will appear on http://pgre.st/ http://pgre.st/ as soon as the API solidifies.
- alexatkeplar 13y agoI've never seen IMMUTABLE used to describe a function before... Wouldn't PURE (a la Rust) be less confusing?
- portmanteaufu 13y agofyi, I believe that the 'pure' keyword has been removed in recent releases of Rust.
- alexatkeplar 13y agoOh 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 13y agoCan't find the original thread, but you'll find [1] interesting. 1: https://mail.mozilla.org/pipermail/rust-dev/2013-January/002903.html https://mail.mozilla.org/pipermail/rust-dev/2013-January/002...
- alexatkeplar 13y agoWow that was very interesting indeed, thanks.
- audreyt 13y agoIn addition to JavaScript, the plv8 module also supports CoffeeScript and LiveScript ( http://livescript.net/ http://livescript.net/ ) procedures, with "CREATE EXTENSION plcoffee" and "CREATE EXTENSION plls" respectively.
- rheide 13y agoJavascript is like a virus.
- TallGuyShort 13y agoDoes anybody else think "Yo dawg, I heard you like..." belongs in that title somewhere?
- gfodor 13y agoAs 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?
- clubhi 13y agoThe 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.
- hgimenez 13y ago(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 13y agoI 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).