Idempotent migrations (similar to EntityFramework)

[ctrlaltdan]

I have seen an open pull request #664 (from 2015) relating to idempotent SQL script generation and was wondering what the current thinking is on this?

Ideally we'd like to see the dotnet fm cli behave in a similar way to the dotnet ef cli and be able to output a single idempotent migrate script.

https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet#dotnet-ef-migrations-script

We've got the following producing a SQL file against the Quickstart on your docs although the SQL file output doesn't build the VersionInfo table, nor are there any checks if Version exists in VersionInfo then...

We were wondering if there was any scope to introduce this as a feature?

CLI

$ dotnet fm migrate -p sqlserver --no-connection -a TestMigrations.dll --preview --verbose -o
-------------------------------------------------------------------------------
PREVIEW-ONLY MODE
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
20200618121800: AddLogTable migrating
-------------------------------------------------------------------------------
CreateTable Log
CREATE TABLE [dbo].[Log] ([Id] BIGINT NOT NULL IDENTITY(1,1), [Text] NVARCHAR(255) NOT NULL, CONSTRAINT [PK_Log] PRIMARY KEY ([Id]))
=> 0.0722851s
INSERT INTO [dbo].[VersionInfo] ([Version], [AppliedOn], [Description]) VALUES (20200618121800, '2020-06-18T16:28:18', N'AddLogTable')
20200618121800: AddLogTable migrated
=> 0.1398978s
Task completed.

SQL:

/* PREVIEW-ONLY MODE ========================================================= */

/* 20200618121800: AddLogTable migrating ===================================== */

/* CreateTable Log */
CREATE TABLE [dbo].[Log] ([Id] BIGINT NOT NULL IDENTITY(1,1), [Text] NVARCHAR(255) NOT NULL, CONSTRAINT [PK_Log] PRIMARY KEY ([Id]))
GO
INSERT INTO [dbo].[VersionInfo] ([Version], [AppliedOn], [Description]) VALUES (20200618121800, '2020-06-18T16:28:18', N'AddLogTable')
GO
/* 20200618121800: AddLogTable migrated */
/* Task completed. */

[jzabroski]

@ctrlaltdan Hi Dan,

I have seen an open pull request #664 (from 2015) relating to idempotent SQL script generation and was wondering what the current thinking is on this?

I think that is trying to do idempotent migrations in a different way, by assuming nothing about the database's current state. It's an interesting approach, but as @DustinVenegas mentions in the comments, his Preconditions idea is more general.

Currently, you can use the in-process runner to do what you want to do, but the FluentMigrator.DotNet.Cli tool doesn't support it yet. If you want to submit a PR to support this, let me know. I have not figured out how difficult it will be. As you can see from https://github.com/fluentmigrator/fluentmigrator/blob/master/adr/proposed/UnitOfWork.md I am trying to define a newer architecture for connectionless clients. It's possible you might run into some problems I list in this ADR (I am not yet done writing the ADR, I just started it last week).

[GFoley83]

Hi @jzabroski. Just bumping this one to see if it's on your radar at all.

Ideally the script output would not assume the state of the Db and the generated SQL output would be something like

SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM [VersionInfo] WHERE [Version] = N'202210281000')
BEGIN
    BEGIN TRANSACTION
    GO
        ALTER TABLE [dbo].[School] ADD IsDeleted bit NULL
    GO
        INSERT INTO [dbo].[VersionInfo] ([Version], [AppliedOn], [Description]) VALUES (202210281000, '2024-05-22T04:01:55', N'blah')
    GO
    COMMIT TRANSACTION
END

SET NOCOUNT OFF;

[jzabroski]

I don't plan to work on it any time soon. I would be willing to think through what a PR should look like if you're interested in contributing. As in, offer implementation hints.