# Working with Database

By
The Quable Team

What is Prisma?
Database setup
Database schema
Prisma client initialization
Prisma configuration
Session management
Tutorial: CRUD with Server Actions

# What is Prisma?

Prisma 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.

Prisma ORM

# 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:

{
  "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:

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

# Prisma client initialization

The Prisma client is initialized as a singleton in src/lib/prisma.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:

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:

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 

# 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:

"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:

"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:

"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:

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:

"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" },
  });
}