Postgraphile: Querying bytea

[tshelver]

I'm submitting a ...

• [ ] bug report
• [ ] feature request
• [ X] question

PostGraphile version: v4.3.3

The only other post I have seen related to this (Jan 22nd) relates to uploading images.

I have a bytea column(jpg / png uploads) that I am trying to query, but when I run a Python type check it says it's string. Consistently errors out trying to load into a python bytes object.
If I use a database tool against the table (LibreOffice Base for example), I can both upload and also display the image that has been uploaded, once queried, so it's being stored correctly in the table.

Is it possible to get bytea objects out? Just starting out with GraphQL, and also a rather middling Python developer

While I am on the subject, eventually I will be rewriting the 'quick fix' front end to the system, probably using Vue.js. So potentially I would also like to upload the bytea data to Postgres as well...

Performance isn't an issue for us. They aren't big images, and we are running a static site, so the images are only queried at build time, and only when changes are made to that section of the site.

Thanks.

[benjie]

I don't think we officially have support for BYTEA, but it should be possible to add it with a small plugin.

[benjie]

Here is said plugin:

const pgBytea = require("postgres-bytea");

module.exports = builder => {
  builder.hook(
    "build",
    build => {
      const {
        graphql: { GraphQLScalarType, Kind },
        pgIntrospectionResultsByKind,
        pgRegisterGqlTypeByTypeId,
        pgRegisterGqlInputTypeByTypeId,
        pg2GqlMapper,
        pgSql: sql,
      } = build;
      const bytea = pgIntrospectionResultsByKind.type.find(
        t => t.name === "bytea" && t.namespaceName === "pg_catalog"
      );
      if (!bytea) {
        // No bytea type found
        return build;
      }
      const BinaryType = new GraphQLScalarType({
        name: "Base64EncodedBinary",
        description: "Binary data encoded using Base64",
        serialize: value => String(value),
        parseValue: value => String(value),
        parseLiteral: ast => {
          if (ast.kind !== Kind.STRING) {
            throw new Error("Can only parse string values");
          }
          return ast.value;
        },
      });

      pgRegisterGqlTypeByTypeId(bytea.id, () => BinaryType);
      pgRegisterGqlInputTypeByTypeId(bytea.id, () => BinaryType);
      pg2GqlMapper[bytea.id] = {
        map: data => pgBytea(data).toString("base64"),
        unmap: str =>
          str === null
            ? sql.null
            : sql.fragment`decode(${sql.value(str)}, 'base64')`,
      };
      return build;
    },
    ["PgBytea"],
    [],
    ["PgTypes"]
  );
};

Here's how to load it: https://www.graphile.org/postgraphile/extending/#loading-plugins

It needs postgres-bytea to be installed.

[tshelver]

Great! WIll give it a try in the morning...

[tshelver]

Good day: I tried adding this, I guess as a new JS/Node and not very experienced Linux user, I am missing something.
When I run the postgraphile \ --append-plugins $pass/plugins/pgBytea.js \ -c postgres://surehome_user:password@localhost:5432/surehome_cms

It says failed linux user authentication. I have tried escaping (single quote / 'oass') the password which contains special characters and also passing as a variable.
If i just run postgraphile wiht the given postgres user, it obviously works.

Any ideas what I am doing wrong? My own lack of knowledge more than anything else...

[benjie]

Auth issues would be better suited to chat so we can help you interactively; come join us: http://discord.gg/graphile

Is the bytea plugin working for you?

[LeoBakerHytch]

Thanks for the plugin, works perfectly, except for one thing — I needed to add an init hook to be able to use the scalar from another plugin created with makeExtendSchemaPlugin:

builder.hook('init', (input, build) => {
    build.addType(BinaryType);
    return input;
});

[benjie]

This'll be fixed in V5; thanks @LeoBakerHytch

[zamfofex]

Hello, @benjie. Thank you for your work in this project! :blush:

After keeping an eye on it for a while, I finally started working with PostGraphile for a new personal project a couple days ago.

It seems to me like (for querying) your plugin is performing three conversions: (1) byte data to Postgres bytea string on the database itself, (2) bytea string to Node.js buffer on the database client, and also (3) buffer to base64 string, also on the database client.

It’d be nice to be able to query the data directly as a buffer, typed array, blob, or some other binary data type, however I’m assuming this isn’t possible due to how a GraphQL server (or client) sends data. (I don’t know for sure, though.)

Then, it’d at least be nice to be able to convert it directly from binary data to a base64 string, preferably on the database client (i.e. the GraphQL server), and not have to go through Buffer. I was wondering if this is somehow possible.

Thanks in advance!

[benjie]

You can’t use binary data in JSON, so it needs some form of encoding/escaping. Does it somehow matter what happens in the middle for you, or are you asking about the end result in the GraphQL JSON?

[zamfofex]

You can’t use binary data in JSON, so it needs some form of encoding/escaping.

I see. That’s what I figured (and it’s fair enough).

Does it somehow matter what happens in the middle for you […]?

Well, it technically doesn’t matter for me (since the data I have is not that large), but it does feel unfortunate to have to perform all of these conversions.

I think it would be neat to have PostGraphile query the data as a binary type, and convert it from binary data directly to base64.

My rationale being that receiving a string representation from the database (whether base64 or a bytea string) would consume more bandwidth than converting it to base64 after receiving it from the database.

[benjie]

Yes it’s about a 33% increase in size of the binary asset, but extremely efficient so assuming your db/server bandwidth is wide it shouldn’t make much difference if this is done in postgres vs node.