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



Add `dimah_survey` and `dimah_response` to your schema, migrate with the tool
you already use, and pass the same `database` to both server audiences.
dimah-survey does not run migrations.

<CodeBlockTabs defaultValue="npm">
  <CodeBlockTabsList>
    <CodeBlockTabsTrigger value="npm">
      npm
    </CodeBlockTabsTrigger>

    <CodeBlockTabsTrigger value="pnpm">
      pnpm
    </CodeBlockTabsTrigger>

    <CodeBlockTabsTrigger value="yarn">
      yarn
    </CodeBlockTabsTrigger>

    <CodeBlockTabsTrigger value="bun">
      bun
    </CodeBlockTabsTrigger>
  </CodeBlockTabsList>

  <CodeBlockTab value="npm">
    ```bash
    npm i @dimah-survey/db fumadb
    ```
  </CodeBlockTab>

  <CodeBlockTab value="pnpm">
    ```bash
    pnpm add @dimah-survey/db fumadb
    ```
  </CodeBlockTab>

  <CodeBlockTab value="yarn">
    ```bash
    yarn add @dimah-survey/db fumadb
    ```
  </CodeBlockTab>

  <CodeBlockTab value="bun">
    ```bash
    bun add @dimah-survey/db fumadb
    ```
  </CodeBlockTab>
</CodeBlockTabs>

Model keys are `survey` and `response`. Copy the tables into your application.
Do not import them from `@dimah-survey/db` at runtime.

`provider` is `"sqlite"`, `"postgresql"`, or `"mysql"`.

<Tabs items="[&#x22;Drizzle&#x22;, &#x22;Prisma&#x22;, &#x22;Kysely&#x22;]">
  <Tab value="Drizzle">
    Add these tables to your Drizzle schema. This copy is SQLite. On PostgreSQL or
    MySQL, use that driver's table builder and set `provider` to match. Keep the
    partial unique index.

    ```ts title="db/schema.ts"
    import { defineRelations, sql } from "drizzle-orm";
    import {
      blob,
      check,
      foreignKey,
      index,
      integer,
      sqliteTable,
      text,
      uniqueIndex,
    } from "drizzle-orm/sqlite-core";

    export const survey = sqliteTable(
      "dimah_survey",
      {
        id: text("id", { length: 255 }).primaryKey().notNull(),
        slug: text("slug", { length: 255 }).notNull(),
        status: text("status").notNull(),
        draftJson: blob("draft_json", { mode: "json" }).notNull(),
        publishedJson: blob("published_json", { mode: "json" }),
        publishedAt: integer("published_at", { mode: "timestamp" }),
        settings: blob("settings", { mode: "json" }).notNull(),
        createdAt: integer("created_at", { mode: "timestamp" })
          .notNull()
          .defaultNow(),
        updatedAt: integer("updated_at", { mode: "timestamp" })
          .notNull()
          .defaultNow(),
      },
      (table) => [
        uniqueIndex("dimah_survey_slug_unique").on(table.slug),
        check(
          "dimah_survey_status_check",
          sql`${table.status} in ('draft', 'active', 'archived')`,
        ),
        index("dimah_survey_status_updated_at_idx").on(
          table.status,
          table.updatedAt,
        ),
      ],
    );

    export const response = sqliteTable(
      "dimah_response",
      {
        id: text("id", { length: 255 }).primaryKey().notNull(),
        surveyId: text("survey_id", { length: 255 }).notNull(),
        respondentId: text("respondent_id", { length: 255 }),
        status: text("status").notNull(),
        definition: blob("definition", { mode: "json" }).notNull(),
        data: blob("data", { mode: "json" }).notNull(),
        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.surveyId],
          foreignColumns: [survey.id],
          name: "dimah_response_survey_fk",
        })
          .onUpdate("restrict")
          .onDelete("restrict"),
        check(
          "dimah_response_status_check",
          sql`${table.status} in ('draft', 'submitted', 'abandoned')`,
        ),
        index("dimah_response_survey_id_updated_at_idx").on(
          table.surveyId,
          table.updatedAt,
        ),
        index("dimah_response_respondent_lookup_idx").on(
          table.surveyId,
          table.respondentId,
          table.status,
        ),
        uniqueIndex("dimah_response_one_open_draft")
          .on(table.surveyId, table.respondentId)
          .where(
            sql`${table.status} = 'draft' and ${table.respondentId} is not null`,
          ),
      ],
    );

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

    export const relations = defineRelations(
      { survey, response, privateDimahSurveySettings },
      (helpers) => ({
        survey: {
          responses: helpers.many.response({
            from: helpers.survey.id,
            to: helpers.response.surveyId,
          }),
        },
        response: {
          survey: helpers.one.survey({
            from: helpers.response.surveyId,
            to: helpers.survey.id,
          }),
        },
      }),
    );
    ```

    ```ts title="db/index.ts"
    import { DimahSurveyDB, db } from "@dimah-survey/db";
    import { drizzleAdapter } from "fumadb/adapters/drizzle";

    export const database = db(
      DimahSurveyDB.client(
        drizzleAdapter({ db: drizzleDb, provider: "sqlite" }),
      ),
    );
    ```

    `drizzleDb` is the Drizzle client you already open. Pass `relations` from the
    schema into that client.
  </Tab>

  <Tab value="Prisma">
    Add these models to your Prisma schema. This copy is PostgreSQL.

    ```prisma title="schema.prisma"
    model Survey {
      id            String    @id @map("id") @db.VarChar(255)
      slug          String    @unique(map: "dimah_survey_slug_unique") @map("slug") @db.VarChar(255)
      status        String    @map("status")
      draftJson     Json      @map("draft_json")
      publishedJson Json?     @map("published_json")
      publishedAt   DateTime? @map("published_at")
      settings      Json      @map("settings")
      createdAt     DateTime  @default(now()) @map("created_at")
      updatedAt     DateTime  @default(now()) @map("updated_at")
      responses     Response[]

      @@index([status, updatedAt], map: "dimah_survey_status_updated_at_idx")
      @@map("dimah_survey")
    }

    model Response {
      id           String    @id @map("id") @db.VarChar(255)
      surveyId     String    @map("survey_id") @db.VarChar(255)
      respondentId String?   @map("respondent_id") @db.VarChar(255)
      status       String    @map("status")
      definition   Json      @map("definition")
      data         Json      @map("data")
      submittedAt  DateTime? @map("submitted_at")
      createdAt    DateTime  @default(now()) @map("created_at")
      updatedAt    DateTime  @default(now()) @map("updated_at")
      survey       Survey    @relation(fields: [surveyId], references: [id], onUpdate: Restrict, onDelete: Restrict, map: "dimah_response_survey_fk")

      @@index([surveyId, updatedAt], map: "dimah_response_survey_id_updated_at_idx")
      @@index([surveyId, respondentId, status], map: "dimah_response_respondent_lookup_idx")
      @@map("dimah_response")
    }

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

      @@map("private_dimah_survey_settings")
    }
    ```

    Prisma cannot express the partial unique index. Add this statement to the
    migration you generate, then apply it as you usually do:

    ```sql title="db/indexes.sql"
    create unique index if not exists dimah_response_one_open_draft
      on dimah_response (survey_id, respondent_id)
      where status = 'draft' and respondent_id is not null;
    ```

    ```ts title="db/index.ts"
    import { DimahSurveyDB, db } from "@dimah-survey/db";
    import { prismaAdapter } from "fumadb/adapters/prisma";

    export const database = db(
      DimahSurveyDB.client(
        prismaAdapter({ prisma, provider: "postgresql" }),
      ),
    );
    ```

    `prisma` is your existing `PrismaClient`.
  </Tab>

  <Tab value="Kysely">
    Add this SQL with your migration tool. This copy is PostgreSQL.

    ```sql title="db/schema.sql"
    create table dimah_survey (
      id varchar(255) primary key,
      slug varchar(255) not null,
      status text not null,
      draft_json jsonb not null,
      published_json jsonb,
      published_at timestamptz,
      settings jsonb not null,
      created_at timestamptz not null default now(),
      updated_at timestamptz not null default now(),
      constraint dimah_survey_slug_unique unique (slug),
      constraint dimah_survey_status_check check (status in ('draft', 'active', 'archived'))
    );

    create table dimah_response (
      id varchar(255) primary key,
      survey_id varchar(255) not null,
      respondent_id varchar(255),
      status text not null,
      definition jsonb not null,
      data jsonb not null,
      submitted_at timestamptz,
      created_at timestamptz not null default now(),
      updated_at timestamptz not null default now(),
      constraint dimah_response_survey_fk
        foreign key (survey_id) references dimah_survey (id)
        on update restrict on delete restrict,
      constraint dimah_response_status_check check (status in ('draft', 'submitted', 'abandoned'))
    );

    create table private_dimah_survey_settings (
      id varchar(255) primary key,
      version varchar(255) not null default '1.0.0'
    );

    create index if not exists dimah_survey_status_updated_at_idx
      on dimah_survey (status, updated_at);

    create index if not exists dimah_response_survey_id_updated_at_idx
      on dimah_response (survey_id, updated_at);

    create index if not exists dimah_response_respondent_lookup_idx
      on dimah_response (survey_id, respondent_id, status);

    create unique index if not exists dimah_response_one_open_draft
      on dimah_response (survey_id, respondent_id)
      where status = 'draft' and respondent_id is not null;
    ```

    ```ts title="db/index.ts"
    import { DimahSurveyDB, db } from "@dimah-survey/db";
    import { kyselyAdapter } from "fumadb/adapters/kysely";

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

    `kysely` is your existing Kysely instance.
  </Tab>
</Tabs>

<Callout>
  The store accepts `provider: "mysql"`, but this page does not include a MySQL
  schema. Translate the tables and indexes before connecting the adapter.
</Callout>

## Next.js [#nextjs]

Keep the store on the Node.js runtime. The last entry is the ORM package that
opens the connection: `drizzle-orm`, `@prisma/client`, or `kysely`.

```ts title="next.config.ts"
import type { NextConfig } from "next";

const nextConfig: NextConfig = {
  serverExternalPackages: ["@dimah-survey/db", "fumadb", "drizzle-orm"],
};

export default nextConfig;
```

## In memory [#in-memory]

`memoryAdapter()` from `@dimah-survey/server` stores rows in the process. They
disappear when the process exits. Use it for tests, or when you do not want
the SQL tables. Pass it as `database` the same way as the SQL store.

```ts
import { memoryAdapter } from "@dimah-survey/server";

export const database = memoryAdapter();
```

## Custom store [#custom-store]

```ts
import type { SurveyStore } from "@dimah-survey/core";
```

<AutoTypeTable path="packages/core/src/types.ts" name="SurveyStore" />

Start and submit callbacks are server-only. They are never accepted from the
HTTP body:

```ts
import type {
  StartResponseLifecycle,
  SubmitResponseLifecycle,
} from "@dimah-survey/core";
```

<AutoTypeTable path="packages/core/src/types.ts" name="StartResponseLifecycle" />

<AutoTypeTable path="packages/core/src/types.ts" name="SubmitResponseLifecycle" />

A conforming store preserves these invariants:

### Documents and snapshots [#documents-and-snapshots]

* Keep `draftJson`, `publishedJson`, and `settings` independent.
* Insert default settings with a new survey; only
  `saveSurveySettings` changes them afterward.
* Copy `publishedJson` into `response.definition` at start and never update
  that column.
* Partial save replaces `data`; persist the object passed by the server.

### Atomic writes [#atomic-writes]

* Check `expectedUpdatedAt` inside the write.
* Evaluate start reuse, collection windows, response caps, and one-open-draft
  rules in the same lock or transaction as the mutation.
* Enforce one draft per survey and identified respondent in application logic;
  use the partial unique index as a database backstop.
* Run the start and submit lifecycle callbacks inside that same boundary.

### Queries and transitions [#queries-and-transitions]

* Anonymous start always inserts. Identified start follows `"one-open"` or
  `"single"` settings.
* Start, partial save, and submit enforce the collection window.
* New starts and submits enforce the response cap.
* Reopen enforces `reopen` and refuses a second identified draft.
* Lists honor pagination, sort by `updatedAt` descending, and omit
  `definition` and `data` from summaries.