Postgrest: Support raw binary output (and input)

[nicklasaven]

Would it be possible to find a good way to output raw binary data from PostgREST?

It would mean returning data like application/octet-stream
https://www.iana.org/assignments/media-types/application/octet-stream

The main problem is that it cannot be put in a json object which is text.

In PostgreSQL this type of data is stored as bytea.

[eric-brechemier]

@nicklasaven What is your use case?

[nicklasaven]

Hi, sorry for late reply

My use case might be rare, so maybe I should solve it in some other way.

But dealing with json is in many cases heavy and not the way to go.

I use PostGIS and are testing solutions with the new twkb export format.

That is a format that compresses the geoemtries by delta-encoding the coordinates and stores them as VarInts, the same way as protobuffer do.

So, what I want to do is getting thetwkb geometries out through the rest api.

But maybe with nginx in front I should get them directly from the db instead. But I like the level of security PostgREST adds by just accepting right formatted requests. It is an easy way to avoid any sql-injection problems.

[begriffs]

Can you explain a little more? You're saying, for instance, that one of your tables has a column with big binary values, and you would like to return one of these values directly without it being embedded in JSON?

[geoervin]

My use case is pretty simple but I have not been to find a solution. I have a content type of application/pdf (blob) that I would like to write (post) to the database using the postgres bytea binary type. My attempts at using with text datatype with base64 encoding got me close but eventually became too complex to justify for my skill set.

[nicklasaven]

My use case might be better to solve outside postgrest as discussed above, but it might be nice to ba able to do it inside postgrest too.

I saw in #257 that @begriffs suggests a one row return limitation. For my use case it would be better if the result was just concatenated. The format includes information about each parts length and have a defined ending for each geoemtry. So getting a long binary string scrubbed from the bytea meta-data would be the best.

Then the job is up to the client to rip it apart and render it on a map.

In my case the binary format also includes an ID so the geometry can be linked to attribute data client side.

But I am not sure this type of usage has a generic usage, so probably better to do in other ways.

An alternative could be a possibility to add a delimiter to the call, like the postgresql function string_agg(string, delimiter). In this case it should be an ascii sequence (or hex string or whatever) so there is no encoding problems when used as binary.

[diogob]

@nicklasaven would encoding the bytea field as base64 and keeping the json be a possible solution?
What is the size of such files in your case?

[geoervin]

When testing a write using Post the CSV format was easiest make work. I could not get an object to write with bytea, only text. My pdf blob's are only around 100k so even with encoding speed was not an issue. I just ran out of gas with encode/decode logic when retrieving from text due to my inexperience with front end (javascript).

[nicklasaven]

@diogob Well, I guess base64 is a way to go. I tested and the encoding is very fast. That should work out of the box by just encoding in a view I guess. Only downside of course is the extra 33 % of size.

About sizes it can be anything. I mean the format really show off when used with large data sets, but then a rest-api isn't the way to go anyway.

[ghost]

I'm looking for a similar feature. My use case is to receive binary image data directly via postgrest.
So i could use something like this

<img src="http://server:3000/image?id=eq.1&select=data">

[ruslantalpa]

@jotpe just out of curiosity, what is the value of having image data in the database, why not files and keep url in the database?

[ghost]

The pros and cons are discussed often. In my case, I want ACID binary data and a single point of storage.

[ruslantalpa]

what if you tried casting the data like
select=data:: bytea
The idea is if you can somehow in sql cast a (bynary) column to a string, you can get that with postgres.
But you can not use it directly as a url for your image, you have to decode it clientside (pass it through a js function)

[ghost]

Yes, thanks.
But my intention was another use case for this feature request. Trying to avoid the use of a js function to convert the string representation of binary data to real binary data.

[ghost]

Lack of knowledge, how could a prototype in Haskell look like?

[diogob]

@nicklasaven @jotpe you can use the PostgreSQL encode function. It's documented here. So any view exposed through PostgREST can take a bytea column and present it as base64. For small images this is very interesting since it lets you load then directly in the browser using image tags.

[ghost]

Thanks. Of course I am using already img tags with an base64 data url, generated by the encode function in postgresql.
But I meant Haskell, how could a prototype looks like in the postgrest Haskell code to implement a raw binary output? Making this comment before possible?

[diogob]

@jotpe currently there would be a lot of hacking to support that example.
I believe the best way would be to create a new constructor for the type Action and an entirely different case in App.hs

[steve-chavez]

@nicklasaven Would concatenating the binary rows with a '\n' be acceptable for your use case? I'm planning to implement this feature.

[nicklasaven]

@steve-chavez interesting! Yes, I guess I can strip away the \n. Or could it be an option to just concatenating without any delimiter?

[begriffs]

I merged @steve-chavez's pull request implementing this feature!

[W1M0R]

@steve-chavez Does PostgREST support binary input? I'd like to post raw binary data (not base64) to /rpc/my_func, where my_func is a stored procedure that has one parameter of type bytea.

[steve-chavez]

@W1M0R Not for now, but progress on binary input is being tracked at https://github.com/PostgREST/postgrest/issues/922.