How do I make an Astro DB transaction?

[desert widget]

I'd like to be able to make a transaction like Drizzle does: https://orm.drizzle.team/docs/transactions#transactions
Apparently there is no such thing in Astro. Is this feature planned?

[manic geyser]

The closest thing AstroDB has is Batch transactions at the moment... in fact, There is actually an error built in that states "db.transaction() is not currently supported. We recommend db.batch() for automatic error rollbacks across multiple queries"

[desert widget]

so how do I rollback after a batch query ?

[desert widget]

if I'm not mistaken, the Astro documentation doesn't show a way to rollback a batch https://docs.astro.build/en/guides/astro-db/#batch-transactions

[manic geyser]

@wheat hound Sorry about the ping, but would you happen to have any insight on batch transaction(rollbacks)? i know you had used them in the past for your projects as you were the one who pointed them out to me when i asked.

"With the libSQL client library, a batch is one or more SQL statements executed in order in an implicit transaction. The transaction is controlled by the libSQL backend. If all of the statements are successful, the transaction is committed. If any of the statements fail, the entire transaction is rolled back and no changes are made."
This is from the Drizzle Docs linked at the bottom of the batch transaction section on the page you just linked... I think this is what the error meant by automatic rollback

[wheat hound]

Batch transactions are rolled back automatically if one of the statements fail

It's all or nothing

[manic geyser]

Oh and just in time for the wizard XD

[desert widget]

all right, it's perfect, thank you

[ember tendon]

@desert widget Have you been able to use batch with no problems? Because Im getting an error while im trying to make a conditional array of db.updates into the batch

[wheat hound]

Are you seeing the error at runtime or on your IDE / astro check?

[ember tendon]

I asked here trying to look for fixes, but you already fix it in the other ticket jasjs

[desert widget]

i've had no issues with batch

[ornate delta]

what if I need to create a record in table A and a record in table B that references it? with batch I cannot save an intermediate var.. isnt it?

[wheat hound]

Yeah, that's where an interactive transaction is needed. Batch transactions can't reference information generated from a previous operation in the batch.

Currently interactive transactions are blocked on Astro DB as an artifact from Astro Studio days. It will be available once we drop compatibility with Studio.

You can use it currently with a LibSQL backend by setting an env var, but you won't get proper typing for it sadly

[minor wadi]

Sorry for bringing up an old post, but could you expand on how transactions could be used by setting an "env var"? I would be ok with losing typing but it just seems wrong to not have a transaction in my scenario. If one of the queries fails the content in the DB will be incomplete and bits of site may break

[wheat hound]

You no longer need a separate env var. By using a libSQL backend, transactions will be enabled.

It's a normal Drizzle transaction.

It just won't be typed

So db.transaction as usual in Drizzle

[minor wadi]

Got you, do you think removing that Omit type declaration would seamlessly resolve the issue with types? I could try setting up a pr for that.

[wheat hound]

Sure. Now that Astro Studio is not an option that can be removed.