Providing images for <img>

author

pkel

In this how-to, you will learn how to create an endpoint for providing images to HTML <img> tags without client side JavaScript. In fact, the presented technique is suitable for providing not only images, but arbitrary files.

We will start with a minimal example that highlights the general concept. Afterwards we present a more detailed solution that fixes a few shortcomings of the first approach.

Warning

Be careful when saving binaries in the database, having a separate storage service for these is preferable in most cases. See Storing Binary files in the Database.

Minimal Example

First, we need a public table for storing the files.

create table files(
  id   int primary key
, blob bytea
);

Let’s assume this table contains an image of two cute kittens with id 42. We can retrieve this image in binary format from our PostgREST API by using Media Type Handlers:

create domain "application/octet-stream" as bytea;

create or replace function file(id int) returns "application/octet-stream" as $$
  select blob from files where id = file.id;
$$ language sql;

Now we can request the RPC endpoint /rpc/file?id=42 with the Accept: application/octet-stream header.

curl "localhost:3000/rpc/file?id=42" -H "Accept: application/octet-stream"

Unfortunately, putting the URL into the src of an <img> tag will not work. That’s because browsers do not send the required Accept: application/octet-stream header. Instead, the Accept: image/webp header is sent by many web browsers by default.

Luckily we can change the accepted media type in the function like so:

create domain "image/webp" as bytea;

create or replace function file(id int) returns "image/webp" as $$
  select blob from files where id = file.id;
$$ language sql;

Now, the image will be displayed in the HTML page:

<img src="http://localhost:3000/file?id=42" alt="Cute Kittens"/>

Improved Version

The basic solution has some shortcomings:

  1. The response Content-Type header is set to image/webp. This might be a problem if you want to specify a different format for the file.

  2. Download requests (e.g. Right Click -> Save Image As) to /files?select=blob&id=eq.42 will propose files as filename. This might confuse users.

  3. Requests to the binary endpoint are not cached. This will cause unnecessary load on the database.

The following improved version addresses these problems. First, in addition to the minimal example, we need to store the media types and names of our files in the database.

alter table files
  add column type text,
  add column name text;

Next, we set modify the function to set the content type and filename. We use this opportunity to configure some basic, client-side caching. For production, you probably want to configure additional caches, e.g. on the reverse proxy.

create domain "*/*" as bytea;

create function file(id int) returns "*/*" as
$$
  declare headers text;
  declare blob bytea;
  begin
    select format(
      '[{"Content-Type": "%s"},'
       '{"Content-Disposition": "inline; filename=\"%s\""},'
       '{"Cache-Control": "max-age=259200"}]'
      , files.type, files.name)
    from files where files.id = file.id into headers;
    perform set_config('response.headers', headers, true);
    select files.blob from files where files.id = file.id into blob;
    if FOUND -- special var, see https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
    then return(blob);
    else raise sqlstate 'PT404' using
      message = 'NOT FOUND',
      detail = 'File not found',
      hint = format('%s seems to be an invalid file id', file.id);
    end if;
  end
$$ language plpgsql;

With this, we can obtain the cat image from /rpc/file?id=42. Thus, the resulting HTML will be:

<img src="http://localhost:3000/rpc/file?id=42" alt="Cute Kittens"/>