database-prisma-sqlite

Database — SQLite + Prisma (Node.js / TypeScript)

Node/TypeScript web apps only. Python projects use SQLAlchemy + SQLite. Record schema design decisions in docs/ai-dev/. Verify stack matches PROJECT_RULES.md.

Initial Setup

npm install prisma @prisma/client
npx prisma init --datasource-provider sqlite

Prisma Client (src/lib/db.ts)

import { PrismaClient } from '@prisma/client'
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient }
export const db = globalForPrisma.prisma || new PrismaClient()
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = db

Singleton pattern prevents duplicate clients during hot reload.

Schema Principles

• All models: id (cuid) + createdAt required
• Editable models: add updatedAt
• Relationships: explicit @relation
• After changes: npx prisma db push
• Add dev.db to .gitignore
• Passwords: always bcrypt hash

CRUD Patterns

import { db } from '@/lib/db'

// Create
const user = await db.user.create({
  data: { email, name, password: hashedPassword }
})

// Read
const posts = await db.post.findMany({
  include: { author: true },
  orderBy: { createdAt: 'desc' }
})

// Update
await db.post.update({ where: { id }, data: { title } })

// Delete
await db.post.delete({ where: { id } })

// Pagination
const posts = await db.post.findMany({ skip: 0, take: 10 })

Authentication Pattern

import bcrypt from 'bcryptjs'

// Signup
const hashed = await bcrypt.hash(password, 10)
await db.user.create({ data: { email, password: hashed } })

// Login
const user = await db.user.findUnique({ where: { email } })
if (!user) throw new Error('User not found')
const valid = await bcrypt.compare(password, user.password)
if (!valid) throw new Error('Invalid password')

Session: iron-session or jose (JWT).