Prisma for AI Agent APIs: Type-Safe Database Access in TypeScript

Why Prisma for Agent APIs

Prisma generates a fully typed database client from your schema. Every query, every relation, every filter is type-checked at compile time. For AI agent APIs where you are juggling conversations, messages, tool calls, and user sessions, this eliminates an entire category of runtime errors — misspelled column names, wrong relation names, type mismatches in filters.

Combined with TypeScript, Prisma gives you autocompletion that knows your exact database shape. This is a significant productivity gain when building the data layer for agent systems.

Schema Design

Define your agent schema in prisma/schema.prisma:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id            String         @id @default(uuid())
  email         String         @unique
  displayName   String?        @map("display_name")
  conversations Conversation[]
  createdAt     DateTime       @default(now()) @map("created_at")

  @@map("users")
}

model Agent {
  id            String         @id @default(uuid())
  name          String
  model         String         @default("gpt-4o")
  instructions  String
  config        Json           @default("{}")
  isActive      Boolean        @default(true) @map("is_active")
  conversations Conversation[]
  createdAt     DateTime       @default(now()) @map("created_at")

  @@map("agents")
}

model Conversation {
  id        String    @id @default(uuid())
  userId    String    @map("user_id")
  agentId   String    @map("agent_id")
  title     String?
  status    String    @default("active")
  metadata  Json      @default("{}")
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  agent     Agent     @relation(fields: [agentId], references: [id])
  messages  Message[]
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")

  @@index([userId, createdAt(sort: Desc)])
  @@map("conversations")
}

model Message {
  id             String     @id @default(uuid())
  conversationId String     @map("conversation_id")
  role           String
  content        String?
  tokenCount     Int?       @map("token_count")
  modelUsed      String?    @map("model")
  toolCalls      Json?      @map("tool_calls")
  conversation   Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)
  createdAt      DateTime   @default(now()) @map("created_at")

  @@index([conversationId, createdAt])
  @@map("messages")
}

The @@map directives keep snake_case table and column names in the database while using camelCase in TypeScript. This is a best practice for Prisma projects that interact with a PostgreSQL database.

Generating and Using the Client

After defining your schema, generate the Prisma client:

npx prisma generate

This creates a typed client in node_modules/.prisma/client. Use it in your API routes:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// Create a conversation with its first message in one transaction
async function startConversation(
  userId: string,
  agentId: string,
  userMessage: string
) {
  return prisma.conversation.create({
    data: {
      userId,
      agentId,
      title: userMessage.slice(0, 100),
      messages: {
        create: {
          role: "user",
          content: userMessage,
        },
      },
    },
    include: {
      messages: true,
    },
  });
}

Prisma wraps nested creates in a transaction automatically. The include option eagerly loads the messages relation in the response.

Querying Conversations with Pagination

Implement cursor-based pagination for conversation lists:

async function getUserConversations(
  userId: string,
  cursor?: string,
  limit = 20
) {
  return prisma.conversation.findMany({
    where: { userId, status: "active" },
    orderBy: { createdAt: "desc" },
    take: limit,
    ...(cursor && {
      skip: 1,
      cursor: { id: cursor },
    }),
    include: {
      messages: {
        take: 1,
        orderBy: { createdAt: "desc" },
      },
      _count: { select: { messages: true } },
    },
  });
}

This query returns conversations with their latest message and a message count. The cursor-based pagination uses Prisma's built-in cursor support, which translates to efficient keyset pagination in SQL.

Interactive Transactions

For operations that require reading and writing atomically — like appending an assistant response after verifying the conversation exists:

async function addAssistantMessage(
  conversationId: string,
  content: string,
  tokenCount: number,
  model: string
) {
  return prisma.$transaction(async (tx) => {
    const conversation = await tx.conversation.findUnique({
      where: { id: conversationId },
    });

    if (!conversation || conversation.status !== "active") {
      throw new Error("Conversation not found or not active");
    }

    const message = await tx.message.create({
      data: {
        conversationId,
        role: "assistant",
        content,
        tokenCount,
        modelUsed: model,
      },
    });

    await tx.conversation.update({
      where: { id: conversationId },
      data: { updatedAt: new Date() },
    });

    return message;
  });
}

The $transaction callback receives a transactional client (tx) that guarantees all operations either commit or roll back together.

Migrations in Practice

Apply schema changes through Prisma Migrate:

# Development: create and apply migration
npx prisma migrate dev --name add_tool_calls_table

# Production: apply pending migrations
npx prisma migrate deploy

Always run prisma migrate dev locally first. It generates a SQL migration file you can review before deploying. In CI/CD, use prisma migrate deploy which only applies pending migrations without generating new ones.

FAQ

How do I handle the Prisma client in serverless environments?

Instantiate PrismaClient once at module scope and reuse it across invocations. In Next.js API routes, use a global singleton pattern: store the client on globalThis in development to survive hot module replacement, and create it once in production. This prevents connection pool exhaustion from creating new clients per request.

Should I use Prisma's Json type or separate tables for tool calls?

Use a separate table when you need to query tool calls independently — for analytics, debugging, or auditing. Use the Json type when tool call data is only accessed as part of its parent message and you want simpler queries. Most production systems benefit from a dedicated tool_calls table.

How does Prisma handle database connection pooling?

Prisma uses a built-in connection pool with a default size of num_cpus * 2 + 1. Configure it via the connection_limit parameter in your DATABASE_URL: postgresql://user:pass@host/db?connection_limit=20. For serverless, consider using Prisma Accelerate or PgBouncer as an external connection pooler.

---

#Prisma #TypeScript #ORM #AIAgents #Database #Nodejs #AgenticAI #LearnAI #AIEngineering