We’re Neon. We’re building Postgres that helps you confidently ship reliable and scalable apps. We made Postgres on Neon work seamlessly with Prisma. This article explains how we did it.

We love Prisma, and so do developers. Prisma ORM makes it easy to perform schema migrations and map any database objects with your existing JavaScript and TypeScript applications, allowing you to integrate type-safe queries into your codebase.

Today, we’re pleased to share significant improvements to the developer experience of Neon using Prisma by adding support to schema migrations via pooled connections, making it possible to use Neon’s default connection string to scale your serverless apps and run schema migrations. 

You can start using Prisma with Neon for free.

For reference, when we first introduced Neon, Prisma users needed a direct database URL, a pooled database URL, and a shadow database URL. Here how your prisma.schema file looked like:

// Initially
datasource db {
  provider  = "postgresql"
  url       = postgres://sally:<password>@ep-throbbing-boat-918849-pooler.us-east-2.aws.neon.tech/neondb?pgbouncer=true 
  directUrl = postgres://sally:<password>@ep-throbbing-boat-918849.us-east-2.aws.neon.tech/neondb
  shadowDatabaseUrl = postgres://sally:<password>@ep-throbbing-boat-918849.us-east-2.aws.neon.tech/shadowdb
}

Now, all you need is one database URL. As a bonus, we also removed the need to specify the query parameter pgbouncer=true when using pooled connections:

// Now
datasource db {
  provider  = "postgresql"
  url       = postgres://sally:<password>@ep-throbbing-boat-918849-pooler.us-east-2.aws.neon.tech/neondb
}

This article discusses each step of the process and the changes made to Neon, PgBouncer, and Prisma to make this possible, including:

1. Schema migration support with pooled connections
2. Dropping a shadow database WITH (FORCE)

Schema migration support with pooled connections

In enhancing the experience with Prisma, we added support for prepared statements and DISCARD ALL/DEALLOCATE ALL to PgBouncer to allow for schema migration using pooled connections. Let’s explore why.

In Postgres, each connection is a backend process that requires memory allocation, which limits the number of concurrent connections. The solution to this problem is connection pooling with PgBouncer, which helps keep the number of active backend processes low.

PgBouncer becomes increasingly important at scale when using serverless services such as AWS Lambda or Vercel functions, since each function call establishes a new connection. We name database connections that use PgBouncer pooled connections.

Additionally, prisma migrate uses prepared statements to optimize SQL query performance, and DEALLOCATE ALL to release all prepared statements in the current session before preparing and executing Prisma Client queries. More on prepared statements in the PgBouncer 1.22.0 support announcement article.

Before version 1.22.0, if you attempted to run prisma migrate commands using a pooled connection, you might have seen the following error:

Error: undefined: Database error
Error querying the database: db error: ERROR: prepared statement "s0" already exists

To scale using pooled connections and be able to perform schema migrations, you had to specify both pooled and direct database URLs and set pgbouncer mode as a query parameter in your schema file. 

Here is how the datasource db block in your prisma.schema file looked like:

// Before PgBouncer 1.22.0 and Prisma Client 5.10.0
datasource db {
  provider  = "postgresql"
  url       = postgres://sally:<password>@ep-throbbing-boat-918849-pooler.us-east-2.aws.neon.tech/neondb?pgbouncer=true 
  directUrl = postgres://sally:<password>@ep-throbbing-boat-918849.us-east-2.aws.neon.tech/neondb
}

Here is how it looks now after adding support for prepared statements and DISCARD ALL/DEALLOCATE ALL to PgBouncer:

// With PgBouncer 1.22.0 and Prisma Client 5.10.0
datasource db {
  provider  = "postgresql"
  url       = postgres://sally:<password>@ep-throbbing-boat-918849-pooler.us-east-2.aws.neon.tech/neondb
}

Note you only need the pooled connection to run Prisma with Postgres. It’s no longer required to specify a direct connection to the database and the pgbouncer=true query parameter. The pooled connection is used to scale your queries and run schema migrations.

This allows Neon to confidently set the default URL to the pooled connection string on the Console and the Vercel Integration.

DROP shadow database WITH (FORCE)

When you run prisma migrate dev, Prisma Migrate uses a shadow database to detect schema drifts and generate new migrations. During that process, Prisma creates, introspects, and then drops a shadow database. More on shadow databases on Prisma’s documentation.

However, certain cloud providers do not allow to drop and create databases via SQL, which forces developers to manually create shadow databases and specify them in the prisma.schema file:

datasource db {
  provider  = "postgresql"
  url       = postgres://sally:<password>@ep-throbbing-boat-918849.us-east-2.aws.neon.tech/neondb
  shadowDatabaseUrl = postgres://sally:<password>@ep-throbbing-boat-918849.us-east-2.aws.neon.tech/shadowdb
}

We have added support for managing roles and databases via SQL on Neon, which allowed for removing the need for manually creating a shadow database. Additionally, Prisma 5.10.0 introduces support for DROP WITH (FORCE) as an alternative drop database path in the schema engine, which allows it to dispose of shadow databases.

So, in your schema.prisma file, you would have:

datasource db {
  provider  = "postgresql"
  url       = postgres://sally:<password>@ep-throbbing-boat-918849.us-east-2.aws.neon.tech/neondb
}

Conclusion

The improvements included in PgBouncer 1.22.0 have significantly streamlined the experience for developers using Postgres on Neon and Prisma, making is more efficient to scale serverless applications and run schema migrations. 

We would love to get your feedback. Follow us on X, join us on Discord and let us know how we can help you build the next generation of web applications.

Shout out to all contributors for making this possible, including:

• Jelte Fennema
• Martijn Dashorst 
• Konstantin Knizhnik
• Sverre Boschman
• Frank Schmager
• Oleg Tselebrovskiy
• Alex Chi Z