How to convert the PostgreSQL bytea value into Data URL value using PHP

Question

PostgreSQL has this datatype called bytea. It is their version of a blob.

In displaying images, png/jpg/gif, the current trend nowadays is to use Data URLs.

My question is how to convert a bytea value into a Base64 Data URL value?

References

bytea - Binary Data Types. PostgreSQL 9.6 Documentation
Data URL - Copy image as data URI by Umar Hansa. Google Developers. May 2015

score 6 · Accepted Answer · answered Feb 23 '18 at 06:24

6

You may also fetch the complete image data URL from your database, e.g.:

SELECT 'data:image/gif;base64,' || encode(image_data, 'base64') AS image_url 
FROM ...

answered Feb 23 '18 at 06:24

clemens

16,716
11
50
65

that thing worked well. Although I have to take note that instead of `encode(image_data, 'base64')`, it should be `encode("image_data", 'base64')`. Implementers should be wary though that in this solution, the resources of the database server gets being used instead of the back-end scripts' – Abel Callejo Feb 23 '18 at 07:37
The column name in my post is just an example. The name and if you should protect it by double quotes depends on the real database. – clemens Feb 23 '18 at 07:39
Yes you are right @clemens, it's just that pgsql has this own convention that you should enclose in quotes the *field names* especially if they are on an upper case – Abel Callejo Feb 23 '18 at 07:43

BareNakedCoder · Answer 2 · 2018-02-23T04:01:50.373

1

$img_bin = hex2bin(substr($the_bytea, 2));
$url = 'data:image/gif;base64,' . base64_encode($img_bin);

Postgres bytea is a string starting with \x followed by hexadecimal characters. To get the image in binary format, need to strip off the leading \x and convert from hexadecimal characters to binary with hex2bin. Now that you have it in binary, you can base64_encode it.

edited Feb 23 '18 at 04:01

answered Feb 23 '18 at 03:52

BareNakedCoder

3,257
2
13
16

bare naked it is. – Abel Callejo Feb 23 '18 at 04:01
You should use `pg_unescape_bytea` instead of `hex2bin`. – clemens Feb 23 '18 at 06:27

score 0 · Answer 3 · answered Aug 11 '23 at 18:02

Posting this answer incase anyone stumbles here using Postgres and sqlalchemy. Thank you to the other answers for helping me get this far.

I was able to use the answer provided by clemens above and place the sql into a column_property and leveraging literal_column to insert a rawsql statement.

class Attachment(BaseModel):
    __tablename__ = "attachments"
    id = Column(UUID(), primary_key=True, default=uuid.uuid4, )

    data = Column(BYTEA(), nullable=True)
    text = Column(Text, nullable=True)
    file_name = Column(String(255), nullable=True)
    content_type = Column(String(255), nullable=True)
    model = Column(String(255), nullable=False)
    model_id = Column(UUID(), nullable=False, index=True)
    meta_data = Column(JSON, nullable=True)
    deleted = Column(BOOLEAN, default=False)

    # return a data url when the attachment is an image, doesnt work in sqlite
    image_url = column_property(
        literal_column("""
            CASE
                WHEN content_type LIKE 'image%' THEN 'data:image/gif;base64,' || encode(data, 'base64')
                ELSE null
            END
            """, type_=String).label("image_url")
    )

How to convert the PostgreSQL bytea value into Data URL value using PHP

References

3 Answers3