support for using a custom dialect?

[verkestk]

It would be great if there was a way to configured a custom dialect without needing to make changes to the goose codebase itself.

I'm using goose to manage postgres migrations - and I really like it, nice job 👏.

I also need to manage migrations for bigquery datasets, and I'd like to use the same tool. I could open a PR that introduces a bigquery-compatible dialect, but it would be really nice if that wasn't necessary.

[mfridman]

The newly added goose.NewProvider supports adding your own store implementation.

WithStore

This allows your custom dialect to implement the database.Store interface and use goose against that.

I've been meaning to document this better.

There's a bit more context here in case you're interested.

#520 (comment)

[verkestk]

Sounds like there's a way to do this. Will close. Thank you for such a quick response!

[mfridman]

I'll try to find some time to continue expanding the Reference section in the docs I've been slowly chipping away at. "Custom store" is on that list :)

[verkestk]

Looking at the WithStore option for bringing our own Provider (https://github.com/pressly/goose/blob/42eab2b6e805616fd54ebb494ec7d1b90863b1d3/database/store.go#L45C2-L45C9):

// Each database dialect requires a specific implementation of this interface. A dialect represents
// a set of SQL statements specific to a particular database system.
type Store interface {
	// Tablename is the version table used to record applied migrations. Must not be empty.
	Tablename() string

	// CreateVersionTable creates the version table. This table is used to record applied
	// migrations. When creating the table, the implementation must also insert a row for the
	// initial version (0).
	CreateVersionTable(ctx context.Context, db DBTxConn) error

	// Insert inserts a version id into the version table.
	Insert(ctx context.Context, db DBTxConn, req InsertRequest) error

	// Delete deletes a version id from the version table.
	Delete(ctx context.Context, db DBTxConn, version int64) error

	// GetMigration retrieves a single migration by version id. If the query succeeds, but the
	// version is not found, this method must return [ErrVersionNotFound].
	GetMigration(ctx context.Context, db DBTxConn, version int64) (*GetMigrationResult, error)

	// ListMigrations retrieves all migrations sorted in descending order by id or timestamp. If
	// there are no migrations, return empty slice with no error. Typically this method will return
	// at least one migration, because the initial version (0) is always inserted into the version
	// table when it is created.
	ListMigrations(ctx context.Context, db DBTxConn) ([]*ListMigrationsResult, error)

	// TODO(mf): remove this method once the Provider is public and a custom Store can be used.
	private()
}

Looks like there's a private member to that interface. Are there any other options?

[mfridman]

I'll fix this up, I want to slightly expand the interface to include a GetLatestVersion method as a future improvement.

Otherwise, this is ready to go.

---

For fun, I implemented a custom Store using an in-memory implementation against a sqlite database.

https://github.com/mfridman/goose-demo/blob/main/customstore/memory/memory.go

Can see the usage here:

https://github.com/mfridman/goose-demo/blob/main/cmd/custom-store/main.go

I also want to add Redis and then move on to some more interesting ones like duckdb.

Obviously these are contrived examples, but they give an idea that goose should work with any correctly implemented Store.

[verkestk]

Do you plan on releasing a new version with this latest change soon?

[mfridman]

Yes, I typically batch a few changes and release every 1-3 months. I'll probably do the next release this weekend.

[beichen1024]

@mfridman Hi, thank you for the option. I tried this out recently and I could not find a way to actually update the global variable store by just using goose.NewProvider("", db, files, goose.WithStore(NewBQStore())). And after that I tried to use goose.Up() to migrate the db, but it does not use the store I implemented but still the default (postgres in my case) so I am getting sql format error.

I am wondering is there a way to actually set the store to be custom store, something similar as "goose.SetDialect()"?

[mfridman]

I appreciate how confusing this might be. The short answer is goose.NewProvider has no global state (except for globally registered Go migrations¹ for backwards compatibility), so you should run:

provider, err := goose.NewProvider(...)

res, err := provider.Up()

When adding the new goose provider we went back and forth on whether to cut a new major version (/v4) or implement it in a backwards-compatible way within the current /v3. We opted for the latter to reduce fragmentation, but this does mean overhead for new users or those not keeping up with the project.

Please let us know if you need an example or clarification on the README or docs.

Maybe in the future, we might add a global goose.SetStore so that all global (non-provider) functions behave as you expect. Sorry for the confusion.

Lastly, I'm curious why you need a custom Store implementation for postgres when goose natively supports it?

Footnotes

1. Even this can be scoped to the provider using a functional option WithGoMigrations ↩

[beichen1024]

@mfridman I am adding a bigquery provider. And in our code, we are already using the already-implemented postgres dialect. We are trying to use goose to manage all migrations in the code for different types of databases.

Used provider.UP() and it worked! Thank you very much.

BTW, I do have a fork with bigquery dialect implemented, are you interested in adding those to official goose? If so, I can continue to work on it to match your code standard and tests.

[mfridman]

Yep, contributions are always welcome. I'm sure other folks would benefit from an implementation.

https://github.com/pressly/goose/tree/387d8f8b101b575bf5ac9cb3476a7b4c4c9916e4/internal/dialect/dialectquery

The only tricky part is adding a supported community driver to the CLI. It's a bit of a pain nowadays because some drivers are supporting only the latest Go version, whereas with goose we try to lag 1-2 releases behind to avoid breaking folks.

I'm trying to see if this is the correct path forward #664

[mfridman]

I cut a v3.20.0 release this morning. I'd like to keep this issue open as a reminder to also update the documentation website.

[mfridman]

I started to expand the docs here:

https://pressly.github.io/goose/documentation/custom-store/

I'll close this issue, but if you have any issues feel free to continue commenting or re-open.