Can you run Grate programmatically like you could with RoundHousE?

[SteveMcGuire1]

We have an upgrader utility that references roundhouse.dll that I would like to migrate to Grate. We run scripts on multiple databases with RoundhousE like this:

var migrator = new Migrate().Set() //set all the props then
migrator.Run();

Is there any way to do the same with Grate?

[hoangthanh28]

Yes, you could. From the NuGet package, it adds up a README for brief guidance on how to do that. With grate version starting from 1.6, please check the doc here
Duplicated with #384

[SteveMcGuire1]

Thanks very much for the reply. I did find that doc toward the end of my day after posting my question and I am working on it today. Looks like this will work for me.

[SteveMcGuire1]

Got this working to a point but had some issues setting the ScriptsRun, ScriptsRunError and Version table names as I used to be able to in RoundhousE.

The GrateConfiguration() has these properties but setting them does not seem to have any effect unless I apply them in a specific way.

var config = new GrateConfiguration(); //set all properties (as I did with RoundhousE) then 
serviceCollection.AddGrate(config);

this seems to ignore many of the settings, does not execute scripts in SqlFilesDirectory and does not create any ScriptsRun tables.

If I use serviceCollection.AddGrate(builder => builder); as shown in the doc I do get the GrateScriptsRun tables and another set for the scripts run from SqlFileDirectory but they are just SchemaName.ScriptsRun.

I finally managed to get this to work as follows:

var config = new GrateConfiguration() {//set all the props here}

 var serviceCollection = new ServiceCollection();
 serviceCollection.AddLogging();
 serviceCollection.AddGrate(builder =>
 {
     builder
         .WithSqlFilesDirectory(sqlFilesDirectory)
         .WithConnectionString(connectionString);
 }).UseSqlServer();

 var serviceProvider = serviceCollection.BuildServiceProvider();
 var grateMigrator = serviceProvider.GetRequiredService<IGrateMigrator>().WithConfiguration(config);

 await grateMigrator.Migrate();

Notice the .WithConfiguration(config) which seemed to be the only way I could get the ScriptsRun table naming to work.
I now have the GrateScriptsRun tables for the internal updating (creating all the tables) which I don't really want or need and another set of _ScriptsRun tables.

I tried this with serviceCollection.AddGrate(config) with and without the latter .WithConfiguration(config) but this did not work and I didn't actually get any scripts run or any ScriptsRun tables created at all.

Reasoning
I have a single upgrader utility using RoundhousE that runs scripts for multiple Applications with one or more of these run on the same database so I want to see their relative script runs in separate tables with the Product name used. The same utility logic is used in a Windows Forms app and is also called from API web hooks and Azure pipelines to run against a single database or all (many hundreds) of databases. With the above I can now do what I have been doing with RoundhousE.

Hope this helps anyone who is trying to do the same and facing the issues I was.

[erikbra]

Hi, @SteveMcGuire1 . Thanks for writing up your findings.

Regarding the Grate* tables, they are something else than the ScriptsRun, ScriptsRunErrors and Version tables. I have created an issue on documenting this better, as it's confusing for people: #583

Your first attempt explained above should work for getting grate up and running. Which errors did you experience?

[SteveMcGuire1]

I have another issue with this in that when I run the process again for a different product and hence with a different name for the ScriptsRun tables it fails on script 02_create_scripts_run_table.sql with the error: Script has changed since the last time it was run. By default this is not allowed - scripts that run once should never change. To change this behavior to a warning, please set WarnOnOneTimeScriptChanges to true and run again. Stopping execution. but despite setting WarnOnOneTimeScriptChanges() and WarnAndIgnoreOnOneTimeScriptChanges() I still get this error and cannot create other Product tables. With RoundhousE I had WarnOnOneTimeScriptChanges = true and Silent = true to make this work.

The only workaround I can see for this is to delete the GrateScriptsRun tables every time I run the process on each database so this is ignored and the new tables are added or updated. If I don't it fails when I switch to another product and therefore table names, even if they already exist. It would be better if this were allowed and one of the above options did indeed ignore the issue and allow things to proceed.

I will try tomorrow using different schema names but I suspect I would have the same issue.

[erikbra]

Just to clarify: Are you trying to run multiple grate migrations, with different configuration, against the same namespace? I might have not thought of that people do this. This will create problems for the internal grate tables (used to handle versioning of the grate tables, not your tables), I think. I would recommend running against different namespaces or schemas