Generate migration script without connection string

[DuncanvR]

Context

We've got an ASP.NET Core app using EF Core (v3.1.4) to connect to an Azure SQL Server, which is being built and deployed in Azure DevOps. To update the database during releases, I'd like to:

1. run dotnet ef migrations script --idempotent -o script.sql in the build pipeline;
2. store the resulting script.sql as a build artefact;
3. retrieve the script in the release pipeline and execute it against the database.

Problem

The problem occurs in the first step, when generating the migration script in the pipeline, as an exception is thrown because the database connection string is not specified. We've set up the app to read the connection string from an environment variable, which isn't defined in the pipeline. I could add it there -- but I'd rather not, because it involves entering the database password -- while I don't understand why the tool needs it.

I can see it needs to know which database provider is used in order to create the correct SQL statements. But to actually generate the script, the information contained in the app (i.e. the migrations themselves) should be sufficient, right?

Attempts

I've tried specifying the following values as connection strings:

1. When null is passed as the connection string, this exception is thrown:

   System.ArgumentNullException: Value cannot be null. (Parameter 'connectionString')
      at Microsoft.EntityFrameworkCore.Utilities.Check.NotEmpty(String value, String parameterName)
      at Microsoft.EntityFrameworkCore.SqlServerDbContextOptionsExtensions.UseSqlServer(DbContextOptionsBuilder optionsBuilder, String connectionString, Action`1 sqlServerOptionsAction)
      ...
   

2. If the connection string is an empty string, this very similar exception is thrown:

   System.ArgumentException: The string argument 'connectionString' cannot be empty.
      at Microsoft.EntityFrameworkCore.Utilities.Check.NotEmpty(String value, String parameterName)
      at Microsoft.EntityFrameworkCore.SqlServerDbContextOptionsExtensions.UseSqlServer(DbContextOptionsBuilder optionsBuilder, String connectionString, Action`1 sqlServerOptionsAction)
      ...
   

3. However, when I pass ";" as the connection string, the tool happily generates the migration script.

Suggestion

I realise the tool instantiates the database context using the code from the application. And for the application itself it can be useful to get a warning about the connection string being empty. But when generating the migration script, leaving it empty should be possible.

I'd therefore suggest changing Microsoft.EntityFrameworkCore.Utilities.Check.NotEmpty() to no longer throw an exception when it finds the given connection string is null or empty, but to print a warning instead.

[DuncanvR]

This seems related to #19587.

[roji]

Likely a duplicate of #8427, which introduces parameterless UseSqlServer/UseSqlite without requiring a connection string.

Note that as a workaround, you can specify some non-null non-empty connection string, which would not be consulted for generating the script. As long as you don't actually need a database connection (which you don't for script generation), this should work.

[DuncanvR]

Thanks @roji! A parameterless version of UseSqlServer() would be even better, indeed. The issue you linked has been closed though, but version 3.1.4 does not have such a method.

[roji]

@DuncanvR yeah, #8427 has been done for 5.0.0-preview1 (check out the milestone).

[DuncanvR]

Right you are; I had missed that. Then I'm happy to close this issue, as it has been addressed.

[roji]

Duplicate of #8427

[slubowsky]

@roji Can now generate when UseSqlServer with no connection string - but unable to remove migration...

dotnet-ef migrations remove
...
System.InvalidOperationException: A relational store has been configured without specifying either the DbConnection or connection string to use.
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.get_DbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation[TState,TResult](Func`3 operation, Func`3 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.GetAppliedMigrations()
   at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsScaffolder.RemoveMigration(String projectDir, String rootNamespace, Boolean force, String language)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.RemoveMigration(String contextType, Boolean force)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.RemoveMigrationImpl(String contextType, Boolean force)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.RemoveMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
A relational store has been configured without specifying either the DbConnection or connection string to use.

[smitpatel]

Remove migration checks if the migration has been applied to database or not. Hence it needs actual connectionstring.
You can use --force if you are confident that you don't need to revert the migration on database.

[slubowsky]

@smitpatel That works and I guess that makes sense, thanks

[slubowsky]

@smitpatel - but update fails unless I put in a dummy connection string - even though I am assigning my connection string dynamically later. Currently I am doing that with a custom ISqlServerConnection implementation but that should be possible in a better way in 5 though I haven't explored that yet.
--force doesnt help for update

[smitpatel]

Update applies migration to database, it needs actual connection string when you call Update. You can generate migration script and apply it directly to database outside of EF.

[slubowsky]

@smitpatel Right. But it is getting the connection string dynamically - currently following approach described here - #19906
Was hoping to remove dummy connection string passed to UseSqlServerbut apparently not possible at this time.

[smitpatel]

#8494 is fixed in 5.0 preview1. Are you using 5.0 preview version? Also, how are you dynamically replacing connection string? If your mechanism is also not in path of how design time services work then you may not be actually replacing connection string. Please file a new issue with details.

[slubowsky]

@smitpatel Im ok with leaving the dummy connection string for now so not going to bother with another issue - but if your curious using 3.1 I am doing exactly what is described by the author in the first entry for issue at #19906

A later comment says "Something similar will be available in 5.0.0" but while I am now beginning to explore new 5.0 features, I have not yet attempted to change how Im dynamically setting the connection string. My first thought when switching to 5.0 was to simply get rid of the unused dummmy connection string passed to UseSqlServer as 5.0 makes that possible but apparently I cant do that. Perhaps if there really is a better approach to dynamically set a connection string when using a DbContextPool available in 5.0, it wont need the dummy connection string, I hope that is the case.

[douglasg14b]

This may be fixed for create a script, but it doesn't work for removing a migration...

[MikeK93]

Hello @DuncanvR. I faced the same issue and did not get what was your solution to generating a migration script without a connection string.

I did use parameterless but the script looks like that:

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

@smitpatel there are scenarios where you have no access to the production database but the migration script still needs to be generated.

So to me, the question is still relevant. How to generate migration script without connection string?

[DuncanvR]

@MikeK93 I don't think you are facing this issue. You say you are using the parameterless overload of UseSqlServer() and you show a generated migration script. If that doesn't contain what you expect, that could be due to a myriad of reasons -- but not because the script isn't being generated.

[MikeK93]

@DuncanvR, that is what I'm talking about. There's no easy way to generate a script with no actual connection to the real DB so that I could give that script to Ops guys. I guess EF could have some option to just generate that migration script and I don't think that would require much effort but the outcome of it would be huge.

[smitpatel]

@smitpatel there are scenarios where you have no access to the production database but the migration script still needs to be generated.

And we support it through parameterless overload of UseSqlServer.

You posted the migration script generated but you are not telling anything about why you consider it to be wrong. The fact that it has generated script means that the script generation works fine. If the output is not as you expect then it is some other issue in your app config somewhere. Please file a new issue with detailed steps and repro code and what exactly you are expecting vs seeing.