Using Prisma with Encore.ts

Prisma 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:

users/database.ts
users/prisma/schema.prisma
users/prisma/client.ts
users/api.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",
  },
});

Step-by-Step Setup

1. Install Dependencies

First, install Prisma and its required dependencies:

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:

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:

# 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:

prisma.config.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:

my-service/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 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:

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:

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:

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

Example:

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 the Encore platform deployments, add to your package.json:

package.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:

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)