Objection.js: How to store binary data type with Postgresql using Objection

[vividcode]

First off, a great repo! I totally rewritten my DB-interface after coming across this awesome and simple to use ORM....

My issue is quite basic and rather a question because I could not get any reference to it from docs.

How does one store binary data (e.g. files) to postgresql using Objection?

JSON schema speaks of contentMediaType but I did not find any reference to it in the docs. Would this be indeed the thing?

properties: {
    myFilefield: { type: "object", contentMediaType: "png" },
}

(I really do not know if Knex supports this with object type, but couldn't think of any other that goes with it here 👆)

And in the knex migration file one would use following?

table.binary("myFilefield")

I considered using Base64 with type = "string" but I believe it could be way more inefficient for storage.
I searched older issues but can't find any reference to any such usage.

Will highly appreciate if there are any guidelines/docs/examples pointing to it.

[koskimas]

I don't think there's a way to validate binary data using json schema, but you can simply insert/update node.js buffers to that column. json schema only deals with json values (bool, string, number, array, object, null). JSON has no binary data type, so there's no way to validate it using json schema.

[vividcode]

thanks for the quick answer!

Does this mean that without schema validation, I can still get attachment (myFilefield - the binary field in original question) using raw()?

Would this then have to be a separate API call just for the binary data because the Model would fail to validate just that field and rest of the things would be fine with Objection Model?

I am not at a point of quickly testing because I am still modeling my architecture along with migration to create DB, (and not fluent in node to flash out a test proj with Objection), so asking to validate my choice of objection over sequelize....

[kibertoad]

@vividcode Knex per se does support binary format, so bypassing the schema validation it should work: https://spin.atomicobject.com/2019/06/17/knex-postgresql-blobs/

[kibertoad]

@vividcode I definitely don't recommend using sequelize, tbh. If you'll find Objection insufficient, you might take a look at mikro-orm, it is considerably more powerful knex-based ORM. It supports blobs, see BlobType: https://mikro-orm.io/docs/custom-types/

[vividcode]

@kibertoad nice pointer to directly using knex. Thanks a lot!

This is my first project for node backend and am still unaware about many ways to optimize.
The atomic spin article I saw yesterday, didn't make much sense, maybe will give it one more try, if I can conjure things up from it.

I came across this article too, which concludes with knex over everything, but I am too beginner to venture that path, with the nested graph requirement I have for my project.

Having seen Objection, I surely don't want .then() calls in node code to fetch things sequentially.

I tried finding some Objection example where this is done-with simple flat table structure having one binary field - all fetched in single API call, but haven't found so far.

[vividcode]

For now I have decided to use a URL field (string) which will be written to only after a file upload to cloud. Anyone feel free to add anything relevant on the matter of BLOB fields using Objection.