amacneil / dbmate Goto Github PK

I just noticed that if I run a dbmate migrate and there is an error in one of the statements, the error being printed out is a bit useless, and doesn't even contain the line number or the actual message from Postgres.

I checked if there are any options for more verbosity but I don't see that either, so atm we simply don't get errors printed out?

Hey, I'm trying to run docker-compose run dbmate new test but I'm getting the following error:

Error: parse "postgres://postgres:[email protected]:5432/dbname?sslmode=disable": first path segment in URL cannot contain colon

I'm setting the database URL using the DATABASE_URL env variable and am using the amacneil/dbmate docker image.

I added dbmate as a service to my docker-compose file which looks like this:

  dbmate:
    image: amacneil/dbmate
    depends_on:
      - database
    env_file:
      - docker.env
    volumes:
      - ./db/migrations:/db/migrations

Thanks

Would be nice if the migration description in addition to the version was stored in the db.

Currently dbmate executes each migration in a new transaction. The potential issue with this is it can leave the database in an incompatible state with either version of the application, which is bad. Potential causes of this sort of failure can be difficult to detect in testing -- db server crash, oom, excessive temp files, tx id wraparound, etc.

I think we should change dbmate to execute all pending migrations in a single transaction. Thoughts?

Would be nice to have a dbmate version command that prints the current schema version.

I have problems with "dbmate" in windows installed using "go get -u github.com/amacneil/dbmate".
"dbmate command not found"

First of all, love this tool, does exactly what I want and no more. Well done.

Small issue with the brew distribution. I install the latest, and the version appears to be 1.3.0, but it is missing the wait command.

$ dbmate
NAME:
   dbmate - A lightweight, framework-independent database migration tool.

USAGE:
   dbmate [global options] command [command options] [arguments...]

VERSION:
   1.3.0

COMMANDS:
     new, n          Generate a new migration file
     up              Create database (if necessary) and migrate to the latest version
     create          Create database
     drop            Drop database (if it exists)
     migrate         Migrate to the latest version
     rollback, down  Rollback the most recent migration
     dump            Write the database schema to disk
     help, h         Shows a list of commands or help for one command

GLOBAL OPTIONS:
   --env value, -e value             specify an environment variable containing the database URL (default: "DATABASE_URL")
   --migrations-dir value, -d value  specify the directory containing migration files (default: "./db/migrations")
   --schema-file value, -s value     specify the schema file location (default: "./db/schema.sql")
   --no-dump-schema                  don't update the schema file on migrate/rollback
   --help, -h                        show help
   --version, -v                     print the version

When I install using go get the wait command is included as expected.

$ dbmate
NAME:
   dbmate - A lightweight, framework-independent database migration tool.

USAGE:
   dbmate [global options] command [command options] [arguments...]

VERSION:
   1.3.0

COMMANDS:
     new, n          Generate a new migration file
     up              Create database (if necessary) and migrate to the latest version
     create          Create database
     drop            Drop database (if it exists)
     migrate         Migrate to the latest version
     rollback, down  Rollback the most recent migration
     dump            Write the database schema to disk
     wait            Wait for the database to become available
     help, h         Shows a list of commands or help for one command

GLOBAL OPTIONS:
   --env value, -e value             specify an environment variable containing the database URL (default: "DATABASE_URL")
   --migrations-dir value, -d value  specify the directory containing migration files (default: "./db/migrations")
   --schema-file value, -s value     specify the schema file location (default: "./db/schema.sql")
   --no-dump-schema                  don't update the schema file on migrate/rollback
   --help, -h                        show help
   --version, -v                     print the version

I can get by using go get, but my teammates might prefer the brew install. Thanks again!

Hello,

I am trying to migrate stored procedures with dbmate but seems like it doesn't support it. I am getting below error while executing dbmate up command:

sql file content for stored procedure:
DELIMITER //
DROP PROCEDURE IF EXISTS foobar //
create procedure foobar (in var1 int)
begin select var1 + 2 as result;
END //
DELIMITER ;

ERROR:
Applying: 20180907100150_create_store_procedure.sql
Error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER //
DROP PROCEDURE IF EXISTS foobar //
create procedure foobar (in var1' at line 1

Due to some recent changes in net/url package, a github.com/go-sql-driver/mysql#Config.FormatDSN resulting string can not be parsed by net/url/#Parse

The net/url package now parses an URL following more closely RFC 3986; therefore a MySQL DSN string such as the following one would not be parsed anymore:

_, err = url.Parse("mysql://boulder@tcp(localhost:3306)/boulder_test?parseTime=true")
if err != nil {
	fmt.Println(err)
}
// parse mysql://boulder@tcp(localhost:3306)/boulder_test?parseTime=true: invalid port ":3306)" after host

The dbmate.New constructor should be kept compatible with the standard MySQL DSN format and not (necessarily) rely on *url.URL.

A work-around is to manually build a string such as:

sqlURL := fmt.Sprintf("mysql://%s:%s@%s/%s", sqlConfig.User, sqlConfig.Passwd, sqlConfig.Addr, sqlConfig.DBName)
u, _ := url.Parse(sqlURL)
dbmate.New(u)

References
https://play.golang.org/p/jUs0pRjjn_Q
golang/go@61bb56a
golang/go#33646

Is it possible (or, could it be made possible) to 1) rollback more than one step at a time, and 2) (ideally) rollback to a specific named or timestamped migration?

Thank you, and great work!

Hi there! Any plans on publishing Docker image on https://hub.docker.com?

Scenario.

2 developers working on different branches of code.

Developer 1 creates a new migration 20180124192200_featureA.sql

Developer 2 creates a new migration 20180124203000_featureB.sql

Developer 2's code is merged and put into production and dbmate is used to apply 20180124203000_FeatureB.sql.

Developer 1's code is merged and put into production and dbmate is used to apply 20180124192200_FeatureA.sql

I want to rollback FeatureA so I run:

$ dbmate rollback

but instead FeatureB is rolled back because it has a higher timestamp.

1. I want dbmate to apply any missing schema migrations. I don't care about the timestamp and like the way 'migrate' works.
2. I want dbmate to rollback the most recently applied migration, not the migration with the highest timestamp.

How to fix this?

Add a timestamp to schema_migrations row and rollback the most recently applied migration that way rather than a descending sortin on the migration version.

Hi,

as far as I can tell, currently there is now explicit lock applied during migrations. That would mean that it is dangerous to run migrations in a concurrent setting, e.g. when several instances of some API server including dbmate are running.

It would be advisable to obtain an exclusive lock of the migrations table before migrating.

Need to add support for Google Cloud Spanner

Like other database migration tools, dbmate only supports numerical version numbers. I would like to be able to version my migrations semantically, using tags like 1.0.0 for example.

Golang libraries exist that allow you to compare semantic versions, like https://github.com/hashicorp/go-version.

Is there any chance of supporting this style of versioning in future? Is there a database limitation that means these can't be used?

$ DATABASE_URL="postgres://root@localhost:5432/test12?sslmode=disable" dbmate up
Error: pq: no schema has been selected to create "schema_migrations" in

What does it mean?
I've created migration file with the creation of a couple of tables then I'm trying to migrate and getting this error. Maybe you have any ideas?

I have a large existing schema that was manually created.

What are the steps to getting dbmate up to date with this schema and start tracking database migrations through dbmate?

Is there any kind of automation that you can suggest?

Running dbmate on my MySQL 5.6 server with the DEFAULT CHARSET utf8mb4 gave me this error:

Error: Error 1071: Specified key was too long; max key length is 767 bytes

It's because on MySql 5.6 InnoDB with utb8mb4 encoding, the max length limit for any key is 191 characters (utf8mb4 chars are 4 bytes each). Digging into the dbmate code, it looks like it's because the schema_migrationstable is created as such:

// CreateMigrationsTable creates the schema_migrations table
func (drv MySQLDriver) CreateMigrationsTable(db *sql.DB) error {
	_, err := db.Exec("create table if not exists schema_migrations " +
		"(version varchar(255) primary key)")

	return err
}

255 chars * 4 bytes = 1020 bytes, which will go over the limit. This command will fail even if the schema_migrations table has already been manually created with a lower varchar limit.

To fix this, we could possibly:

• Detect if the version is 5.6 and limit the varchar to 191
• Specify latin1 encoding for the schema_migrations table, which only takes 1 byte per character

What do you think? Thanks for making a great tool!

I see you have a comparison table in your readme, but I don't see Squitch in there which has been around for quite a while and would be a good yardstick here.

$ ls -1
20170123192101_testing.sql
20180123192101_testing.sql

Maybe something like:

./dbmate -d . status

revisision, status
20170123192101_testing, APPLIED
20180123192101_testing.sql, UNAPPLIED

Hi, I have the following migration :

-- migrate:up
COPY clients.business_types (id, label) FROM stdin delimiter ',';
ASS,Association
EARL,Entreprise agricole à responsabilité limitée
\.

-- migrate:down

I cant apply it, it gives me the following error :

postgres_1  | 2018-03-10 14:37:22.868 UTC [837] ERROR:  syntax error at or near "ASS" at character 68
postgres_1  | 2018-03-10 14:37:22.868 UTC [837] STATEMENT:
postgres_1  | 	COPY clients.business_types (id, label) FROM stdin delimiter ',';
postgres_1  | 	ASS,Association
postgres_1  | 	EARL,Entreprise agricole à responsabilité limitée
postgres_1  | 	\.

I encountered a strange bug: I had a space in front of -- migrate:down, and the migration was getting applied, successfully, except that it wasn't being run (silently failing).

This was pretty bad and took a while for me to fix.

We should likely have stricter parsing of the syntax, if this can silently fail

While working on wrapper to invoke the command from nodejs, I've found that it would be more flexible to specify the database url via a command line argument rather than having dbmate read it from a .env file. This allows the app and testing environment to provide the database url after loading it however it wants.

Hello,

I want to run dbmate inside the provided docker container, but it's not clear to me how this works in practice. I'm learning docker at the same time here so this may be part of my ignorance.

If I run the dbmate using docker run, then it writes the migration file inside the docker container that was created. But how would I go about accessing that file again? I tried specifying the migrations directory using the --migrations-dir command, but still can't find a way to edit the migration file that was created.

Hi,

When I dbmate migrate the schema dump includes the current AUTO INCREMENT of each table, which is bad since if I want to import the schema as is (without going through all the migrations) to a server or another computer it will start the row count from where my local table ends.

Thanks

Executing:

$ bin/dbmate migrate --no-dump-schema

Gives:

Incorrect Usage: flag provided but not defined: -no-dump-schema

NAME:
   dbmate migrate - Migrate to the latest version

USAGE:
   dbmate migrate [arguments...]
Error: flag provided but not defined: -no-dump-schema

dbmate status would display the current migration status.

It would also be nice to have an optional flag that would just output the number of pending migrations so that external systems can leverage that to warn when migrations are pending during an application boot process for example.

Is there a way to enable logging? I'd like to get an output of the migration.

For example with the following, I'd like to look in a log and see exactly how many rows were inserted. It might not be what I expected.

START TRANSACTION;

INSERT INTO db.tbl
  SELECT col1, col2, col3 FROM db.tbl2
  WHERE col2 IN ( val1, val2, ... );

SELECT ROW_COUNT();

COMMIT;

PostreSQL

-- migrate:up
ALTER TYPE my_enum ADD VALUE 'my_value';

Running this migrations results in

Error: pq: ALTER TYPE ... ADD cannot run inside a transaction block

I'm trying to use dbmate to bring up database on newly provisioned postgres instance.
It does not matter if database exists or not, as well as extension uuid-ossp created with schema public or not. The db instance is AWS RDS PostgreSQL 9.5.
dbmate fails to execute _init.sql script from misgrations folder.
As the result, database created, but initial script is not executed.
The error is:
Error: pq: must be owner of extension uuid-ossp
I'm not sure what is pq in this context, and how to fix it.
Any ideas?

I've been using dbmate with a node project. It works great but it would be nice if I could integrate it with the rest of my projects dependencies. While NPM is focused on Javascript, you can publish any binary to the repository (see https://github.com/sanathkr/go-npm for a go specific tool and instructions on doing this).

This would not only make it easier to use with node projects, but for some users would make it easier to install since NPM handles cross platform binaries automatically. You would even get support for npx dbmate, which lets you run a binary without explicitly installing it first. This would also simplify using it on Heroku.

We have this snippet:

FROM node:lts-slim
RUN curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/download/v1.7.0/dbmate-linux-amd64; chmod +x /usr/local/bin/dbmate
WORKDIR /var/www/
COPY . .
ENV DATABASE_URL="postgres://postgres:pass@/bbf_dev?unix_sock=/cloudsql/pg-bbf-instance-dev/.s.PGSQL.5432"
RUN /usr/local/bin/dbmate -e DATABASE_URL migrate

it simply fails with the migrate command

Step #0: Step 4/10 : WORKDIR /var/www/
Step #0:  ---> Running in ef0ea58fcc5d
Step #0: Removing intermediate container ef0ea58fcc5d
Step #0:  ---> 66c868af67d1
Step #0: Step 5/10 : COPY . .
Step #0:  ---> 2bc0858ba21c
Step #0: Step 6/10 : ENV DATABASE_URL="postgres://postgres:pass@/bbf_dev?unix_sock=/cloudsql/pg-bbf-instance-dev/.s.PGSQL.5432"
Step #0:  ---> Running in 4b06ce1334ac
Step #0: Removing intermediate container 4b06ce1334ac
Step #0:  ---> 12cf38003b1a
Step #0: Step 7/10 : RUN /usr/local/bin/dbmate -e DATABASE_URL migrate
Step #0:  ---> Running in 8429b83962b2
Step #0: �[91mError: dial tcp 127.0.0.1:5432: connect: connection refused
Step #0: The command '/bin/sh -c /usr/local/bin/dbmate -e DATABASE_URL migrate' returned a non-zero code: 1
Finished Step #0
ERROR
ERROR: build step 0 "gcr.io/cloud-builders/docker" failed: exit status 1

it tries a default 127.0.0.1:5432 but neither the .env file nor the ENV DATABASE_URL is being picked up as we don't have 127.0.0.1 neither in the .env file.

Is something broken? Is there another syntax?

I build another example, with export the var is there, but dbmate is not picking up.

Step #0: Step 7/11 : RUN export
Step #0:  ---> Running in b2f14d01c305
Step #0: export DATABASE_URL='postgres://postgres:pass@/bbf_dev?unix_sock=/cloudsql/pg-bbf-instance-dev/.s.PGSQL.5432'
Step #0: export HOME='/root'
Step #0: export HOSTNAME='b2f14d01c305'
Step #0: export NODE_VERSION='12.13.1'
Step #0: export PATH='/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin'
Step #0: export PORT='8080'
Step #0: export PWD='/var/www'
Step #0: export YARN_VERSION='1.19.1'
Step #0: Removing intermediate container b2f14d01c305
Step #0:  ---> 9ed92413d49b
Step #0: Step 8/11 : RUN /usr/local/bin/dbmate -e DATABASE_URL migrate
Step #0:  ---> Running in 1bb033a84122
Step #0: �[91mError: dial tcp 127.0.0.1:5432: connect: connection refused
Step #0: The command '/bin/sh -c /usr/local/bin/dbmate -e DATABASE_URL migrate' returned a non-zero code: 1

the question might be, that it's not picking up the unix_sock

see the GCP guide for unix sockets here: https://cloud.google.com/sql/docs/postgres/connect-run

Similar to Rails, we should dump the current database schema to a db/schema.sql file after running migrations.

This allows developers interacting with a git repo to better track the current state of a database (e.g. when reviewing pull requests, it's much easier to tell what has changed versus reviewing a single migration without context).

I don't believe this is possible to do with the mysql/postgres libraries, so we may need to shell out to mysqldump/pg_dump if they are available, and either error or fail silently if not available.

Suggested changes:

• dbmate migrate - should automatically dump the current schema after applying migrations
• dbmate dump - new command: should dump the current schema

Isnt it better to store the applied migrations in the database migrations table.

I am a little bit worried that there is no confirmation before dropping a database. A single slip-up in typing can ruin all the data on a production server.

With all respect to the simplicity of the original design, I see the solution as an interactive confirmation with printing the host[:port], user and database name in the prompt to make sure the user understands what s/he is going to do, and the --force|-f option for use in scripts.

Are you interested in implementing this? Thanks.

It appears that the timestamp prefix of the schema migration files are used to indicate the ordering of migrations. This feature prohibits using completely custom filenames (e.g. copying a file and changing the suffix and/or using more human-readable timestamps). Moreover, if the filenames are incorrect (or there's a tie in the dates) then migrations could be applied in the wrong order. To resolve this issue, the migration times could be embedded in the files; furthermore dbmate new could ensure a new migration occurs after all existing ones. Additionally it could be helpful to have a tool that can verify the order in which migrations get applied.

Wondering if there is a command if a particular target DB is consistent with the current migrations, without doing destructive changes like dbmate up.

To reproduce:

$ go get -u github.com/amacneil/dbmate
$ dbmate new stuff
DEPRECATED Action signature.  Must be `cli.ActionFunc`.  This is an error in the application.  Please contact the distributor of this application if this is not you.  See https://github.com/urfave/cli/blob/master/CHANGELOG.md#deprecated-cli-app-action-signature

Seems to still work fine.

Follow up request from #72

Why there is no possibility?

dbmate only supports files ending with '\n' instead of the usual Windows/DOS format which is '\r\n'.

Trying to perform a dbmate up command with migration files that have Windows/DOS line ending will result in an error, but it will work if the file is converted back to Unix line endings.

The error produced when trying to run on migration files with DOS file endings:

Applying: 20180605202455_query.sql
Error: Error 1065: Query was empty

Suppose I have some base database structure, which I can apply different changes to, what would be a good way to do that?

One way I thought I could do that could do something like:

• db/migrations/base.sql
• db/migrations/first/migration.sql
• db/migrations/first/base.sql -> ../base.sql
• db/migrations/second/migrations.sql
• db/migrations/second/base.sql -> ../base.sql

So using as symlink, and then call dbmate passing one or the other directory.
I think this would work, is there another better way to do something similar?

I was getting an "Error 1045: Access denied for user...", given a valid connection string. Checked privileges and made sure everything was right on database side. It seems that mysql is fine with connection strings with exclamation points in them, while net/url mangles them and passes them to mysql as %21, which mysql interprets literally.

This assumption doesn't hold true for DigitalOcean's managed databases, where the default postgres database does not get created. I think this is the appropriate bit of code in question: https://github.com/amacneil/dbmate/blob/master/pkg/dbmate/postgres.go#L30. I'm assuming this is so the wait doesn't fail if the database doesn't exist yet, but there's no guarantee postgres exists either, so I'm not sure what the suggest.

It seems like MySQL 8 databases aren't supported due to the new authentication method. I get the following error when running dbmate up using the brew OSX package:

Error: this authentication plugin is not supported

Hi,

great project, it ticks off all of my requirements for a light weight migrations tool, but the following:

The table of applied migrations should contain a column for the hash of each migrations file

This prevents applying a chain of migrations if an already applied migration file has different contents than previously seen. See e.g. node-postgres-migrations for an example where this is implemented.

Additionally, migrations should always be applied in order, so a database with the migrations 100_foo and 200_bar should refuse to apply 150_baz.

• I have more than 5 migrations files. There have been issues where dbmate doesn't apply schema change to databases though it prints that migration name in console.

I think today there is a shutdown race that sometimes causes a rollback, but to make it consistent I think we need a signal handler in here:
https://github.com/amacneil/dbmate/blob/master/pkg/dbmate/db.go#L188,L203

How about the first sigint triggers the rollback (and subsequent exit), and the second one does an immediate exit?