Help using transactions with Drizzle

Hello! I am a little confused on how to set up and use Neon to enable transactions. I can see in the docs here (https://github.com/neondatabase/serverless?tab=readme-ov-file#pool-and-client) that Pool and Client need to be used, but unsure of how this would look exactly / impact my app. I am using Drizzle in a Remix app. Are there any examples anyone can share on how to migrate this type of config to use Pool & Client?

GitHub

GitHub - neondatabase/serverless: Connect to Neon PostgreSQL from s...

Connect to Neon PostgreSQL from serverless/worker/edge functions - neondatabase/serverless

4 Replies

wee-brown•2y ago

Something like this should work, as far as I know:

import { Pool } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-serverless';
const pool = new Pool({ connectionString: env.DATABASE_URL });
const db = drizzle(pool)

// Example transaction
await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew'));
  await tx.transaction(async (tx2) => {
    await tx2.update(users).set({ name: "Mr. Dan" }).where(eq(users.name, "Dan"));
  });
});

import { Pool } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-serverless';
const pool = new Pool({ connectionString: env.DATABASE_URL });
const db = drizzle(pool)

// Example transaction
await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan'));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew'));
  await tx.transaction(async (tx2) => {
    await tx2.update(users).set({ name: "Mr. Dan" }).where(eq(users.name, "Dan"));
  });
});

plain-purpleOP•2y ago

oh cool, ok thanks! I'll give it a go. Do I need to be concenered about any differences in using a pool? I saw examples online where they manually disconnected / closed the connection for example.

wee-brown•2y ago

Great question. Yes, there are some things to be aware of. Namely, if you have autosuspend enabled in Neon, you should set the pool idleTimeout as explained in this article: https://neon.tech/blog/using-neons-auto-suspend-with-long-running-applications#managing-connections-with-client-side-pooling

Neon

Using Neon's Auto-Suspend with Long-Running Applications - Neon

We’re Neon, a cloud-native serverless Postgres solution. With Neon, your Postgres databases and environments are just one click away. You can still benefit from serverless Postgres if your application isn’t serverless. Try using Neon’s serverless Postgres with your long-running applications today. We refer to Neon as serverless Postgres because ...

wee-brown•2y ago

TLDR; the idel timeout should be lower than the autosuspend timeout.

Gaming

Programming

Help using transactions with Drizzle

Did you find this page helpful?