Prisma ORM

Prisma ORM is a TypeScript ORM with a declarative schema, a generated type-safe client, and the migration tool Prisma Migrate. CedarDB works with Prisma’s postgresql provider and the @prisma/adapter-pg driver adapter, so you can define your schema, manage it with migrations, and query it like a PostgreSQL database. Apply schema changes with prisma db push, or create versioned migrations with prisma migrate dev and apply them with prisma migrate deploy.

This page was tested with prisma, @prisma/client, and @prisma/adapter-pg 7.10.0 and pg 8.23.1.

This page is a self-contained example that you can follow from start to finish. It points out what is specific to CedarDB. For all features, see Prisma’s documentation of the Prisma schema, Prisma Migrate, and Prisma Client.

Prerequisites

• Node.js 20 or later.
• A running CedarDB server that accepts TCP connections (see Install CedarDB locally or CedarDB Cloud).
• A CedarDB superuser to create the application role and database.

Set up CedarDB

Connect to CedarDB as a superuser and create a role for your application that owns its database:

CREATE ROLE myuser WITH LOGIN CREATEDB PASSWORD 'CorrectHorseBatteryStaple';
CREATE DATABASE mydb OWNER myuser;

As the database owner, Prisma can create tables, types, and indexes without further privileges. CREATEDB allows prisma migrate dev to create and drop a temporary shadow database. If you only use prisma db push or prisma migrate deploy, you can omit it. This works in every CedarDB edition. Granting privileges with GRANT instead requires a CedarDB enterprise license.

Set up the project

mkdir hello-prisma && cd hello-prisma
npm init -y
npm pkg set type=module
npm install --save-dev prisma@7 typescript tsx @types/node @types/pg
npm install @prisma/client@7 @prisma/adapter-pg@7 pg dotenv

{
  "compilerOptions": {
    "module": "ESNext",
    "moduleResolution": "bundler",
    "target": "ES2023",
    "strict": true,
    "esModuleInterop": true,
    "ignoreDeprecations": "6.0"
  }
}

npx prisma init --datasource-provider postgresql --output ../generated/prisma

DATABASE_URL="postgresql://myuser:CorrectHorseBatteryStaple@localhost:5432/mydb?schema=public"

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

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

datasource db {
  provider = "postgresql"
}

enum Role {
  ADMIN
  MEMBER
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  role  Role    @default(MEMBER)
  posts Post[]
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  price     Decimal @default(0) @db.Decimal(10, 2)
  author    User    @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  Int

  @@index([authorId])
}

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

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });

export { prisma };

Create and change the schema

Choose one of two workflows:

• Push the schema with prisma db push to apply it directly, without migration files. Use it for prototyping.
• Use versioned migrations with prisma migrate dev to keep every change as a SQL file that you review and commit. prisma migrate deploy applies them to other environments, such as production.

Push the schema

Push the schema to CedarDB and generate Prisma Client:

npx prisma db push
npx prisma generate

After you change prisma/schema.prisma, run both commands again. Prisma compares the schema with the tables in CedarDB and applies only the difference, for example with ALTER TABLE ... ADD COLUMN or DROP COLUMN. If a change can lose data, Prisma asks for confirmation.

Use versioned migrations

npx prisma migrate dev --name init
npx prisma generate

model User {
  // ...
  createdAt DateTime @default(now()) @db.Timestamp(6)
}

npx prisma migrate dev --name add_created_at
npx prisma generate

npx prisma migrate deploy

Reset a development database

To start over, drop and recreate the database, then push the schema or apply the migrations again:

DROP DATABASE mydb;
CREATE DATABASE mydb OWNER myuser;

Query data

Create a script.ts that runs queries against CedarDB:

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

async function main() {
  // Start from empty tables, deletes cascade to the posts
  await prisma.user.deleteMany();

  // Create a user with related posts
  const alice = await prisma.user.create({
    data: {
      name: "Alice",
      email: "alice@prisma.io",
      posts: {
        create: [
          { title: "Hello World", content: "This is my first post!", published: true, price: "9.99" },
          { title: "Draft" },
        ],
      },
    },
  });

  // Filter on a relation and count related records
  const authors = await prisma.user.findMany({
    where: { posts: { some: { published: true } } },
    select: { email: true, _count: { select: { posts: true } } },
  });

  // Aggregate and group
  const stats = await prisma.post.aggregate({ _sum: { price: true }, _count: true });
  const byRole = await prisma.user.groupBy({ by: ["role"], _count: { _all: true } });

  // Interactive transaction
  await prisma.$transaction(async (tx) => {
    const bob = await tx.user.create({ data: { email: "bob@prisma.io", name: "Bob" } });
    await tx.post.create({ data: { title: "Bob's post", authorId: bob.id } });
  });

  // Upsert
  await prisma.user.upsert({
    where: { email: "bob@prisma.io" },
    update: { role: "ADMIN" },
    create: { email: "bob@prisma.io", role: "ADMIN" },
  });

  // Fetch all users with their posts
  const allUsers = await prisma.user.findMany({ include: { posts: true }, orderBy: { id: "asc" } });

  // Delete, cascades to the posts
  await prisma.user.delete({ where: { id: alice.id } });

  console.log(JSON.stringify({ authors, stats, byRole, allUsers }, null, 2));
}

main()
  .then(async () => {
    await prisma.$disconnect();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });

Run the script:

npx tsx script.ts

CedarDB limitations

• Decimal fields without a native type are created as decimal(65,30), which exceeds the maximum precision of 38 (see Numeric). Add a native type with a precision of at most 38, for example @db.Decimal(10, 2).
• The isolation level Serializable is not supported. CedarDB runs all transactions with snapshot isolation and accepts lower isolation levels for compatibility without changing behaviour (see Transactions).