Serving Assets From the Database

Bytes that arrive at runtime, such as a user's avatar, a generated chart, or a PDF built from a row, live in a table, and a route serves them with the same content-hashed caching the asset pipeline gives build-time files under /assets. Every app server reads the same table, so every server serves the same bytes.

Postgres and object storage are both first-class homes for these bytes. This page keeps them in a bytea column. For video and other large media, keep the bytes in S3 or any object store and the row as metadata, the object key and the hash, and the caching below works unchanged.

The thing to get right is caching. A build-time asset is safe to cache forever because its URL contains a hash of its bytes, so changing the file changes the URL. This page does the same for a database row: store a hash of the data, put it in the URL, and serve with a long Cache-Control.

For getting bytes in from a file input, see file-upload.

Migration

Terminal
elements create migration 'add media' -tables=media

app/migrations/<timestamp>-add-media.migration.sql
-- add media

create table media (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  contentType text not null,
  data bytea not null,

  -- The cache key. Postgres recomputes it whenever data changes, so it can
  -- never drift from the bytes it names.
  hash text generated always as (encode(sha256(data), 'hex')) stored,

  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now()
);

create index mediaHashIdx on media (hash);

A generated column is the reason this works without discipline. Write new bytes and the hash follows in the same statement, so a stale URL is not something a caller can cause.

sha256() is built into Postgres. No extension.

Service

The gallery page and the app/routes/media.ts byte route both read this module, so it is shared rather than colocated with the page.

app/shared/services/media.ts
import { sql } from "@elements/app";

export interface Media {
  id: string;
  name: string;
  contentType: string;
  hash: string;
}

export interface MediaBytes extends Media {
  data: Buffer;
}

/** The URL for a row. The hash is what makes it safe to cache forever. */
export function mediaUrl(m: Media): string {
  return `/media/${m.id}/${m.hash}`;
}

/** @rpc */
export async function listMedia(): Promise<Media[]> {
  return sql<Media>(`
    select
      id,
      name,
      contentType,
      hash
    from media
    order by createdAt desc
  `).all();
}

export function readMedia(id: string): MediaBytes {
  return sql<MediaBytes>(`
    select
      id,
      name,
      contentType,
      hash,
      data
    from media
    where id = ${id}
  `).firstOrThrow();
}

readMedia reads the database and nothing calls it from the browser, so it stays out of the browser bundle and takes its query with it. listMedia is an @rpc, so the browser gets a stub that calls the server and never sees the sql.

Route

The route returns bytes, not html, so it is not a page. Create it by hand.

app/routes/media.ts
import { Request, Response } from "@elements/app";
import { readMedia } from "#app/shared/services/media";

// Types we are willing to render on our own origin. Everything else is sent
// as an opaque download rather than letting the browser sniff it.
const INLINE = new Set([
  "image/png",
  "image/jpeg",
  "image/gif",
  "image/webp",
  "image/svg+xml",
  "application/pdf",
]);

const YEAR = 31536000;

export default function serveMedia(req: Request, res: Response) {
  let m = readMedia(req.params.id);

  // The URL carries the hash, so a request for the wrong one is a request for
  // bytes that no longer exist. 404 rather than serve the current bytes under
  // a stale key: a cache somewhere would then hold them under that key.
  if (req.params.hash !== m.hash) {
    res.status(404);
    return res.end();
  }

  if (INLINE.has(m.contentType)) {
    res.setHeader("Content-Type", m.contentType);
  } else {
    res.setHeader("Content-Type", "application/octet-stream");
    res.setHeader("Content-Disposition", `attachment; filename="${m.name}"`);
  }

  // Immutable is the whole point of putting the hash in the path. The bytes at
  // this URL cannot change, so a browser never needs to revalidate.
  res.setHeader("Cache-Control", `public, max-age=${YEAR}, immutable`);

  return m.data;
}

firstOrThrow() inside readMedia gives a 404 when the id does not exist. Returning a Buffer sends raw bytes; the response layer recognizes it and skips the json serializer.

Register it:

index.ts
import serveMedia from "#app/routes/media";

app.route("/media/:id/:hash", serveMedia);

Template

The hash is already on the row, so the template builds the URL from data it has. No extra request.

app/pages/gallery/template.ehtml
import { mediaUrl, Media } from "#app/shared/services/media";

<html class="page-gallery" (items: Media[])>
  <head>
    <title>Gallery</title>
  </head>
  <body>
    <main class="page-shell">
      <div class="gallery">
        <img e:for={m of items} src={mediaUrl(m)} alt={m.name} width="240">
      </div>
    </main>
  </body>
</html>

app/pages/gallery/index.ts
import { Request, Response } from "@elements/app";
import { listMedia } from "#app/shared/services/media";
import gallery from "./template";

export default async function route(req: Request, res: Response) {
  return new gallery({ items: await listMedia() });
}

Replacing bytes

Update the row and the hash changes with it, so the URL changes, so every cache that held the old bytes is bypassed rather than invalidated.

app/shared/services/media.ts
export function replaceMedia(id: string, data: Buffer, contentType: string) {
  sql(`
    update media
    set
      data = ${data},
      contentType = ${contentType},
      updatedAt = now()
    where id = ${id}
  `);
}

Nothing has to be purged. The page re-renders with the new URL on its next request because listMedia reads the new hash.

Notes

• A bytea column serves images, PDFs and other files up to a few megabytes directly, reading each value into memory whole. For video and other large media, keep the bytes in an object store such as S3 and the row as metadata, the object key and a hash you compute on upload. The URL and the caching stay the same.
• The hash is sha256 of the raw bytes, so two identical uploads produce the same hash under different ids. To deduplicate, make hash unique and look it up before inserting.
• A private asset sends private, max-age=0, must-revalidate in place of public. Check the session in the route, so every view is authorized.