Next.js 14 - Multiple connections within a project with Prisma ORM

In my last projects, I needed to establish a DB connection to more than one Postgres DB. Since I was using the ORM Prisma, I had the challenge of managing these multiple connections within the Prisma Context. In this story, I will show you how to do this with some easy steps.

Prisma NPM Packages

The prisma/client and the prisma package are enough.

    "@prisma/client": "^5.14.0",
    "prisma": "^5.14.0",

After performing a default installation you should run this command:

npx prisma init

prisma

schema.prisma

Model your Prisma Schema for Postegres

Since I am using the Postgres DB’s from Neon.tech, I copy & paste the Database-Url from my Neon.tech backend to my .env.local.

# .env.local
DATABASE_URL="postgresql://xxxxxxxx/dbname/?sslmode=require"

Now it’s time for the schema file, which you can find under /projectroot/prisma/ called schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "debian-openssl-1.1.x"]
  output        = "./generated/client"
}

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

model logins {
  id        Int      @id @default(autoincrement())
  email     String
  name      String
  logins    Int
  objectId  String?
  lastLogin DateTime @default(now())
}

Binary targets in the generator client Section

Prisma Client JS (prisma-client-js) uses several engines. Engines are implemented in Rust and are used by Prisma Client in the form of executable, platform-dependent engine files. Depending on which platform you are executing your code on, you need the correct file. "Binary targets" are used to define which files should be present for the target platform(s). For example, debian-openssl-1.1.x if you are deploying to Ubuntu 18+, or native if you are working locally.

Output

Defines where the generated Client will be saved on your file system.

Provider (Datasource)

A data source determines how Prisma ORM connects your database, and is represented by the datasource block in the Prisma schema. We are using the data source — provider postgresql

Relationmode

The ability to set the relation mode was introduced as part of the referentialIntegrity preview feature in Prisma ORM version 3.1.1, and is generally available in Prisma ORM versions 4.8.0 and later.

The relationMode field was renamed in Prisma ORM version 4.5.0, and was previously named referentialIntegrity.

For relational databases, the available options are:

• foreignKeys: this handles relations in the database with foreign keys. This is the default option for all relational database connectors and is active if no relationMode

  is explicitly set in the datasource block.

• prisma: this emulates relations in Prisma Client. You should also enable this option

  when you use the MySQL connector with a PlanetScale database and don't have native foreign key constraints enabled in your PlanetScale database settings.

Generation Prisma Client

With the command below, you will generate the Prisma Client and save it in the Output directory provided in the schema.prisma file.

npx prisma generate

Adding the Path to the Schema

npx prisma generate --schema=prisma/schema.prisma

Multiple Schema Files

If we add the path to the schema file, we can generate multiple Prisma Clients.

If we create a second “second_schema.prisma” file in the base directory /prisma, we are able to create a second Prisma client to a different DB with this command.

npx prisma generate --schema=prisma/second_schema.prisma

Defining Env variable -> DATABASE_URL_SECOND

# .env.local

DATABASE_URL="postgresql://xxxxxxxx/dbname/?sslmode=require"
DATABASE_URL_SECOND="postgresql://xxxxxxxx/dbname?sslmode=require"generator 

client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "debian-openssl-1.1.x"]
  output        = "./generated/client_second"
}

datasource db {
  provider     = "postgresql"
  url          = env("DATABASE_URL_SECOND")
  relationMode = "prisma"
}

model roles {
  id      Int      @id @default(autoincrement())
  name    String
  addedOn DateTime @default(now())
}

Connection File Prisma.ts and Prisma_second.ts

The code below is setting up a singleton instance of the Prisma Client to manage database connections. Let’s break down the purpose of each part:

import { PrismaClient } from "../../prisma/generated/client";

const prismaClientSingleton = () => {
  return new PrismaClient();
};

type PrismaClientSingleton = ReturnType<typeof prismaClientSingleton>;

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClientSingleton | undefined;
};

const prisma = globalForPrisma.prisma ?? prismaClientSingleton();

export default prisma;

if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;

Importing PrismaClient:

import { PrismaClient } from "../../prisma/generated/client";

• This line imports the PrismaClient class from the Prisma client generated code. The Prisma client is a type-safe database client that allows you to interact with your database.

Creating a Prisma Client Singleton Function:

const prismaClientSingleton = () => {   return new PrismaClient(); };

• This function, prismaClientSingleton, is defined to create and return a new instance of PrismaClient.

Defining a Type for the Singleton:

type PrismaClientSingleton = ReturnType<typeof prismaClientSingleton>;

• This line defines a TypeScript type, PrismaClientSingleton, based on the return type of the prismaClientSingleton function. This is used to provide type safety.

Setting Up a Global Object to Store the Singleton:

const globalForPrisma = globalThis as unknown as {   prisma: PrismaClientSingleton | undefined; };

Here, globalForPrisma is a typed version of globalThis to ensure it has a property prisma that is either a PrismaClientSingleton instance or undefined.

Creating or Reusing the Prisma Client Instance:

const prisma = globalForPrisma.prisma ?? prismaClientSingleton();

This line checks if globalForPrisma.prisma it already has an instance of PrismaClient. If it does, it uses that instance; if not, it creates a new one using prismaClientSingleton().

Exporting the Singleton Instance:

export default prisma;

prisma

Assigning the Instance in Development Mode:

if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;

In non-production environments (like development), this line assigns the prisma instance to globalForPrisma.prisma. This ensures that the same Prisma Client instance is reused across module reloads, avoiding the creation of multiple instances, which can lead to database connection issues.

The code is aimed at creating a singleton instance of the Prisma Client to manage database connections efficiently. It ensures that:

• Only one instance of PrismaClient is created and used throughout the application.

• In development mode, the instance is stored globally to prevent issues with hot-reloading, which can cause multiple instances to be created.

This pattern is commonly used to manage resources like database connections in server-side JavaScript applications, especially when using ORM tools like Prisma.

Second Connection File

It imports the second Prisma client.

import { PrismaClient } from "../../prisma/generated/client_second";

// Lazy load PrismaClient and reuse the instance to avoid excessive connections
const prismaClientSingleton = () => {
  return new PrismaClient();
};

type PrismaClientSingleton = ReturnType<typeof prismaClientSingleton>;

const globalForPrismaSecond = globalThis as unknown as {
  prismaSecond: PrismaClientSingleton | undefined;
};

const prisma_second =
  globalForPrismaSecond.prismaSecond ??
  prismaClientSingleton();

if (process.env.NODE_ENV !== "production") {
  globalForPrismaSecond.prismaSecond = prisma_second;
}

export default prisma_second;

Import Connections Files

We are ready to use the files created for the DB connection in our components or API routes.

import prisma from "@/lib/prisma";

await prisma.logins.create({
      data: {
        email: test@test.it,
        name: testname,
        logins: 1,
        objectId: objectID_String,
      },
    });

or

import prisma_second from "@/lib/prisma_second";

    const Second = await prisma_second.roles.findFirst(
      {
        where: { name },
      }
    );

I hope this story was helpful and that you can now use multiple DB connections with Prisma for the same project.

Cloudapp-dev, and before you leave us

Thank you for reading until the end. Before you go:

Please consider clapping and following the writer! 👏 on our Medium Account

Or follow us on twitter -> Cloudapp.dev