# Using Prisma with Encore.ts


[Prisma](https://prisma.io/) is a modern TypeScript ORM that provides type-safe database access and migrations. With Prisma, you define your database schema in a `schema.prisma` file and use Prisma's CLI to generate SQL migrations and a TypeScript client.

This guide explains how to integrate Prisma with Encore.ts, leveraging Encore's built-in database management while using Prisma's powerful ORM features.

## How Prisma works with Encore

Encore and Prisma work together seamlessly:
- **Prisma** generates the migration files and TypeScript client
- **Encore** manages database creation, connections, and applies migrations
- You use Encore's `SQLDatabase` to provide connection strings to Prisma

The key to this integration is configuring Prisma to use Encore's shadow database for its operations, preventing any conflicts between the two systems.

## Quick Example

Here's a complete example of using Prisma with Encore.ts:

```ts
-- users/database.ts --
import { SQLDatabase } from "encore.dev/storage/sqldb";

// Define a database named 'users', using the database migrations
// in the "./prisma/migrations" folder (generated by Prisma).
export const DB = new SQLDatabase("users", {
  migrations: {
    path: "./prisma/migrations",
    source: "prisma",
  },
});

-- users/prisma/schema.prisma --
generator client {
  provider        = "prisma-client"
  output          = "./generated"
  previewFeatures = ["queryCompiler", "driverAdapters"]
}

datasource db {
  provider = "postgresql"
  // Connect Prisma CLI to Encore's shadow database
  // This prevents interference with Encore's migration system
  url      = env("SHADOW_DB_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  createdAt DateTime @default(now())
}

-- users/prisma/client.ts --
import { PrismaClient } from "./generated/client";
import { PrismaPg } from "@prisma/adapter-pg";
import { DB } from "../database";

// Create and export the Prisma client instance
export const prisma = new PrismaClient({
  adapter: new PrismaPg({ connectionString: DB.connectionString }),
});

// Re-export types from the generated client
export * from "./generated/client";

-- users/api.ts --
import { api } from "encore.dev/api";
import { prisma } from "./prisma/client";

interface CreateUserRequest {
  email: string;
  name: string;
}

// Example API endpoint using Prisma
export const createUser = api(
  { method: "POST", path: "/users", expose: true },
  async (req: CreateUserRequest) => {
    const user = await prisma.user.create({
      data: req,
    });
    return user;
  }
);
```

<GitHubLink
href="https://github.com/encoredev/examples/tree/main/ts/prisma"
desc="Complete Prisma + Encore.ts example"
/>

## Step-by-Step Setup

### 1. Install Dependencies

First, install Prisma and its required dependencies:

```bash
npm install prisma --save-dev
npm install @prisma/client @prisma/adapter-pg dotenv --save
```

### 2. Create Project Structure

Create the following directory structure for your service:

```
my-service/
├── database.ts
├── prisma/
│   ├── schema.prisma
│   ├── migrations/
│   ├── generated/     (will be created by Prisma)
│   └── client.ts
└── api.ts
```

### 3. Set Up the Database

Create `database.ts` to define your Encore database:

```ts
import { SQLDatabase } from "encore.dev/storage/sqldb";

// Export the database so it can be used in the Prisma client
export const DB = new SQLDatabase("myapp", {
  migrations: {
    path: "./prisma/migrations",
    source: "prisma",
  },
});
```

Run `encore run` to create the database (make sure the migrations folder has been created first).

### 4. Configure Database Connections

Prisma needs to connect to Encore's shadow database for migration operations. The shadow database is a temporary database that Prisma uses to detect schema drift and generate migrations without affecting your main database.

Get the connection strings:

```bash
# Main database connection (for Prisma Studio)
encore db conn-uri myapp

# Shadow database connection (for Prisma CLI operations)
encore db conn-uri myapp --shadow
```

Create a `.env` file in your project root:

```
# Connection strings for local development
DB_URL=<main-database-connection-string>
SHADOW_DB_URL=<shadow-database-connection-string>
```

### 5. Create Prisma Configuration

Create `prisma.config.ts` in your project root:

```ts
import "dotenv/config";
import type { PrismaConfig } from "prisma";
import { PrismaPg } from "@prisma/adapter-pg";

type Env = {
  DB_URL: string;
};

export default {
  earlyAccess: true,
  schema: "./my-service/prisma/schema.prisma",
  studio: {
    adapter: async (env: Env) => {
      // Connect Prisma Studio to the main Encore database
      return new PrismaPg({ connectionString: env.DB_URL });
    },
  },
} satisfies PrismaConfig<Env>;
```

### 6. Create Your Prisma Schema

Create `my-service/prisma/schema.prisma`:

```
generator client {
  provider        = "prisma-client"
  output          = "./generated"
  previewFeatures = ["queryCompiler", "driverAdapters"]
}

datasource db {
  provider = "postgresql"
  // IMPORTANT: Use shadow database URL for Prisma CLI operations
  url      = env("SHADOW_DB_URL")
}

// Define your models here
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
}
```

### 7. Create the Prisma Client Wrapper

Create `my-service/prisma/client.ts`:

```ts
import { PrismaClient } from "./generated/client";
import { PrismaPg } from "@prisma/adapter-pg";
import { DB } from "../database";

// Create and export the Prisma client instance
export const prismaClient = new PrismaClient({
  adapter: new PrismaPg({ connectionString: DB.connectionString }),
});

// Re-export types from the generated client
export * from "./generated/client";
```

### 8. Generate Initial Migration

Generate and apply your first migration:

```bash
npx prisma migrate dev --name init
encore run
```

This will:
1. Create the `prisma/migrations` directory
2. Generate SQL migration files
3. Generate the Prisma client in `prisma/generated`
4. Apply the migration to your development database

### 9. Use Prisma in Your Code

Now you can use Prisma in your API endpoints:

```ts
import { api, APIError } from "encore.dev/api";
import { prismaClient, Prisma } from "./prisma/client";

interface CreateUserRequest {
  email: string;
  name: string;
}

export const createUser = api(
  { method: "POST", path: "/users", expose: true },
  async (req: CreateUserRequest) => {
    try {
      const user = await prismaClient.user.create({
        data: req,
      });
      return user;
    } catch (error) {
      if (error instanceof Prisma.PrismaClientKnownRequestError) {
        if (error.code === "P2002") {
          throw APIError.alreadyExists("User with this email already exists");
        }
      }
      throw error;
    }
  },
);

export const getUsers = api(
  { method: "GET", path: "/users", expose: true },
  async (): Promise<{
    users: { name: string; email: string; id: number }[];
  }> => {
    return { users: await prismaClient.user.findMany() };
  },
);
```

## Working with Migrations

### Generate New Migrations

When you make changes to your `schema.prisma` file:

```bash
npx prisma migrate dev --name describe-your-change
```

Example:
```bash
npx prisma migrate dev --name add-user-role
```

### How Migrations Work

1. **Prisma generates** SQL migration files based on your schema changes
2. **Encore applies** these migrations automatically:
   - Locally when you run `encore run`
   - In cloud environments during deployment
   - During tests when using `encore test`

## Deployment

### Generate Client During Build

For Encore Cloud deployments, add to your `package.json`:

```json
{
  "scripts": {
    "postinstall": "npx prisma generate"
  }
}
```

This ensures the Prisma client is generated during deployment.

### Production Considerations

- Encore automatically provides database connection strings in production
- No environment variables need to be configured for production
- Migrations are applied automatically during deployment

## Using Prisma Studio

Prisma Studio provides a GUI for your database:

```bash
npx prisma studio
```

Opens at `http://localhost:5555` where you can:
- Browse and filter data
- Create, update, and delete records
- Explore relationships

## Troubleshooting

### Connection Issues

If Prisma or Prisma Studio can't connect:
1. Verify connection strings in `.env`
2. Restart Encore (`encore run`)