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

## Migration

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

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

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

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

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

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

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

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