Split goose into separate modules (same repository)

More of a thought at the moment, but wanted to jot this down.

Currently, the goose package and the CLI are tracked as a single Go module and versioned together (think single go.mod in the entire repository). However, there are some limitations to this approach:

Limitations

• The package and CLI share the same minimum Go version. This means existing and new SQL drivers (as direct dependencies) must be compatible with the minimum supported Go version in goose. For example, in https://github.com/pressly/goose/pull/658#discussion_r1424098562, we'd need to bump the minimum version, and even though the package doesn't use the driver, it is still bumping the "global" go.mod file.
• In the CLI, we want to use the latest Go version and the most up-to-date drivers when building the CLI binary. This is at odds with the goose package, which aims to be backwards compatible with the last few Go releases and support the lowest possible version.

So, what I'm proposing is to add go.{mod|sum} files into ./cmd/goose and track the CLI and its dependencies as a separate module.

module github.com/pressly/goose/cmd/goose

For local development can use

1. go work init (initialize a go.work file)
2. go work use -r . (recursively add all modules, and sub-modules)

There are a few downsides to this approach through,

Cons

• Need to figure out versioning between the CLI and the package
• Users would no longer be able to install the binary like so:

# before
go install github.com/pressly/goose/v3/cmd/goose@latest
# after
go install github.com/pressly/goose/cmd/goose/v3@latest

• Cannot use ./internal packages in github.com/pressly/goose/v3 module because it is now a separate module.

Pros

• Ability to pin the goose CLI module to the latest Go version, while keeping the goose package go.mod a few Go versions back
• Flexibility to use any driver, no limitation on minimum Go version
• Versioning may be considered a pro, since consumers don't have a dependency bump for unrelated code changes.