5 ms·

PLJS – JavaScript Language Plugin for PostreSQL

78 points by alonp99 3y ago

cyanydeez 3y ago
I hope this supplants the v8 standard since trying to build v8 breaks every release, which makes plv8 no longer packaged with typical releases.
- jerrysievert 3y ago
  3.2alpha uses v8-cmake (who were great about accepting the needed patches) to build, and is a bit more stable. Though v8 has become much slower every release when crossing the membrane that pljs is much faster under many use cases, even in early alpha.
tantalor 3y ago
Typo in headline
- codetrotter 3y ago
  PostreSQL. People from Spainish speaking countries would love it!
  - acumenical 3y ago
    "DessertSQL" in English but I thought we were past quirky names.
jsdwarf 3y ago
Use cases? Examples?
- nattaylor 3y ago
  (I haven't tried pljs but I think this is relevant) I've used JavaScript UDFs in Snowflake for array processing and also dumb POCs things like importing an entire library for useragent parsing
  - elyobo 3y ago
    Similar, we use JS UDFs in BigQuery for JSON parsing (ELT instead of ETL) and occasionally for other tasks that are just easier in JS than pure SQL.
- ttfkam 3y ago
  Off the top of my head: The ability to do advanced regexp replacements with a function parameter in JS far outstrips anything native in Postgres. Array manipulation, reordering, and deduplication are worlds easier in JS than native Postgres. Manipulating byte buffers in JS are much faster and more flexible than introspecting bytea columns in native Postgres. JSON parsing and object transformation are much easier, faster, and clearer than native Postgres (even with the recent jsonb improvements).
- jerrysievert 3y ago
  my favorite (plv8, but relevant) was working at an esri r&d center, and having corporate remind us that we needed to include esri's json format in our product offering in addition to geojson (our supported format). I was away at a conference (jsconf, amusingly), and simply added the arcgis to geojson converter that was already written in javascript as part of another open source project we had released, and voila, we could suddenly support it in the database. one of the product engineers quickly added an additional database call to the backend, and we were all set. simple problems, simple solutions, especially when dealing with json conversion.
jerrysievert 3y ago
Author here. Not what I expected to see on hn during a lazy Sunday afternoon.
- yxre 3y ago
  Thank you for creating this. I have used it a lot in a few different projects. So much better than postgres's procedural built in
  - jerrysievert 3y ago
    to be clear, I maintain plv8, but wrote pljs out of the frustration of maintaining plv8 for so many years :)
samanator 3y ago
I just tried building plv8 today. After 2 hours, I gave up and just copied the ready made artifact from a docker image. Here is more context for the QuickJS engine experiment as the runtime for a pl language. https://github.com/plv8/plv8/issues/364 https://github.com/plv8/plv8/issues/364
- jerrysievert 3y ago
  a bit more than an experiment at this point. pljs, even in its early state, has some very good results: https://github.com/plv8/plv8/issues/531#issuecomment-1627883921 https://github.com/plv8/plv8/issues/531#issuecomment-1627883... passing through v8's javascript/c++ membrane has always been painful, and appears to be getting worse.
laurencerowe 3y ago
I don’t know if this is just be a weird artefact of running in Docket on an Arm Mac, but I was surprised to find it substantially faster to run my tree walking code on Node and call out to Postgres than run it all in Postgres with plv8. Perhaps it was using a fresh interpreter per transaction?
- jerrysievert 3y ago
  it doesn't use a fresh interpreter per transaction, but does per connection due to postgres using a process model. also, there are a ton of different variables. if you're interested in talking about it, there's a discord: https://discord.gg/5fJN52Se https://discord.gg/5fJN52Se
asah 3y ago
Embedded JS enables pushing down application logic into the database, which can result in shocking gains versus pulling each record out of the database and into the middleware. In one project I worked on, we got 100x performance improvement, not a typo. Plv8 is widely available from cloud hosting providers including AWS, GCP and Azure.
friendzis 3y ago
Embedding business logic/application code in a database is a very different development model than using database as a relatively transparent record store. I am not against having business logic in database, there are very legitimate uses for that. However, few things must be noted. First, code is idempotent: you compile, package, deploy code, then nuke it all out and replace with different version, be it older or newer. Swapping application binaries/images/containers is the gold standard in deployment. Databases are... Perpetual. Rolling back DDLs without rolling back data can be a very complex exercise. Second, there is no automatic separation of interface and implementation in databases. Database engine will not automatically enforce constraints embedded in stored procedures. Which means extra care must be taken and additional processes must be implemented to control access, otherwise you risk corrupting data. Embedding application code in database breaks separation boundary, which changes project management. Now you have database development and deployment much more closely tied to application code development and deployment. Without huge care in project management this effectively inhibits any non-linear development flows.
- doliveira 3y ago
  Yeah, I'm interested in knowing how people manage it. With Redis, I usually put the Lua code within the same repo and embed the Lua code within the deployment artifact, so it gets tested and deployed together with the rest of the code. Then I call SCRIPT LOAD at the application initialization and I get a SHA-1 hash to call the script. At the very least, this approach makes things feel much safer. I would like to know if there better ways to do it, and if there are similar approaches for Postgres procedures.
  - claytongulick 3y ago
    I wrote this[1] several years ago to help with that. It lets you write a function in nodejs that actually executes in plv8 inside the database. To your project code though, it mostly looks like a normal js function (with some limitations). Amongst other benefits, it allows you to manage your plv8 functions as a normal part of your repo. [1]https://github.com/claytongulick/pgproxy https://github.com/claytongulick/pgproxy
- 3y ago

78 points by alonp99 3y ago

I hope this supplants the v8 standard since trying to build v8 breaks every release, which makes plv8 no longer packaged with typical releases.

jerrysievert 3y ago
3.2alpha uses v8-cmake (who were great about accepting the needed patches) to build, and is a bit more stable. Though v8 has become much slower every release when crossing the membrane that pljs is much faster under many use cases, even in early alpha.

tantalor 3y ago

Typo in headline

codetrotter 3y ago
PostreSQL. People from Spainish speaking countries would love it!
- acumenical 3y ago
  "DessertSQL" in English but I thought we were past quirky names.

jsdwarf 3y ago

Use cases? Examples?

nattaylor 3y ago
(I haven't tried pljs but I think this is relevant) I've used JavaScript UDFs in Snowflake for array processing and also dumb POCs things like importing an entire library for useragent parsing
- elyobo 3y ago
  Similar, we use JS UDFs in BigQuery for JSON parsing (ELT instead of ETL) and occasionally for other tasks that are just easier in JS than pure SQL.
ttfkam 3y ago
Off the top of my head: The ability to do advanced regexp replacements with a function parameter in JS far outstrips anything native in Postgres. Array manipulation, reordering, and deduplication are worlds easier in JS than native Postgres. Manipulating byte buffers in JS are much faster and more flexible than introspecting bytea columns in native Postgres. JSON parsing and object transformation are much easier, faster, and clearer than native Postgres (even with the recent jsonb improvements).
jerrysievert 3y ago
my favorite (plv8, but relevant) was working at an esri r&d center, and having corporate remind us that we needed to include esri's json format in our product offering in addition to geojson (our supported format). I was away at a conference (jsconf, amusingly), and simply added the arcgis to geojson converter that was already written in javascript as part of another open source project we had released, and voila, we could suddenly support it in the database. one of the product engineers quickly added an additional database call to the backend, and we were all set. simple problems, simple solutions, especially when dealing with json conversion.

jerrysievert 3y ago

Author here. Not what I expected to see on hn during a lazy Sunday afternoon.

yxre 3y ago
Thank you for creating this. I have used it a lot in a few different projects. So much better than postgres's procedural built in
- jerrysievert 3y ago
  to be clear, I maintain plv8, but wrote pljs out of the frustration of maintaining plv8 for so many years :)

samanator 3y ago

I just tried building plv8 today. After 2 hours, I gave up and just copied the ready made artifact from a docker image. Here is more context for the QuickJS engine experiment as the runtime for a pl language. https://github.com/plv8/plv8/issues/364 https://github.com/plv8/plv8/issues/364

jerrysievert 3y ago
a bit more than an experiment at this point. pljs, even in its early state, has some very good results: https://github.com/plv8/plv8/issues/531#issuecomment-1627883921 https://github.com/plv8/plv8/issues/531#issuecomment-1627883... passing through v8's javascript/c++ membrane has always been painful, and appears to be getting worse.

laurencerowe 3y ago

I don’t know if this is just be a weird artefact of running in Docket on an Arm Mac, but I was surprised to find it substantially faster to run my tree walking code on Node and call out to Postgres than run it all in Postgres with plv8. Perhaps it was using a fresh interpreter per transaction?

jerrysievert 3y ago
it doesn't use a fresh interpreter per transaction, but does per connection due to postgres using a process model. also, there are a ton of different variables. if you're interested in talking about it, there's a discord: https://discord.gg/5fJN52Se https://discord.gg/5fJN52Se

asah 3y ago

Embedded JS enables pushing down application logic into the database, which can result in shocking gains versus pulling each record out of the database and into the middleware. In one project I worked on, we got 100x performance improvement, not a typo. Plv8 is widely available from cloud hosting providers including AWS, GCP and Azure.

friendzis 3y ago

Embedding business logic/application code in a database is a very different development model than using database as a relatively transparent record store. I am not against having business logic in database, there are very legitimate uses for that. However, few things must be noted. First, code is idempotent: you compile, package, deploy code, then nuke it all out and replace with different version, be it older or newer. Swapping application binaries/images/containers is the gold standard in deployment. Databases are... Perpetual. Rolling back DDLs without rolling back data can be a very complex exercise. Second, there is no automatic separation of interface and implementation in databases. Database engine will not automatically enforce constraints embedded in stored procedures. Which means extra care must be taken and additional processes must be implemented to control access, otherwise you risk corrupting data. Embedding application code in database breaks separation boundary, which changes project management. Now you have database development and deployment much more closely tied to application code development and deployment. Without huge care in project management this effectively inhibits any non-linear development flows.

doliveira 3y ago
Yeah, I'm interested in knowing how people manage it. With Redis, I usually put the Lua code within the same repo and embed the Lua code within the deployment artifact, so it gets tested and deployed together with the rest of the code. Then I call SCRIPT LOAD at the application initialization and I get a SHA-1 hash to call the script. At the very least, this approach makes things feel much safer. I would like to know if there better ways to do it, and if there are similar approaches for Postgres procedures.
- claytongulick 3y ago
  I wrote this[1] several years ago to help with that. It lets you write a function in nodejs that actually executes in plv8 inside the database. To your project code though, it mostly looks like a normal js function (with some limitations). Amongst other benefits, it allows you to manage your plv8 functions as a normal part of your repo. [1]https://github.com/claytongulick/pgproxy https://github.com/claytongulick/pgproxy
3y ago