# Backend with TypeScript, PostgreSQL & Prisma: Data Modeling & CRUD

URL: https://www.prisma.io/blog/backend-prisma-typescript-orm-with-postgresql-data-modeling-tsjs1ps7kip1
Published: 2020-07-30
Updated: 2026-07-03
Authors: Daniel Norman
Series: Building a modern backend with TypeScript, PostgreSQL and Prisma
Description: Learn to design a PostgreSQL data model and run type-safe CRUD and aggregate queries with Prisma ORM 7 and TypeScript. Updated for the rust-free Prisma Client.

> **Update (July 2026):** This article was updated for Prisma ORM 7. It uses the rust-free `prisma-client` generator, the `prisma.config.ts` config file, and driver adapters. All commands and code work verbatim on Prisma ORM 7 with Node.js 20.19+ on 20.x, 22.12+ on 22.x, or 24+.

This article is part of a series on building a backend with TypeScript, PostgreSQL, and Prisma. In this first part, you will design a data model, apply it to your database with Prisma Migrate, and perform CRUD and aggregate queries with Prisma Client.

## Introduction [#introduction]

The goal of the series is to explore and demonstrate different patterns, problems, and architectures for a modern backend by solving a concrete problem: **a grading system for online courses.** This is a good example because it features diverse relation types and is complex enough to represent a real-world use case.

### What the series covers [#what-the-series-covers]

The series focuses on the role of the database in every aspect of backend development, covering:

* Data modeling
* CRUD
* Aggregations
* API layer
* Validation
* Testing
* Authentication
* Authorization
* Integration with external APIs
* Deployment

### What you will learn today [#what-you-will-learn-today]

This first article lays out the problem domain and develops the following aspects of the backend:

1. **Data modeling:** Mapping the problem domain to a database schema
2. **CRUD:** Implementing Create, Read, Update, and Delete queries with [Prisma Client](https://www.prisma.io/docs/orm/v7/prisma-client)
3. **Aggregation:** Implementing aggregate queries with Prisma Client to calculate averages and more

By the end of this article you will have a Prisma schema, a corresponding database schema created by Prisma Migrate, and a seed script that uses Prisma Client to perform CRUD and aggregation queries.

> **Note:** Throughout the guide you'll find various **checkpoints** that enable you to validate whether you performed the steps correctly.

## Prerequisites [#prerequisites]

### Assumed knowledge [#assumed-knowledge]

This series assumes basic knowledge of TypeScript, Node.js, and relational databases. If you're experienced with JavaScript but haven't had the chance to try TypeScript, you should still be able to follow along. The series uses PostgreSQL, however, most of the concepts apply to other relational databases such as MySQL. Beyond that, no prior knowledge of Prisma is required.

### Development environment [#development-environment]

You should have the following installed:

* [Node.js](https://nodejs.org/en/) 20.19+ on 20.x, 22.12+ on 22.x, or 24+

If you're using Visual Studio Code, the [Prisma extension](https://marketplace.visualstudio.com/items?itemName=Prisma.prisma) is recommended for syntax highlighting, formatting, and autocomplete in your schema.

You will create a managed [Prisma Postgres](https://www.prisma.io/postgres) database from the terminal during setup, so you don't need a local database server. If you prefer to run PostgreSQL locally, a Docker alternative is included in the database step.

## Create the project [#create-the-project]

Create a new directory and initialize a TypeScript project:

```sh
mkdir grading-app
cd grading-app
npm init -y
npm install typescript tsx @types/node --save-dev
```

Install Prisma ORM and the packages it needs to talk to PostgreSQL:

```sh
npm install prisma@7.9.1 @types/pg --save-dev
npm install @prisma/client@7.9.1 @prisma/adapter-pg pg dotenv
```

Here is what each package does:

* `prisma`: the Prisma CLI for running commands like `prisma init`, `prisma migrate`, and `prisma generate`
* `@prisma/client`: the Prisma Client library for querying your database
* `@prisma/adapter-pg`: the driver adapter that connects Prisma Client to PostgreSQL through `node-postgres`
* `pg`: the node-postgres database driver
* `@types/pg`: TypeScript type definitions for node-postgres
* `dotenv`: loads environment variables from your `.env` file

Prisma ORM 7 is ESM-first. Update your `tsconfig.json` for ESM compatibility:

```json
{
  "compilerOptions": {
    "module": "ESNext",
    "moduleResolution": "bundler",
    "target": "ES2023",
    "strict": true,
    "esModuleInterop": true
  }
}
```

And enable ESM in your `package.json`:

```json
{
  "type": "module"
}
```

## Create your database [#create-your-database]

This tutorial uses [Prisma Postgres](https://www.prisma.io/postgres), a managed PostgreSQL database that you can provision straight from the terminal. Create one with:

```sh
npx create-db
```

The command provisions a new database and prints a `postgres://...` connection string. Keep that string handy. You will add it to your `.env` file when you initialize Prisma ORM, and you can claim the database in the [Prisma Console](https://console.prisma.io) to keep it.

Prisma Postgres speaks the standard PostgreSQL protocol, so everything in this tutorial works identically against any other PostgreSQL database.

### Alternative: run PostgreSQL locally with Docker [#alternative-run-postgresql-locally-with-docker]

If you would rather run the database locally, create a `docker-compose.yml` file in the project root:

```yaml
services:
  postgres:
    image: postgres:17
    restart: always
    environment:
      POSTGRES_USER: prisma
      POSTGRES_PASSWORD: prisma
      POSTGRES_DB: grading-app
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:
```

Start the database:

```sh
docker compose up -d
```

## Initialize Prisma ORM [#initialize-prisma-orm]

Set up your Prisma ORM project with the following command:

```sh
npx prisma init --output ../generated/prisma
```

This command does three things:

* Creates a `prisma/` directory with a `schema.prisma` file for your data model
* Creates a `.env` file for environment variables
* Creates a `prisma.config.ts` file for project configuration

The `prisma.config.ts` file is the single place where you configure how Prisma interacts with your project: schema location, migrations, seed scripts, and the database connection. Because it is a TypeScript file, you can load values dynamically, for example with `dotenv`. The generated file looks like this:

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

The generated `prisma/schema.prisma` uses the `prisma-client` generator. This generator produces a rust-free, ESM-compatible client and writes the generated code into your project source instead of `node_modules`, so your build tools and file watchers treat it like any other part of your app:

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

datasource db {
  provider = "postgresql"
}
```

Finally, set the connection string in `.env`. Use the `postgres://...` URL that `npx create-db` printed when you created your database:

```sh
DATABASE_URL="postgres://<your-connection-string-from-create-db>"
```

If you chose the Docker alternative, use the local connection string instead:

```sh
DATABASE_URL="postgresql://prisma:prisma@localhost:5432/grading-app"
```

> **Note:** It's considered best practice to keep secrets out of your codebase. The connection URL is loaded from the environment by `prisma.config.ts`, so the schema file contains no credentials.

## Data model for a grading system for online courses [#data-model-for-a-grading-system-for-online-courses]

### Defining the problem domain and entities [#defining-the-problem-domain-and-entities]

When building a backend, one of the foremost concerns is a proper understanding of the *problem domain*. The problem domain (or problem space) refers to all the information that defines the problem and constrains the solution. By understanding the problem domain, the shape and structure of the data model becomes clear.

The online grading system has the following entities:

* **User:** A person with an account. A user can be either a teacher or a student through their relation to a course. In other words, the same user who's a teacher of one course can be a student in another course.
* **Course:** A learning course with one or more teachers and students, as well as one or more tests. For example: an "Introduction to TypeScript" course can have two teachers and ten students.
* **Test:** A course can have many tests to evaluate the students' comprehension. Tests have a date and are related to a course.
* **Test result:** Each test can have multiple test result records per student. Additionally, a `TestResult` is also related to the teacher who graded the test.

> **Note:** An entity represents either a physical object or an intangible concept. For example, a **user** represents a person, whereas a **course** is an intangible concept.

The entities can be visualized to demonstrate how they would be represented in a relational database (in this case PostgreSQL). The [diagram](https://dbdiagram.io/d/5f19635fe586385b4ff7a26d) below adds the columns relevant for each entity and foreign keys to describe the relationships between the entities.

![](https://www.prisma.io/blog/backend-prisma-typescript-orm-with-postgresql-data-modeling-tsjs1ps7kip1/imgs/grading-app-schema.png)

The first thing to note about the diagram is that every entity maps to a database table.

The diagram has the following relations:

* **one-to-many (also known as `1-n`)**:
  * `Test` ↔ `TestResult`
  * `Course` ↔ `Test`
  * `User` ↔ `TestResult` (via `graderId`)
  * `User` ↔ `TestResult` (via `studentId`)
* **many-to-many (also known as `m-n`):**
  * `User` ↔ `Course` via the `CourseEnrollment` [relation table](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/relations/many-to-many-relations) with two *foreign keys*: `userId` and `courseId`. Many-to-many relations typically require an additional table. This is necessary so that the grading system can have the following properties:
    * A single course can have many associated users (as students or teachers)
    * A single user can be associated with many courses.

> **Note:** A relation table (also known as a JOIN table) connects two or more other tables to create a relation between them. Creating relation tables is a common data modeling practice in SQL to represent relationships between different entities. In essence, it means that "one m-n relation is modeled as two 1-n relations in the database".

### Understanding the Prisma schema [#understanding-the-prisma-schema]

To create the tables in your database, you first define your [Prisma schema](https://www.prisma.io/docs/orm/contract-authoring/psl-syntax). The Prisma schema is a declarative definition of your database tables. It serves as the source of truth for both the generated Prisma Client and for [Prisma Migrate](https://www.prisma.io/docs/orm/v7/prisma-migrate), which creates the database schema.

### Define models [#define-models]

The fundamental building block of the Prisma schema is the [`model`](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/models). Every model maps to a database table.

Here is an example showing the basic signature of a model:

```prisma
model User {
  id        Int     @id @default(autoincrement())
  email     String  @unique
  firstName String
  lastName  String
  social    Json?
}
```

Each field has a name followed by a type and optional field attributes. The `User` model breaks down as follows:

| Name        | Type     | Scalar vs Relation | Type modifier  | Attributes                                       |
| :---------- | :------- | :----------------- | :------------- | :----------------------------------------------- |
| `id`        | `Int`    | Scalar             | -              | `@id` (primary key), `@default(autoincrement())` |
| `email`     | `String` | Scalar             | -              | `@unique`                                        |
| `firstName` | `String` | Scalar             | -              | -                                                |
| `lastName`  | `String` | Scalar             | -              | -                                                |
| `social`    | `Json`   | Scalar             | `?` (optional) | -                                                |

Prisma defines a [set of data types](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/models#scalar-fields) that map to native database types depending on the database used.

The `Json` type stores free-form JSON. This is useful for information that can vary across `User` records and change without affecting the core functionality of the backend. In the `User` model it stores social links, for example a Bluesky or LinkedIn handle. Adding a new social profile link requires no database migration.

With a good understanding of the problem domain, add the following models to your `prisma/schema.prisma` file:

```prisma
model User {
  id        Int     @id @default(autoincrement())
  email     String  @unique
  firstName String
  lastName  String
  social    Json?
}

model Course {
  id            Int     @id @default(autoincrement())
  name          String
  courseDetails String?
}

model Test {
  id        Int      @id @default(autoincrement())
  updatedAt DateTime @updatedAt
  name      String // Name of the test
  date      DateTime // Date of the test
}

model TestResult {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  result    Int // Percentage precise to one decimal point represented as `result * 10^-1`
}
```

Each model has all the relevant fields while ignoring relations, which come next.

### Define relations [#define-relations]

#### One-to-many [#one-to-many]

To define a [one-to-many](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/relations/one-to-many-relations) relation between `Test` and `TestResult`, add the following three fields:

* A `testId` field of type `Int` (*relation scalar*) on the "many" side of the relation, `TestResult`. This field represents the *foreign key* in the underlying database table.
* A `test` field of type `Test` (*relation field*) with a `@relation` attribute mapping the relation scalar `testId` to the `id` primary key of the `Test` model.
* A `testResults` field of type `TestResult[]` (*relation field*) on `Test`.

```prisma
model Test {
  id        Int      @id @default(autoincrement())
  updatedAt DateTime @updatedAt
  name      String
  date      DateTime

  testResults TestResult[] // relation field
}

model TestResult {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  result    Int

  testId Int // relation scalar field
  test   Test @relation(fields: [testId], references: [id]) // relation field
}
```

Relation fields like `test` and `testResults` can be identified by their value type pointing to another model. Their names affect how relations are accessed programmatically with Prisma Client, but they don't represent real database columns.

#### Many-to-many [#many-to-many]

Many-to-many relations can be [*implicit* or *explicit*](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/relations/many-to-many-relations) in the Prisma schema.

To create an implicit many-to-many relation between `User` and `Course`, you would define relation fields as lists on both sides:

```prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  firstName String
  lastName  String
  social    Json?
  courses   Course[]
}

model Course {
  id            Int     @id @default(autoincrement())
  name          String
  courseDetails String?
  members       User[]
}
```

With this, Prisma manages the relation table for you. However, one of the requirements of the grading system is to relate users to a course with a **role**, as either a *teacher* or a *student*. That means storing meta-information about the relation in the database.

This is what **explicit** many-to-many relations are for. With an explicit relation, you define the relation table as its own model and can add extra fields to it. Define a `CourseEnrollment` model and point the `courses` field on `User` and the `members` field on `Course` at it:

```prisma
model User {
  id        Int                @id @default(autoincrement())
  email     String             @unique
  firstName String
  lastName  String
  social    Json?
  courses   CourseEnrollment[]
}

model Course {
  id            Int                @id @default(autoincrement())
  name          String
  courseDetails String?
  members       CourseEnrollment[]
}

model CourseEnrollment {
  createdAt DateTime @default(now())
  role      UserRole

  // Relation fields
  userId   Int
  user     User   @relation(fields: [userId], references: [id])
  courseId Int
  course   Course @relation(fields: [courseId], references: [id])

  @@id([userId, courseId])
  @@index([userId, role])
}

enum UserRole {
  STUDENT
  TEACHER
}
```

Things to note about the `CourseEnrollment` model:

* It uses the `UserRole` enum to denote whether a user is a student or a teacher of a course.
* `@@id([userId, courseId])` defines a multi-field primary key of the two fields. This ensures that every `User` can only be associated with a `Course` once, either as a student or as a teacher but never both.

To learn more about relations, check out the [relations documentation](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/relations).

### The full schema [#the-full-schema]

Now that you've seen how relations are defined, update `prisma/schema.prisma` with the complete data model:

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

datasource db {
  provider = "postgresql"
}

model User {
  id        Int     @id @default(autoincrement())
  email     String  @unique
  firstName String
  lastName  String
  social    Json?

  // Relation fields
  courses     CourseEnrollment[]
  testResults TestResult[]       @relation(name: "results")
  testsGraded TestResult[]       @relation(name: "graded")
}

model Course {
  id            Int     @id @default(autoincrement())
  name          String
  courseDetails String?

  // Relation fields
  members CourseEnrollment[]
  tests   Test[]
}

model CourseEnrollment {
  createdAt DateTime @default(now())
  role      UserRole

  // Relation fields
  userId   Int
  courseId Int
  user     User   @relation(fields: [userId], references: [id])
  course   Course @relation(fields: [courseId], references: [id])

  @@id([userId, courseId])
  @@index([userId, role])
}

model Test {
  id        Int      @id @default(autoincrement())
  updatedAt DateTime @updatedAt
  name      String
  date      DateTime

  // Relation fields
  courseId    Int
  course      Course       @relation(fields: [courseId], references: [id])
  testResults TestResult[]
}

model TestResult {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  result    Int // Percentage precise to one decimal point represented as `result * 10^-1`

  // Relation fields
  studentId Int
  student   User @relation(name: "results", fields: [studentId], references: [id])
  graderId  Int
  gradedBy  User @relation(name: "graded", fields: [graderId], references: [id])
  testId    Int
  test      Test @relation(fields: [testId], references: [id])
}

enum UserRole {
  STUDENT
  TEACHER
}
```

Note that `TestResult` has two relations to the `User` model: `student` and `gradedBy`, representing both the student who took the test and the teacher who graded it. The `name` argument on the `@relation` attribute [disambiguates the relations](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/relations#disambiguating-relations) when a single model has more than one relation to the same model.

## Migrating the database [#migrating-the-database]

With the Prisma schema defined, use Prisma Migrate to create the actual tables in the database:

```sh
npx prisma migrate dev --name init
```

The command does two things:

* **Saves the migration:** Prisma Migrate takes a snapshot of your schema and figures out the SQL necessary to carry out the migration. The migration file containing the SQL is saved to `prisma/migrations`.
* **Runs the migration:** Prisma Migrate executes the SQL in the migration file to create the database schema.

**Checkpoint:** You should see output similar to the following:

```
migrations/
  └─ 20260703110127_init/
    └─ migration.sql

Your database is now in sync with your schema.
```

Congratulations, you have successfully designed the data model and created the database schema. In the next step, you will use Prisma Client to query the database.

## Generating Prisma Client [#generating-prisma-client]

Prisma Client is a type-safe database client generated from your Prisma schema. It exposes a TypeScript API tailored to your models, with autocomplete and compile-time guarantees for every query.

Generate it with:

```sh
npx prisma generate
```

**Checkpoint:** You should see output similar to: `✔ Generated Prisma Client (7.8.0) to ./generated/prisma`

The client is generated into `generated/prisma` in your project, as configured by the `output` option of the generator. Prisma ORM 7 generates the client into your project source rather than `node_modules`. Your dev server, bundler, and file watchers see the generated code as regular application code, and regenerating after a schema change no longer requires restarting tooling that caches `node_modules`.

## Seeding the database [#seeding-the-database]

In this step, you will write a seed script that fills the database with sample data using Prisma Client CRUD operations. You will also use [nested writes](https://www.prisma.io/docs/orm/v7/prisma-client/queries/relation-queries#nested-writes) to create database rows for related entities in a single operation.

First, register the seed script in `prisma.config.ts` so the Prisma CLI knows how to run it:

```ts
import "dotenv/config";
import { defineConfig } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
    seed: "tsx prisma/seed.ts",
  },
  datasource: {
    url: process.env["DATABASE_URL"],
  },
});
```

Then create `prisma/seed.ts` with the client setup. In Prisma ORM 7 you instantiate Prisma Client with a [driver adapter](https://www.prisma.io/docs/orm/v7/core-concepts/supported-databases/database-drivers), which manages the connection to PostgreSQL through `node-postgres`:

```ts
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 });

function addDays(date: Date, days: number): Date {
  return new Date(date.getTime() + days * 24 * 60 * 60 * 1000);
}

async function main() {
  // Seed operations go here, step by step below
}

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

### Creating a user [#creating-a-user]

Begin by creating a user inside the `main` function:

```ts
const grace = await prisma.user.create({
  data: {
    email: 'grace@hey.com',
    firstName: 'Grace',
    lastName: 'Bell',
    social: {
      bluesky: 'gracebell',
      linkedin: 'gracebell',
    },
  },
})
```

The operation creates a row in the `User` table and returns the created user, including the generated `id`. The returned `grace` value is fully typed: the `User` type is exported from your generated client, so you can also import it directly with `import type { User } from "../generated/prisma/client"`.

Run the seed script with:

```sh
npx prisma db seed
```

As you follow the next steps, you will run the seed script more than once. To avoid hitting unique constraint errors, delete the contents of the database at the beginning of the `main` function:

```ts
await prisma.testResult.deleteMany({})
await prisma.courseEnrollment.deleteMany({})
await prisma.test.deleteMany({})
await prisma.user.deleteMany({})
await prisma.course.deleteMany({})
```

> **Note:** These commands delete all rows in each database table. Use carefully and avoid this in production!

### Creating a course and related tests and users [#creating-a-course-and-related-tests-and-users]

In this step, you will create a *course* and use a **nested write** to create related *tests* in the same operation. Add the following to the `main` function:

```ts
const weekFromNow = addDays(new Date(), 7)
const twoWeeksFromNow = addDays(new Date(), 14)
const monthFromNow = addDays(new Date(), 28)

const course = await prisma.course.create({
  data: {
    name: 'CRUD with Prisma',
    tests: {
      create: [
        { date: weekFromNow, name: 'First test' },
        { date: twoWeeksFromNow, name: 'Second test' },
        { date: monthFromNow, name: 'Final exam' },
      ],
    },
    members: {
      create: {
        role: 'TEACHER',
        user: {
          connect: {
            email: grace.email,
          },
        },
      },
    },
  },
  include: {
    tests: true,
  },
})
```

This creates one row in the `Course` table and three related rows in the `Test` table through their one-to-many relation. It also relates Grace to the course as a teacher through the explicit many-to-many relation: a row is created in the `CourseEnrollment` relation table with the `TEACHER` role.

When using nested writes, there are two options:

* **`connect`**: Create a relation to an existing row
* **`create`**: Create a new row and the relation to it

In the case of `tests`, you passed an array of objects to `create` which are all linked to the created course.

In the case of `members`, both `create` and `connect` were used: even though the user already exists, a *new* row in the `CourseEnrollment` relation table needs to be created, and `connect` links it to the existing user.

> **Note:** The [`include`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/select-fields) argument fetches relations in the result. Here it returns the created tests, which you will need to relate test results to tests in a later step.

### Creating users and relating them to a course [#creating-users-and-relating-them-to-a-course]

Next, create more users and relate them to the course as *students*:

```ts
const shakuntala = await prisma.user.create({
  data: {
    email: 'devi@prisma.io',
    firstName: 'Shakuntala',
    lastName: 'Devi',
    courses: {
      create: {
        role: 'STUDENT',
        course: {
          connect: { id: course.id },
        },
      },
    },
  },
})

const david = await prisma.user.create({
  data: {
    email: 'david@prisma.io',
    firstName: 'David',
    lastName: 'Deutsch',
    courses: {
      create: {
        role: 'STUDENT',
        course: {
          connect: { id: course.id },
        },
      },
    },
  },
})
```

### Adding test results for the students [#adding-test-results-for-the-students]

Looking at the `TestResult` model, it has three relations: `student`, `gradedBy`, and `test`. Adding a single test result looks as follows:

```ts
await prisma.testResult.create({
  data: {
    gradedBy: {
      connect: { email: grace.email },
    },
    student: {
      connect: { email: shakuntala.email },
    },
    test: {
      connect: { id: test.id },
    },
    result: 950,
  },
})
```

To add a test result for both David and Shakuntala for each of the three tests, loop over the course's tests:

```ts
const testResultsDavid = [650, 900, 950]
const testResultsShakuntala = [800, 950, 910]

let counter = 0
for (const test of course.tests) {
  await prisma.testResult.create({
    data: {
      gradedBy: { connect: { email: grace.email } },
      student: { connect: { email: shakuntala.email } },
      test: { connect: { id: test.id } },
      result: testResultsShakuntala[counter],
    },
  })

  await prisma.testResult.create({
    data: {
      gradedBy: { connect: { email: grace.email } },
      student: { connect: { email: david.email } },
      test: { connect: { id: test.id } },
      result: testResultsDavid[counter],
    },
  })

  counter++
}
```

Congratulations, you have created sample data for users, courses, tests, and test results in your database.

To explore the data visually, run [Prisma Studio](https://www.prisma.io/studio). Prisma Studio is a visual editor for your database:

```sh
npx prisma studio
```

## Aggregating the test results with Prisma Client [#aggregating-the-test-results-with-prisma-client]

Prisma Client can perform aggregate operations on the number fields (such as `Int` and `Float`) of a model. Aggregate operations compute a single result from a set of rows, for example the *minimum*, *maximum*, and *average* of the `result` column over a set of `TestResult` rows.

In this step, you will run two kinds of aggregate operations:

1. For each **test** in the course across all **students**, resulting in aggregates representing how difficult the test was:

   ```ts
   for (const test of course.tests) {
     const results = await prisma.testResult.aggregate({
       where: {
         testId: test.id,
       },
       _avg: { result: true },
       _max: { result: true },
       _min: { result: true },
       _count: true,
     })
     console.log(`test: ${test.name} (id: ${test.id})`, results)
   }
   ```

   This results in the following:

   ```
   test: First test (id: 1) {
    _avg: { result: 725 },
    _max: { result: 800 },
    _min: { result: 650 },
    _count: 2
   }
   test: Second test (id: 2) {
    _avg: { result: 925 },
    _max: { result: 950 },
    _min: { result: 900 },
    _count: 2
   }
   test: Final exam (id: 3) {
    _avg: { result: 930 },
    _max: { result: 950 },
    _min: { result: 910 },
    _count: 2
   }
   ```

2. For each **student** across all **tests**, resulting in aggregates representing the student's performance in the course:

   ```ts
   // Get aggregates for David
   const davidAggregates = await prisma.testResult.aggregate({
     where: {
       student: { email: david.email },
     },
     _avg: { result: true },
     _max: { result: true },
     _min: { result: true },
     _count: true,
   })
   console.log(`David's results (email: ${david.email})`, davidAggregates)

   // Get aggregates for Shakuntala
   const shakuntalaAggregates = await prisma.testResult.aggregate({
     where: {
       student: { email: shakuntala.email },
     },
     _avg: { result: true },
     _max: { result: true },
     _min: { result: true },
     _count: true,
   })
   console.log(`Shakuntala's results (email: ${shakuntala.email})`, shakuntalaAggregates)
   ```

   This results in the following terminal output:

   ```
   David's results (email: david@prisma.io) {
    _avg: { result: 833.3333333333334 },
    _max: { result: 950 },
    _min: { result: 650 },
    _count: 3
   }
   Shakuntala's results (email: devi@prisma.io) {
    _avg: { result: 886.6666666666666 },
    _max: { result: 950 },
    _min: { result: 800 },
    _count: 3
   }
   ```

### Grouping aggregates with groupBy [#grouping-aggregates-with-groupby]

Prisma Client also supports [`groupBy`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/aggregation-grouping-summarizing#group-by) queries, which combine grouping and aggregation in a single database query. The per-test loop above can be expressed as one query:

```ts
const resultsByTest = await prisma.testResult.groupBy({
  by: ["testId"],
  _avg: { result: true },
  _max: { result: true },
  _min: { result: true },
  _count: true,
})
```

This returns one row per `testId` with the same aggregate values as the loop, using a single `GROUP BY` query in PostgreSQL instead of one query per test.

## Summary and next steps [#summary-and-next-steps]

This article covered a lot of ground, starting with the problem domain and then delving into data modeling, the Prisma schema, database migrations with Prisma Migrate, CRUD operations with Prisma Client, and aggregations.

Mapping out the problem domain before jumping into code is generally good advice because it informs the design of the data model, which impacts every aspect of the backend. And because the Prisma schema is the single source of truth for both your database structure and your generated client, the data model you designed here carries through every layer of the application, whether the code is written by you, your team, or an AI agent working from the same schema.

While Prisma aims to make working with relational databases easy, it helps to have a deeper understanding of the underlying database. Check out [Prisma's Data Guide](https://www.prisma.io/dataguide) to learn more about how databases work.

In the next parts of this series, you'll learn more about:

* API layer
* Validation
* Testing
* Authentication
* Authorization
* Integration with external APIs
* Deployment

The database you created with `npx create-db` runs on [Prisma Postgres](https://www.prisma.io/postgres), which you can claim and manage in the [Prisma Console](https://console.prisma.io). If AI agents are part of your development workflow, the [Prisma MCP server](https://www.prisma.io/docs/ai/tools/mcp-server) lets them create and manage databases as they need them. And when you reach the deployment part of this series, [Prisma Compute](https://www.prisma.io/compute) runs your backend on the same platform as your database.

This series teaches Prisma ORM 7, which remains supported, with its docs at [prisma.io/docs/orm/v7](https://www.prisma.io/docs/orm/v7). If you want to see where Prisma ORM is heading for AI-assisted development, read [The Next Evolution of Prisma ORM](https://www.prisma.io/blog/the-next-evolution-of-prisma-orm). Prisma 8 is the agent-native evolution of the ORM. It builds on the idea at the heart of this article: your schema is a single data contract, and every query against it is type-safe and returns structured output that humans and coding agents can build on safely. It runs in parallel with an existing project so you can migrate incrementally, and the [performance benchmark](https://www.prisma.io/blog/prisma-next-performance-benchmark) covers what the rewrite means for throughput and client size.