Using tzio with single connections

[frekw]

Do you have a use case where you can't get a DataSource? Notify me by creating an issue!

Ok, here we go! :)

In my application, I make use of Postgres advisory locks. They can either be tied to a session or a transaction, and will be freed automatically in both cases.

My use case is using advisory locks for a session (connection), in order to make sure that a background worker is only run on a single application instance in order to avoid race conditions.

When I was using doobie without tzio I could simply create a Transactor wrapping a single sql.Connection, and thus get excellent control over the scope of my locks. However, since tzio builds upon DataSource, doing this seems a bit tricker. What I'm currently thinking is to write a custom implementation ConnectionSource.Service that just no-ops e.g closeConnection and handle the setup/teardown solely in the ZLayer initializing the connection, but it would be handy if it was easier to go from a single sql.Connection to a tzio.Database in the (few) cases that's useful.

Another use case I can think if is LISTEN / NOTIFY in Postgres, which is also maintains per-connection state. Even if it doesn't work great with jdbc.

[gaelrenoux]

Creating a simple Database (and/or a ConnectionSource) based on a single connection is possible, but it would be quite dangerous: it would behave unpredictably whenever it is used in concurrent threads. In order to do this properly, I would need the Database to enforce some kind of lock on the unique connection. I'll take a look at ZIO's Semaphores, they might be the solution.

There's also a method that provides a tzio Connection from a JdbcConnection: Database.connectionFromJdbc. I didn't document it because it's mostly an internal method, but it's public. Would that solve your use case?

[frekw]

Hi!

Yes, you're right, in order to expose it as a public API it would make sense to wrap it in some lock in order to remove gotchas.

I completely missed Database.fromJdbcConnection when digging through the code. That sounds exactly what I was looking for, I'll give it a go!

Thanks for a great library. Feels really refreshing to not have to deal with EitherTs all over the place!

[gaelrenoux]

So I tried it out, and I ended up coding it this afternoon ^^ It's only in master for now, but you have a layer ConnectionSource.fromConnection (then you'll need to use Database.fromConnectionSource to get the DB).