Serving Assets From the Database

Bytes that arrive at runtime cannot go through the asset pipeline. A user's avatar, a generated chart, a PDF built from a row: none of them exist at build time, so none of them get a content-hashed filename under /assets. They live in a table and a route hands them back.

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

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

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:

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

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.

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

• Keep large files out of Postgres. A bytea column is read into memory whole on every request, so a 50MB video is a 50MB allocation per concurrent reader. Rows up to a few megabytes are fine; past that, store the bytes in object storage and keep the row as metadata plus a key.
• 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 cannot use public in Cache-Control. Check the session in the route and send private, max-age=0, must-revalidate instead, and accept that every view costs a request.