Determine primary postgresql node

[Aw3same]

Question

I'm using prisma in my NextJS application. And I have a PotgreSQL cluster with 3 nodes, each one in a different URL.

Normally the node 1 is the primary, and the other two are read-only. But sometimes, this changes, so now the node 2 is the primary and the one is read-only.

Having this configuration:

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

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

I get the error:

Cannot execute INSERT in a read-only transaction

This is because my DATABASE_URL now is not the primary node, but is a read-only. Is there a way to determine which node is the one that can write, or try across all nodes ?

Thanks in advance!

How to reproduce (optional)

Expected behavior (optional)

No response

Information about Prisma Schema, Client Queries and Environment (optional)

// Add your schema.prisma

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  shadowDatabaseUrl = env("DATABASE_URL_N2")
}
/**...*/

• OS: Windows
• Database: PostgreSQL
• Node.js version: 20.10.0
• Prisma version: 5.9.1

[ludralph]

Hi @Aw3same 👋

Thank you for raising this question.

Prisma Client currently does not have built-in support for managing read and write replicas in a PostgreSQL cluster. In your current configuration, Prisma is using the DATABASE_URL environment variable to connect to the database. If this URL points to a read-only replica, you will not be able to perform write operations.

One possible workaround could be to manage the database connections at the application level. You could create two instances of PrismaClient, one for read operations and one for write operations, each with its own connection URL. Here's an example:

import { PrismaClient } from '@prisma/client'

const readClient = new PrismaClient()
const writeClient = new PrismaClient({ datasources: { mydb: { url: process.env.WRITE_DB_URL }} })

In this example, readClient would connect to the read-only replica and writeClient would connect to the primary node. You would need to manage the WRITE_DB_URL environment variable to always point to the primary node. It might interest you to note that there is an extension for working with read replicas here. However, the core functionality of determining which node is writable will have to be handled by you since we do not have support for it.

If this answers your question, it would be great if you could mark this Discussion as answered to indicate that it has been resolved.

Otherwise please let us know how else we can help you further or close the Discussion if it was resolved in some other way 🙏

[Aw3same]

Yep, thats what I've think! Thanks for the answer!