Batching statements in MySQL transactions

I'm using Drizzle with Planetscale - I have a transaction which kinda looks like this.

await tx.delete(review).where(eq(review.userId, session.user.id));
await tx.delete(session).where(eq(session.userId, session.user.id));
await tx.delete(account).where(eq(account.userId, session.user.id));
await tx.delete(user).where(eq(user.id, session.user.id));

await tx.delete(review).where(eq(review.userId, session.user.id));
await tx.delete(session).where(eq(session.userId, session.user.id));
await tx.delete(account).where(eq(account.userId, session.user.id));
await tx.delete(user).where(eq(user.id, session.user.id));

The thing is as far as I understand - for every step of the transaction a request is made to my Planetscale database which makes sense given I might need a result of this statement, maybe I need to know how many rows were affected. Because of this this transaction basically makes 4 separate requests to Planetscale. Is there a way to tell Drizzle to just batch this 4 statements as a single request? Given that I don't care about the result of a statement, only if it was successful or not - I only care that these 4 statements are executed in sequence. If that's not possible - is that a limitation of Drizzle, Planetscale or SQL in general?

15 Replies

Huge Letters•5mo ago

bump

Angelelz•5mo ago

If your queries don't depend on the one before, you can use promise.all to send them in parallel

Huge Letters•5mo ago

there's no guarantee they're gonna be done sequentially - they don't depend on the result of the ones before but they do depend on the correct execution order if we take my example there's no guarantee that this is what DB will execute

DELETE FROM `review`...;
DELETE FROM `session`...;
DELETE FROM `account`...;
DELETE FROM `user`...;

DELETE FROM `review`...;
DELETE FROM `session`...;
DELETE FROM `account`...;
DELETE FROM `user`...;

it might reorder them depending on how fast each request reaches the database. and in practice this just gives an error that another statement is already executing for this transaction so its locked

francis•5mo ago

drizzle doesn't expose pipelining (which is what I think you want)

Huge Letters•5mo ago

I've looked through the docs and saw they have batching for libSQL - which I think sounds what I need but for MySQL. I've played around a bit with sql`` operator and constructed a multiple statement request with it but it seems there's also a limitation on planetscale side and they don't accept it(report syntax error when you put ; in the statement). But anyways I was wondering if there's a simpler API in Drizzle for that

francis•5mo ago

there is not (I asked about this for postgres a few days ago and the conclusion we came to was there isn't one)

Huge Letters•5mo ago

I see, thanks!

francis•5mo ago

the pipelining mechanism is driver specific, and drizzle doesn't expose an interface for it you could drop down to the underlying driver for just this one case if you really need the speed

Huge Letters•5mo ago

I was thinking it's simpler than that and drizzle would just take multiple statements and concat them to something like "statement1;statement2;etc..."

francis•5mo ago

ah, I think I see the problem. sql servers do not accept statements like that.

Huge Letters•5mo ago

I see it would be harder for it then to retrieve the results of the query but in my scenario i dont care

francis•5mo ago

the splitting of statements on ; happens on the client side. if you send postgres or mysql a single statement that is actually two with a semicolon in the middle, it'll complain

francis•5mo ago

this is why e.g. for postgres, they had to explicitly add server support for pipelining (sending multiple statements at the same time to execute in order where you don't care about the results) in a new query protocol version: see https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-PIPELINING

PostgreSQL Documentation

55.2. Message Flow

55.2. Message Flow # 55.2.1. Start-up 55.2.2. Simple Query 55.2.3. Extended Query 55.2.4. Pipelining 55.2.5. Function Call 55.2.6. COPY Operations 55.2.7. …

Huge Letters•5mo ago

I see, thank you I was discussing this with a friend who is a python dev and he said their ORM batches transaction statements like I want - but he didnt know the specifics how that works. So I've assumed it's something more or a less trivial

!Stan_---__-•4mo ago

Rip I also need this for Planetscale :/ Hoping it gets added soon 🤞

Gaming

Programming

Batching statements in MySQL transactions