Fluentmigrator: Support for IDbConnections / factories instead of connection strings

[mbatista]

I have a need to connect to my Azure Sql database by utilizing authentication tokens, which cannot be provided in the connection string. Rather, they are a property of the Connection itself.

The request is that fluentmigrator supports injection of connection factories that generates connections for migration classes and migration runner.

[fubar-coder]

I propose a service that can do a pre-open and post-open configuration for a connection, as this would allow both changing the connection configuration and applying some commands directly after a connection gets opened.

We already had DB-specific factories, but I feel that they duplicate the existing infratstructure from ADO.NET (DbProviderFactory) and in the end, they seemed to cause more problems than solving them.

[Elycin]

+1 to this issue from me.
Looking to use a single database connection to handle migrations and for ServiceStack.ORMLite.

[huronbikes]

+1. This will be nice for working with a 3rd party application that recommends using their connection factory instead of a connection string.

[jzabroski]

@xrobotekx brought up in the Gitter chat on March 12th, 2020 that SQLite has an extension called SQLCipher which doesn't use a normal connection string to connect to the database. Rather, the user must issue a PRAGMA KEY = 'passphrase' command to decrypt the database.

[jzabroski]

Another related issue for Postgres, using SSL Certs: https://github.com/fluentmigrator/fluentmigrator/issues/968

[ankrause]

Being able to provide/specify a factory for IDbConnection may also help address transient error handling.

In most cases I wouldn't expect this to be a substantial issue for migrations given that they occur relatively infrequently. In the serverless Azure SQL scenario, however, a paused database will return connection errors to the first client(s) which are currently unhandled by FluentMigration / SqlConnection. Specific libraries such as EF might prevent this with their SQL/Azure transient error handling logic typically built into an IDbConnection object of some kind, but by default the FluentMigrator SqlServer implementations create SqlConnections with no additional retry logic that I can see.

The current solution to this would be to wrap the migrator in such logic. That said, many applications likely already have transient error handling strategies for their runtime non-migration connections which from what I see tends to be a "reliable" implementation of IDbConnection. It would be nice to provide this same logic to FluentMigrator.

Doc on resuming from pause here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-serverless#connectivity

If there's already a different recommendation on this that would be great as well.

[jzabroski]

@ankrause excellent suggestion. I started grooming this feature last month as you can see from my recent activity.

If you're interested in taking this on, right now the code is in not a good spot for outsiders to contribute given I'm moving stuff around projects a lot to improve packaging, testing, and .NET Core 3.1 support. I can ping you in about a month when it's ready for a new set of eyes.

The other related issue to all this is the question of who should own a connection. I was going to do a code spike proof-of-concept of a logical transaction manager to decouple us from concrete db connections. Effectively, we have a few minor (but important) bugs around connectionless runners. One issue is the connectionless runner assumes you don't want a transaction, so for people who want to print out a script file to hand to their DBAs to review and run, that sucks. The other dangling issue is it isn't clear who "owns" the connection in FluentMigrator and when it should be closed.

What I think I'd prefer to see is a UnitOfWork concept and register UnitOfWorkParticipants into the UnitOfWork. Something like:

c# interface IUnitOfWork { void Commit(); } interface IUnitOfWorkParticipant { void Enlist(); }

This would allow handling not just connectionless UnitOfWork, but also doing things like generating an audit report/snapshot. It might also be possible to enhance UnitOfWork to address refreshing any migration context data like the VersionInfo table. Awhile ago somebody added Migration Constraints as a feature and it has some holes in its design. In particular, the constraints are a snapshot as-of the TaskExecutor starting. So, the original use case was to only run a migration if a previous minimum version was met. But what happens if you run one migration, then another? Semantics of what is minimum during the middle of the run become murky, and you could miss that migration because its "view" is stale.