Dev experience: prevent needed API restart when dropping & rebuilding the db

[th0rgall]

Jeff:

One annoying thing though is having to shut down the API server and close any other connections to drop the database

Thor:

I restarted the API a few times while developing. Got confused whether the API server was blocked, or the DB server was erroring. So can't tell for sure yet!

Jeff:

I couldn't say for sure either! In Readup's case though Npgsql (the C# library) does the connection pooling so it will leave open and reused "physical" db connections to improve performance... but does come with downsides like the persistent connections being left open for a time.

Might not even have to if Npgsql is smart enough to just notice that a connection has been closed by the server (when the db is dropped) and open a new one during the next request. Seems like it would be pretty reasonable behavior.

Thor:

After further debugging, I'm pretty sure this doesn't happen. However, maybe it could happen. The Npgsql seeder script I wrote doesn't experience issues when database is reset, maybe there is some Npgsql configuration possible where you could disable the connection pooling, that would always start fresh connections each time in case of a database drop. We could enable this selectively only for development, in case we need to iterate a lot on the DB schema/test migrations.