[What is Prisma?](#what-is-prisma)
[Database setup](#database-setup)
[Database schema](#database-schema)
[Prisma client initialization](#prisma-client-initialization)
[Prisma configuration](#prisma-configuration)
[Session management](#session-management)
[Tutorial: CRUD with Server Actions](#tutorial-crud-with-server-actions)

---

!!!info
This is a guide to help you understand how the Quable App template works with **Prisma 7** and SQLite for database operations.
!!!

---

## What is Prisma?

[Prisma](https://www.prisma.io/) is an open-source database toolkit, known for simplifying database access with an Object-Relational Mapping (ORM) approach. It allows developers to write database queries in TypeScript, enhancing readability and maintainability.

Prisma ensures type safety, reducing runtime errors by generating a type-safe database client. Prisma supports multiple relational databases like **PostgreSQL**, **MySQL**, and **SQLite**, making it a versatile tool for modern web development.

<p align="left">
  <img src="https://tsed.io/prisma-2.svg" width="400" alt="Prisma ORM">
</p>

---

## Database setup

The template uses **Prisma 7** with the `@prisma/adapter-better-sqlite3` adapter for SQLite.

### Dependencies

The following packages are included in the template:

```json
{
  "dependencies": {
    "@prisma/adapter-better-sqlite3": "^7.3.0",
    "@prisma/client": "^7.3.0"
  },
  "devDependencies": {
    "prisma": "^7.3.0"
  }
}
```

### Database file

The SQLite database is stored as a file. The location is configured via the `DATABASE_URL` environment variable:

```bash
DATABASE_URL="file:./prisma/dev.db"
```

---

## Database schema

The Prisma schema file (`prisma/schema.prisma`) defines the database structure:

```prisma
generator client {
  provider   = "prisma-client-js"
  engineType = "client"
}

datasource db {
  provider = "sqlite"
}

model QuableInstance {
  id        Int       @id @default(autoincrement())
  name      String    @unique
  token     String
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  sessions  Session[]

  @@map("quable_instance")
}

model Session {
  id               String         @id @default(uuid())
  userId           Int
  dataLocale       String
  interfaceLocale  String         @default("en")
  documentIds      Json?
  quableInstanceId Int
  createdAt        DateTime       @default(now())
  updatedAt        DateTime       @updatedAt
  quableInstance   QuableInstance @relation(fields: [quableInstanceId], references: [id], onDelete: Cascade)

  @@index([quableInstanceId], map: "session_quableInstanceId_fkey")
  @@map("session")
}
```

### QuableInstance model

Stores the credentials for each Quable PIM instance that has installed the app:

| Field | Type | Description |
|-------|------|-------------|
| `id` | Int | Auto-incremented primary key |
| `name` | String | Unique instance name (e.g., `my-company`) |
| `token` | String | API authentication token |
| `createdAt` | DateTime | Creation timestamp |
| `updatedAt` | DateTime | Last update timestamp |
| `sessions` | Session[] | Related user sessions |

### Session model

Stores active user sessions. Each time a user opens the app from the PIM, a new session is created:

| Field | Type | Description |
|-------|------|-------------|
| `id` | String | UUID primary key (auto-generated) |
| `userId` | Int | PIM user ID |
| `dataLocale` | String | User's data locale (e.g., `fr_FR`) |
| `interfaceLocale` | String | User's interface locale (default: `en`) |
| `documentIds` | Json? | Array of document IDs (for slot actions) |
| `quableInstanceId` | Int | Foreign key to QuableInstance |
| `createdAt` | DateTime | Creation timestamp |
| `updatedAt` | DateTime | Last update timestamp |

!!! Note
Sessions are automatically deleted when their parent `QuableInstance` is removed, thanks to `onDelete: Cascade`.
!!!

---

## Prisma client initialization

The Prisma client is initialized as a singleton in `src/lib/prisma.ts`:

```ts
import { PrismaBetterSqlite3 } from "@prisma/adapter-better-sqlite3";
import { PrismaClient } from "@prisma/client";

const adapter = new PrismaBetterSqlite3({ url: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });
export default prisma;
```

Key differences from previous versions:
- Uses the **Better SQLite3 adapter** instead of the default Prisma engine.
- The adapter is passed to the `PrismaClient` constructor.
- The database URL is read from `process.env.DATABASE_URL`.

You can then import and use the client anywhere in your server-side code:

```ts
import prisma from "@/lib/prisma";

const instances = await prisma.quableInstance.findMany();
```

---

## Prisma configuration

The `prisma.config.ts` file at the project root configures Prisma's behavior:

```ts
import "dotenv/config";
import { defineConfig } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  datasource: {
    url: process.env["DATABASE_URL"],
  },
});
```

### Common Prisma commands

```bash
# Generate the Prisma client after schema changes
npx prisma generate

# Create a new migration
npx prisma migrate dev --name my_migration_name

# Apply migrations in production
npx prisma migrate deploy

# Open Prisma Studio (visual database editor)
npx prisma studio
```

---

## Session management

The template includes session utilities in `src/lib/session.ts`:

```ts
"use server";

import { Session } from "@prisma/client";
import prisma from "./prisma";
import { cookies } from "next/headers";

export async function getSession(
  sessionId: string
): Promise<null | (Session & { quableInstance: { name: string } })> {
  if (!sessionId) return null;

  const session = await prisma.session.findUnique({
    where: { id: sessionId },
    include: { quableInstance: { select: { name: true } } },
  });
  if (!session) return null;

  // Check if session has expired (24h TTL)
  const now = new Date();
  const createdAt = new Date(session.createdAt);
  const expiresAt = new Date(createdAt.getTime() + 24 * 60 * 60 * 1000);

  if (expiresAt < now) {
    await prisma.session.delete({ where: { id: sessionId } });
    return null;
  }

  return session;
}

export async function getCurrentSession() {
  const cookieStore = await cookies();
  const sessionId = cookieStore.get("sessionId")?.value || "";
  return await getSession(sessionId);
}
```

### getSession(sessionId)

Fetches a session by its UUID. If the session has expired (older than 24 hours), it is automatically deleted and `null` is returned. The session includes the related `quableInstance.name`.

### getCurrentSession()

A convenience function that reads the `sessionId` from the HTTP cookies (set by the middleware) and calls `getSession()`. This is the primary way to get the current user's session in Server Actions and Server Components.

---

## Tutorial: CRUD with Server Actions

Let's walk through how database operations work in the template using the **Key-Value** example.

### 1. Create the Server Action

Server Actions are functions marked with `"use server"` that can be called from client components. Create a file `src/lib/actions/key-value.ts`:

```ts
"use server";

import { getCurrentSession } from "../session";
import prisma from "../prisma";
import { KeyValueModel, QuablePimClient } from "@quable/quable-pim-js";
import { getTranslations } from "next-intl/server";

export async function addKeyValue(
  key: string,
  value: string
): Promise<{ keyValue?: KeyValueModel | null; error?: string | null }> {
  const t = await getTranslations("common");
  try {
    const session = await getCurrentSession();
    if (!session) {
      throw new Error("Session not found");
    }

    const quableInstance = await prisma.quableInstance.findUniqueOrThrow({
      where: { id: session.quableInstanceId },
    });

    const quablePimClient = new QuablePimClient({
      apiToken: quableInstance.token,
      instanceName: quableInstance.name,
    });

    const keyValue = await quablePimClient.API.REST.KeyValue.create({
      isProtected: false,
      isPublic: true,
      id: key,
      value: value,
    });

    return { keyValue, error: null };
  } catch (error) {
    if (typeof error === "object") {
      const err = error as { response?: { code?: number; message?: string } };
      if (err.response?.code === 400) {
        return { keyValue: null, error: err.response.message };
      }
    }
    return { keyValue: null, error: t("toasts.add_key_value_error") };
  }
}
```

This action:
1. Gets the current session from cookies.
2. Retrieves the Quable instance credentials from the database.
3. Creates a `QuablePimClient` to interact with the PIM API.
4. Creates a key-value pair and returns the result.

### 2. Call the action from a Client Component

Create a form page at `src/app/[session]/(slots)/page/page.tsx`:

```tsx
"use client";

import { addKeyValue } from "@/lib/actions/key-value";
import { Box, Card, CardActions, CardContent, CardHeader, Grid, Typography } from "@mui/material";
import { TextField, Button } from "@quable/ui";
import { useMutation } from "@tanstack/react-query";
import { useTranslations } from "next-intl";
import { useForm, SubmitHandler } from "react-hook-form";
import { toast } from "react-toastify";

export default function HomePage() {
  const t = useTranslations();
  const { register, formState, handleSubmit, reset } = useForm<{
    key: string;
    value: string;
  }>({
    defaultValues: { key: "", value: "" },
    mode: "onChange",
  });

  const { isPending, mutateAsync } = useMutation({
    mutationFn: (data: { key: string; value: string }) =>
      addKeyValue(data.key, data.value),
    onSuccess: (data) => {
      if (data.keyValue) {
        toast.success(t("toasts.add_key_value_success"));
        reset();
      } else {
        toast.error(data.error);
      }
    },
    onError: () => {
      toast.error(t("toasts.add_key_value_error"));
    },
  });

  const onSubmit: SubmitHandler<{ key: string; value: string }> = (data) => {
    mutateAsync(data);
  };

  return (
    <div className="page-layout">
      <Typography variant="h4">{t("common.app_name")}</Typography>
      <Box sx={{ display: "flex", justifyContent: "center", alignItems: "center", height: "calc(100vh - 80px)" }}>
        <form noValidate onSubmit={handleSubmit(onSubmit)}>
          <Card elevation={2}>
            <CardHeader title={t("configuration.add_key_value")} />
            <CardContent>
              <Grid container spacing={2}>
                <Grid item xs={12}>
                  <TextField
                    disabled={isPending}
                    label={t("configuration.key")}
                    fullWidth
                    margin="normal"
                    error={!!formState.errors.key}
                    helperText={formState.errors.key?.message}
                    {...register("key", { required: { value: true, message: t("common.key_required") } })}
                  />
                </Grid>
                <Grid item xs={12}>
                  <TextField
                    disabled={isPending}
                    label={t("configuration.value")}
                    fullWidth
                    margin="normal"
                    error={!!formState.errors.value}
                    helperText={formState.errors.value?.message}
                    {...register("value", { required: { value: true, message: t("common.value_required") } })}
                  />
                </Grid>
              </Grid>
            </CardContent>
            <CardActions sx={{ justifyContent: "flex-end", padding: 2 }}>
              <Button isLoading={isPending} disabled={isPending} variant="contained" type="submit" color="primary">
                {t("common.save")}
              </Button>
            </CardActions>
          </Card>
        </form>
      </Box>
    </div>
  );
}
```

### Key patterns

- **`useMutation`** from React Query wraps the Server Action call, providing `isPending` state and `onSuccess`/`onError` callbacks.
- **`react-hook-form`** manages form state, validation, and submission.
- **`react-toastify`** shows success/error notifications via `toast.success()` and `toast.error()`.
- **`useTranslations`** from next-intl provides localized strings.

### 3. Extend the schema

To add your own models, edit `prisma/schema.prisma`:

```prisma
model Translation {
  id        Int      @id @default(autoincrement())
  sourceText String
  targetText String
  sourceLang String
  targetLang String
  createdAt  DateTime @default(now())

  @@map("translation")
}
```

Then run:

```bash
npx prisma migrate dev --name add_translation_table
```

This generates a migration file and updates the Prisma client. You can then use the new model in your Server Actions:

```ts
"use server";
import prisma from "../prisma";

export async function createTranslation(data: {
  sourceText: string;
  targetText: string;
  sourceLang: string;
  targetLang: string;
}) {
  return await prisma.translation.create({ data });
}

export async function getTranslations() {
  return await prisma.translation.findMany({
    orderBy: { createdAt: "desc" },
  });
}
```

!!!success
You now know how to work with Prisma in the Quable App template. Next, we'll explore how to interact with the Quable PIM API using the `@quable/quable-pim-js` SDK.
!!!