Next.js, Drizzle and Supabase DB with transactions?
Hey,
I really appreciate the value of transactions. They grant safety when handling multiple changes at once.
I am setting up my Next.js project. I want to use Drizzle to handle my tables in my code.
I don't like that Supabase doesn't really support transactions (only with these sql functions).
There is this drizzle doc: https://orm.drizzle.team/docs/connect-supabase
But as I don't have a lot of knowledge about DBs, this isn't totally clear for me which way I should set it up when using Next.js and wanting to support transactions.
Can you please help me?
Drizzle ORM - Supabase
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
8 Replies
I'm confused by what you are asking
can you not do e.g.
const allUsers = await db.transaction(async (tx) => { tx.select(....;?
just add transactions in their selection examples
supabase absolutely supports transactions, just not with their postgREST apiMy question is:
In the docs, there are 3 ways to setup supabase (the last 3 code blocks):
https://orm.drizzle.team/docs/connect-supabase
Which one should I use for using Next.js and to support transactions? And does it comes with drawbacks?
Drizzle ORM - Supabase
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
depends on how you deploy. for nextjs serverless you want connection pooling at the transaction level
Sorry but I don't really understand what that means, could you please clarify?
What's is the transaction level?
Connect to your database | Supabase Docs
Connect to Postgres from your frontend, backend, or serverless environment
you want session mode for persistent servers, or transaction mode for serverless
Connection pooling is one of the harder concepts to deal with when learning SQL initially.
Basically, SQL servers typically don't communicate over HTTP but with sockets (like websockets). You can think of HTTP as like sending a text message versus sockets are making a phone call. Those sockets are resource expensive and take a while to start and stop, but you can keep them around to do more than just one thing at a time and give you safety for things like transactions. Databases have a limit to how many sockets or "phone calls" they can have open at a given time. So, you use a pooler to manage those open socket connections for you.
When you're running in a serverless environment your code boots up and exits quickly and often, so poolers keeps open sockets around for you to quickly reuse.
You'll need to choose the transaction mode based on how you are running your code (serverless vs serverfull) because it affects how those connections/sockets will be pooled.
Hope this helps understand the difference a bit
Many thanks, @francis and @SKUZZIE !