display jpg image saved to postgresql database (oid) for html

Question

I recorded an image in the postgresql database with the OID type and now I intend to display this image on an html page with the tag . I verified that through the lo_export I can send to a folder and read from there, but I intended to read straight without having to export, someone knows a way to do it?

Answer 1

Use the encoded file as string in Base64, and you can include it directly in HTML as follows (example with a very small PNG, to fit the answer):

<img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAACw0lEQVQ4jYWT20uTYRzH37+hMwU1qNy0tvcpAjtQEURBhRQVBVFdZAftwFtBe6eOXtxsdSEVIR1J6SbNaNooRZmPzg62DIqKnNVa6GauvOhGYRfPp4s3AzvQFz43D58vPBffn6b9FmkIR+yUMGJeITt/EvMKKU8JQxrC8bs/sWwK62XNdrI9l1AjEjUSRY1EyaUfMNR1jt7QaqQprL+XvXrtp/snUV8foLJNkL0Lw4022buobBMq3UCybgvS1GsnlKOmsD41H4Phehiqg8w1yNRA+pJNpgYyV+FLHQzXk6xZRnT8Jy2mcMTPrYChG5C+CAMh+HwaUmWQ8v6kzH4bCNnOh7PET+fTYgqHFvV5jEx4KwycgZQJySMETiwhcFxA/07o30ng+CJ8JQKSR2xn4AzpWyuI+jyG1l6uy1zvHkgehfe7oa8I3/45+IpnwetCeF2Ir3gWxdtmQl+R7SSPkuveRHu5LrX2cl2qFxsgsQ1erYTnHtRTF+qJE/U4z+aJE9Xjgl7ddt5tRj1aRnu5W2ptFW6pOpyo1tkE900ieGAywUNTqSqdRtXh6Tal0wiWTCV4cArB4kmoNgeqM5+2CrfUWv1umevWUZ1zue6bwf9yvWwGqmseuUeCFr9bag/9C4zB5sWouJtEeD7xzmv/LL961kCiOQ/VqzN4fwkP/QsMLWy6HN3nBbxdD28Ekct5xFovMDb6/VdxbPQ78a6bRC474e0ieLeR7guCsOmypx2xFlqJe2vhww7oX0WsYSnVFW6qvB6qvDrVfg+xO8vh/Rr4uItEeB0Ra+HESTdVFtT2RTZD2g+D+yGzF7Il8PUwDO2DdAlkKumLbKWpsmDilMcTDhRYHVdWkXrqI/etAUbbYCxKbuQeqZ4KoldWEw4U/P2YxnPbcjkag/lGY8gl60NOWX/WKe+EXLIxmG/ctlx/nPMPet9k/gK9chkAAAAASUVORK5CYII=">

However, if you inserted the image from the filesystem as an example of the documentation, the data is stored as large Object hexadecimal coded in a field bytea:

create table image (
    name            text,
    extension       text,
    raster          oid
);
insert into image values ('smiley', 'png', lo_import('/tmp/smiley.png'));

select pg_typeof(lo_get(raster)) from image where name = 'smiley';
 pg_typeof 
-----------
 bytea
(1 row)

To get the image in Base64 format, pass the return of lo_get() for the function encode(), making explicit the desired format:

select encode(lo_get(raster), 'base64') from image where name = 'smiley';
                                    encode                                    
------------------------------------------------------------------------------
 iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAACw0lEQVQ4jYWT20uTYRzH37+hMwU1+
 qNy0tvcpAjtQEURBhRQVBVFdZAftwFtBe6eOXtxsdSEVIR1J6SbNaNooRZmPzg62DIqKnNVa6Gau+
 vOhGYRfPp4s3AzvQFz43D58vPBffn6b9FmkIR+yUMGJeITt/EvMKKU8JQxrC8bs/sWwK62XNdrI9+
 l1AjEjUSRY1EyaUfMNR1jt7QaqQprL+XvXrtp/snUV8foLJNkL0Lw4022buobBMq3UCybgvS1Gsn+
 lKOmsD41H4Phehiqg8w1yNRA+pJNpgYyV+FLHQzXk6xZRnT8Jy2mcMTPrYChG5C+CAMh+HwaUmWQ+
 8v6kzH4bCNnOh7PET+fTYgqHFvV5jEx4KwycgZQJySMETiwhcFxA/07o30ng+CJ8JQKSR2xn4Azp+
 WyuI+jyG1l6uy1zvHkgehfe7oa8I3/45+IpnwetCeF2Ir3gWxdtmQl+R7SSPkuveRHu5LrX2cl2q+
 FxsgsQ1erYTnHtRTF+qJE/U4z+aJE9Xjgl7ddt5tRj1aRnu5W2ptFW6pOpyo1tkE900ieGAywUNT+
 qSqdRtXh6Tal0wiWTCV4cArB4kmoNgeqM5+2CrfUWv1umevWUZ1zue6bwf9yvWwGqmseuUeCFr9b+
 ag/9C4zB5sWouJtEeD7xzmv/LL961kCiOQ/VqzN4fwkP/QsMLWy6HN3nBbxdD28Ekct5xFovMDb6+
 /VdxbPQ78a6bRC474e0ieLeR7guCsOmypx2xFlqJe2vhww7oX0WsYSnVFW6qvB6qvDrVfg+xO8vh+
 /Rr4uItEeB0Ra+HESTdVFtT2RTZD2g+D+yGzF7Il8PUwDO2DdAlkKumLbKWpsmDilMcTDhRYHVdW+
 kXrqI/etAUbbYCxKbuQeqZ4KoldWEw4U/P2YxnPbcjkag/lGY8gl60NOWX/WKe+EXLIxmG/ctlx/+
 nPMPet9k/gK9chkAAAAASUVORK5CYII=
(1 row)

Then just take the result and tag it <img> as exemplified:

<img src="data:image/png;base64,{RESULTADO DA QUERY}">

Since this representation in HTML requires the format of the image to be informed, I suggest that it be saved in the reference table. So your query can return the already ready tag for inclusion in HTML:

select 
  format(
    '<img src="data:image/%s;base64,%s">', 
    extension,
    encode(lo_get(raster), 'base64')
  ) as img 
from image where name = 'smiley';
                                                     img                                                      
--------------------------------------------------------------------------------------------------------------
 <img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAACw0lEQVQ4jYWT20uTYRzH37+hMwU1+
 qNy0tvcpAjtQEURBhRQVBVFdZAftwFtBe6eOXtxsdSEVIR1J6SbNaNooRZmPzg62DIqKnNVa6Gau                                +
 vOhGYRfPp4s3AzvQFz43D58vPBffn6b9FmkIR+yUMGJeITt/EvMKKU8JQxrC8bs/sWwK62XNdrI9                                +
 l1AjEjUSRY1EyaUfMNR1jt7QaqQprL+XvXrtp/snUV8foLJNkL0Lw4022buobBMq3UCybgvS1Gsn                                +
 lKOmsD41H4Phehiqg8w1yNRA+pJNpgYyV+FLHQzXk6xZRnT8Jy2mcMTPrYChG5C+CAMh+HwaUmWQ                                +
 8v6kzH4bCNnOh7PET+fTYgqHFvV5jEx4KwycgZQJySMETiwhcFxA/07o30ng+CJ8JQKSR2xn4Azp                                +
 WyuI+jyG1l6uy1zvHkgehfe7oa8I3/45+IpnwetCeF2Ir3gWxdtmQl+R7SSPkuveRHu5LrX2cl2q                                +
 FxsgsQ1erYTnHtRTF+qJE/U4z+aJE9Xjgl7ddt5tRj1aRnu5W2ptFW6pOpyo1tkE900ieGAywUNT                                +
 qSqdRtXh6Tal0wiWTCV4cArB4kmoNgeqM5+2CrfUWv1umevWUZ1zue6bwf9yvWwGqmseuUeCFr9b                                +
 ag/9C4zB5sWouJtEeD7xzmv/LL961kCiOQ/VqzN4fwkP/QsMLWy6HN3nBbxdD28Ekct5xFovMDb6                                +
 /VdxbPQ78a6bRC474e0ieLeR7guCsOmypx2xFlqJe2vhww7oX0WsYSnVFW6qvB6qvDrVfg+xO8vh                                +
 /Rr4uItEeB0Ra+HESTdVFtT2RTZD2g+D+yGzF7Il8PUwDO2DdAlkKumLbKWpsmDilMcTDhRYHVdW                                +
 kXrqI/etAUbbYCxKbuQeqZ4KoldWEw4U/P2YxnPbcjkag/lGY8gl60NOWX/WKe+EXLIxmG/ctlx/                                +
 nPMPet9k/gK9chkAAAAASUVORK5CYII=">
(1 row)

Enter the result of this query into your HTML file and you’re done!

---

Disclaimer regarding HTML performance

Note the large amount of information contained even in a small image like the one in the example!

It is important to remember that encoding images in Base64 string for HTML inclusion dramatically increases the size of the document, as the images are "attached" within it.

This can greatly reduce the loading and processing time of your HTML by the browser, since the common method of referencing external files enables download parallelism, the use of Lazy loading, HTTP cache etc., something impossible when we put everything inside a single archive.

There is also the controversy of storing images in databases. If you’re not performing unique database operations on binary files, you’ll surely get a lot more performance by serving them directly as static files in your web server. This way your application can provide the images to the client without even having to talk to the bank, or just by going to it to get the right URL of the file to be shown.

Carefully analyze your use case before deciding to use Base64 encoding in HTML, as well as to store binary data on a database server.