Add database migration commands to Cornucopia CLI

[ce1r]

Describe the problem

It would be convenient to have the CLI automatically search the migrations directory for sequential or timestamped migrations and run them in order.

Also, having the CLI source the .env file in the current directory for the DATABASE_URL environment variable would be a good addition as well.

Describe the solution you'd like

I'd like to have the commands cornucopia migrate {add | run | revert} added, alongside potentially some database management management commands like cornucopia database {create | drop | reset | setup}.

Alternatives considered

Just using the SQLx CLI for it.

Additional context

No response

Importance

Would make my life easier

[LouisGariepy]

Hi @ce1r ! Thanks for your suggestion.

Let's break this down into a couple proposals since they are orthogonal.

1. Sourcing .env. I am personally in favor of this, and we already pull in DATABASE_URL from clap's env anyway.

2. Searching a directory for migrations and applying them in sequential order. This already works through unix shell glob expansion, for example cornucopia schema migrations/*.sql will expand files in order. That said, I'm not opposed to implementing that logic in-code rather that relying on external shell behavior, with the benefit being that the behavior and syntax become the same on all platforms.

   Moreover, I think we should add schema/migration files setup to the config file, this would be a good ergonomics win.

3. Adding database management commands. Could you provide some use cases or your desired workflow? I don't really see why this would be beneficial.

4. Adding migration management commands. If you use a live database, then you can already manage your migrations via the framework of your choice. In that case you probably use Refinery or another specialized tool dedicated to database migrations. If you use schema or fresh then the schema application is already handled for you. Again, could you please provide more usecases on why this would be desirable?

Thanks!

[ce1r]

Hey, they're just quick ways to set up a database without having to use any extra command line tools. The SQLx cli works fine, though I'd prefer a single tool for it. The fresh and schema already do the same thing but not for a live DB.