Proposal to Extend Command Set for Marten CLI

[jabellard]

I'm starting this discussion to propose an extension of the command set for the Marten CLI.

Currently, the infrastructure in place to create database scripts is very easy to use and works very well.

However, I think it can be extended to streamline the process of managing database migrations. For context:
To manage migrations in a Marten project, I usually do the following:

1. Create a folder in my source code called Migrations
2. Use the existing commands (e.g., marten-dump and marten-patch) to create scripts that are stored in that folder
3. Commit those migrations to my source code
4. Use them to bring my database schema up to date

There's a few problems with that approach. Let's say I create 3 patches: P1, P2 and P3. I then need to apply each one of those patches as a migration only once and in order. Sounds pretty straightforward, but this process is prone to human errors - errors that I want to avoid at all cost, especially in production. For instance, what if I applied P2, forgot that I did, and then tried applying it again at a later time? This can be catastrophic for some migrations. Or maybe I applied P1, forgot that I never applied P2, and then moved forward with applying P3 before P2. These potential issues become even more probable when managing migrations that have to be applied across many different environments (e.g., dev, qa, and prod).

I am well aware that I can just use the marten-patch command to create one-off scripts to help sidestep these issues, but there a couple reasons why that won't work for me:

1. Even if I wanted to, this is not feasible in some corporate environments. At my workplace, for instance, production databases live in a prod network which is not accessible from a development environment. As such, I can't just configure my app with the production connection string to create a patch that will bring that database schema up to date.
2. To run a migration, I have to use an internal tool from our DBA team to submit a ticket, which needs to be approved by a Team Lead/Manager. That ticket also needs a reference (e.g., a file in a git repository) to the SQL script for the migration.

My proposal to solve these problems is to add a few commands to the marten CLI that will help streamline the process of managing migrations. The way those commands will work draw heavy inspiration from the Entity Framework CLI.
Here's the list of commands I think should be added:

1. Command to add a migration:
   This command adds a new migration which will consists of the following files in newly created folder named <timestamp>_<migration-name>:
   <timestamp>_up.sql: Used to run the migration
   <timestamp>_down.sql: Used to revert the migration
   <timestamp>_snapshot.<extension>: The current state of the schema. This snapshot will be used the next time a migration is to be created. Is it possible to serialize this state as JSON or binary? I'm not sure how to do this yet.
   Entity Framework analog: dotnet ef migrations add

2. Command to create a migration script:
   This command creates a migration script, which can be idempotent. The fact that this script can be idempotent will be a big win.
   Entity Framework analog: dotnet ef migrations script

3. Command to update the database
   Updates the database to the last migration or to a specified migration.
   Entity Framework analog: dotnet ef database update

I want to work on this feature, but didn't want to start writing any code before hearing some initial thoughts from the maintainers. Please let me know what ya'll think.
@jeremydmiller , @oskardudycz , @mysticmind