Timeouts after deployment when using Supavisor Session

I have the following application:
- NextJS App router app
- Deployed on Vercel
- Supabase Postgres database
- Drizzle ORM
- WorkOS Authkit

I have a transaction in one of my routers:

export const pollRouter = createTRPCRouter({
  create: protectedProcedure
    .input(createPollSchema)
    .mutation(async ({ ctx, input }) => {
      return await ctx.db.transaction(async (trx) => {
        await trx.insert(a).values(...).returning();
        await trx.insert(b).values(...).returning();
        await trx.insert(c).values(...).returning();
      })
...

export const pollRouter = createTRPCRouter({
  create: protectedProcedure
    .input(createPollSchema)
    .mutation(async ({ ctx, input }) => {
      return await ctx.db.transaction(async (trx) => {
        await trx.insert(a).values(...).returning();
        await trx.insert(b).values(...).returning();
        await trx.insert(c).values(...).returning();
      })
...

This is to ensure

doesn't get inserted if

fails (is there a better approach to this that doesn't use Postgres transactions?)

I thought this was working well but recently I discovered that sometimes when creating a poll it would intermittently fail.

When I looked into it I was using the Supavisor Transaction mode connection string which didn't seem to support Postgres transactions. I forget what the message in the log was but it was something along the lines of "prepared statement not existing". I then read:

Transaction mode does not support prepared statements. To avoid errors, turn off prepared statements for your connection library.

https://supabase.com/docs/guides/database/connecting-to-postgres

So I disabled it like so:

postgres(env.DATABASE_URL, { prepare: false });

postgres(env.DATABASE_URL, { prepare: false });

However, after making this change there was now a noticeable performance drop when using my application. Everything felt sluggish. I don't have exact metrics but clicking between different pages felt almost instant before making this change. Afterwards it seems like every page click took at least a second longer.

So I then explored the option of Supavisor Session mode and used this connection string instead.

...Continued below...

Connect to your database | Supabase Docs

Connect to Postgres from your frontend, backend, or serverless environment

Timeouts after deployment when using Supavisor Session

I have the following application:
- NextJS App router app
- Deployed on Vercel
- Supabase Postgres database
- Drizzle ORM
- WorkOS Authkit

I have a transaction in one of my routers:

export const pollRouter = createTRPCRouter({
  create: protectedProcedure
    .input(createPollSchema)
    .mutation(async ({ ctx, input }) => {
      return await ctx.db.transaction(async (trx) => {
        await trx.insert(a).values(...).returning();
        await trx.insert(b).values(...).returning();
        await trx.insert(c).values(...).returning();
      })
...

export const pollRouter = createTRPCRouter({
  create: protectedProcedure
    .input(createPollSchema)
    .mutation(async ({ ctx, input }) => {
      return await ctx.db.transaction(async (trx) => {
        await trx.insert(a).values(...).returning();
        await trx.insert(b).values(...).returning();
        await trx.insert(c).values(...).returning();
      })
...

This is to ensure

doesn't get inserted if

Transaction mode does not support prepared statements. To avoid errors, turn off prepared statements for your connection library.

https://supabase.com/docs/guides/database/connecting-to-postgres

So I disabled it like so:

postgres(env.DATABASE_URL, { prepare: false });

postgres(env.DATABASE_URL, { prepare: false });

Connect to your database | Supabase Docs

Connect to Postgres from your frontend, backend, or serverless environment

Timeouts after deployment when using Supavisor Session

Timeouts after deployment when using Supavisor Session

Similar Threads

Similar Threads

Similar Threads