planetscale session ID is not preserved

[anszom]

I've encountered some problems when using kysely's migration module. The migration process starts by acquiring a global database lock (SQL GET_LOCK) and releasing it afterwards (SQL RELEASE_LOCK).

The lock is scoped to the "connection", so releasing the lock must happen inside the same connection, otherwise it has no effect. This by itself works correctly with the HTTP-based @planetscale/database driver, because it maintains a session ID within the Connection object.

Unfortunately, kysely-planetscale acquires a new connection for each request. In effect, database locks remain in place until they time out after 20 seconds.

It's likely that useSharedConnection would help, but that feature doesn't work at all (#40), and the README implies that it is not to be relied on.

[jacobwgillespie]

Hey! Do you know how kysely's migration module is handling connections? The main issue with useSharedConnection (and why it's accidentally doing nothing now) is that uses a single connection for all queries, which both prevents parallel queries as well as mixes things like SET with queries that aren't intended. It basically collapses everything into a single connection.

There was some discussion about this here: #33 (comment)

This driver already handles creating a new persistent connection for transactions, so if kysely's migrations were run in transactions I think it would work. But maybe there's some other way for kysely to request a persistent connection for a migration?

[anszom]

I'm relatively new to kysely, but I've ran some simple tests and I've noticed that:

1. For standalone queries, kysely requests a new connection for each query
2. For transactions, kysely runs all of the nested queries on one connection
3. The same happens for migrations, the whole lock/unlock sequence runs on a single connection

So the driver doesn't need to do anything smart at all. Simply using a new Planetscale.Connection for each acquireConnection is all that is needed to achieve the desired results.