Turso -schema DBs don't work with Goose migrations

[JordanMartinWebDev]

Description

When attempting to migrate a Turso schema database with Goose, the following error occurs: 2024/05/10 11:47:00 goose run: failed to execute SQL: BEGIN error code 500: Internal Error: migration error: Connection left in transaction state

From talking to Turso maintainer @avinassh in the Discord, this fails because Goose attempts to run migrations in a transaction, which the Turso schema DBs do not accept. I then attempted to migrate without Goose transactions as documented here: https://pressly.github.io/goose/blog/2022/overview-sql-file/#migrations-outside-transaction

This returns with the same error as before.

Suspected Problem:

Goose keeps track of migrations in a migrations table inside of a DB. It's believed that this table creation and insertion is likely running within a transaction and it causing this problem.

Link to troubleshooting Discord Thread: https://discord.com/channels/933071162680958986/1229116060859502716

[haaawk]

This is sort of expected. Turso multi database schema migrations are asynchronous. User has to call special API to wait for the migration to finish. I doubt Goose does that.

[JordanMartinWebDev]

I'm not sure of all the inner workings of Turso, but from 1000 foot view I would think this should work.

If the schema db is just a db that children propagate their schema from (and Goose is sending a non-transactional query), it should update that parent db and return successful. Then the propagation should happen from the schema db to the children asynchronously without Goose being involved at all.

I think the problem here is that Goose also has a side effect of sending transactional queries for their migration table, which from talking to avinassh the schema dbs don't support.

I could be very wrong here though.

[haaawk]

That's not the way it works. The call registers update tasks for all databases and then returns. The parent db is updated last.

I don't think this is a transaction issue. We wrap the schema change into a transaction anyway so this non-transactional query Goose produces ends up being one big transaction anyway.

[avinassh]

I am the cause for the confusion. I forgot about transactions being run async for migrations and I guessing it could be due to Goose editing migration table

[JordanMartinWebDev]

Sounds like where we have landed is this should be a feature request for Goose and not for libsql.

[haaawk]

Possibly yes but let us see if we can fix it on our side.