go-pg / migrations Goto Github PK

Right now creating migration files requires postgresql connection even if it's not used during the process

There's also no possibility to make custom wrapper, since create function is private and can't be imported.

It would help a lot if any one of these two were possible

Hi!

If I have multiple db.Execs in a migration, there's a bug: if a second Exec fails the changes from the first are already committed. It makes the migration not revertible.

The proper solution would be to execute both up & down in a transaction. Can this be done a default?

Very important and implicit action missed in the main example

Back around version 6.3.0 the gopg_migrations table would be created if it did not exist with the "up" cmd.

The latest versions (6.5.2 onward) all seem to require that init be run separately now. If I'm starting up off of a clean database, I'd like my app to create thegopg_migrations table if it does not exist and then run my migrations without running init separately.

If I missed how this can easily be done in the latest version, please let me know.

Can't install go-pg packages as dependency, seems to be no longer available.
I tried to make installation with "go dep" dependency manager.

I've got the multiple errors
github.com/go-pg/pg/v9 is missing; required by github.com/go-pg/[email protected].

Seems like, that I can't use a migrations mechanism with a sharding: https://github.com/go-pg/sharding

After read the source code, I've understood, that migrations always run on a "public" schema. But I want to run migrations on every shard apart.

I'm sure that is my fault, and some confusion with vendoring, but may be somebody know what to do with this.

I just trying to run example and have and error:

cannot use db (type *"github.com/go-pg/pg".DB) as type migrations.DB in argument to migrations.Run:
	*"github.com/go-pg/pg".DB does not implement migrations.DB (wrong type for Begin method)
		have Begin() (*"github.com/go-pg/pg".Tx, error)
		want Begin() (*"github.com/go-pg/migrations/vendor/github.com/go-pg/pg".Tx, error)

my code is very simple:

package main

import (
	"github.com/go-pg/migrations"
	"github.com/go-pg/pg"
)

func main() {

	db := pg.Connect(&pg.Options{
		User:     "user",
		Database: "database",
	})

	oldVersion, newVersion, _ := migrations.Run(db)

}

Version: "github.com/go-pg/migrations/v7"
Running against cockroachdb, which should support IF EXISTS.

Error:

ERROR #0A000 unimplemented at or near \"if\""

Code:

func MigrateDatabase(db *pg.DB, schemaName string) error {
	// set the metadata table name
	migrations.SetTableName(fmt.Sprintf("%s.%s", schemaName, migrationsTableName))

	// discover the local migration files
	if err := migrations.DefaultCollection.DiscoverSQLMigrations(migrationsPath); err != nil {
		return errors.New(fmt.Sprintf("could not discover migrations: %v", err))
	}

	// try intializing the migrations meta table and migrating
	log.Debug("initializing migrations database")
	_, _, err := migrations.Run(db, "init")
	if err != nil {
		log.Warnf("could not init migrations: %v", err)
	}

	log.Debugf("migrating database")
	oldVersion, newVersion, err := migrations.Run(db, "up")
	if err != nil {
		return errors.New(fmt.Sprintf("could not migrate: %v", err))
	}

	log.Debugf("upgraded db from version %d to version %d", oldVersion, newVersion)
	return nil
}

It's very rare in big projects, that migration files are located in the same directory as an executable file or in the root directory. If the project implements the standard layout or is just big enough to have many parts and sub packages, migration files are normally stored in some place, far away from root or cmd directory.

I've implemented custom folder support on my project, that discovers migration files by the path, provided with the CLI parameter. But I would like to see and find it very useful to have such option out-of-the-box.

If it doesn't contradict the main idea of the library, I would like to implement it as an optional CLI argument, that defines a custom path to the migration directory.

error ERROR #42P01 relation "gopg_migrations" does not exist. when running migrations for the first time.
It should be check that the table exists before locking

This error rising if I try to add a .go migration file in directory where already are .sql files.
Seems like a call of func (c *Collection) discoverSQLMigrations(file string) error
in func (c *Collection) register(tx bool, fns ...func(DB) error) error cause such behaviour. Is there any reason for the call?

Does the library have a ready-to-use executable somewhere?
To just download&run like

# download and install the executable
go get github.com/go-pg/migrations/cmd/pg-migrations
# run migrations as an app
pg-migrations init
pg-migrations up 2

I want migrations to run automatically when the application starts in production/staging. Any pointers on how this can be achieved?

Perhaps I need to compile the migration main package into a binary, but how to deal with mixture of .go and .sql files?

SQL migrations for Golang and PotgreSQL

Missing "s".

When we second time call migration with parameter init, we have a error relation "gopg_migrations" already exists.

Hi,
I don't know if I'm wrong but it looks like you can't have all migrations in .sql files only, you have to have at least one migration in a .go file.

You can reproduce this by cloning the example folder and removing all but the *.sql files.

A workaround I found is to create a dumb 0_dumb.up.gofile doing nothing as you can see :

package main

import (
  "errors"
  "fmt"

  "github.com/go-pg/migrations"
)

func init() {
  migrations.MustRegisterTx(func(db migrations.DB) error {
    fmt.Println("Dumb")
    return errors.New("Dumb")
  }, func(db migrations.DB) error {
    fmt.Println("Dumb")
    return errors.New("Dumb")
  })
}

And only then it works.
I'm using the latest version as of today and my go version is go1.11 darwin/amd64

I would like to use this library for commercial purposes. Are you able to add a license to the repository so I know if that is possible?

In team environment where everyone is working on their own branch, the integer-based migration approach breaks down as it causes collisions.

One solution is to prefix migrations with a timestamp instead of integer. The version table can keep track of the timestamp instead of the version number.

I'm using directory structure equivalent to one given in example.
1_initial.go 2_dictionary_seed.go ... main.go

If I'm running one migration at a time, like
go run main.go 1_initial.go up
or
go run main.go 1_initial.go down
It works fine.
But when I'm trying to run all at once, like
go run *.go up
or
go run *.go down
It fails with an error:

GetFileAttributesEx *.go: The filename, directory name, or volume label syntax is incorrect.

Hi,
I've encountered an issue, and it seems like it's coming from this library.
It used to work perfectly until I switched from GOPATH to go.mod, now everytime I try to deploy my binary I ended get this error at runtime when launching it :

panic: open /Users/MY_USERNAME/workspace/PROJECT_NAME/api/migration: no such file or directory

goroutine 1 [running]:
github.com/go-pg/migrations.(*Collection).MustRegister(...)
        /Users/MY_USERNAME/golang/pkg/mod/github.com/go-pg/[email protected]+incompatible/collection.go:326
github.com/go-pg/migrations.MustRegister(0xc00012bf58, 0x2, 0x2)
        /Users/MY_USERNAME/golang/pkg/mod/github.com/go-pg/[email protected]+incompatible/default.go:31 +0x7f
api/migration.init.0()
        /Users/MY_USERNAME/workspace/PROJECT_NAME/api/migration/001_initial.go:13 +0x5d

Here is what is in 001_initial.go:13 :

package migration

import (
	"log"

	"github.com/go-pg/migrations"
	packr "github.com/gobuffalo/packr/v2"
)

var MigrationBox = packr.New("Migrations Box", "../../database/migrationsAPI")

func init() {
	migrations.MustRegisterTx(func(db migrations.DB) error { // <-- Line 13
		log.Println("Running 001_initial.up.sql...")

		migrationContent, err := MigrationBox.FindString("001_initial_up.sql")
		if err != nil {
			return err
		}

		_, err = db.Exec(migrationContent)
		return err
	}, func(db migrations.DB) error {
		log.Println("Running 001_initial.down.sql...")

		migrationContent, err := MigrationBox.FindString("001_initial_down.sql")
		if err != nil {
			return err
		}

		_, err = db.Exec(migrationContent)
		return err
	})
}

I don't think it comes from packr2 because It's working locally, even when I move the binary into another location on my machine.

Thanks for your help.

Hi,

I created an issue earlier -> #59 about a strange behaviour I never encountered before.
I got this after switching from GOPATH to go.mod.

What I found is that during the switching, go-pg/migration got updated to the latest version.
And after some investigation, I realized that it broke in the v6.7.1.

It seems like after the 6.7.1, the migrate tool is looking for go files at runtime, and can't find it.
While on the other hand, previous versions were working just fine.

My guess (might be wrong) is because of this commit :
Replace filepath.Walk with ioutil.ReadDir

So I downgraded to v6.7.0 and now I'm alright.

Thanks for the work

If we register multiple migrations per file, executing "up" works as expected, executing all of them, but not "down". It only executes the first migration in the list. This can be seen here and here

Instead of having seperate .up.sql and down.sql it'd be nice to have something like https://github.com/rubenv/sql-migrate which allows comments like -- +migrate up and easily allows both up and down to be written in the same file.

Is it possible to use this tool with sql files (0001_init.up.sql 0001_init.down.sql)?

Currently, all files which call Register variants add migrations to the global g.migrations list. This prevents clients from registering + running multiple sets of migrations within the same Go process. This can be particularly useful during testing, but might also be useful in production scenarios.

It would also be useful to configure registration behavior. For example, the behavior of Register has changed so that it automatically scans for SQL files and adds them as migrations. While this is certainly useful for some users, it has broken our workflow because we were already handling SQL files in our own particular (different) way. We would like to selectively disable this feature.

Proposal: Introduce a public Group type with Register methods that register migrations to that particular group. Group can have fields for configuring registration behavior, such as DiscoverSQLFiles bool. The current Register functions would operate on a default Group object.

This "configurable instances + default global" approach is analogous to e.g. http.Server.

RunMigrations creates NewCollection => ignores global SetTableName, which updates DefaultCollection.
It breaks backward compatibility.

It would be great to add meaningful godoc comments and examples, document the ways the library can be used (how to use a different migration directory, how to register migrations from the code, etc.)

Is it possible to use the models from an application using go-pg? The examples only show how to work with sql files, is people actually writing those sql queries in their application or there is a way to get the dynamic query from go-pg to write migrations?

go get returns error, while still downloading package:
>go get github.com/go-pg/migrations github.com/go-pg/migrations ..\github.com\go-pg\migrations\db.go:27: cannot use pg.Q(tableName) (type orm.FormatAppender) as type types.ValueAppender in return argument: orm.FormatAppender does not implement types.ValueAppender (missing AppendValue method)
when I'm trying to run examples, it gives the same error:
>go run main.go init github.com/go-pg/migrations ..\..\..\github.com\go-pg\migrations\db.go:27: cannot use pg.Q(tableName) (type orm.FormatAppender) as type types.ValueAppender in return argument: orm.FormatAppender does not implement types.ValueAppender (missing AppendValue method)

I just tried the example, and it doesn't seem to read the .go migrations (1,2,3) and jumps straight to the .sql migration, which causes errors of course.

Forcing it go migrate to specific version doesn't seem to work either:

$ go run main.go up 1
version is 0

It seems that it might have something to do with the recent update.

Hi! I try to run your migration example, but I need to store all versions in separate folder.
like this:

when I am trying to: "go run migrate.go" , the program could not find the version 1_initMdmDb.go.. How can I define the path to it

At the time of this writing (latest release: v8.0.2), there is a line of code that ensures the creation of the schema (if not exists) when creating the table that stores the list of migrations executed:

_, err := db.Exec(`CREATE SCHEMA IF NOT EXISTS ?`, pg.SafeQuery(schema))

Source: https://github.com/go-pg/migrations/blob/v8.0.2/collection.go#L641

It is executed only if the targeted schema is not public.

So when using a schema that is not public, e.g. myschema, the following query will always be run at init:

CREATE SCHEMA IF NOT EXISTS myschema

This poses a problem in situations where the current PostgreSQL user doesn't have the CREATE privilege at the database level (hence they can't create new schemas) but only the CREATE privilege on a specific schema created beforehand by another superuser.

In that case, the line above would fail (although the schema already exists) and there would be no way to use this library.

These situations are common when people try to follow strict least privilege policies, e.g. but not only in multi-tenant scenarios.

On a side-note, the fact that the library doesn't check for the existence nor tries to create the schema if it is equal to public seems a little weird. I'm not sure what the rationale is for this special case.

A fix is proposed in: #106

I would like to add a usage section with detailed CLI options description and common app description and practical information.
Currently, it looks not so friendly without an actual description of usage options and a kind of starting point.

When i run migrations in the Google App Engine Standard Environment i got the next error:

panic: open /tmp/staging/srv/internal/app/someapp/repository/migrations: no such file or directory goroutine 1 [running]: github.com/go-pg/migrations.(*Collection).MustRegisterTx(...) /go/pkg/mod/github.com/go-pg/[email protected]+incompatible/collection.go:333 github.com/go-pg/migrations.MustRegisterTx(0xc000407f58, 0x2, 0x2) /go/pkg/mod/github.com/go-pg/[email protected]+incompatible/default.go:35 +0x7f some.website.net/example/project/internal/app/someapp/repository/migrations.init.0() /tmp/staging/srv/internal/app/someapp/repository/migrations/1562685639_initialize_db.go:41 +0x4ad

I am sure that this is due to the fact that in this environment writing / reading of the file system is prohibited. Is there any way around this problem?

@vmihailenco, need your input on this issue.

Imagine you have a set of migrations. Let's say the last migration you performed was number 148. Now, you decided to add a new migration using a timestamp-based version such as 20231010072528. When you up the migration it is working perfectly.

Here's the issue:
When you want to "down" a migration, you'd expect to go back to the previous version, which should be 148 in this case. However, what's happening currently is that instead of going back to 148, the system is subtracting 1 from 20231010072528 and taking you to 20231010072527. This behavior doesn't match the expectations.

migrations.Run() function should return the correct previous version, which in this case should be 148, rather than the unexpected 20231010072527.

Note: Migration is getting down as expected but version number is not correct.

Code sample which reproduces the issue:
go-pg-migration-issue.zip

For running all migrations in a global transaction, the README suggests code like the following:

var oldVersion, newVersion int64
err = db.RunInTransaction(func(tx *pg.Tx) error {
	oldVersion, newVersion, err = migrations.Run(tx, flag.Args()...)
	return err
})
if err != nil {
	panic(err)
}

After upgrading from v6.2.0 to v6.7.3, the code now fails:

BEGIN
SELECT count(*) FROM "pg_tables" WHERE (schemaname = 'public') AND (tablename = 'gopg_migrations')
SET idle_in_transaction_session_timeout = 0
LOCK TABLE gopg_migrations

		SELECT version FROM gopg_migrations ORDER BY id DESC LIMIT 1
	
COMMIT
panic: pg: transaction has already been committed or rolled back

I don't see nested transactions in the output, but it does look like the code will attempt to create a transaction for each migration: https://github.com/go-pg/migrations/blob/master/collection.go#L454

I just migrated to go modules to use go-pg v7 sigh. When I try to build or run my application I get:

go get -u ./... gives me:

go.mod:

	github.com/go-pg/migrations v6.7.3+incompatible
	github.com/go-pg/pg v8.0.5+incompatible // indirect
	github.com/go-pg/pg/v9 v9.0.0-beta.2

go.sum:

github.com/go-pg/migrations v6.7.3+incompatible h1:mKayeWTNGhYA9P9wzZNSDoumJRhfB4fEmfAlxNTVwtA=
github.com/go-pg/migrations v6.7.3+incompatible/go.mod h1:DtFiob3rFxsj0He8fye6Ta4eukFW80IfdY10zb2yH1c=
github.com/go-pg/pg v8.0.5+incompatible h1:+USAV4GOW4mlX1tt1DsEQ1ZSqVkrkDlPPG+t4DqzpAA=
github.com/go-pg/pg v8.0.5+incompatible/go.mod h1:a2oXow+aFOrvwcKs3eIA0lNFmMilrxK2sOkB5NWe0vA=
github.com/go-pg/pg/v9 v9.0.0-beta.2 h1:Z+WDTDFN32omw0VSyA3Bj8L3x1+tbuoU7ytUg6+E4QI=
github.com/go-pg/pg/v9 v9.0.0-beta.2/go.mod h1:iVSTa1IJiCa0cN5cJJD5n0k3zYliVQC35Wq8nU82zIo=

cannot use tx (type *"github.com/go-pg/pg".Tx) as type *"github.com/go-pg/pg/v9".Tx in argument to xxx.

Hey,

It is currently difficult to use this package embedded into an existing project.
It would be nice if the "migration" variable would be public, that way we can write an alternative register method that appends Migration structs to this array variable.

Something like

var (
    Migrations []Migration
    sortMu     sync.Mutex
)

func Register(migration Migration) {

    Migrations = append(migrations, migration)
}

Perhaps make an alternative method to register migrations by file?

Some migration tools allow developers to use them as a standalone / CLI as well as a library. I see some changes to the Run method would be probably needed for this.

My question is: would you guys be OK with this? Thanks

I embed my sql files with the new go:embed:

//go:embed *.sql
var EmbedFs embed.FS
var HttpFs = http.FS(EmbedFs)

then I call migrations.DefaultCollection.DiscoverSQLMigrationsFromFilesystem(migration.HttpFs, "/") to discover the sql files from the embedded filesystem. this works on linux but not on windows.

the following path makes it also work on windows as the underlying fs only uses forward slashed:

diff --git a/collection.go b/collection.go
index 29c8d99..7811b63 100644
--- a/collection.go
+++ b/collection.go
@@ -219,7 +219,7 @@ func (c *Collection) DiscoverSQLMigrationsFromFilesystem(fs http.FileSystem, dir
                }
 
                m := newMigration(version)
-               filePath := filepath.Join(dir, fileName)
+               filePath := path.Join(dir, fileName)
 
                if strings.HasSuffix(fileName, ".up.sql") {
                        if m.Up != nil {