People asked a lot of questions about what you can do with the datatype and PLV8! My slides are available from the talk at this dropbox link. Speakerdeck seems to be busted for the moment. And here’s my gist with the ‘liberate()’ function.
Here are some links to resources I’ve found for using PLV8 and the JSON datatype:
- Postgres core documentation for JSON
- Embracing the web with JSON and plv8
- node-postgres is the most popular module, older: Node.js adapter for Postgres — with Aurynn’s patches this is awesome.
- LISTEN/NOTIFY example
- Building a MongoDB clone in Postgres — Jerry is also working on a set of functions that convert PostGIS types to GeoJSON!
- What’s new in Postgres 9.3
- JSON and joins — it’s not beautiful, but it is possible. Someone also asked me about “subdocuments” in the style of MongoDB, with foreign keys. I don’t have a good answer, but am looking into it.
- Live updates to Meteor to Postgres
- Heroku supporting 9.3beta
And folks who took notes from my talk:
Not to detract from Aurynn’s work, but postgres-js (aka postgres, see https://npmjs.org/package/postgres ) hasn’t seen much activity. The most popular Node – PostgreSQL client is node-postgres ( https://github.com/brianc/node-postgres aka pg, see https://npmjs.org/package/pg ).
+1 on node-postgres. Brian has been very receptive of pull requests, and the library is moving forward with cleaning things up like javascript float issues (from numeric types in postgres), better transaction support, etc.
Also, to add to the list of ‘getting started with json/javascript and postgres integration’, my biggest hurdle I had developing postgres-backed node.js/plv8 apps was the multi-line strings. My work often involves larger queries that don’t fit in the typical CRUD format or are good cases for a lightweight ORM and ‘with’ clauses, window functions, etc. that might span 20+ rows commonly. Doing escapes often backfired if a single line had a space at the end:
"select a,b,c \
from jobs \
join employees on \
employees.job_pk=jobs.job_pk \
order by a"
plus, you can’t take your query and paste it in psql with the escapes if you want to debug, say with explain analyse.
Other options include array concats:
var foo = ["select a,b,c",
"from jobs",
"..."]
foo.join(" ");
or pluses:
"select a, b, c"+
"from jobs"+
"...."
it just makes queries easier to include typos, harder to read and limits converting to psql pastes, pgpsql->plv8 quick conversions, etc.
I’ve been thinking about migrating to coffescript to get around this (it supports multiline strings), but that sort of breaks one big advantage of javascript in that I can read/write one language for the web without as much context shift throughout the day, or even borrowing snippets as much. I know ecmascript 6 has a draft for multiline strings, but I don’t think there is a v8 harmony option for that yet and it actually makes multiline strings vs single line strings spanning multiple code lines (it magically inserts \n for you).
I’m curious if you’ve run across this question often with folks trying out plv8/node+postgres, and what you’ve recommended?
Hah! Thanks for this awesome comment.
I don’t have a good solution. I found myself wishing desperately for Pythonic triple-quoting (“”” … “””) and string replacement.
I use editor extensions that scream red when I leave extra spaces to avoid the trailing space issues. Maybe the multi-line spaces is something that the plv8 parser could specifically support. I’ll raise the issue with the folks that I know and see if anyone is interested in tackling it.