How to convert Postgres bytea to base64 string

Question

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...

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
– Mike Organek, Commented Oct 14, 2020 at 22:19
@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
– Get Off My Lawn, Commented Oct 15, 2020 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
– Get Off My Lawn, Commented Oct 15, 2020 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
– Mike Organek, Commented Oct 15, 2020 at 18:23

Dmitry Minkovsky · Accepted Answer · 2020-10-17 03:53:18Z

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.

answered Oct 17, 2020 at 3:53

Dmitry Minkovsky

38.4k29 gold badges127 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Get Off My Lawn Over a year ago

Okay great! It is saving now, but how to I convert it back to a base64 when doing a query with Hasura?

Get Off My Lawn Over a year ago

NVM: btoa(user.avatar.replace('\\x', '').match(/\w{2}/g).map(a => String.fromCharCode(parseInt(a, 16))).join(""))

Dmitry Minkovsky Over a year ago

Yep, exactly! 👍

Collectives™ on Stack Overflow

How to convert Postgres bytea to base64 string

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related