2

I have a base64 image that I am trying to save in a Postgres database (using Hasura). The field is of type bytea I am not sure how to save this data to the field.

I tried passing data:image/png;base64,sisodjodo... to the field, and it saves it like this: \x6956424f5277304b47676f414141414...

When I get it back it doesn't seem to come back in the same manner that it was saved.

// Query the database and save resulting object
const user = {
  avatar: '\x6956424f5277304b47676f414141414...'
}
user.avatar = btoa(user.avatar);
console.log(user.avatar);
// Prints: XHg2OTU2NDI0ZjUyNzczMDRiNDc2NzZmNDE0MTQxNDE0Z...
Get Off My Lawn
  • 34,175
  • 38
  • 176
  • 338
  • Whatever `btoa()` is, it's not treating your `'\x6956424f5277304b47676f41414141'` as a binary. It is instead treating it like the string representation of `\x6956424f5277304b47676f41414141`. I don't do javascript since it's the devil's native tongue, but I was able to duplicate your output using `select encode('\\x6956424f5277304b47676f41414141', 'base64');` in a language not of darkness. – Mike Organek Oct 14 '20 at 22:19
  • Use `text`, not `bytea`, for a base64 encoded image. – Laurenz Albe Oct 15 '20 at 06:08
  • @MikeOrganek `btoa()` creates a Base64-encoded ASCII string from a binary string (i.e., a String object in which each character in the string is treated as a byte of binary data). – Get Off My Lawn Oct 15 '20 at 14:20
  • @LaurenzAlbe I am not sure I can I am not the database administrator, so I don't have the ability to change it. – Get Off My Lawn Oct 15 '20 at 14:22
  • That representation you see is the hex string representation of the binary data stored in the column. Whatever you are using to retrieve it from the database is treating it as a string instead of a byte array. Can you include the code you use to query PostgreSQL? – Mike Organek Oct 15 '20 at 18:23
  • The query that is used is generated by Hasura, so I assume that they are selecting it as a string like you say. I did however find a way to make the column a text column through the Hasura interface like @LaurenzAlbe had mentioned. – Get Off My Lawn Oct 15 '20 at 19:16

1 Answers1

2

Postgres bytea fields must be entered in one of two formats, the simplest one being hex. This format looks like \x[your byte string in hex]

So, suppose you have a table called things with a bytea column field, you can insert a new row with the byte string 010101 like this:

mutation {
  insert_things_one(object: {
    field: "\\x010101"
  }) {
    field
  }
}

The result will be:

{
  "data": {
    "insert_thing_one": {
      "field": "\\x010101"
    }
  }
}

Note in GraphQL you have to escape the \. You can verify in SQL that it's stored correctly:

> select * from things;

field
\x010101

If you want to store a base64-encoded byte string per your question, you need to strip off the leading data:image/png;base64, and then encode the base64 string as hex in whatever language you're using. In JS: Decode Base64 to Hexadecimal string with javascript

PS. I would not recommend using text fields to store base64 encoded strings. It will cost you a lot more storage than necessary. Just re-encode into hex, per my answer above.

Dmitry Minkovsky
  • 36,185
  • 26
  • 116
  • 160