# 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](file-upload).

## Migration

```bash
elements create migration 'add media' -tables=media
```

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

```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`:

```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`:

```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:

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

```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`:

```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.

```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

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