example migration needed

[donpdonp]

trafficstars

after getting things setup, it turns out i have no idea what migrate is expecting inside each migration file. I put in the plain SQL commands but the tool dies on that. I've scanned the docs and the repo for an example or description but found none.

./cli -path ./migrations -database "postgres://localhost/cointhink_dev" up
error: unable to parse file 20170517_create_tokens.up.sql

[mattes]

Could you open a PR with improved documentation? Right now there are some examples here: https://github.com/mattes/migrate/tree/master/database/postgres/examples/migrations

Does that help and eventually help fixing the parsing error?

[KanybekMomukeyev]

@mattes read all readme text, and did not understand, where i can find clear documentation and examples? Any migrate file, how to create/generate, implement, write own, etc... forex for postgres

[mattes]

If you're asking "user guide/ manual". We don't have anything like that so far.

[taywrobel]

@KanybekMomukeyev my best guess is that there are duplicate migration versions present in your migrations directory. It looks like you're using a version number represented by a date, down to single day granularity. In order to preserve ordering of migrations, the version number of the migrations (int prefix) must be unique.

Do you have another migration present in the directory versioned as 20170517?

[KanybekMomukeyev]

@twrobel3 thanks for you reply, after 2 days of trying build, finally did it work. I think there should be tutorial, which explains step by step, for dummies like me)).

[KanybekMomukeyev]

Like this one: Postgres example (1)

$ go get -u -d github.com/mattes/migrate/cli github.com/lib/pq
$ go build -tags 'postgres' -o /usr/local/bin/migrate github.com/mattes/migrate/cli

(2)

migrate create -ext sql -dir migrations create_user

(3) 1500199974_create_user.up.sql

CREATE TABLE IF NOT EXISTS users (
    user_id BIGSERIAL PRIMARY KEY NOT NULL,
    role_id INTEGER,
    user_image_path VARCHAR (300),
    first_name VARCHAR (300),
    second_name VARCHAR (300),
    email VARCHAR (300) UNIQUE,
    password VARCHAR (500),
    phone_number VARCHAR (300),
    address VARCHAR (300),
    updated_at BIGINT
);

(3) 1500199974_create_user.down.sql

DROP TABLE IF EXISTS users;

(4) Run code

func MigrateDatabase(path string) {
	db, err := sql.Open("postgres", path)
	defer db.Close()

	if err != nil {
		fmt.Println(err)
	}

	driver, err := postgres.WithInstance(db, &postgres.Config{})

	m, err := migrate.NewWithDatabaseInstance(
		"file://dbManagers/migrations/",
		"postgres", driver)

	if err != nil {
		fmt.Println(err)
	}

	m.Up()

	//m.Down()

	defer m.Close()
}

[nkev]

Not working for me. The schema_migrations table gets created so I know there is no issue with DB access, but the table is empty and the 2 SQL files that get created are also empty. Can anyone help? Here is my create command (on Macbook Pro):

migrate -database postgres://postgres:postgres@localhost:5432/ur?sslmode=disable -path ./migrations create -ext sql -dir ./migrations  _initial

Running with sudo access makes no difference.

[nkev]

Can anyone help me with this, it's very frustrating. Why could I be getting empty SQL files with the create command? All of the following have the same result. Both sql files and schema table that are generated are always empty:

migrate create -dir ./migrations db_start

migrate create -dir migrations db_start

migrate create -ext sql -dir migrations db_start

migrate -database postgres://postgres:postgres@localhost:5432/ur?sslmode=disable create -dir migrations db_start

Am I supposed to populate the initial SQL files or something? That kind of defeats the purpose.

[mattes]

It just creates empty sql files with the correct naming convention. What are you expecting to see as the content?

[nkev]

I'm expecting SQL create statements of the initial state of the database. I'm pretty sure that's what the fork github.com/gemnasium/migrate did for me. What am I missing about the point of empty SQL files?

[mattes]

Can you give an example?

[nkev]

Sure. With github.com/gemnasium/migrate my create command created 2 SQL files: 20170106065339_initialdb.up.sql containing:

CREATE EXTENSION IF NOT EXISTS hstore;

CREATE SEQUENCE profiles_profile_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE profiles (
    profile_id bigint DEFAULT nextval('profiles_profile_id_seq'::regclass) NOT NULL,
    user_id bigint,
   --remaining columns ommited for brevity
);

ALTER TABLE ONLY profiles ADD CONSTRAINT profiles_pkey PRIMARY KEY (profile_id);

--The rest of it omitted for brevity

...and 20170106065339_initialdb.down.sql containing:

DROP TABLE profiles;
DROP SEQUENCE profiles_profile_id_seq;

So when my co-developers start off, they create an empty database and just run migrate up and all the DB schema on my machine is cloned on theirs. From that point we all use create/up/down to manage the schema changes.

So what am I missing about the point of the empty SQL files?

[mattes]

Isn't that highly opinionated though?

Here is another problem (which could be solved). The create command doesn't know about the driver, so the Postgres example wouldn't make sense for mongodb or other drivers.

[nkev]

So what am I missing about the point of the empty SQL files? :) Seriously, what is the purpose of these files?

[mattes]

At this point it makes the process of creating two timestamped files faster. There is more discussion here:

https://github.com/mattes/migrate/issues/202#issuecomment-297123511 https://github.com/mattes/migrate/issues/205

Happy to merge PRs that make things better.

[nkev]

Ok, thanks for the explanation.

[nkev]

Sorry to bother you again but I just cannot get this to work. Step by step instructions in https://github.com/mattes/migrate/tree/master/cli would help a lot for newbies. I create a new migration:

migrate -database postgres://postgres:postgres@localhost:5432/ur?sslmode=disable create -dir migrations  test

Two empty files get created : 1501665062_test.up and 1501665062_test.down and an empty schema_migrations table gets created. I then add a column to one of my tables and run create again:

migrate -database postgres://postgres:postgres@localhost:5432/ur?sslmode=disable create -dir migrations  test2

Two more empty files get created : 1501665118_test2.up and 1501665118_test2.down and the schema_migrations table is still empty.

What am I doing wrong? How does the package know what has changed if it does not save any database state?

[mattes]

Here is what you want to do:

$ migrate create -dir migrations -ext sql test
$ ls migrations
1501691280_test.down.sql
1501691280_test.up.sql
$ migrate -path ./migrations -database postgres://postgres:postgres@localhost:5432/ur?sslmode=disable up

[taywrobel]

@nkev @mattes I think I can clear up the confusion here --

@knev seems to think that the migrate will pull the database state out of the database using introspection, and generate the content of the migration files. This is very advanced, database-specific, and may not exist in all database systems, and as such is not supported.

Instead, you should be entering your migration DDL operations into the migration files manually. The create command only creates placeholder files in the correct location and with the correct filename format. They are expected to be empty initially.

For a full example, if your initial state involves a single users table, you may do something like this:

$ migrate create -dir migrations -ext sql initialize-db
$ ls migrations
1501694030_initialize-db.down.sql 1501694030_initialize-db.up.sql
$ echo 'CREATE TABLE users (user_id integer unique, name varchar(40), email varchar(40));' >> migrations/1501694030_initialize-db.up.sql
$ echo 'DROP TABLE users;' >> migrations/1501694030_initialize-db.down.sql
$ migrate -path ./migrations -database postgres://postgres:postgres@localhost:5432/ur?sslmode=disable up

At this point, the table users will be created, and the migration metadata will be recorded in the schema_migrations table. Since a down migration was also created, you can run:

$ migrate -path ./migrations -database postgres://postgres:postgres@localhost:5432/ur?sslmode=disable down

And that will delete the users table.

However, the content of the migration files must be created manually, and will not be pulled from the database using introspection.

[nkev]

Thanks guys. Do I have to manually change the content of every migration file or only the first up/down pair? If I have have to keep track of every change I do in the DB and populate the migration files manually every time, it sort of defeats the purpose, don't you think? If it's only for the first pair then how do I generate my DB changes into a new up/down migration pair?

[nkev]

@twrobel3 Thanks for taking the time to explain the initial empty SQL files.

However, the content of the migration files must be created manually, and will not be pulled from the database using introspection.

Does that mean I have to keep track of all my DB changes myself and create the SQL manually for each migration pair generated or just the first time, as in your example? If every time, that requires too much maintenance on my part.

But assuming the "SQL" files do get populated by this package, then after the initial create, I added a column to my users table, then ran the following:

migrate -path ./migrations -database postgres://postgres:postgres@localhost:5432/ur?ssl
mode=disable create -dir ./migrations -ext sql user_additions

...this created 2 new empty SQL files again.

I tried with -path, without -path, with and without -dir ...but the sql files are always empty.

Either there's something wrong with my system or I am just not getting this...

[lmas]

Sorry friend, but I think you're not getting it :p

Like twrobel3 said, this package doesn't create any migration files with any SQL schema in them. It will always create empty files, no matter what you try do with the create command. You have to manually populate the migration files with the SQL schema changes yourself. Always. For each and every migration you want.

What this package seems to do, is that it will apply those manually edited up/down migration files against a database as cleanly as possible. Nothing more. No auto-generated SQL migration files from existing databases (that you're trying to do). Nope, no sir.

[lmas]

If every time, that requires too much maintenance on my part. ... ... then after the initial create, I added a column to my users table...

Just take a moment to think about this. 🤔

You're already typing up a SQL command to add that column to your database, in some terminal/CLI or in some kind of fancy database editor or whatever. And then you hit run and it updates your database. Two easy steps.

After you've made changes to your database, I guess you run (or want to run) some migration tool against the database to let it generate migration files for you, automagically? Well, that's a whole 3rd step you don't need to do!

Now, what if, instead of using your regular SQL tool, you would type up the same SQL commands in one of those empty migration files? And when you want to update your database, you just run migrate <insert flags> up. So far it's basically the same amount of work you did before, two steps right? Sure, but we skipped the 3rd step aaand we already got a migration file ready to send to your co-developers!

Of course, I don't know how you and your co-developers actually work or with what existing tools. But given your examples of your workflow, when you're making database changes and getting migration files, I would say this workflow I just typed up would be less work for you, while the maintenance is the same :D

At least this is what I understand what this package will do, I haven't actually got the chance to use it yet so I apologize if I'm completely out of it and am talking nonsense..

[nkev]

...except that with these kind of migration packages you have to ALSO manually create the down scripts as well as up scripts because the whole point of migrations is to be able to go up and down n steps. Otherwise, there's no point using migrations at all, you can just create the up scripts in your repo and each dev can apply them as necessary.

[lmas]

... the whole point of migrations is to be able to go up and down n steps.

In simple projects, or in early prototyping stages, it's just enough to go up.

But whatever, the whole point is to have migration scripts saved in some way or another, otherwise others can't apply the database changes you've done. Agreed?

Now, I don't what kind of experience you've had before, but you won't get those migration scripts magically by using this pkg, or with any other golang pkgs (that I could find on the awsome-go list or with a quick googling). Sure, other tools in other languages will do this for you using heavy magic (like Django, written in Python), but things are usually kept simpler in golang.

[VaoTsun]

do I understand it right, that you can't put more then one statement to sql file?..

[lurenasan]

copy some KanybekMomukeyev's code and Do some supplements when i use mysql migrate Like this one: Mysql example (1)

$ go get -u -d github.com/mattes/migrate github.com/go-sql-driver/mysql
$ go build -tags 'mysql' -o /usr/local/bin/migrate github.com/mattes/migrate/cli

(2)

migrate create -ext sql -dir migrations create_user

(3) 1500199974_create_user.up.sql

CREATE TABLE IF NOT EXISTS users (
    user_id BIGSERIAL PRIMARY KEY NOT NULL,
    role_id INTEGER,
    user_image_path VARCHAR (300),
    first_name VARCHAR (300),
    second_name VARCHAR (300),
    email VARCHAR (300) UNIQUE,
    password VARCHAR (500),
    phone_number VARCHAR (300),
    address VARCHAR (300),
    updated_at BIGINT
);

(3) 1500199974_create_user.down.sql

DROP TABLE IF EXISTS users;

(4) Run code use Cli

migrate -database mysql://root:root@localshot:3306/db_name?sslmode=disable -path ./migrations up

there will raise no match err and if you don't use the sslmode will raise addr unknown err so the correct useage is

#if your db is localhost and the port is 3306
migrate -database mysql://root:root@/db_name?sslmode=disable -path ./migrations up
#if your db is outside don't forget use tcp conn
migrate -database mysql://root:root@tcp(addr:port)/db_name?sslmode=disable -path ./migrations up

[calvinchankf]

hi all, i have tried migrate create -ext sql -dir migrations users and it successfully created up&down files but the terminal shows me that

Time format not specified

what does it actaully mean? did i do something wrong?

i have looked into the source code and i am still confused, it seems like it is just a log message(?) https://github.com/golang-migrate/migrate/blob/master/cli/commands.go#L76

[duong-se]

How can we solve no matches found when migrate with mysql ? Run migrate -database root:@tcp(127.0.0.1:3306)/crm -path ./database/migrations up

Error zsh: no matches found: root:@tcp(127.0.0.1:3306)/crm