Database with Prisma

Prisma is a modern database toolkit that simplifies database access with type-safe queries. It consists of three main components:

• Prisma Client: An auto-generated, type-safe query builder for Node.js & TypeScript
• Prisma Migrate: A declarative migration system for your database schema
• Prisma Studio: A GUI to view and edit data in your database

Dastack uses Prisma with PostgreSQL, but Prisma also supports MySQL, SQLite, SQL Server, MongoDB, and CockroachDB.

The database schema is defined in prisma/schema.prisma. This file contains your database connection, model definitions, and field types.

The default schema includes the following models:

• User: A user in the system with roles
• Doc: A document with text content and optional file attachment
• File: Binary file storage with metadata

To extend the schema, you'll need to modify the prisma/schema.prisma file and then run migrations to apply your changes to the database.

1. Edit the Schema

Add or modify models in the schema file. For example, to add a new Task model:

model Task {
  id         String   @id @default(ulid())
  ref        String   @unique @default(dbgenerated("generate_short_id()"))
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt
  title      String
  description String?
  status     String   @default("pending")
  due_date   DateTime?
  userId     String?
  user       User?    @relation(fields: [userId], references: [id])
}

2. Update the User Model for Relations

If you're creating relations to existing models, update them as well:

model User {
  id         String   @id @default(ulid())
  ref        String   @unique @default(dbgenerated("generate_short_id()"))
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt
  name       String
  email      String?  @unique
  phone      String?  @unique
  roles      Role[]   @default([USER])
  tasks      Task[]   // New relation field
}

3. Generate a Migration

After modifying your schema, create a migration to apply the changes:

This command will:

• Create a new migration file in prisma/migrations
• Apply the changes to your development database
• Regenerate the Prisma Client

4. Update the Prisma Client (if needed)

If you only need to regenerate the Prisma Client without creating a migration:

Dastack provides a configured Prisma client instance that you can import in your server components or API routes:

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

// Example of fetching all users
const users = await db.user.findMany()

You can use all Prisma Client features like filtering, sorting, pagination, and relations:

// Find users with the ADMIN role and include their tasks
const admins = await db.user.findMany({
  where: {
    roles: {
      has: "ADMIN"
    }
  },
  include: {
    tasks: true
  }
})

Prisma Studio is a visual database browser that lets you view and edit your data:

This will open a browser window at http://localhost:5555 where you can:

• Browse all your data
• Filter and sort records
• Add, edit, or delete records
• View relationships between models