Prisma

Presentation : Prisma is an object relational mapping tool for TypeScript.

Key advantages of an ORM are :

• app maintainability : decoupling between code and database
• ease of use : auto-generation from a schema of models with methods corresponding to basic SQL queries. Yet raw SQL queries are still usable.
• type safety
• maintaining migrations : versioning of database state

---

Installation and configuration

npm install prisma --save-dev
npm install @prisma/client @prisma/adapter-better-sqlite3 better-sqlite3

• check TS config and package.json (module compatible)

Configuration

• schema.prisma : specifies path of generated client files, model and constraints for table
• prisma.config.ts : specifies paths of schema, migrations, database

Use cases

Instanciation

import 'dotenv/config'
import { PrismaBetterSqlite3 } from '@prisma/adapter-better-sqlite3'
import { appenv } from '../config/env.js'
import { PrismaClient } from './generated/prisma/client.js' // Note the import from the generated folder

const connectionString = appenv.UM_DB_URL

const adapter = new PrismaBetterSqlite3({ url: connectionString })
export const prisma = new PrismaClient({ adapter })

Update table and client from model

• modify model in schema.prisma
• run a migration with npx prisma migrate dev --name <myname> (it will also execute npx prisma generate begind the curtain to update client)

Tip

database can be visualized with npx prisma studio

Model attributes can have various contraints

• @id : primary key
• @default(autoincrement())
• @unique
• ? : nullable field

CRUD functions in data layer

export async function findProfileByUsername(
  username: string
): Promise<UserProfile | null> {
  // .findUnique is optimized for @unique fields
  return await prisma.userProfile.findUnique({
    where: { username },
  })
}

Do's & Don'ts

✅ Do	❌ Don't
Use a single instance	Instantiate new PrismaClient() in every file (causes connection issues).
Use findUnique when searching by ID or Email.	Use findFirst
Update schema.prisma then run a migration.	Modify SQLite file manually
Type function return values with generated types (e.g., Promise<UserProfile>).	Use any for DB objects.

Note

prisma functions are async, so we need to call them with await

Tip

models can be used to type functions return value, but also in tests

Important

current way of sending db is npx prisma db push in production stage of Dockerfile but we should use npx prisma migrate deploy in prod (no risk of data loss)

Resources

Type	Resource	Notes
📄	Official Docs	Main reference
🎥	Prisma in 60 mn	Not watched
💻	Prisma examples	Production examples - not checked

Legend: 📄 Doc, 📘 Book, 🎥 Video, 💻 GitHub, 📦 Package, 💡 Blog