N
Neon•14mo ago
automatic-azure

Transactions with Drizzle

I am a developer working with Drizzle, Neon, and Vercel in an edge deployment environment. I am experiment with Neon db in a personal project after using Vercel PG for a while. Considering that Vercel PG uses Neon, I expected that there would be little issue with swapping out the drivers. However, I was a bit confused when my transaction I ran with the http driver failed. I did a bit of researching and looking through this forum and it seems that the http driver does not support transactions. While I think it is clear enough with how to change from http to websocket, I have a few questions I would like answered: 1. Why is it that http transactions are not supported? I am nowhere near a networks expert so I am genuinely interested in what the challenges / reason are for why it has not been added when it is such a popular functionality of sql. 2. Is there a current roadmap to add support for http transactions? 3. When working in my environment described above, what do I lose in terms of performance and DX when I make the change from http to websocket? 4. Can use the http driver for everything non-transactional and the websocket only when needed? Thanks for your time :)
4 Replies
xenial-black
xenial-black•14mo ago
Why is it that http transactions are not supported
Transactions require an open, stateful connection. This is something that HTTP doesn't provide
Can use the http driver for everything non-transactional and the websocket only when needed?
Yes
automatic-azure
automatic-azureOP•14mo ago
Great thanks for the help! So just to confirm, I can have something like: 
import { Pool, neonConfig } from '@neondatabase/serverless';
import ws from 'ws';
neonConfig.webSocketConstructor = ws;

const pool = new Pool({ connectionString: process.env.DATABASE_URL! });
export const dbWebSocket = drizzle(pool);
const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });
import { Pool, neonConfig } from '@neondatabase/serverless';
import ws from 'ws';
neonConfig.webSocketConstructor = ws;

const pool = new Pool({ connectionString: process.env.DATABASE_URL! });
export const dbWebSocket = drizzle(pool);
const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });
and only use the websocket object when I need transactions? Or will this cause some sort of issues?
xenial-black
xenial-black•14mo ago
Yes
xenial-black
xenial-black•14mo ago
https://neon.tech/docs/connect/choose-connection I recommend going through this guide
In serverless environments such as Vercel Edge Functions or Cloudflare Workers, WebSocket connections can't outlive a single request. That means Pool or Client objects must be connected, used and closed within a single request handler. Don't create them outside a request handler; don't create them in one handler and try to reuse them in another; and to avoid exhausting available connections, don't forget to close them. See Pool and Client for details.
Neon
Choosing your driver and connection type - Neon Docs
When setting up your application’s connection to your Neon Postgres database, you need to make two main choices The right driver for your deployment &8212; Neon Serverless driver or a TCP based driver...

Did you find this page helpful?