Dbmate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers.

Overview

Dbmate

GitHub Build Go Report Card GitHub Release

Dbmate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers.

It is a standalone command line tool, which can be used with Go, Node.js, Python, Ruby, PHP, or any other language or framework you are using to write database-backed applications. This is especially helpful if you are writing many services in different languages, and want to maintain some sanity with consistent development tools.

For a comparison between dbmate and other popular database schema migration tools, please see the Alternatives table.

Table of Contents

Features

  • Supports MySQL, PostgreSQL, SQLite, and ClickHouse.
  • Uses plain SQL for writing schema migrations.
  • Migrations are timestamp-versioned, to avoid version number conflicts with multiple developers.
  • Migrations are run atomically inside a transaction.
  • Supports creating and dropping databases (handy in development/test).
  • Supports saving a schema.sql file to easily diff schema changes in git.
  • Database connection URL is definied using an environment variable (DATABASE_URL by default), or specified on the command line.
  • Built-in support for reading environment variables from your .env file.
  • Easy to distribute, single self-contained binary.

Installation

macOS

Install using Homebrew:

$ brew install dbmate

Linux

Download the binary directly:

$ sudo curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64
$ sudo chmod +x /usr/local/bin/dbmate

Docker

You can run dbmate using the official docker image (remember to set --network=host or see this comment for more tips on using dbmate with docker networking):

$ docker run --rm -it --network=host amacneil/dbmate --help

If you wish to create or apply migrations, you will need to use Docker's bind mount feature to make your local working directory (pwd) available inside the dbmate container:

$ docker run --rm -it --network=host -v "$(pwd)/db:/db" amacneil/dbmate new create_users_table

Heroku

To use dbmate on Heroku, the easiest method is to store the linux binary in your git repository:

$ mkdir -p bin
$ curl -fsSL -o bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64
$ chmod +x bin/dbmate
$ git add bin/dbmate
$ git commit -m "Add dbmate binary"
$ git push heroku master

You can then run dbmate on heroku:

$ heroku run bin/dbmate up

Commands

dbmate --help    # print usage help
dbmate new       # generate a new migration file
dbmate up        # create the database (if it does not already exist) and run any pending migrations
dbmate create    # create the database
dbmate drop      # drop the database
dbmate migrate   # run any pending migrations
dbmate rollback  # roll back the most recent migration
dbmate down      # alias for rollback
dbmate status    # show the status of all migrations (supports --exit-code and --quiet)
dbmate dump      # write the database schema.sql file
dbmate wait      # wait for the database server to become available

Command Line Options

The following options are available with all commands. You must use command line arguments in the order dbmate [global options] command [command options]. Most options can also be configured via environment variables (and loaded from your .env file, which is helpful to share configuration between team members).

  • --url, -u "protocol://host:port/dbname" - specify the database url directly. (env: $DATABASE_URL)
  • --env, -e "DATABASE_URL" - specify an environment variable to read the database connection URL from.
  • --migrations-dir, -d "./db/migrations" - where to keep the migration files. (env: $DBMATE_MIGRATIONS_DIR)
  • --migrations-table "schema_migrations" - database table to record migrations in. (env: $DBMATE_MIGRATIONS_TABLE)
  • --schema-file, -s "./db/schema.sql" - a path to keep the schema.sql file. (env: $DBMATE_SCHEMA_FILE)
  • --no-dump-schema - don't auto-update the schema.sql file on migrate/rollback (env: $DBMATE_NO_DUMP_SCHEMA)
  • --wait - wait for the db to become available before executing the subsequent command (env: $DBMATE_WAIT)
  • --wait-timeout 60s - timeout for --wait flag (env: $DBMATE_WAIT_TIMEOUT)

Usage

Connecting to the Database

Dbmate locates your database using the DATABASE_URL environment variable by default. If you are writing a twelve-factor app, you should be storing all connection strings in environment variables.

To make this easy in development, dbmate looks for a .env file in the current directory, and treats any variables listed there as if they were specified in the current environment (existing environment variables take preference, however).

If you do not already have a .env file, create one and add your database connection URL:

$ cat .env
DATABASE_URL="postgres://[email protected]:5432/myapp_development?sslmode=disable"

DATABASE_URL should be specified in the following format:

protocol://username:[email protected]:port/database_name?options
  • protocol must be one of mysql, postgres, postgresql, sqlite, sqlite3, clickhouse
  • host can be either a hostname or IP address
  • options are driver-specific (refer to the underlying Go SQL drivers if you wish to use these)

Dbmate can also load the connection URL from a different environment variable. For example, before running your test suite, you may wish to drop and recreate the test database. One easy way to do this is to store your test database connection URL in the TEST_DATABASE_URL environment variable:

$ cat .env
DATABASE_URL="postgres://[email protected]:5432/myapp_dev?sslmode=disable"
TEST_DATABASE_URL="postgres://[email protected]:5432/myapp_test?sslmode=disable"

You can then specify this environment variable in your test script (Makefile or similar):

$ dbmate -e TEST_DATABASE_URL drop
Dropping: myapp_test
$ dbmate -e TEST_DATABASE_URL --no-dump-schema up
Creating: myapp_test
Applying: 20151127184807_create_users_table.sql

Alternatively, you can specify the url directly on the command line:

$ dbmate -u "postgres://[email protected]:5432/myapp_test?sslmode=disable" up

The only advantage of using dbmate -e TEST_DATABASE_URL over dbmate -u $TEST_DATABASE_URL is that the former takes advantage of dbmate's automatic .env file loading.

PostgreSQL

When connecting to Postgres, you may need to add the sslmode=disable option to your connection string, as dbmate by default requires a TLS connection (some other frameworks/languages allow unencrypted connections by default).

DATABASE_URL="postgres://username:[email protected]:5432/database_name?sslmode=disable"

A socket or host parameter can be specified to connect through a unix socket (note: specify the directory only):

DATABASE_URL="postgres://username:[email protected]/database_name?socket=/var/run/postgresql"

A search_path parameter can be used to specify the current schema while applying migrations, as well as for dbmate's schema_migrations table. If the schema does not exist, it will be created automatically. If multiple comma-separated schemas are passed, the first will be used for the schema_migrations table.

DATABASE_URL="postgres://username:[email protected]:5432/database_name?search_path=myschema"
DATABASE_URL="postgres://username:[email protected]:5432/database_name?search_path=myschema,public"

MySQL

DATABASE_URL="mysql://username:[email protected]:3306/database_name"

A socket parameter can be specified to connect through a unix socket:

DATABASE_URL="mysql://username:[email protected]/database_name?socket=/var/run/mysqld/mysqld.sock"

SQLite

SQLite databases are stored on the filesystem, so you do not need to specify a host. By default, files are relative to the current directory. For example, the following will create a database at ./db/database.sqlite3:

DATABASE_URL="sqlite:db/database.sqlite3"

To specify an absolute path, add a forward slash to the path. The following will create a database at /tmp/database.sqlite3:

DATABASE_URL="sqlite:/tmp/database.sqlite3"

ClickHouse

DATABASE_URL="clickhouse://username:[email protected]:9000/database_name"

or

DATABASE_URL="clickhouse://127.0.0.1:9000?username=username&password=password&database=database_name"

See other supported connection options.

Creating Migrations

To create a new migration, run dbmate new create_users_table. You can name the migration anything you like. This will create a file db/migrations/20151127184807_create_users_table.sql in the current directory:

-- migrate:up

-- migrate:down

To write a migration, simply add your SQL to the migrate:up section:

-- migrate:up
create table users (
  id integer,
  name varchar(255),
  email varchar(255) not null
);

-- migrate:down

Note: Migration files are named in the format [version]_[description].sql. Only the version (defined as all leading numeric characters in the file name) is recorded in the database, so you can safely rename a migration file without having any effect on its current application state.

Running Migrations

Run dbmate up to run any pending migrations.

$ dbmate up
Creating: myapp_development
Applying: 20151127184807_create_users_table.sql
Writing: ./db/schema.sql

Note: dbmate up will create the database if it does not already exist (assuming the current user has permission to create databases). If you want to run migrations without creating the database, run dbmate migrate.

Pending migrations are always applied in numerical order. However, dbmate does not prevent migrations from being applied out of order if they are committed independently (for example: if a developer has been working on a branch for a long time, and commits a migration which has a lower version number than other already-applied migrations, dbmate will simply apply the pending migration). See #159 for a more detailed explanation.

Rolling Back Migrations

By default, dbmate doesn't know how to roll back a migration. In development, it's often useful to be able to revert your database to a previous state. To accomplish this, implement the migrate:down section:

-- migrate:up
create table users (
  id integer,
  name varchar(255),
  email varchar(255) not null
);

-- migrate:down
drop table users;

Run dbmate rollback to roll back the most recent migration:

$ dbmate rollback
Rolling back: 20151127184807_create_users_table.sql
Writing: ./db/schema.sql

Migration Options

dbmate supports options passed to a migration block in the form of key:value pairs. List of supported options:

  • transaction

transaction

transaction is useful if you need to run some SQL which cannot be executed from within a transaction. For example, in Postgres, you would need to disable transactions for migrations that alter an enum type to add a value:

-- migrate:up transaction:false
ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';

transaction will default to true if your database supports it.

Waiting For The Database

If you use a Docker development environment for your project, you may encounter issues with the database not being immediately ready when running migrations or unit tests. This can be due to the database server having only just started.

In general, your application should be resilient to not having a working database connection on startup. However, for the purpose of running migrations or unit tests, this is not practical. The wait command avoids this situation by allowing you to pause a script or other application until the database is available. Dbmate will attempt a connection to the database server every second, up to a maximum of 60 seconds.

If the database is available, wait will return no output:

$ dbmate wait

If the database is unavailable, wait will block until the database becomes available:

$ dbmate wait
Waiting for database....

You can also use the --wait flag with other commands if you sometimes see failures caused by the database not yet being ready:

$ dbmate --wait up
Waiting for database....
Creating: myapp_development

You can customize the timeout using --wait-timeout (default 60s). If the database is still not available, the command will return an error:

$ dbmate --wait-timeout=5s wait
Waiting for database.....
Error: unable to connect to database: dial tcp 127.0.0.1:5432: connect: connection refused

Please note that the wait command does not verify whether your specified database exists, only that the server is available and ready (so it will return success if the database server is available, but your database has not yet been created).

Exporting Schema File

When you run the up, migrate, or rollback commands, dbmate will automatically create a ./db/schema.sql file containing a complete representation of your database schema. Dbmate keeps this file up to date for you, so you should not manually edit it.

It is recommended to check this file into source control, so that you can easily review changes to the schema in commits or pull requests. It's also possible to use this file when you want to quickly load a database schema, without running each migration sequentially (for example in your test harness). However, if you do not wish to save this file, you could add it to your .gitignore, or pass the --no-dump-schema command line option.

To dump the schema.sql file without performing any other actions, run dbmate dump. Unlike other dbmate actions, this command relies on the respective pg_dump, mysqldump, or sqlite3 commands being available in your PATH. If these tools are not available, dbmate will silenty skip the schema dump step during up, migrate, or rollback actions. You can diagnose the issue by running dbmate dump and looking at the output:

$ dbmate dump
exec: "pg_dump": executable file not found in $PATH

On Ubuntu or Debian systems, you can fix this by installing postgresql-client, mysql-client, or sqlite3 respectively. Ensure that the package version you install is greater than or equal to the version running on your database server.

Note: The schema.sql file will contain a complete schema for your database, even if some tables or columns were created outside of dbmate migrations.

Internals

schema_migrations table

By default, dbmate stores a record of each applied migration in a schema_migrations table. This table will be created for you automatically if it does not already exist. The table schema is very simple:

CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(255) PRIMARY KEY
)

Dbmate records only the version number of applied migrations, so you can safely rename a migration file without affecting its applied status.

You can customize the name of this table using the --migrations-table flag or $DBMATE_MIGRATIONS_TABLE environment variable. If you already have a table with this name (possibly from a previous migration tool), you should either manually update it to conform to this schema, or configure dbmate to use a different table name.

Alternatives

Why another database schema migration tool? Dbmate was inspired by many other tools, primarily Active Record Migrations, with the goals of being trivial to configure, and language & framework independent. Here is a comparison between dbmate and other popular migration tools.

dbmate goose sql-migrate golang-migrate activerecord sequelize
Features
Plain SQL migration files
Support for creating and dropping databases
Support for saving schema dump files
Timestamp-versioned migration files
Custom schema migrations table
Ability to wait for database to become ready
Database connection string loaded from environment variables
Automatically load .env file
No separate configuration file
Language/framework independent ✴️ ✴️
Drivers
PostgreSQL
MySQL
SQLite
CliсkHouse

✴️ In theory these tools could be used with other languages, but a Go development environment is required because binary builds are not provided.

If you notice any inaccuracies in this table, please propose a change.

Contributing

Dbmate is written in Go, pull requests are welcome.

Tests are run against a real database using docker-compose. To build a docker image and run the tests:

$ make docker-all

To start a development shell:

$ make docker-sh
Comments
  • Postgres defaults to unix socket with no host

    Postgres defaults to unix socket with no host

    Closes #229

    When there's no host given in a postgres connection url, use the unix domain default socket. Previously, the default was a localhost TCP connection. This default behavior is more in line with standard postgres clients out there, like psql.

    opened by wraithm 22
  • Feature: Nested directories in migration directory?

    Feature: Nested directories in migration directory?

    In a project with a lot of migration files, it would be great to structure those in separate sub-directories, such as:

    ├── 0001.sql
    ├── 0005.sql
    ├── week1
    │   └── 0002.sql
    └── week2
        ├── 0003.sql
        └── 0004.sql
    

    I know that it is possible to specify the migration directory, but this makes the other folders "invisible" to DBMate. While this still works and won't (seem to) break DBMate, it would be great to have this work natively.

    I would like to volunteer to implement this feature, if needed.

    opened by JanmanX 8
  • add separate migration option

    add separate migration option

    Adds separate migration option. If true queries will be applied separately one by one. It is useful for databases which does not support multiple queries such as ClickHouse.

    Default values for this option is false.

    opened by ybogo 8
  • adding verbose output for statement execution

    adding verbose output for statement execution

    Adding a verbose command line flag to allow users to print the result of each statement execution. Should not disrupt the existing flow for users as it's completely optional. Summary of changes:

    • new verbose flag
    • new field for db to print result of migrations
    • new interface methods for drivers to pass verbose flag
    • capture and print of result for every exec based on verbose flag across db and drivers
    opened by vperiyasamy 8
  • use unix_sock with Google cloud platforms: Cloud SQL syntax

    use unix_sock with Google cloud platforms: Cloud SQL syntax

    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:[email protected]/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:[email protected]/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: Error: 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:[email protected]/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: Error: 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

    opened by pentium10 8
  • Environment Variables within SQL Scripts

    Environment Variables within SQL Scripts

    Hello,

    I would like access to variables within the .env file from each .sql file; i.e.: if I have

    # .env
    ROLE="authentication"
    ROLE_PASS="hunter2"
    

    Then I could use a placeholder of

    -- 2020020319418_db_role.sql
    create role :"ROLE" login password :'ROLE_PASS'
    

    Is this of interest to folks or is this already possible and I just can't seem to see how? It doesn't seem like golang's pq support's psql's \set type statements in scripts either. Maybe there's a way to do this through pq I'm unaware of?

    feature request 
    opened by xemoka 7
  • schema dump includes `AUTO INCREMENT`

    schema dump includes `AUTO INCREMENT`

    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

    feature request 
    opened by david-a 7
  • How use in Windows?

    How use in Windows?

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

    opened by Stunext 7
  • Inconsistencies with migrate and rollback.

    Inconsistencies with migrate and rollback.

    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.

    opened by matthewlenz 7
  • rollback: export ErrNoRollback to rollback all

    rollback: export ErrNoRollback to rollback all

    Hi,

    First, thanks for this great project!

    I'm integrating dbmate as a golang library in my project and, in order to test all migrations, I want to apply all rollback migrations until the original state. To achieve this, I'm using the following function:

    func rollbackAll(mate *dbmate.DB) error {
    	for {
    		err := mate.Rollback()
    		if err == nil {
    			continue
    		}
    		// no more migration to rollback
    		if err.Error() == "can't rollback: no migrations have been applied" {
    			return nil
    		}
    		if err != nil {
    			return err
    		}
    	}
    }
    

    In order to avoid issues with future upgrades, would it be possible to export the following error as a global ?

    https://github.com/amacneil/dbmate/blob/1613f72c4f9456ebe1b72eb1fa8a55c648a7587a/pkg/dbmate/db.go#L483

    like:

    const ErrNoRollback = errors.New("can't rollback: no migrations have been applied")
    

    I can make a PR if that sounds good to you.

    This issue is linked to https://github.com/amacneil/dbmate/issues/221 and https://github.com/amacneil/dbmate/pull/256 but it allows more flexibility when using it as a library.

    Thanks!

    opened by gnuletik 6
  • Windows defender falsely reports trojan

    Windows defender falsely reports trojan

    Windows defender falsely detects a trojan in the windows build (1.13.0). Ran it on virus total and it's basically the only platform to report it as a virus. Not sure if this can be resolve some how (send msft a build, code sign the exe?) but wanted to post here:

    https://www.virustotal.com/gui/file/cf7174d63cab975cb3773d983188ab8920e91092e70f26bf1e299059951962ca?nocache=1

    opened by rminderhoud 6
  • Add --number flag to dbmate down

    Add --number flag to dbmate down

    It bugged me that I had to rollback migrations one by one if I needed to rollback multiple. Also, the option to rollback all migrations at once (instead of drop) could be useful for CI testing.

    opened by mg98 0
  • Invalid Sqlite schema.sql when INTEGER PRIMARY KEY AUTOINCREMENT is used

    Invalid Sqlite schema.sql when INTEGER PRIMARY KEY AUTOINCREMENT is used

    TL;DR

    Dbmate always outputs the full schema, including sqlite_sequence which is auto-generated when autoincrement appears in at least one table definition. This table is reserved for internal use in sqlite and cannot be created by the user (see https://www.sqlite.org/autoinc.html section 3).

    This schema.sql file fails to load on an empty database.

    Setup:

    dbmate 1.15.0, sqlite 3.39

    To reproduce:

    Run dbmate new test, let's call it db/migrations/20220818223405_test.sql.

    The file should contain:

    -- migrate:up
    
    create table t (
      id INTEGER PRIMARY KEY AUTOINCREMENT
    );
    
    -- migrate:down
    
    drop table t
    

    Run dbmate -u sqlite3:test.db up

    The file db/schema.sql should contain:

    CREATE TABLE IF NOT EXISTS "schema_migrations" (version varchar(255) primary key);
    CREATE TABLE t (
      id INTEGER PRIMARY KEY AUTOINCREMENT
    );
    CREATE TABLE sqlite_sequence(name,seq);
    -- Dbmate schema migrations
    INSERT INTO "schema_migrations" (version) VALUES
      ('20220818223405');
    

    Now test the schema file, it should be possible to use on an empty db to initialise it.

    Run sqlite3 test2.db

    SQLite version 3.39.....
    Enter ".help" for usage hints.
    sqlite> .read db/schema.sql
    Parse error near line 5: object name reserved for internal use: sqlite_sequence
    

    Solution

    The table sqlite_sequence should not be extracted in the schema, sqlite will generate it automatically.

    opened by doctor-pi 0
  • Won't generate schmea.sql for MySQL 8

    Won't generate schmea.sql for MySQL 8

    dbmate doesn't generate schmea.sql (my ./db/schema.sql stays empty), while everything else works fine

    My docker-compose.yml:

    version: '3'
    
    services:
      db:
        image: mysql:8.0
        hostname: ${DB_HOST}
        ports:
          - ${DB_PORT}:${DB_PORT}
        volumes:
          - ./db/inner/:/var/lib/mysql/
        environment:
          MYSQL_DATABASE: "${DB_NAME}"
          MYSQL_USER: "${DB_USERNAME}"
          MYSQL_PASSWORD: "${DB_PASSWORD}"
          MYSQL_ROOT_PASSWORD: "${DB_PASSWORD_ROOT}"
    
      db-migrations:
        depends_on:
          - db
        image: amacneil/dbmate:1.15
        volumes:
          - ./db/migrations/:/opt/db/migrations/
          - ./db/schema.sql:/opt/db/schema.sql
        entrypoint: ["dbmate", "-d", "/opt/db/migrations/", "-s", "/opt/db/schema.sql", "-u", "mysql://${DB_USERNAME}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME}", "--wait", "up"]
    

    Run:

    docker compose build
    docker compose run
    

    Says:

    [+] Running 3/2
     - Network mta-server_default            Created                                                                                                                                                                                                                          0.7s 
     - Container mta-server-db-1             Created                                                                                                                                                                                                                          0.1s
     - Container mta-server-db-migrations-1  Created                                                                                                                                                                                                                          0.1s
    Attaching to mta-server-db-1, mta-server-db-migrations-1
    mta-server-db-1             | 2022-08-09 16:55:05+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.30-1.el8 started.
    mta-server-db-1             | 2022-08-09 16:55:05+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
    mta-server-db-1             | 2022-08-09 16:55:05+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.30-1.el8 started.
    mta-server-db-1             | '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
    mta-server-db-1             | 2022-08-09T16:55:05.514030Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
    mta-server-db-1             | 2022-08-09T16:55:05.518043Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30) starting as process 1
    mta-server-db-1             | 2022-08-09T16:55:05.521751Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
    mta-server-db-1             | 2022-08-09T16:55:05.551886Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
    mta-server-db-1             | 2022-08-09T16:55:07.723229Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
    mta-server-db-1             | 2022-08-09T16:55:10.255933Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
    mta-server-db-1             | 2022-08-09T16:55:10.256263Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
    mta-server-db-1             | 2022-08-09T16:55:10.266916Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
    mta-server-db-1             | 2022-08-09T16:55:10.584804Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
    mta-server-db-1             | 2022-08-09T16:55:10.584864Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.30'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
    mta-server-db-migrations-1  | Waiting for database.....
    mta-server-db-migrations-1  | Applying: 20220809153010_initial.sql
    mta-server-db-migrations-1 exited with code 0
    
    opened by Aunmag 0
  • Use system variables in migrations

    Use system variables in migrations

    I am encrypting a set of fields with AES_ENCRYPT and I would like to use an environment variable to specify the secret key instead of hard-coding it.

    Example:

     UPDATE users SET enc_last_name = AES_ENCRYPT(last_name,'1234');
    

    I would like 1234 to be some variable like @secret_key.

    Does dbmate support this use case?

    opened by fabioDMFerreira 1
  • Add support for statement timeout

    Add support for statement timeout

    Context

    When running migrations, some statements might take a little bit longer which results in a statement timeout which is usually either a default value or a global value set in some configuration file.

    What we want is to be able to modify this default statement timeout according to our needs.

    Solution approach

    A CLI flag of --statement-timeout has been added to provide the user the ability of passing a custom statement timeout.

    Caveats

    Even though modifications are on the interface level, currently an implementation for statement timeout is only provided for PostgresSQL since this is the database I am familiar with and easily have access to. For other supported databases (clickhouse, mysql, sqlite), the --statement-timeout has no effect.

    In case there's enough interest in this work, I can put some time for the other databases (clickhouse, mysql, sqlite)

    opened by fouadkada 0
  • Adds flyway to the comparison table

    Adds flyway to the comparison table

    Resolves https://github.com/amacneil/dbmate/issues/249 for flyway

    Some notes which could change the ticks:

    • Timestamp-versioned migration files I checked this one because a developer can put the timestamp in their migration file name (this was the SOP for the team when I used flyway). Timestamp versions is not done as default/automatically. Additionally, flyway also has an installed_on column in their migration table. So dates can be tracked multiple ways.
    • Automatically load .env file Flyway automatically loads its config file, not sure if this is the same thing.
    • No separate configuration file there is a config file, however a developer could choose to write a script using only environmental variables
    • Language/framework independent can be Java dependent, but has a command-line option making it language and platform agnostic.
    opened by mwhitm 0
Releases(v1.15.0)
Owner
Adrian Macneil
Adrian Macneil
A simple database migration tool using an sql.DB connection and fs.FS for the migration source

A simple database migration tool using an sql.DB connection and fs.FS for the migration source. It has no non-test dependencies.

 maragu 35 Jun 17, 2022
SQL schema migration tool for Go.

sql-migrate SQL Schema migration tool for Go. Based on gorp and goose. Using modl? Check out modl-migrate. Features Usable as a CLI tool or as a libra

Ruben Vermeersch 2.6k Sep 19, 2022
Migration - Commonly used migration tools

Migration Commonly used migration tools Usage package main import ( "context"

infinite loop 0 Feb 16, 2022
Migration - Commonly used migration tools

Migration Commonly used migration tools Usage package main import ( "context"

infinite loop 0 Feb 16, 2022
Goose database migration tool - fork of https://bitbucket.org/liamstask/goose

goose Goose is a database migration tool. Manage your database schema by creating incremental SQL changes or Go functions. Goals of this fork github.c

Pressly Inc. 3k Sep 27, 2022
A database migration tool written in Go.

dbmagritte created by Austin Poor A database migration tool written in Go. Usage Commands: init: Set up the repo by creating a .dbmagritte.yaml file a

Austin Poor 0 Jan 29, 2022
A migration engine to deploy database changes in your golang + mongodb app.

bisonmigration A migration engine to deploy database changes in your golang + mongodb app. Migration files register their UP and DOWN functions in the

F. Levanti 0 Jan 30, 2022
Minimalistic database migration helper for Gorm ORM

Gormigrate Gormigrate is a minimalistic migration helper for Gorm. Gorm already has useful migrate functions, just misses proper schema versioning and

null 821 Sep 20, 2022
Dead simple Go database migration library.

migrator Dead simple Go database migration library. Features Simple code Usage as a library, embeddable and extensible on your behalf Support of any d

David Lobe 130 Sep 4, 2022
Database migration through structures - development

goMigration 基于 Golang 的数据库迁移工具,目前仍在开发中,有兴趣的小伙伴可以联系我一起~ 食用方法 go get https://github.com/DGuang21/goMigration 手动将其安装 可通过 gom gen create_c_user_table 方法生

Daguang 3 Dec 2, 2021
Database schema evolution library for Go

Try browsing the code on Sourcegraph! Darwin Database schema evolution library for Go Example package main import ( "database/sql" "log" "github.

Guiabolso 133 Aug 27, 2022
Simple Migration Tool - written in Go

Pravasan Simple Migration tool intend to be used for any languages, for any db. Please feel free to criticize, comment, etc. Currently this is working

null 29 Sep 26, 2022
Simple migration tool for MySQL

prrn Simple migration tool for MySQL This is a CLI that helps you create a DB migration file. There is no need to write up and down files from scratch

kamijin_fanta 7 Nov 10, 2021
Schema management CLI for MySQL

Skeema is a tool for managing MySQL tables and schema changes in a declarative fashion using pure SQL. It provides a CLI tool allowing you to: Export

Skeema 1.1k Sep 26, 2022
GitHub's Online Schema Migrations for MySQL

gh-ost GitHub's online schema migration for MySQL gh-ost is a triggerless online schema migration solution for MySQL. It is testable and provides paus

Leon 0 Apr 3, 2020
Opinionated tool for database structure management and migrations

trek Requirements At least version 13 of postgres is needed. Installation go install . Setup Create config.yaml: model_name: <model_name> db_name: <db

Stack11 3 Jan 7, 2022
Database migrations. CLI and Golang library.

migrate Database migrations written in Go. Use as CLI or import as library. Migrate reads migrations from sources and applies them in correct order to

null 9.7k Sep 19, 2022
Database migrations. CLI and Golang library.

Database migrations written in Go. Use as CLI or import as library.

null 27 May 30, 2021
Django style fixtures for Golang's excellent built-in database/sql library.

go-fixtures Django style fixtures for Golang's excellent built-in database/sql library. Currently only YAML fixtures are supported. There are two rese

Richard Knop 28 Aug 8, 2022