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

Answer 1

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.