elwinar/rambler

A simple and language-independent SQL schema migration tool

rambler's Issues

`rambler reverse --all` returns exit code 0 on failure

When we reverse a series of migrations and one fails in the middle, rambler returns exit code 0 instead of something non-zero indicating a failure.

Sample of a script we were using that showed this symptom:

#!/usr/bin/env sh

set -euo pipefail

rambler reverse --all
20:56 info  found 14 available migrations
20:56 info  found 14 applied migrations
20:56 info  14 migrations to reverse
20:56 info  reversing 00074_create-catalog-user.sql
20:56 info  reversing 00073_create-etls-user.sql
20:56 info  reversing 00072_create-periscope-user.sql
20:56 info  reversing 00071_create-fakebook-user.sql
unable to reverse migration 00071_create-fakebook-user.sql: pq: role "fakebook" cannot be dropped because some objects depend on it

DROP user fakebook;

$ echo $?
0

Release version 3.3 to cover commits since last release

As of writing this, the last release is v3.2.0 dated 2015-09-15. There have been 11 commits since then, one of which has functionality I need for some of my projects. Could you generate a v3.3.0 or v3.2.1 release so I can simplify my ansible scripts?

I looked into doing it myself as a pull request but there's not a lot of info out there on how to get this done.

Retag the version for go modules

The issue

When getting rambler from go tools, I don't get the latest tagged version.

Though I don't care about the version value, the go get-able version does not provide a --debug flag, and does not fully comply with the configuration's environment behavior.

Reproduce

$ go get github.com/elwinar/rambler
go: finding github.com/bradfitz/slice latest
go: downloading github.com/mattn/go-sqlite3 v1.10.0
go: extracting github.com/mattn/go-sqlite3 v1.10.0

Behaviour

$ rambler --help
NAME:
   rambler - Migrate all the things!

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

AUTHOR:
   Romain Baugue <[email protected]>

COMMANDS:
     apply    apply the next migration
     reverse  reverse the last migration
     help, h  Shows a list of commands or help for one command

GLOBAL OPTIONS:
   --configuration value, -c value  path to the configuration file (default: "rambler.json")
   --environment value, -e value    set the working environment (default: "default")
   --help, -h                       show help
   --version, -v                    print the version

$ rambler --version
rambler version 

Expected

$ rambler --help
NAME:
   rambler - Migrate all the things!

USAGE:
   rambler-backup [global options] command [command options] [arguments...]

VERSION:
   5.0.0-2-g79c51d2

AUTHOR:
   Romain Baugue <[email protected]>

COMMANDS:
     apply    apply the next migration
     reverse  reverse the last migration
     help, h  Shows a list of commands or help for one command

GLOBAL OPTIONS:
   --configuration value, -c value  path to the configuration file (default: "rambler.json")
   --environment value, -e value    set the working environment (default: "default")
   --debug                          display debug messages
   --help, -h                       show help
   --version, -v                    print the version

$ rambler --version
rambler version  5.0.0-2-g79c51d2

Possible to use with just environment variables?

It seems rambler.json is required:

unable to load configuration from file: open rambler.json: no such file or directory

Would be nice to use only environment variables so connection settings can differ based on environment (dev, staging, production) without resorting to weird hacks :)

What about using HJSON ?

I recently stumbled on https://hjson.org/.
I find it awesome and quite easier to use than JSON for things like configuration : the ability to add comments and the allowed flex in the syntax make it easier to use.

What would you think about using this from now on, with a fallback on plain old JSON for backward compatibility matters ?

@shawndellysse
@rsterbin
@cjhubert

Down statements aren't applied in reverse order

It seem that down statements aren't applied in reverse order, as specified in the documentatioN;

Hive support

Add support for Apache Hive

No errors or tables created

I made a migration file and a config file, it did create the migration table in the database, but when I run apply nothing happens and no errors are outputted.

It would be cool if there were some output to debug what happens :)

When rambler errors exit with a non-zero code

Break sections into statements

Actually, a migration is broke down into up and down sections, which fit the simplest case and force the user to write a new section for each statement.

It would be nice to be able to break each section into the composing statements by doing a basic parsing of the string.

First ideas:

• Splitting around ; is a good starting point
• PL/SQL, procedures and triggers use ; multiple times by declaration, to there should be a way to detect this type of statements. Ideally without any user intervention (like adding a -- +rambler Whatever tag before and after). Maybe parsing and detecting BEGIN keywords would be sufficient.

Support for Postgres sslmode

Rambler seems to hardcode the sslmode flag for Postgres databases to disable making it impossible to use it with databases that enforce TLS connections. Would it be possible to expose that value as part of rambler's JSON configuration file or environment variables?

rambler apply --all Requires Old Migration Files to Exist

This is kind of special use case, but I have here's a setup I have:

One repo for programmable infrastructure. This contains migrations that setup up Postgres schemas, users, functions, and so on:

$ rambler apply --all
18:31 info  initializing database
18:31 info  found 5 available migrations
18:31 info  found 0 applied migrations
18:31 info  5 migrations to apply
18:31 info  applying 201809070000_uuid-extension.sql
18:31 info  applying 201809070001_public-isolation.sql
18:31 info  applying 201809070002_public-functions.sql
...
18:31 info  done

One or more repos for specific services that rely on the programmable infrastructure migrations to have run first. On running their migrations I get:

$ rambler apply --all
18:31 info  found 5 available migrations
unable to retrieve applied migrations: unable to open file migrations/201809070000_uuid-extension.sql: open migrations/201809070000_uuid-extension.sql: no such file or directory

In my opinion an apply doesn't need to read migration files that were already applied, thoughts?

Mac release not working on install

After a bit of fuss trying to convince Big Sur that it was safe to execute the downloaded file, I managed to get it installed, but it's not working as expected, and the error message leaves something to be desired:

After installing, I tried to get something like a help message:

0 [rsterbin@adelle ~]$ rambler
unable to initialize the migration service: directory  unavailable: stat : no such file or directory

A few years ago I'd set up a project (with rambler from installed from source), and while I don't remember whether there was a usage/error/help message, I do remember that I need to give it a config file. Maybe that was the problem? Nope:

0 [rsterbin@adelle ~/git/stmvirgin/lituricalendar/db/migrations main]$ cat config.json
{
	"directory": ".",
	"driver": "postgres",
	"host": "localhost",
	"port": 5432,
	"user": "rsterbin",
	"password": "xxredactedxx",
	"database": "liturgicalendar",
	"table": "migrations"
}
0 [rsterbin@adelle ~/git/stmvirgin/lituricalendar/db/migrations main]$ ls
20160810131200_schema_services.sql          20160810133220_stmarys_seasons.sql          20160811165200_schema_completed.sql         20170204161816_schema_overrides.sql
20160810131215_stmarys_services.sql         20160810134300_schema_fixed_feasts.sql      20170108223042_schema_floating_feasts.sql   20170204162938_stmarys_overrides.sql
20160810132300_schema_precedence.sql        20160810134357_stmarys_fixed_feasts.sql     20170108223353_stmarys_floating_feasts.sql  20170225143814_schema_calculated.sql
20160810132316_stmarys_precedence.sql       20160810135100_schema_moveable_feasts.sql   20170127204345_schema_federal_holidays.sql  config.json
20160810133200_schema_seasons.sql           20160810135105_stmarys_moveable_feasts.sql  20170127204836_stmarys_federal_holidays.sql
0 [rsterbin@adelle ~/git/stmvirgin/lituricalendar/db/migrations main]$ rambler
unable to initialize the migration service: directory  unavailable: stat : no such file or directory
0 [rsterbin@adelle ~/git/stmvirgin/lituricalendar/db/migrations main]$ rambler apply
unable to initialize the migration service: directory  unavailable: stat : no such file or directory

So I threw the release away and installed from source instead:

0 [rsterbin@adelle ~]$ go install github.com/elwinar/rambler
go install: version is required when current directory is not in a module
	Try 'go install github.com/elwinar/rambler@latest' to install the latest version
0 [rsterbin@adelle ~]$ go install github.com/elwinar/rambler@latest

(skipping install output, it's long and it worked)

Trying again:

0 [rsterbin@adelle ~]$ rambler
unable to load configuration from file: open rambler.json: no such file or directory

That's an error I can do something about:

0 [rsterbin@adelle ~]$ cd ~/git/stmvirgin/lituricalendar/db/migrations
0 [rsterbin@adelle ~/git/stmvirgin/lituricalendar/db/migrations main]$ mv config.json rambler.json
0 [rsterbin@adelle ~/git/stmvirgin/lituricalendar/db/migrations main]$ rambler
NAME:
   rambler - Migrate all the things!

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

AUTHOR:
   Romain Baugue <[email protected]>

COMMANDS:
   apply    apply the next migration
   reverse  reverse the last migration
   help, h  Shows a list of commands or help for one command

GLOBAL OPTIONS:
   --configuration value, -c value  path to the configuration file (default: "rambler.json")
   --environment value, -e value    set the working environment (default: "default")
   --debug                          display debug messages
   --help, -h                       show help

tl;dr installing the rambler-darwin-10.6-amd64 release on OSX 11.4 doesn't seem to be working, although installing from source works just fine.

Rambler exits with status 0 on apply failure

Example failures that exit with status 0:

unable to check for database state: pq: role "postgres" does not exist

unable to check for database state: dial tcp [::1]:5432: connect: connection refused

I would expect it to exit with a non-zero status to indicate the command failed. My primary use case for this is to ensure a Travis CI build fails when rambler apply --all fails.

make migrations table name configurable

table name migrations is too common, either make it configurable or at least prefix it with something like rambler_migrations, thank you for a great project

Unable to run go install

I am unable to run the following command successfully:
go install github.com/elwinar/rambler@latest

It produces the error:
github.com/imdario/mergo: github.com/imdario/[email protected]: parsing go.mod: module declares its path as: dario.cat/mergo but was required as: github.com/imdario/mergo

Support PG schemas

PG differentiates between schema and database. It also recommends against the use of public to the point that a PG admin will delete the schema 'public'. The present state of the tool does not support schemas. Please enable them.

A PG schema is defined as schema.table. While it is possible that someone might have a table name with '.' in it, they are operating outside the norm of PG. It is also possible to write a SQL statement that FQ's the database name like 'db.schema.table' though it odd and against best-practices.

At minimum the tool needs to support schema.table. The migration table needs to live in the schema, not in public. Failing to properly isolate the migration in the schema can lead to undefined behavior when multiple projects independently run rambler under different schemas.

New Dockerfile in added in ce8928d73653f286e5f439478367e4d01f67c077 breaks rambler under docker.

The reason for this is that the generated rambler binary for the dockerfile isn't statically-linked, causing it to fail to load the libraries, which causes it to fail to start at all.

postgres connection strings

Hi - love Rambler 👍

Is there a way of using a postgres connection string instead of password, username etc?

Official Docker Image

We're using rambler with Kubernetes to manage out database migrations. We'd love to have an official Docker image instead of building our own.

Currently, we are using a rambler binary built from #19 which can read config values from the environment. Our Dockerfile is:

FROM alpine

ENV APP_HOME=/migrator
RUN mkdir -p ${APP_HOME}
WORKDIR ${APP_HOME}

COPY bin/rambler rambler
COPY rambler.json rambler.json
COPY migrations migrations

CMD ["./rambler", "apply", "-a"]

An example from our Docker compose file:

version: '2'
services:
  migrator:
    build: ./migrator
    volumes:
      - "./migrator/migrations:/migrator/migrations"
    depends_on:
      - mysql
    environment:
      RAMBLER_HOST: mysql
      RAMBLER_PORT: 3306
      RAMBLER_USER: user
      RAMBLER_PASSWORD: password
      RAMBLER_DATABASE: db_name
  mysql:
    image: mysql:5.6
    volumes:
      - "/var/lib/mysql"
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: db_name
      MYSQL_USER: user
      MYSQL_PASSWORD: password

Right now, rambler will bail out when it can't find a config file, so we've only put the static, non-secret configuration in the file (driver, directory, and protocol).

The Kubernetes Job specification looks like:

apiVersion: batch/v1
kind: Job
metadata:
  name: migrator
spec:
  template:
    metadata:
      name: migrator
    spec:
      containers:
      - name: migrator
        image: idnumber.dkr.ecr.us-west-2.amazonaws.com/migrator:v20161102122200
        env:
          - name: RAMBLER_HOST
            valueFrom:
              secretKeyRef:
                name: mysql-configuration
                key: host
          - name: RAMBLER_PORT
            valueFrom:
              secretKeyRef:
                name: mysql-configuration
                key: port
          - name: RAMBLER_DATABASE
            valueFrom:
              secretKeyRef:
                name: mysql-configuration
                key: name
          - name: RAMBLER_USER
            valueFrom:
              secretKeyRef:
                name: mysql-configuration
                key: user
          - name: RAMBLER_PASSWORD
            valueFrom:
              secretKeyRef:
                name: mysql-configuration
                key: password
      restartPolicy: Never

I think specifying the volume locally, like it's being done in the docker-compose file, works really well. However, I'm not sure how that would work in the actual K8s cluster. Perhaps something a gitRepo volumes would work. Either way, I don't think that's a rambler concern.

How should statements that cause an implicit commit be handled ?

Some statements (CREATE|DROP|ALTER TABLE for example) cause MySQL (and probably others RDBMS) to implicitly COMMIT the current transaction, which prevent rollbacks.

As working with this kind of statement is the primary goal of rambler, using transactions is probably useless as even when there is an error in the migration, it can't be rollbacked.

To solve the problem, my current solution is: remove the transactions from the code, then add some advices on migration writing, like using IF NOT EXISTS while writing the migration then removing it before committing to the VCS.

Typo in repo description

In the GitHub repo description, above the commit count,

independant

should be

independent

Installation on mac

Hi,

I installed go on mac and executed the installation using
go install github.com/elwinar/rambler@latest

go: finding module for package github.com/go-sql-driver/mysql
go: finding module for package github.com/client9/xson/hjson
go: finding module for package github.com/mattn/go-sqlite3
go: finding module for package github.com/bradfitz/slice
go: finding module for package github.com/lib/pq
go: finding module for package github.com/kelseyhightower/envconfig
go: finding module for package github.com/urfave/cli
go: finding module for package github.com/imdario/mergo
go: found github.com/bradfitz/slice in github.com/bradfitz/slice v0.0.0-20180809154707-2b758aa73013
go: found github.com/client9/xson/hjson in github.com/client9/xson v0.0.0-20180321172152-0e50cdfc08c0
go: found github.com/imdario/mergo in github.com/imdario/mergo v0.3.12
go: found github.com/kelseyhightower/envconfig in github.com/kelseyhightower/envconfig v1.4.0
go: found github.com/urfave/cli in github.com/urfave/cli v1.22.5
go: found github.com/go-sql-driver/mysql in github.com/go-sql-driver/mysql v1.6.0
go: found github.com/lib/pq in github.com/lib/pq v1.10.3
go: found github.com/mattn/go-sqlite3 in github.com/mattn/go-sqlite3 v1.14.9

I get only this much log and not logs after this and cannot find rambler command in the terminal to execute. Tried a new terminal window as well.
I downloaded rambler-darwin-10.6-amd64 but how do you execute it?
I am new to go just wanted to run some existing migrations. Please help me install rambler.

Configure a build pipeline

• Continuous integration
• Automatic cross-compilation
• Code coverage

Implement dry-run

Hi,

Is it possible to implement a dry-run feature on apply and/or reverse behavior ?
Here is an example (based on our current stack) of what would be done:

rambler apply -a --dry-run
15:31 info dry-run initializing database
15:31 info dry-run found x available migrations
15:31 info dry-run found y applied migrations
15:31 info dry-run z migrations to apply
15:31 info dry-run applying 2019_08_09_09_23_10.create_customer_table.sql
15:31 info dry-run applying 2019_08_09_14_05_00.create_user_table.sql
15:31 info dry-run applying 2019_08_09_14_55_00.create_role_table.sql

Support for Alpine Linux (docker)

Hi! Thanks for the very nice tool!

I’m trying to add rambler to our existing docker image, which uses alpine:

RUN wget -O rambler https://github.com/elwinar/rambler/releases/download/v5.1.0/rambler-linux-amd64
RUN chmod +x rambler

However, I get this error when trying to run the downloaded file inside docker:

standard_init_linux.go:211: exec user process caused "no such file or directory"

Switching to a non-alpine docker image makes rambler work, but I’d like to stay with alpine.

I don’t know much about this stuff, but as far as I understand you would need to provide a musl version of the linux binaries in addition to the glibc ones provided now.

Here’s a sample Dockerfile to try things out:

FROM alpine

RUN wget -O rambler https://github.com/elwinar/rambler/releases/download/v5.1.0/rambler-linux-amd64
RUN chmod +x rambler

ENTRYPOINT ["/rambler"]
CMD ["--help"]

Would it be possible to add alpine support? Thanks!

Make a presentation website

Something simple, modern, that loads fast and describe the philosophy of Ramble : simpler is better. Download links for the current release and links to the github project and the constributors github accounts and/or personnal websites.

unable to check for database state: dial tcp: lookup port=5432: no such host

Hi,

I encountered the above error with the rambler.json below. My intention was to migrate multiple databases on multiple hosts.

{
    "driver": "postgresql",
    "protocol": "tcp",
    "directory": "sql",
    "port": 5432,
    "table": "migrations",
    "environments": {
        "development": {
            "host": "my.hostname.com",
            "port": 5432,
            "user": "someuser",
            "password": "xxx",
            "database": "db_template1"
        },
        "testing": {
            "host": "other.hostname.com",
            "port": 5432,
            "user": "username",
            "password": "xxx",
            "database": "db_template2"
        }
    }
}

Revert on error

Context

For a migration like this:

-- rambler up
CREATE TABLE ...

-- rambler down
DROP TABLE ...

-- rambler up
-- a failing query here

-- rambler down
-- ...

Workflow:
We run rambler apply, the first up works but the second one fails.
We fix the second query, but we can't run it because the first one will fail as the resource is already created.

Proposal

Revert migration for created resources.

Using the exemple above, if second query fails, DROP TABLE must be run.

Add a SQLite driver

No schema selected?

I have a bevy of migrations that are failing to run, due to the following error:

info  initializing database
unable to initialize database: pq: no schema has been selected to create in

RAMBLER_SCHEMA is set to a schema that gets created inside of the migrations.

Unfortunately, the project is closed source or else I would share a link here, but is there a way to log additional debug?

Rambler version: 5.3.0

Create/reverse to commands

Hi :)

I needed a migration tool, so I happened to pick yours, thanks to your stackoverflow answer.
I implemented "create" and "reverse to" commands, and kindly edited the way you check consistency
of available and applied migrations. I am actually not completely done with "reverse to" command.
Would you be open to contributions ?

Add the refresh command

The refresh command should revert the last migration then re-apply it. It should accept the --all flag to revert all migrations then apply them once again.

PGDATABASE required for Travis CI

Not entirely sure why, but Travis CI does not respect either database out of rambler.json or RAMBLER_DATABASE.

Here's an example .travis.yml:

language: go

services:
  - postgresql

addons:
  postgresql: "9.6"

go:
  - "1.10.x"

env:
  - DB_USERNAME=postgres DB_PASSOWRD= RAMBLER_USER=postgres RAMBLER_PASSWORD=

before_install:
  - cp .netrc ~
  - chmod 600 .netrc

install:
  - go get github.com/golang/lint/golint
  - go get github.com/golang/dep/cmd/dep

  - go get github.com/elwinar/rambler
  - go install github.com/elwinar/rambler

before_script:
  - psql --username postgres --command 'CREATE DATABASE countries;'

script:
  - export PGDATABASE=countries
  - rambler apply --all
  - psql --username postgres --command '\du+'
  - psql --username postgres --command '\l+'
  - psql --username postgres --dbname countries --command '\dn+'
  - psql --username postgres --dbname countries --command '\dt+'
  - psql --username postgres --dbname countries --command '\dt+ countries.'

  - dep ensure
  - go test ./... -v
  - go vet ./...
  - golint $(go list ./...)
  - go build ./cmd/server

Without PGDATABASE rambler uses the default postgres database. Setting PGDATABASE in env causes the CREATE DATABASE to fail:

$ psql --username postgres --command 'CREATE DATABASE countries;'
psql: FATAL:  database "countries" does not exist
The command "psql --username postgres --command 'CREATE DATABASE countries;'" failed and exited with 2 during .

I have a feeling this has to do with the connection string and an empty password for the postgres user.

host from rambler.json, not added to final connection string

also with env variable is the same

when we change port, it is affected, but host is always "localhost"

arm64 support for linux and darwin?

Hi! Thank you for the very nice tool!

Would it be possible to get arm64 support for both linux and darwin?

I see that the Makefile already includes darwin/arm64 but there does not seem to be a release built with that: https://github.com/elwinar/rambler/blob/master/Makefile#L1

Googling XGo (we're currently trying to build a custom docker image that adds arm64 support) I noticed that there seem to be at least two other forks that seem more up to date than Karalabe's:

• https://github.com/crazy-max/xgo
• https://github.com/techknowlogick/xgo/

Support for Firebird SQL

Any plans on supporting Firebird SQL?

https://github.com/nakagami/firebirdsql
works well with
https://golang.org/pkg/database/sql/