--drop without continuing migration use case

[Jantero93]

EDIT: Tried rewrite whole post with better english

• grate v1.7.4
• Entity Framework Core v8.0.4

I have a use case where I need to drop a database without continuing the migration. Specifically, I have a database rebuild script that first drops the database. I want to handle creating the database with Grate (setting collation, etc.) on SQL Server.

So my flow on database rebuild

1. Only drop and create database (I want --drop flag without further migrations applied)
2. Run EF Core Migrations
3. Run rest Grate migrations

So my ultimate goal have

RebuildDatabase.sh

containining (WITH SINGLE GRATE FOLDER) something like

grate --folders=./grate \
--connectionstring="example-database" \
--drop

dotnet ef database update

grate --folders=./grate \
--connectionstring="example-database" 

Now my workaround is to create set Drop & Create database script different folder and run them from master db (I can't use --drop flag, because that would trigger migrations before Entity Framework Core migrations. And when I dont use --drop, grate will not trigger scripts in dropDatabase folder)

So my current setup is something like this (example)

# Step 1: Drop and create the database using Grate
grate --folders=./grate/initDb \
--connectionstring="SQL-server-master-database" 

# Step 2: Entity Framework migrations
dotnet ef database update

# Step 3: Rest migrations
grate --folders=./grate/migrations \
--connectionstring="example-database" \

Then my another ultimate goal is have UpdateDatabase.sh what would contain

dotnet ef database update

grate --folders=./grate \
--connectionstring="example-database" 

[Jantero93]

I know it's kind of strange to have two db managment tool. But Im only using Entity Framework Core with OpenIddict related tables because EF can handle migration automatically. Otherwise I want use only grate and handle db calls manually with Dapper etc.

[erikbra]

Hi, @Jantero93 - to try to generalise a bit, how do you run your EF migrations? It looks like the recommended way from Microsoft to run EF migrations in production is to generate SQL scripts:

https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/applying?tabs=dotnet-core-cli

It even looks like they have some functionality similar to grate, with the Idempotent SQL scripts (same page, further down).

Microsoft says in this same page:

The EF command-line tools can be used to apply migrations to a database. While productive for local development and
testing of migrations, this approach isn't ideal for managing production databases:

The SQL commands are applied directly by the tool, without giving the developer a chance to inspect or modify them. This can be dangerous in a production environment.
The .NET SDK and the EF tool must be installed on production servers and requires the project's source code.

I would recommend:

• As part of your development or CI workflow, generate SQL scripts from EF migrations, using dotnet ef migrations script (optionally with from and to parameters
• Store the scripts in the grate up folder, numbered in ascending order (maybe in a sub-folder per generated migration if there are many scripts generated), so that each migration will be applied in the correct order when you run grate

Depending on the rest of your grate scripts, where they fit in with the EF migrations, you might want to use another folder than up for the EF migrations scripts, if you always want to run these before the other migrations. But I would definitely recommend using a one-time-scripts type of folder for EF migrations, as this fits well with how EF migrations work.

You can customise the grate Folder configuration completely, if you want either a simpler or more complex folder structure than the default: https://erikbra.github.io/grate/folder-configuration/