Drizzle Team•3mo ago

Transaction help

Hi, we've been using neon serverless with drizzle driver (@neondatabase/serverless & drizzle-orm/neon-http ) successfully for a while. I wanted to know if drizzle has syntax which supports this "non-iteractive" style of transaction mentioned here in neons serverless docs. https://github.com/neondatabase/serverless?tab=readme-ov-file#transaction

import { neon } from '@neondatabase/serverless';
const sql = neon(process.env.DATABASE_URL);
const showLatestN = 10;

const [posts, tags] = await sql.transaction([
  sql`SELECT * FROM posts ORDER BY posted_at DESC LIMIT ${showLatestN}`,
  sql`SELECT * FROM tags`,
]);

import { neon } from '@neondatabase/serverless';
const sql = neon(process.env.DATABASE_URL);
const showLatestN = 10;

const [posts, tags] = await sql.transaction([
  sql`SELECT * FROM posts ORDER BY posted_at DESC LIMIT ${showLatestN}`,
  sql`SELECT * FROM tags`,
]);

Forgive my ignorance I can't see this in drizzle docs and from playing out with the transaction method it doesn't seem possible? I ask as from what I understand, when using neon-serverless with drizzle via neon-http the only way to use interactive transactions with nextjs on vercel is with Pool or Client usage, which seems like we’re limited to usage only in our API routes if we wanted interactive

GitHub

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

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

11 Replies

dfrn•3mo ago

Hey, From my understanding, this is not possible; I think they do not support transactions at all in the http driver. i got transaction working by moving to the postgres driver.

JustWayne•3mo ago

The neon http driver does have limited transactions as you showed there, but Drizzle can't use them probably because they're too limited - https://github.com/drizzle-team/drizzle-orm/blob/027921fda2ba61fd6271e49a576e1abd76613fc1/drizzle-orm/src/neon-http/session.ts#L253 So you have to use the neon client to do everything for that call. At least you can access it from your drizzle db instance though like this: const sql = db.$client; and then await sql.transaction([sql'SELECT yada = ${yada};']) It would be nice if Drizzle's sql template builder was compatible with the one needed by the call to neon's .transaction() but it doesn't work.

JustWayne•3mo ago

Oh, but there might be a way to convert a Drizzle query to a Neon query with db.$client.query(...) so then you could do const { sql, params } = queryFromDrizzle.toSQL(); and then const queryForNeon = db.$client.query(sql, params); and then you can pass queryForNeon as one of the transaction array elements...

JustWayne•3mo ago

I didn't start using transactions in my serverless code yet, but I will be glad to know if this worked for you for when I do. I don't see why it won't work though because the SQL generated by Drizzle is in the exact format as required by queryWithPlaceholders as shown in the docs there. So I guess the main limitations of transactions with Neon HTTP are basically that you can't do any branching logic within your transaction. Should be usable at least for scenarios where you want to write all your related records as a single transaction.

willemOP•3mo ago

hey thanks for taking the time, i will try the const sql = db.$client approach for now, but yeah generally we've been fine and have altered our approach to work around not having transactions, but some cases would be nice what does using the postgres driver entail when using nextjs on vercel? our db provider is neon also

dfrn•3mo ago

Basically, using a TCP/UDP connection instead of HTTP. There's also a Neon driver that does this - I had an issue, I really can't remember, so I just used the standard postgres driver. One issue you'll see with this, is that every invocation of the serverless function will need to create a connection to the db, which is slow. And too many connections open can overwhelm the db. We are in Cloudfare so we just used Hyperdrive, and it solves those issues. Idk if Vercel has something like that tbh

willemOP•3mo ago

yeah like in the docs above, if using neon directly with nextjs api routes, you need to create, use and close the connection in the span of the request, so that checks out thanks for the help 🫡

dfrn•3mo ago

https://orm.drizzle.team/docs/connect-neon If you see the docs there are examples. I am using the node-postgre, but hyperdrive solves the issues, I mentioned for me. Maybe you could try using the websockets example - idk if Vercel supports that, and see if that works for you.

Drizzle ORM - Neon

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

dfrn•3mo ago

Happy to help!

willemOP•3mo ago

for anyone else who has come here, I believe this has my desired outcome: - https://orm.drizzle.team/docs/batch-api

Drizzle ORM - Batch

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

JustWayne•3mo ago

Excellent! And it does exactly what I need with the Neon driver - https://github.com/drizzle-team/drizzle-orm/blob/027921fda2ba61fd6271e49a576e1abd76613fc1/drizzle-orm/src/neon-http/session.ts#L216

Gaming

Programming

Transaction help

Did you find this page helpful?