Use custom `Session` to run migrations

[taig]

I'm using a custom skunk Session builder to connect to Postgres via Unix Sockets on Google Cloud Run. Dumbo unfortunately doesn't expose a way to run migrations with my own Session, so I'm currently unable to run migrations in production environments.

[rolang]

hi @taig I'll have a think about that, should be doable...
Migrations worked previously by passing a skunk.Session instead of connection config, I changed it because some session configs needed to be set in dumbo like search_path and typer strategy, but those could be set otherwise. The other idea was also not to expose the skunk interface as the underlying database library could potentially be a different one.

[taig]

Makes perfect sense. Personally, I wouldn't mind a public unsafe method that expects the Session to fulfill the necessary preconditions.

[rolang]

I wouldn't mind a public unsafe method that expects the Session to fulfill the necessary preconditions.

@taig I think it doesn't even need to be marked as unsafe. The typer strategy actually doesn't matter for migrations, you should not need to set it to anything else other than BuiltinsOnly, but it will also work with the other.
One thing of concern would be the search_path you can set via session parameter. This setting will be relevant when you are working with schemas other than the default one (public) or multiple schemas.

It seems that you can verify and override it after the session was initiated via SHOW / SET command.
I added this step in the PR with a hint in the logs that you may want to pass it as session parameter instead before overriding. Should also be all covered with tests.

Updated the docs with how to use it with a custom session. Feel free to drop any questions or comments in the PR if you have time to go over it. I think I am going to merge it soon.

[taig]

@rolang Thank you very much for tackling this. I just checked out your PR locally and gave it a try. I couldn't test it in the actual production environment, but locally the new API worked just fine.

I still got a couple more questions that are not directly related:

1. I have a bunch of SQL sample data that I have to send to the database sometimes. I noticed that you had a similar problem feeding the migration files into postgres via skunk. So I'm planning to take your code as a starting point to solve that. I'm just mentioning this in case you think it could make sense to expose APIs for this in dumbo (it's probably a bad idea).
2. I find dumbo's macro based resource loading with Scala 3 a bit confusing. Is there a technical limitation that requires this? I actually ended up scanning the classpath for resources myself and then using the Dumbo.withResources builder. I have migration files that are embedded in dependencies and I couldn't load them with the macro API.

[rolang]

@taig

1. I think I'd not expose anything that doesn't work in combination with the migration history state. For dumbo I raised this PR to get session.executeDiscard(...) for executing any kind of statements like content from a migration file. You maybe want to try to use that instead, if the execution of the SQL samples is not related to the migration history state. Example usage looks something like this:

val mySql: String = "SELECT 1; CREATE TABLE foo(); -- anything else"
session.executeDiscard(sql"#${mySql}".command)

Dumbo also supports repeatable migrations if it could fit your use case.

2. I see, yes the limitation was Scala Native support, you can't list embedded resources from a location at runtime like on JVM, and this way worked across platforms. I got this idea from the implementation in indoorvivants/roach. It actually also has the benefit of giving you more guarantees at compile time.
   I haven't tried to use it with resources that are shipped with another package as dependency. I usually have dumbo placed in the same package with the resources which can be used as dependency somewhere else.
   I will need to check if it can somehow work with macros as well, otherwise one could also add the Scala 2 implementation to the JVM version. I would raise another issue for that.

[rolang]

Custom session constructor is released with v0.5.0.
Will close this issue.

I have migration files that are embedded in dependencies and I couldn't load them with the macro API

Raised #86 for that.

[taig]

Thank you very much for the release. I'm looking forward to adapting dumbo in the next days and get rid off all the jdbc & flyway cruft 🙏

1. Running my sample files through session.executeDiscard seems easy enough (-:
2. In my case, I definitely would appreciate having the Scala 2 implementation available for the JVM. But I got it working already, so it's no big deal either.