# Database (https://form.dimah.dev/docs/database)



`dimahForm({ database })` needs a `ResponseStore`. `@dimah-form/server` has no ORM. `@dimah-form/db` is optional — install it only when drafts must survive a restart.

| Adapter           | Package              | When                                             |
| :---------------- | :------------------- | :----------------------------------------------- |
| `memoryAdapter()` | `@dimah-form/server` | Tests, local, [Quickstart](https://form.dimah.dev/docs/quickstart)     |
| `db(formDb)`      | `@dimah-form/db`     | Production (FumaDB + Drizzle, Prisma, or Kysely) |
| Custom            | your code            | Implement [`ResponseStore`](https://form.dimah.dev/docs/configuration) |

***

## 1. In-memory [#1-in-memory]

No extra packages. Data lives in the process and is gone on restart.

```ts title="lib/form.ts"
import { dimahForm, memoryAdapter } from "@dimah-form/server";
import { forms } from "@/lib/forms";

export const form = dimahForm({
  database: memoryAdapter(),
  forms,
});
```

***

## 2. Production [#2-production]

Install `@dimah-form/db` and `fumadb` when you need SQL:

<Tabs items="[&#x22;pnpm&#x22;, &#x22;npm&#x22;, &#x22;yarn&#x22;, &#x22;bun&#x22;]">
  <Tab value="pnpm">
    `bash pnpm add @dimah-form/db fumadb `
  </Tab>

  <Tab value="npm">
    `bash npm install @dimah-form/db fumadb `
  </Tab>

  <Tab value="yarn">
    `bash yarn add @dimah-form/db fumadb `
  </Tab>

  <Tab value="bun">
    `bash bun add @dimah-form/db fumadb `
  </Tab>
</Tabs>

<Steps>
  <Step>
    ### Schema [#schema]

    Copy these tables, or generate with the [CLI](#cli) and re-add the indexes (FumaDB `generate` does not emit them).

    <Tabs items="[&#x22;Drizzle&#x22;, &#x22;Prisma&#x22;, &#x22;Kysely&#x22;]">
      <Tab value="Drizzle">
        ```ts title="lib/db/schema.ts"
        import { defineRelations } from "drizzle-orm";
        import {
          sqliteTable,
          text,
          blob,
          integer,
          foreignKey,
          index,
        } from "drizzle-orm/sqlite-core";

        export const questionnaire = sqliteTable(
          "questionnaire",
          {
            id: text("id", { length: 255 })
              .primaryKey()
              .notNull()
              .$defaultFn(() => crypto.randomUUID()),
            slug: text("slug", { length: 255 }).unique(),
            title: text("title").notNull(),
            definition: blob("definition", { mode: "json" }).notNull(),
            status: text("status").notNull(),
            createdAt: integer("created_at", { mode: "timestamp" })
              .notNull()
              .defaultNow(),
            updatedAt: integer("updated_at", { mode: "timestamp" })
              .notNull()
              .defaultNow(),
          },
          (table) => [
            index("questionnaire_status_updated_at_idx").on(
              table.status,
              table.updatedAt,
            ),
          ],
        );

        export const response = sqliteTable(
          "response",
          {
            id: text("id", { length: 255 })
              .primaryKey()
              .notNull()
              .$defaultFn(() => crypto.randomUUID()),
            questionnaireId: text("questionnaire_id", { length: 255 }).notNull(),
            status: text("status").notNull(),
            definition: blob("definition", { mode: "json" }).notNull(),
            answers: blob("answers", { mode: "json" }).notNull(),
            respondentId: text("respondent_id", { length: 255 }),
            submittedAt: integer("submitted_at", { mode: "timestamp" }),
            createdAt: integer("created_at", { mode: "timestamp" })
              .notNull()
              .defaultNow(),
            updatedAt: integer("updated_at", { mode: "timestamp" })
              .notNull()
              .defaultNow(),
          },
          (table) => [
            foreignKey({
              columns: [table.questionnaireId],
              foreignColumns: [questionnaire.id],
              name: "response_questionnaire_questionnaire_fk",
            })
              .onUpdate("restrict")
              .onDelete("restrict"),
            index("response_questionnaire_id_updated_at_idx").on(
              table.questionnaireId,
              table.updatedAt,
            ),
            index("response_respondent_id_updated_at_idx").on(
              table.respondentId,
              table.updatedAt,
            ),
            index("response_draft_lookup_idx").on(
              table.questionnaireId,
              table.respondentId,
              table.status,
              table.updatedAt,
            ),
          ],
        );

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

        export const relations = defineRelations(
          { questionnaire, response, private_dimah_form_settings },
          (r) => ({
            questionnaire: {
              responses: r.many.response({
                alias: "response_questionnaire",
              }),
            },
            response: {
              questionnaire: r.one.questionnaire({
                from: r.response.questionnaireId,
                to: r.questionnaire.id,
                alias: "response_questionnaire",
              }),
            },
          }),
        );
        ```
      </Tab>

      <Tab value="Prisma">
        ```prisma title="prisma/schema.prisma"
        generator client {
          provider = "prisma-client-js"
        }

        datasource db {
          provider = "postgresql"
        }

        model Questionnaire {
          id         String   @id @default(cuid()) @map("id") @db.VarChar(255)
          slug       String?  @unique @map("slug") @db.VarChar(255)
          title      String   @map("title")
          definition Json     @map("definition")
          status     String   @map("status")
          createdAt  DateTime @default(now()) @map("created_at")
          updatedAt  DateTime @default(now()) @map("updated_at")
          responses  Response[] @relation("response_questionnaire")

          @@index([status, updatedAt], map: "questionnaire_status_updated_at_idx")
          @@map("questionnaire")
        }

        model Response {
          id              String    @id @default(cuid()) @map("id") @db.VarChar(255)
          questionnaireId String    @map("questionnaire_id") @db.VarChar(255)
          status          String    @map("status")
          definition      Json      @map("definition")
          answers         Json      @map("answers")
          respondentId    String?   @map("respondent_id") @db.VarChar(255)
          submittedAt     DateTime? @map("submitted_at")
          createdAt       DateTime  @default(now()) @map("created_at")
          updatedAt       DateTime  @default(now()) @map("updated_at")
          questionnaire   Questionnaire @relation("response_questionnaire", fields: [questionnaireId], references: [id], onUpdate: Restrict, onDelete: Restrict)

          @@index([questionnaireId, updatedAt], map: "response_questionnaire_id_updated_at_idx")
          @@index([respondentId, updatedAt], map: "response_respondent_id_updated_at_idx")
          @@index([questionnaireId, respondentId, status, updatedAt], map: "response_draft_lookup_idx")
          @@map("response")
        }

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

          @@map("private_dimah_form_settings")
        }
        ```
      </Tab>

      <Tab value="Kysely">
        Generate Kysely types with the CLI, then add these indexes:

        ```sql title="db/dimah-form-indexes.sql"
        CREATE INDEX IF NOT EXISTS questionnaire_status_updated_at_idx
          ON questionnaire (status, updated_at);

        CREATE INDEX IF NOT EXISTS response_questionnaire_id_updated_at_idx
          ON response (questionnaire_id, updated_at);

        CREATE INDEX IF NOT EXISTS response_respondent_id_updated_at_idx
          ON response (respondent_id, updated_at);

        CREATE INDEX IF NOT EXISTS response_draft_lookup_idx
          ON response (questionnaire_id, respondent_id, status, updated_at);
        ```
      </Tab>
    </Tabs>
  </Step>

  <Step>
    ### Client [#client]

    <Tabs items="[&#x22;Drizzle&#x22;, &#x22;Prisma&#x22;, &#x22;Kysely&#x22;]">
      <Tab value="Drizzle">
        ```ts title="lib/db.ts"
        import { createClient } from "@libsql/client";
        import { DimahFormDB } from "@dimah-form/db";
        import { drizzle } from "drizzle-orm/libsql";
        import { drizzleAdapter } from "fumadb/adapters/drizzle";
        import { relations } from "./schema";

        const sqlite = createClient({
          url: process.env.DATABASE_URL ?? "file:local.db",
        });

        export const formDb = DimahFormDB.client(
          drizzleAdapter({
            db: drizzle({ client: sqlite, relations }),
            provider: "sqlite",
          }),
        );
        ```
      </Tab>

      <Tab value="Prisma">
        ```ts title="lib/db.ts"
        import { PrismaClient } from "@prisma/client";
        import { DimahFormDB } from "@dimah-form/db";
        import { prismaAdapter } from "fumadb/adapters/prisma";

        const prisma = new PrismaClient();

        export const formDb = DimahFormDB.client(
          prismaAdapter({ prisma, provider: "postgresql" }),
        );
        ```
      </Tab>

      <Tab value="Kysely">
        ```ts title="lib/db.ts"
        import { DimahFormDB } from "@dimah-form/db";
        import { kyselyAdapter } from "fumadb/adapters/kysely";
        import { db } from "./kysely";

        export const formDb = DimahFormDB.client(
          kyselyAdapter({ db, provider: "postgresql" }),
        );
        ```
      </Tab>
    </Tabs>
  </Step>

  <Step>
    ### Connect [#connect]

    ```ts title="lib/form.ts"
    import { db } from "@dimah-form/db";
    import { dimahForm } from "@dimah-form/server";
    import { formDb } from "@/lib/db";
    import { forms } from "@/lib/forms";

    export const form = dimahForm({
      database: db(formDb),
      forms,
    });

    export type Form = typeof form;
    ```
  </Step>
</Steps>

***

## CLI [#cli]

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

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

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

`generate` overwrites the file. Re-add these indexes afterward:

| Index                                      | Columns                                                     |
| ------------------------------------------ | ----------------------------------------------------------- |
| `questionnaire_status_updated_at_idx`      | `status`, `updated_at`                                      |
| `response_questionnaire_id_updated_at_idx` | `questionnaire_id`, `updated_at`                            |
| `response_respondent_id_updated_at_idx`    | `respondent_id`, `updated_at`                               |
| `response_draft_lookup_idx`                | `questionnaire_id`, `respondent_id`, `status`, `updated_at` |

***

## Custom store [#custom-store]

Implement [`ResponseStore`](https://form.dimah.dev/docs/configuration) and pass it to `dimahForm({ database })` directly. Do not wrap it in `db()`.

***

## Next Steps [#next-steps]

<Cards>
  <Card title="Snapshots" href="/docs/snapshots" description="How snapshots and CAS updatedAt are stored." />

  <Card title="Auth" href="/docs/auth" description="Protect records with guard." />

  <Card title="Configuration" href="/docs/configuration" description="The ResponseStore interface." />
</Cards>