How to determine the current runner default schema from within a migration? about fluentmigrator HOT 11 CLOSED

I would suggest using dependency injection and inject the tenant schema name into every migration through some kind of Tenant service. I'm not supporting something like this out of the box. I think it's easy enough for you to build it yourself.

from fluentmigrator.

Okay, as I am using the dynamic default schema approach suggested here I tried doing this:

    public class InitialTenantSchema : Migration
    {
        private readonly IConventionSet _runnerConventions;

        public InitialTenantSchema(IConventionSet runnerConventions)
        {
            _runnerConventions = runnerConventions;
        }

This part works great, I get the DefaultConventionSet object with the desired target schema name that I created for the runner, no other custom infrastructure is needed.

Unfortunately the DefaultSchemaConvention class returned by DefaultConventionSet.SchemaConvention gives no public way to see what default schema name it is carrying.

Would you support this change to DefaultSchemaConvention?

--- a/src/FluentMigrator.Runner.Core/Conventions/DefaultSchemaConvention.cs
+++ b/src/FluentMigrator.Runner.Core/Conventions/DefaultSchemaConvention.cs
@@ -65,7 +65,7 @@ namespace FluentMigrator.Runner.Conventions
         /// default schema name is null or empty and returns the new default
         /// schema name when the <paramref name="originalSchemaName"/> is null
         /// or empty</returns>
-        string GetSchemaName(string originalSchemaName)
+        public string GetSchemaName(string originalSchemaName)
         {
             return _defaultSchemaNameConvention.GetSchemaName(originalSchemaName);
         }

With that change, I assume I could do this in my migrations:

var schemaName = _runnerConventions.SchemaConvention.GetSchemaName(string.Empty);

from fluentmigrator.

That's not what I'm suggesting.

I'm suggesting you can inject into every migration a tenant service and you can ask your tenant what it's schema is. Why tightly couple this to Fluentmigrator? Having a higher order abstraction you control allows you to do way more things while using Fluentmigrator as the mechanism for various tenants policies.

I think the only part I didn't think through in my initial reply is how you would manage VersionInfo table. In that sense, you probably need a custom convention set that also injects your tenants service.

I really think this is the way. Fluentmigrator already has all the hooks you need.

from fluentmigrator.

It may not be what you're suggesting, but it's a very simple and neat solution.

I would really like to avoid designing and building a lot of extra infrastructure just to transport one value that is already being provided to the runner, and that would be readily available via existing FM mechanisms if one private get method were made public.

Why tightly couple this to Fluentmigrator?

...because FluentMigrator is the tool being used to manage the database structure and the database schema name to use is an integral part of that? This seems an extremely reasonable ask. Why not make that one method public so that the information is accessible via native FM mechanisms?

Please don't focus on my specific use case example. Instead, is this a reasonable answer to the question "how does the migration determine the configured default schema?"

from fluentmigrator.

If you omit InSchema today, it will use whatever injected DefaultSchemaConvention you injected. You don't need to query it, except to write Execute.Sql scripts parameterized by tenants schema.

It might make sense to support template variables like {DefaultSchema} to make Execute.Sql as friendly as the rest of the API. Then you would not need to inject anything so your migration code will read more like a conversation with your database about up and down migration and less about technical plumbing like dependency injection. No explicit constructors, no private variables to house dependencies.

Is there some feature I'm missing this idea would not cover?

from fluentmigrator.

I'm not using InSchema() at all, I'm relying on the default schema provided to the runner.

You don't need to query it, except to write Execute.Sql scripts parameterized by tenants schema.

Which is exactly the problem I'm trying to solve.

It might make sense to support template variables like {DefaultSchema} to make Execute.Sql as friendly as the rest of the API.

I was indeed contemplating that as a possibility but omitted it from my initial question for simplicity's sake. Supporting template variables / token substitution in Execute.Sql() with a predefined list of useful tokens, permitting something like Execute.Sql("ALTER TABLE $(DefaultSchema).mumble ADD ...");, would absolutely cover this need. 👍

Are template variables / substitution tokens already supported in Execute.Sql()? Perhaps related to this or this?

If so, using that capability is not well documented. Also, it's not useful for this if the migration itself has to define all of the substitutions - there would still need to be some mechanism for the migration to retrieve the default schema name to include in the substitution map. The token replacer would instead need to reserve some predefined tokens like DefaultSchema and populate them from FM hidden data and any replacement map the migration provided would be in addition to that.

from fluentmigrator.

Remind me, as a short term solution, why can't you inject IDefaultSchemaNameConvention https://github.com/fluentmigrator/fluentmigrator/blob/main/src%2FFluentMigrator.Runner.Core%2FConventions%2FDefaultSchemaNameConvention.cs ?

I don't think you need any changes.

With regards to documentation, PR to fluentmigrator/documentation on best practices standing up a tenant per schema solution would be welcome

It looks like PR #643 to add tokens never got the logic merged into main so it was lost with time and forgotten about.

from fluentmigrator.

I'll try that and see what happens, thanks.

from fluentmigrator.

Okay... Doing this:

public class InitialGlobalSchema : Migration
{
    private readonly string? _schemaName;

    public InitialGlobalSchema(IDefaultSchemaNameConvention schemaConvention)
    {
        _schemaName = schemaConvention.GetSchemaName(null);
    } 

works if you do this:

return new ServiceCollection()
    .AddScoped<IConventionSet>(_ => new DefaultConventionSet(schemaName, null))
    .AddScoped<IDefaultSchemaNameConvention>(_ => new DefaultSchemaNameConvention(schemaName))  // <- ADD
    // Add common FluentMigrator services
    .AddFluentMigratorCore()
    .ConfigureRunner((rb) => rb
        ...

While a little ugly because of the explicit second copy of the schema name, this is acceptable as a temporary solution until native template token replacement is implemented for Sql.Execute() or until DefaultSchemaConvention.GetSchemaName() is made public.

from fluentmigrator.

Create an Autofac-style Aggregate Service by hand and inject that into a common migration base class. Let the container resolve that. I say by hand because it's MSEDI and not Autofac.

Just how I would do it.

from fluentmigrator.

Yeah, I will likely make a base class for that to neaten things up, but long term I would still much rather see the default schema name available natively via FM.

from fluentmigrator.