Updating different database via CLI (Pass connection string to dotnet ef database update)

[drosskopf]

The EF Core CLI, does not allow me to switch databases via command line. It would be great, to either specify the appsettings via cli to pull the data from, or to specify the connection string via cli parameter.

[ajcvickers]

Notes from triage: this is something we are considering for the future. It may depend on #6525 before it can be implemented. For now, the way to do this is to pull the connection string from configuration and either change the configuration for each run or use something like environment variables.

[urza]

Notes from triage: this is something we are considering for the future. It may depend on #6525 before it can be implemented. For now, the way to do this is to pull the connection string from configuration and either change the configuration for each run or use something like environment variables.

Could you please elaborate on the idea of using environment variables a little bit please? How whould you use that?

I have one Model (DbContext) and 10 databases created with this context. In runtime I create the Context with constructor that takes the database name as parameter and puts that into connection string in OnConfiguring method.

Now I would like to create migration and update all 10 databases.

My idea was to create cli migration script but fot that I would need the migration commant to accept connection string parameter (this got me to this issue). I am not sure how I can use environment variables for that...

[ajcvickers]

@urza The idea is to change something in a script that can be read inside your code. So, for example, set an environment variable that contains the connection string, then run database updates in a loop changing the value of the environment variable each time.

[diegosasw]

Notes from triage: this is something we are considering for the future. It may depend on #6525 before it can be implemented. For now, the way to do this is to pull the connection string from configuration and either change the configuration for each run or use something like environment variables.

Is there a way right now to have the startup-project configure the connection string so that when the dotnet ef database update --startup-project ExecutorProject runs the connection string is passed to the DbContext?

I don't fully understand what's the need of the startup project other than targeting the .net framework. In fact:

1. If the startup-project does not contain any class at all, it's not possible to create migrations.
2. If the startup-project contains a Program.cs, even if empty, the migration can be created
3. If the startup-project contains a Program.cs with the following:

class Program
{
    public static void Main(string[] args)
    {
        throw new Exception("why does this not mess up the migration?");
    }
}

Then I would expect the migration creation to fail. But it succeeds. So the dotnet ef does not seem to care about the content of this startup-project and therefore..
how could I change the connection string at runtime through settings or environment variables?
I am afraid I share @MarcinJuraszek 's concern. I don't see how a setting or environment variable can be used.

PS: To better understand my question. I just closed this: #19228
Sorry, it's the first time I use EF in long time and I am just looking for a way to make the connection string configurable for dotnet ef CLI migrations.

[diegosasw]

If all the dotnet ef CLI cares about is "being executed from" a specific .net framework (i.e: the one defined by the --startup-project and that's why we cannot use dotnet ef with netstandard libraries) , but does not seem to run its entry point (e.g: Program.cs), why not to remove this need altogether and have something like:

dotnet ef migrations add InitialCreate --framework netcoreapp3.1

that should be available somewhere in the PATH

and then have something such as

dotnet ef database update --framework netcoreapp3.1 --connectionString "D:\foo\database.db"

Later on, whether some env variable or appsettings is used to source this parameters for the CLI, that shouldn't be a problem imo.

[znelson32]

I have an EF Core 3.0 project that contains EF entities and DataContext. It's part of a larger framework which includes DI. The connection string will be handled through that app framework and supplied in a normal fashion. However, to even create an initial migration in the EF project there has to be a connection string. If I can't pass it as a command line arg to dotnet ef migrations add then I have to hard-code it inside the data context - that makes no sense.

How did we get to this point with EF where we absolutely must have a program.cs with an appconfig json with a connection string inside it just to set up and maintain EF in a separate library? I understand the need to talk to a database obviously but I should be able to supply the connection string on the command line itself, not require all kinds of Core plumbing in disparate projects within the same solution just to get the add migration command to work.

[jstallm]

The solution for me here was to temporarily update the Startup.cs file within the project which depends on EF Core and manually set the connection string to the connstring specific to the env, then run
dotnet ef database update

I then just repeated the process of updating the connection string for the next env, and reran dotnet ef database update

[robjackstewart]

Similiarly, I needed this feature to faciliate running migrations as a step in my DevOps Pipeline, but retrieveing the connection details from a keyvault, rather than leaving them in a file in source control so I ended up leveraging the Program.cs to do this for me:

using System;
using System.Linq;
using Microsoft.AspNetCore;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using WebApi.Data.DbContexts;

namespace WebApi
{
    public class Program
    {
        const string runMigrationArgument = "runmigration";
        public static void Main(string[] args)
        {
            var webHost = BuildWebHost(args);

            if (args.Any(x => x.ToLower().Replace("-", string.Empty).Contains(runMigrationArgument)))
            {
                MigrateDatabase(webHost);
                return;
            }

            webHost.Run();

        }

        public static void MigrateDatabase(IWebHost host)
        {
            var services = (IServiceScopeFactory)host.Services.GetService(typeof(IServiceScopeFactory));

            using (var scope = services.CreateScope())
            {
                var db = scope.ServiceProvider.GetRequiredService<MyDbContext>();
                db.Database.Migrate();
            }
        }

        public static IWebHost BuildWebHost(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseApplicationInsights()
                .UseIISIntegration()

                .ConfigureLogging((hostingContext, logging) =>
                {
                    logging.AddConfiguration(hostingContext.Configuration.GetSection("Logging"));
                    logging.AddConsole();
                    logging.AddDebug();
                    logging.AddEventSourceLogger();
                })

                .UseHealthChecks("/health", System.TimeSpan.FromSeconds(3))     // Or to host on a separate port: .UseHealthChecks(port) // Add Nuget - Microsoft.AspNetCore.HealthChecks
                .UseStartup<Startup>()
                .Build();
    }
}

This way you can set the connection string as a standard argument to the dotnet run command like so dotnet run --Data:ConnectionString="<insert connection string>" --run-migration.

Note: This will also run with every argument style for like run-migration, --run-migration, runMigration etc.

Hope this helps in the mean time

[mhosman]

In my case I have a multi-tenancy app with database-per-tenant approach. How can I run migrations since connection strings are retrieved dynamically by host? (Now I'm hardcoding a connection string just for migrations). Maybe it could be great to create migrations without the need of a connection string in order to create the code. Later, an update-database with a new optional field, let's say --connection-string. This opens the possibility to create a script and apply a specific migration to the connection strings I want.

[H-a-w-k]

I ended up with
[System.Environment]::SetEnvironmentVariable("ConnectionStrings:Default", "YourString")
dotnet ef ...

and in startup using
_config.GetConnectionString("Default")

[Quietscheente]

A Workaround that works good for me:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)

                    {
                        string connectionString = "-";

                        try // raises an exception when input is piped
                        {
                            if (!Console.KeyAvailable)
                                connectionString = "-";
                        }
                        catch
                        {
                            // blocks when input is not piped
                            if (Console.In.Peek() != -1)
                                connectionString = Console.ReadLine();
                        }

                        optionsBuilder.UseOracle(connectionString);
                    }
                    
        }

With CLI:

echo Data Source=XYZ; User Id=ABC; Password=123 | dotnet ef ...