Prisma

Prisma provides an abstract API to work with your code.

Constrast with Hibernate, prisma doesn't track the activity of an object, it simply provide the abstract api for us to manually save and fetch the object itself.

Initialise

pnpm prisma init --datasource-provider postgresql --output ../src/generated/prisma

Prisma config

A file call prisma.config.ts looks roughly like this

// This file was generated by Prisma, and assumes you have installed the following:
// npm install --save-dev prisma dotenv
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"],
  },
});

In which our DATABASE_URL would be the DB URL in .env

Sync current database to prisma

To sync the schema

pnpm prisma db pull

To generate the models and stuff

pnpm prisma generate

Create new database from scratch

In prisma/schema.prisma, do

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

/// This table contains check constraints and requires additional setup for migrations. Visit https://pris.ly/d/check-constraints for more info.
model deposits {
  id           String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  player_id    String
  amount_cents Int
  created_at   DateTime @default(now()) @db.Timestamptz(6)

  @@index([player_id], map: "deposits_player_idx")
}

model player_bonus {
  player_id   String @id
  bonus_cents Int    @default(0)
}

model processed_events {
  event_id     String   @id
  processed_at DateTime @default(now()) @db.Timestamptz(6)
}

This is to initialise the shape of our database, we can then run

pnpm prisma migrate dev --create-only --name init

This will generate the file in prisma/migrations

We can inspect and modify the SQL as we needed, in our case, we need the CHECK however prisma does not support it, we can modify the migration file to have this at the bottom

ALTER TABLE "deposits"
  ADD CONSTRAINT deposits_amount_positive CHECK (amount_cents > 0);

A full auto-generated prisma migration file might look like this

-- CreateTable
CREATE TABLE "deposits" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "player_id" TEXT NOT NULL,
    "amount_cents" INTEGER NOT NULL,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "deposits_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "player_bonus" (
    "player_id" TEXT NOT NULL,
    "bonus_cents" INTEGER NOT NULL DEFAULT 0,

    CONSTRAINT "player_bonus_pkey" PRIMARY KEY ("player_id")
);

-- CreateTable
CREATE TABLE "processed_events" (
    "event_id" TEXT NOT NULL,
    "processed_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "processed_events_pkey" PRIMARY KEY ("event_id")
);

-- CreateIndex
CREATE INDEX "deposits_player_idx" ON "deposits"("player_id");


ALTER TABLE "deposits"
  ADD CONSTRAINT deposits_amount_positive CHECK (amount_cents > 0);

Once we done, we do this

pnpm prisma migrate dev # apply the migration to database, dev here is short for development workflow not environment
pnpm prisma generate # generate the classes in our file

Once done, we can change the schema like so , add this under the deposits

currency     String   @default("AUD")

And then we just do

pnpm prisma migrate dev --name add_currency

pnpm prisma generate

Deploy to prod

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/lab_prod" pnpm prisma migrate deploy

Example

After generated, the client is ready, we can create our own prisma client

import "dotenv/config";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "./generated/prisma/client.js";

const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
  throw new Error("DATABASE_URL is not set");
}

const adapter = new PrismaPg({ connectionString });
export const prisma = new PrismaClient({ adapter });

Use the prisma client

import { prisma } from "./prisma.js";

export async function recordDeposit(playerId: string, amountCents: number) {
  const bonusCents = Math.floor(amountCents * 0.1);

  // If the callback throws, Prisma rolls everything back.
  // Inside it, use tx and never prisma, or the work leaves the transaction.
  await prisma.$transaction(async (tx) => {
    await tx.deposits.create({
      data: { player_id: playerId, amount_cents: amountCents },
    });

    await tx.player_bonus.upsert({
      where: { player_id: playerId },
      create: { player_id: playerId, bonus_cents: bonusCents },
      update: { bonus_cents: { increment: bonusCents } },
    });
  });
}

In here ts is the db client within that transaction

We can then call this function like so

import { recordDeposit } from "./db.js";

// inside the POST /deposits handler:
await recordDeposit(playerId, amountCents);