Expose dialectquery for custom dialects without having to reimplement stores

[LukasHeimann]

Hi everyone,

thank you for your great work to provide goose as an extensible schema migration tool! In my project, I want to use goose with an unsupported database. I know that I can use a custom store as documented at https://pressly.github.io/goose/documentation/custom-store/

However, with an SQL-like store, the implementation of a custom store is a bit tedious. Basically, I have to copy large chunks of https://github.com/pressly/goose/blob/main/database/dialect.go, and only exchange the sql queries used for the individual commands.

It would be way easier, if goose would expose a method to create a new dialect.store based on a given dialectquery.Querier, which offers a contract that is way easier to implement without duplicating store implementations.

Expand to see detailed explanations on why this is helpful + links to code

E.g.

goose/database/dialect.go

Lines 114 to 124 in 6a70e74

	func (s *store) GetLatestVersion(ctx context.Context, db DBTxConn) (int64, error) {
	q := s.querier.GetLatestVersion(s.tablename)
	var version sql.NullInt64
	if err := db.QueryRowContext(ctx, q).Scan(&version); err != nil {
	return -1, fmt.Errorf("failed to get latest version: %w", err)
	}
	if !version.Valid {
	return -1, fmt.Errorf("latest %w", ErrVersionNotFound)
	}
	return version.Int64, nil
	}

func (s *store) GetLatestVersion(ctx context.Context, db DBTxConn) (int64, error) {
	q := "SELECT ..."
	var version sql.NullInt64
	if err := db.QueryRowContext(ctx, q).Scan(&version); err != nil {
		return -1, fmt.Errorf("failed to get latest version: %w", err)
	}
	if !version.Valid {
		return -1, fmt.Errorf("latest %w", ErrVersionNotFound)
	}
	return version.Int64, nil
}

It would be way easier, if I could instantiate a new store as with

goose/database/dialect.go

Lines 29 to 30 in 6a70e74

	// NewStore returns a new [Store] implementation for the given dialect.
	func NewStore(dialect Dialect, tablename string) (Store, error) {

But passing in something that fulfills the Querier interface

goose/internal/dialect/dialectquery/dialectquery.go

Lines 5 to 29 in 6a70e74

	// Querier is the interface that wraps the basic methods to create a dialect specific query.
	type Querier interface {
	// CreateTable returns the SQL query string to create the db version table.
	CreateTable(tableName string) string
	// InsertVersion returns the SQL query string to insert a new version into the db version table.
	InsertVersion(tableName string) string
	// DeleteVersion returns the SQL query string to delete a version from the db version table.
	DeleteVersion(tableName string) string
	// GetMigrationByVersion returns the SQL query string to get a single migration by version.
	//
	// The query should return the timestamp and is_applied columns.
	GetMigrationByVersion(tableName string) string
	// ListMigrations returns the SQL query string to list all migrations in descending order by id.
	//
	// The query should return the version_id and is_applied columns.
	ListMigrations(tableName string) string
	// GetLatestVersion returns the SQL query string to get the last version_id from the db version
	// table. Returns a nullable int64 value.
	GetLatestVersion(tableName string) string
	}

This would drastically reduce the efforts for us, and not force us to duplicate code from dialect.go. Let me know if you'd consider something like this! I feel like the implementation would not be too difficult, basically just a new method dialect.NewStoreFromQuerier(querier dialectquery.Querier, tablename string)

[mfridman]

Thanks for filing an issue. I have some thoughts on how we might solve this, will try to respond this weekend.