# Database Setup (https://v2.s3.dimah.dev/docs/db/setup)

This guide assumes you already have Drizzle, Prisma, or Kysely connected to
a database. A working reference is
[`examples/with-db`](https://github.com/dimah-kz/dimah-s3/tree/v2/examples/with-db)
(Next.js + Drizzle + SQLite).

Upgrading from 1.x? The tables changed: follow
[Upgrade the schema](https://v2.s3.dimah.dev/docs/db/upgrade) first.

### Install [step] [#install-step]

```npm
npm i @dimah-s3/db@beta
```

You do not install `fumadb`. The adapters ship from
`@dimah-s3/db/adapters/*`, and the tables from `@dimah-s3/db/drizzle/*`,
`prisma/`, and `sql/`.

### Tables [step] [#tables-step]

The plugin uses two tables, `storage_object` and `storage_folder`, plus a
small settings table that records the schema version (`2.0.0`).

<Tabs items={["Drizzle", "Prisma", "SQL", "Kysely"]}>
<Tab value="Drizzle">

The tables ship prebuilt in `@dimah-s3/db/drizzle/sqlite`, `/pg`, and
`/mysql`. Re-export them from your schema file so `drizzle-kit` creates them
next to your own tables:

```ts title="db/schema.ts"
import { dimahS3Tables } from "@dimah-s3/db/drizzle/sqlite"; // or /pg, /mysql

export const { storageObject, storageFolder, private_dimah_s3_settings } =
  dimahS3Tables;

// Your own tables go here.
```

Keep the three names as they are. The adapter finds the tables by these
keys, and it reads the schema version from `private_dimah_s3_settings`.

```bash
npx drizzle-kit push
```

The tables carry the secondary indexes, so `drizzle-kit` creates those too.
On MySQL, the tables set the same collations as `sql/mysql.sql`:
`utf8mb4_bin`, so keys and folders compare byte for byte, and
`utf8mb4_unicode_ci` on `filename`, so search ignores case.

</Tab>
<Tab value="Prisma">

The models ship in `prisma/dimah-s3.prisma`, with no generator or
datasource. With a multi-file schema, copy the file into your schema folder.
Otherwise, paste its models into `schema.prisma`.

```bash
cp node_modules/@dimah-s3/db/prisma/dimah-s3.prisma prisma/schema/
npx prisma migrate dev --name dimah-s3
```

Keep the model names `StorageObject`, `StorageFolder`, and
`private_dimah_s3_settings`. The adapter calls them by name. The models
declare the secondary indexes, so the migration creates them too.

The models work as they are on PostgreSQL and SQLite. On MySQL, follow the
header of the file: add `@db.VarChar(1024)` to `key`, `folder`, `path`, and
`parent`, `@db.DateTime(3)` to the timestamps, and index prefixes such as
`@@unique([bucket, key(length: 512)])`.

</Tab>
<Tab value="SQL">

Run the file for your database once. Each one creates the tables, the
indexes, and the settings row:

```bash
psql "$DATABASE_URL" -f node_modules/@dimah-s3/db/sql/pg.sql
# or sql/sqlite.sql, sql/mysql.sql
```

`sql/mysql.sql` indexes `key` on its first 512 characters and `folder` on
its first 255. Two keys in one bucket that share their first 512 characters
count as the same key.

</Tab>
<Tab value="Kysely">

`migrate(client)` creates the tables and indexes, and later upgrades them.
It needs the Kysely adapter (next step):

```ts title="scripts/migrate.ts"
import { migrate } from "@dimah-s3/db/cli";
import { dimahS3Db } from "@/lib/dimah-s3-db";

await migrate(dimahS3Db);
```

`migrate` does not work on MySQL. Use `sql/mysql.sql` there.

</Tab>
</Tabs>

### Database client [step] [#database-client-step]

Wrap your ORM connection with an adapter, then create the dimah-s3 DB
client:

<Tabs items={["Drizzle", "Prisma", "Kysely"]}>
<Tab value="Drizzle">

drizzle-orm 1.x is required (`1.0.0-rc.5` or later).

```ts title="lib/dimah-s3-db.ts"
import { DimahS3DB } from "@dimah-s3/db";
import { drizzleAdapter } from "@dimah-s3/db/adapters/drizzle";
import Database from "better-sqlite3";
import { defineRelations } from "drizzle-orm";
import { drizzle } from "drizzle-orm/better-sqlite3"; // or your driver
import * as schema from "../db/schema";

export const db = drizzle({
  client: new Database("local.db"),
  relations: defineRelations(schema),
});

export const dimahS3Db = DimahS3DB.client(
  drizzleAdapter({ db, provider: "sqlite" }), // or "postgresql" | "mysql"
);
```

</Tab>
<Tab value="Prisma">

```ts title="lib/dimah-s3-db.ts"
import { DimahS3DB } from "@dimah-s3/db";
import { prismaAdapter } from "@dimah-s3/db/adapters/prisma";
import { prisma } from "@/lib/prisma";

export const dimahS3Db = DimahS3DB.client(
  prismaAdapter({ prisma, provider: "postgresql" }),
);
```

</Tab>
<Tab value="Kysely">

```ts title="lib/dimah-s3-db.ts"
import { DimahS3DB } from "@dimah-s3/db";
import { kyselyAdapter } from "@dimah-s3/db/adapters/kysely";
import { kysely } from "@/lib/kysely";

export const dimahS3Db = DimahS3DB.client(
  kyselyAdapter({ db: kysely, provider: "postgresql" }),
);
```

</Tab>
</Tabs>

On PostgreSQL, the timestamp columns have no time zone. The driver must
write and read `Date` values the same way: `node-postgres` does by default.

### Register the plugin [step] [#register-the-plugin-step]

Pass `db()` in `plugins`. `getSession` loads the caller, and `resolveScope`
returns a stable owner string for them, or `null` to reject the request
with `UNAUTHORIZED` (`401`):

```ts title="lib/s3.ts"
import { S3Client } from "@aws-sdk/client-s3";
import { db } from "@dimah-s3/db";
import { dimahS3, route } from "@dimah-s3/server";
import { getSession } from "@/lib/auth"; // your auth
import { dimahS3Db } from "@/lib/dimah-s3-db";

const awsS3 = new S3Client({ requestChecksumCalculation: "WHEN_REQUIRED" });

export const s3 = dimahS3({
  client: awsS3,
  bucket: process.env.S3_BUCKET!,
  plugins: [
    db({
      client: dimahS3Db,
      getSession: (request) => getSession(request),
      resolveScope: ({ session }) =>
        session ? `user:${session.userId}` : null,
    }),
  ],
  routes: {
    files: route({
      upload: { maxFileSize: 20 * 1024 * 1024 },
      download: true,
      delete: true,
    }),
  },
});
```

Set `DIMAH_S3_SECRET` in your environment: `dimahS3()` reads it to sign
[upload tickets](https://v2.s3.dimah.dev/docs/server/upload-tickets), which `db()` binds to the
scope.

The plugin's context is on `s3.db` (and `s3.context.db`). A database at
another schema version throws on the first query. Call `checkSchema` at
startup to fail fast:

```ts
await s3.db.checkSchema();
const page = await s3.db.objects.list({ scope: "user:123", folder: null });
```

<Callout type="warn" title="Features stay opt-in">
  Plugin hooks merge onto every route unless the route sets
  `plugins: { db: false }`. You still enable each operation on the route
  yourself: the plugin does not turn them on. Enable download or delete on
  this **same** route when those callers share these objects
  ([combining operations](https://v2.s3.dimah.dev/docs/server/routes#combining-operations)).
</Callout>

## Scope [#scope]

Every row belongs to a scope, such as `user:123` or `org:acme`. Upload
tickets are bound to it, so a ticket from one scope cannot confirm or
complete an upload in another.

`resolveScope` says who is calling. It receives the `request`, the
`session` (the `getSession` result), the `locals` your guards set, the
`operation`, and the `route` when the call has one. It never sees the
target object: the scope comes from the caller, so the same caller gets the
same scope when an upload ticket is bound, used, and confirmed. Checks on
the target belong in [`authorize`](#authorize).

- `getSession` runs at most once per `Request`.
- `resolveScope` runs once per call, operation, and route. Two `s3.api`
  calls that share a `Request` but pass different `locals` resolve
  separately.

<AutoTypeTable path="packages/db/src/types/plugin.ts" name="ScopeContext" />

| `operation` | Runs for                                                   |
| ----------- | ---------------------------------------------------------- |
| `upload`    | presign, multipart init, and `s3.put`                      |
| `confirm`   | confirm, and the confirm step of multipart complete        |
| `multipart` | part, list, abort, and complete                            |
| `download`  | presigned download and `GET /file`                         |
| `delete`    | delete, and each key of `deleteMany`                       |
| `list`      | `GET /db/objects`                                          |
| `read`      | `GET /db/object`                                           |
| `update`    | `PATCH /db/object`, `POST /db/folder`, `DELETE /db/folder` |

## Authorize [#authorize]

A row belongs to the scope that wrote it. A caller from another scope gets
`OBJECT_NOT_FOUND` (`404`), the same as for a key with no row, so nobody
learns which keys other scopes hold. (An upload to a key another scope holds
gets `FORBIDDEN`: upload keys are the server's choice.) `authorize` runs
after that check and can only narrow it: return `false` to reject the
request with `FORBIDDEN` (`403`).

It gets the scope context plus the resolved `scope`, and what the call
targets: the `key`, the target `folder` of an upload, a listing, a move, or
a folder endpoint, and on upload the client's `clientMetadata`. `object` is
the stored row when the request targets one: confirm, the multipart calls,
download, delete, read, and update. It is the server view, with `scope`,
`s3Metadata`, and the attributes as stored, not what `attributes.toClient`
sends the browser.

<AutoTypeTable path="packages/db/src/types/plugin.ts" name="AuthorizeContext" />

An upload on a `replace: "overwrite"` route that replaces an active object
runs `authorize` with `object` set to that object, and `folder` is that
object's folder, not `clientMetadata.folder`.

```ts title="lib/s3.ts"
const inArchive = (path?: string | null) =>
  path === "archive" || path?.startsWith("archive/") === true;

db({
  client: dimahS3Db,
  getSession,
  resolveScope: ({ session }) => (session ? `user:${session.userId}` : null),
  // `archive/` is read-only.
  authorize: ({ operation, folder, object }) => {
    if (["list", "read", "download"].includes(operation)) return true;
    return !inArchive(folder) && !inArchive(object?.folder);
  },
});
```

## Plugin options [#plugin-options]

```ts
import type { DbPluginOptions } from "@dimah-s3/db";
```

<AutoTypeTable path="packages/db/src/types/plugin.ts" name="DbPluginOptions" />

`quota` and `attributes` are covered in the [API](https://v2.s3.dimah.dev/docs/db/api#quota)
page.

## Route options [#route-options]

Set per-route options under `plugins: { db: … }`:

```ts title="lib/s3.ts"
routes: {
  // One key per user: a new upload replaces the old avatar instead of
  // failing with OBJECT_EXISTS. `requireUserId` is your auth helper.
  avatar: route({
    upload: {
      fileTypes: ["image/*"],
      object: async ({ request }) => ({
        key: `${await requireUserId(request)}/avatar`,
      }),
    },
    download: true,
    plugins: { db: { replace: "overwrite" } }, // [!code ++]
  }),
  // Not tracked: no rows, no ownership checks.
  public: route({
    upload: true,
    download: { guard: requireAdmin },
    plugins: { db: false }, // [!code ++]
  }),
},
```

<AutoTypeTable path="packages/db/src/types/plugin.ts" name="DbRouteOptions" />

With `replace: "overwrite"`, the row stays `active` and keeps the previous
object's metadata until the new upload is confirmed; S3 replaces the bytes as
soon as the new PUT lands. The row keeps its folder, attributes, and
`createdAt`. Until then, quota counts the larger of the two sizes. A retried
confirm of the upload before it still succeeds, even after the overwrite
started.

`plugins: { db: false }` turns off tracking and every db hook for the route.
Its upload tickets are not bound to a scope. When such a route enables
download or delete with no route or feature guard, the plugin logs a warning
at startup: nothing then checks who owns the object.

## Delete behavior [#delete-behavior]

App deletes always go through the normal server delete path (`api.delete`,
the `Delete` component, `useDelete`, or your own delete hooks).
`s3.db.objects` has no delete method: it never touches S3.

When that path runs:

1. S3: `DeleteObject` removes the object.
2. Database: `deleteMode` decides (default `"soft"`).

A `pending` row whose object never landed (a closed tab, a network error)
is cleaned up the same way, so it stops counting toward the
[quota](https://v2.s3.dimah.dev/docs/db/api#quota) before [purge](https://v2.s3.dimah.dev/docs/db/purge) runs. A multipart
upload in flight is aborted first. Deleting a soft-deleted object again
succeeds, like a plain S3 delete. A key with no row is `OBJECT_NOT_FOUND`:
its owner cannot be checked.

| `deleteMode` | S3 object           | Database row                                     |
| ------------ | ------------------- | ------------------------------------------------ |
| `"soft"`     | permanently deleted | kept as `status: "deleted"` with `deletedAt` set |
| `"hard"`     | permanently deleted | row removed entirely                             |

Listings hide soft-deleted rows by default.

<Callout type="warn" title="Not a recycle bin">
  Soft delete is not a recycle bin. The file is gone from S3. The row is kept
  for audit and history.
</Callout>

```ts
db({
  client: dimahS3Db,
  resolveScope,
  deleteMode: "hard", // [!code ++]
});
```

## Frequently asked questions [#frequently-asked-questions]

<Accordions>

<Accordion title="When should I use hard delete?">

Use `deleteMode: "hard"` when every delete should also erase the row (for
example GDPR-style erasure). To keep soft-deleted rows for a while and then
remove them, run [`s3.db.pruneDeleted`](https://v2.s3.dimah.dev/docs/db/purge#prune-deleted-rows)
from a job.

</Accordion>

<Accordion title="Does s3.put create a row?">

Yes. `s3.put` runs the same hooks with `kind: "server"`. Pass the caller's
`request` (or trusted `locals`) so `resolveScope` knows who it is, and
`clientMetadata.folder` to file it in a folder:

```ts
await s3.put(
  {
    route: "files",
    name: "report.csv",
    type: "text/csv",
    body: csv,
    clientMetadata: { folder: "reports" },
  },
  { request },
);
```

</Accordion>

</Accordions>

## CLI [#cli]

`runCli` runs the FumaDB CLI against your client, for example to generate an
ORM schema. Prefer `migrate(client)` to migrate: the CLI's `migrate` command
jumps straight to the latest schema and skips the 2.0.0 indexes. Only
`migrate:up`, one version at a time, runs them.

```ts title="scripts/db-cli.ts"
import { runCli } from "@dimah-s3/db/cli";
import { dimahS3Db } from "@/lib/dimah-s3-db";

await runCli(dimahS3Db);
```

The shipped tables, models, and SQL files already include these indexes:

| Index                               | Table            | Columns                                   |
| ----------------------------------- | ---------------- | ----------------------------------------- |
| `storage_object_scope_folder_idx`   | `storage_object` | `scope`, `status`, `folder`, `created_at` |
| `storage_object_scope_updated_idx`  | `storage_object` | `scope`, `status`, `updated_at`           |
| `storage_object_scope_filename_idx` | `storage_object` | `scope`, `status`, `filename`             |
| `storage_object_scope_size_idx`     | `storage_object` | `scope`, `status`, `size`                 |
| `storage_object_status_expires_idx` | `storage_object` | `status`, `expires_at`                    |
| `storage_folder_scope_parent_idx`   | `storage_folder` | `scope`, `parent`                         |