# Database Setup (https://v1.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/main/examples/with-db)
(Next.js + Drizzle + SQLite).

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

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

### Schema [step] [#schema-step]

Add the `storage_object` table (including the recommended indexes), or
[generate it with the CLI](#cli).

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

CLI / Drizzle output imports `fumadb/cuid` — alias it to
`@paralleldrive/cuid2` in your `tsconfig` paths.

```ts title="db/dimah-s3.ts"
import { defineRelations, sql } from "drizzle-orm";
import {
  sqliteTable,
  text,
  blob,
  integer,
  uniqueIndex,
  index,
} from "drizzle-orm/sqlite-core";
import { createId } from "fumadb/cuid";

export const storageObject = sqliteTable(
  "storage_object",
  {
    id: text("id", { length: 255 })
      .primaryKey()
      .notNull()
      .$defaultFn(() => createId()),
    scope: text("scope").notNull(),
    bucket: text("bucket").notNull(),
    key: text("key").notNull(),
    route: text("route").notNull(),
    contentType: text("content_type"),
    size: blob("size", { mode: "bigint" }),
    eTag: text("e_tag"),
    filename: text("filename"),
    status: text("status").notNull(),
    metadata: blob("metadata", { mode: "json" }),
    acl: text("acl"),
    uploadId: text("upload_id"),
    declaredSize: blob("declared_size", { mode: "bigint" }),
    confirmedAt: integer("confirmed_at", { mode: "timestamp" }),
    expiresAt: integer("expires_at", { mode: "timestamp" }),
    createdAt: integer("created_at", { mode: "timestamp" })
      .notNull()
      .default(sql`(unixepoch())`),
    updatedAt: integer("updated_at", { mode: "timestamp" })
      .notNull()
      .default(sql`(unixepoch())`),
    deletedAt: integer("deleted_at", { mode: "timestamp" }),
  },
  (table) => [
    uniqueIndex("storage_object_bucket_key_uk").on(table.bucket, table.key),
    // Recommended — FumaDB `generate` does not emit these yet.
    index("storage_object_scope_status_created_idx").on(
      table.scope,
      table.status,
      table.createdAt,
    ),
    index("storage_object_scope_route_idx").on(table.scope, table.route),
    index("storage_object_status_expires_idx").on(
      table.status,
      table.expiresAt,
    ),
    index("storage_object_status_created_idx").on(
      table.status,
      table.createdAt,
    ),
  ],
);

export const private_dimah_s3_settings = sqliteTable(
  "private_dimah_s3_settings",
  {
    id: text("id", { length: 255 }).primaryKey().notNull(),
    version: text("version", { length: 255 }).notNull().default("1.0.0"),
  },
);

export const relations = defineRelations({
  storageObject,
  private_dimah_s3_settings,
});
```

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

```prisma title="prisma/schema.prisma"
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
}

model StorageObject {
  id           String    @id @map("id") @db.VarChar(255)
  scope        String    @map("scope")
  bucket       String    @map("bucket")
  key          String    @map("key")
  route        String    @map("route")
  contentType  String?   @map("content_type")
  size         BigInt?   @map("size")
  eTag         String?   @map("e_tag")
  filename     String?   @map("filename")
  status       String    @map("status")
  metadata     Json?     @map("metadata")
  acl          String?   @map("acl")
  uploadId     String?   @map("upload_id")
  declaredSize BigInt?   @map("declared_size")
  confirmedAt  DateTime? @map("confirmed_at")
  expiresAt    DateTime? @map("expires_at")
  createdAt    DateTime  @default(now()) @map("created_at")
  updatedAt    DateTime  @updatedAt @map("updated_at")
  deletedAt    DateTime? @map("deleted_at")

  @@unique([bucket, key], map: "storage_object_bucket_key_uk")
  @@index([scope, status, createdAt], map: "storage_object_scope_status_created_idx")
  @@index([scope, route], map: "storage_object_scope_route_idx")
  @@index([status, expiresAt], map: "storage_object_status_expires_idx")
  @@index([status, createdAt], map: "storage_object_status_created_idx")
  @@map("storage_object")
}

model PrivateDimahS3Settings {
  id      String @id @map("id") @db.VarChar(255)
  version String @default("1.0.0") @map("version") @db.VarChar(255)

  @@map("private_dimah_s3_settings")
}
```

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

There is no committed schema file for Kysely. Migrate with the CLI, then add
the [indexes](#cli) with SQL:

```bash
node --import tsx scripts/db-cli.mts migrate
```

</Tab>
</Tabs>

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

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

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

drizzle-orm 1.x (the schema above) and 0.44 / 0.45 are both supported. 1.x
needs FumaDB 0.6 or later, and the client takes `relations` from
`defineRelations()`.

<Tabs items={["1.x", "0.x"]}>
<Tab value="1.x">

```ts title="lib/db.ts"
import { drizzle } from "drizzle-orm/better-sqlite3"; // or your driver
import { relations } from "./db/dimah-s3";

export const db = drizzle({
  client,
  relations,
});
```

</Tab>
<Tab value="0.x">

On 0.44 / 0.45, drop the `relations` export from the schema file.
`import * as schema` would otherwise treat it as a table.

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

export const db = drizzle(client, { schema });
```

</Tab>
</Tabs>

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

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

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

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

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

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

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

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

</Tab>
</Tabs>

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

Pass `db()` in `plugins`. `resolveScope` must return a stable ownership
string, or `null` to reject unauthenticated callers (`401`):

```ts title="lib/s3.ts"
import { dimahS3, route } from "@dimah-s3/server";
import { db } from "@dimah-s3/db";
import { dimahS3Db } from "@/lib/dimah-s3-db";

export const s3 = dimahS3({
  client: awsS3,
  bucket: process.env.S3_BUCKET!,
  plugins: [
    db({
      // [!code ++]
      client: dimahS3Db, // [!code ++]
      resolveScope: async (request) => {
        // [!code ++]
        const session = await getSession(request); // [!code ++]
        return session ? `user:${session.userId}` : null; // [!code ++]
      }, // [!code ++]
    }), // [!code ++]
  ],
  routes: {
    avatar: route({
      upload: {
        fileTypes: ["image/*"],
        maxFileSize: 2 * 1024 * 1024,
      },
      download: true,
      delete: true,
    }),
  },
});
```

The same store is available as `s3.db` (and `s3.context.db`):

```ts
s3.db.objects.listByScope({ scope: "user:123" });
```

### Scope context [#scope-context]

`resolveScope` receives a second argument with the values the endpoint
already validated: `operation` (`"upload"`, `"confirm"`, `"multipart"`,
`"download"`, `"delete"`, or `"list"`), `route`, the object `key`, and, on
upload, the client's `clientMetadata`. A scope that depends on the key does
not have to parse the request. A batch delete resolves once per key, and one
request never calls the resolver twice for the same operation and key.

```ts title="lib/s3.ts"
db({
  client: dimahS3Db,
  resolveScope: async (request, { key }) => {
    const session = await getSession(request);
    if (!session) return null;
    // Keys look like `{route}/{kind}/{id}/…`.
    const kind = key?.split("/")[1]; // [!code highlight]
    return kind === "org" ? `org:${session.orgId}` : `user:${session.userId}`;
  },
});
```

<AutoTypeTable
  path="packages/db/src/types/storage-object.ts"
  name="ScopeContext"
/>

<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://v1.s3.dimah.dev/docs/server/routes#combining-operations)).
</Callout>

### Plugin options [#plugin-options]

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

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

### Delete behavior [#delete-behavior]

App deletes always go through the normal server delete path (`api.delete`,
the Delete button, `useDelete`, or your own delete hooks). Do not call
`s3.db.objects.softDelete` or `hardDelete` for that. Those store helpers
only update the database. They are not a replacement for deleting the S3
object.

When that path runs:

1. S3 — `DeleteObject` removes the object
2. Database — controlled by `deleteMode` (default `"soft"`)

Delete is idempotent. A `pending` row whose object never landed (a closed
tab, a network error, an object a lifecycle rule removed) is cleaned up the
same way, so it stops counting toward [quota](https://v1.s3.dimah.dev/docs/db/api#quota-guards)
before the purge job runs. A pending multipart upload is aborted by its
`uploadId`.

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

Omit `deleteMode` and the row stays as `deleted` after S3 removal. 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: "soft", // [!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 DB row
(for example GDPR-style erasure). To prune old soft-deleted audit rows
later, call `s3.db.objects.hardDelete({ bucket, key })` from a script or
job — that helper only removes the row; it does not delete from S3.

</Accordion>

</Accordions>

## CLI [#cli]

Generate adapter-specific schema (or run migrations) through FumaDB:

```ts title="scripts/db-cli.mts"
import { DimahS3DB } from "@dimah-s3/db";
import { runCli } from "@dimah-s3/db/cli";
import { drizzleAdapter } from "fumadb/adapters/drizzle";

void runCli(
  DimahS3DB.client(drizzleAdapter({ db: {} as never, provider: "sqlite" })),
);
```

```bash
node --import tsx scripts/db-cli.mts generate latest -o ./db/dimah-s3.ts
```

`generate` overwrites the output file. Re-add secondary indexes afterward,
or keep them in a separate migration. On drizzle-orm 1.x, also replace
`.defaultNow()` with ``sql`(unixepoch())``. `defaultNow()` is deprecated and
stores milliseconds, while `mode: "timestamp"` reads seconds.

| Index                                     | Columns                        |
| ----------------------------------------- | ------------------------------ |
| `storage_object_scope_status_created_idx` | `scope`, `status`, `createdAt` |
| `storage_object_scope_route_idx`          | `scope`, `route`               |
| `storage_object_status_expires_idx`       | `status`, `expiresAt`          |
| `storage_object_status_created_idx`       | `status`, `createdAt`          |