A simple database migration tool using an sql.DB connection and fs.FS for the migration source

Overview

Migrate

GoDoc CircleCI

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

This project is work-in-progress and has a lot of rough edges.

Made in 🇩🇰 by maragu, maker of online Go courses.

Usage

go get -u github.com/maragudk/migrate
package main

import (
	"context"
	"database/sql"
	"embed"
	"io/fs"

	_ "github.com/jackc/pgx/v4/stdlib"
	"github.com/maragudk/migrate"
)

// migrations is a directory with sql files that look like this:
// migrations/1.up.sql
// migrations/1.down.sql
// migrations/2.up.sql
// migrations/2.down.sql
//go:embed migrations
var dir embed.FS

func main() {
	db, err := sql.Open("pgx", "postgresql://postgres:[email protected]:5432/postgres?sslmode=disable")
	if err != nil {
		panic(err)
	}
	migrations, err := fs.Sub(dir, "migrations")
	if err != nil {
		panic(err)
	}
	m := migrate.New(db, migrations)
	if err := m.MigrateUp(context.Background()); err != nil {
		panic(err)
	}

	if err := m.MigrateDown(context.Background()); err != nil {
		panic(err)
	}
}
Comments
  • version comparison of string values

    version comparison of string values

    :wave: hey there I came across this package (which looks really cool) and I noticed this line:

    if thisVersion > currentVersion {
    	continue
    }
    

    I was wondering how the version compare would handle this scenario:

    9.up.sql
    10.up.sql
    

    given the following result when comparing the two version strings:

    fmt.Println("9" > "10")
    // true
    

    have you considered parsing the version as a number?

    or maybe you would consider allowing the user to customize the regular expression to ensure extra safety? For example I could customize the up and down expressions to ensure the version is expressed as a date:

    // 20210510.up.sql
    regexp.MustCompile(`^([\d]{7}).up.sql$`)
    

    or I could customize the expression to ignore the trailing description and only use the digits for comparison:

    // 20210510-create-user-table.up.sql
    regexp.MustCompile(`^([\d]{7})[\w-]+.up.sql$`)
    
    opened by ashwilliams1 7
  • Allow setting a custom name for migrations table

    Allow setting a custom name for migrations table

    I think it would be useful and more flexible. Maybe with some functional options or just another New* function.

    • Just an exported field:
    type Migrator struct {
    	DB *sql.DB
    	FS fs.FS
    	Table string
    }
    
    m := New(db, migrations)
    m.Table = "migrate_inator"
    
    • With another New* function:
    type Migrator struct {
    	DB *sql.DB
    	FS fs.FS
    	table string
    }
    
    // New Migrator with default options.
    func New(db *sql.DB, fs fs.FS) *Migrator {
    	return &Migrator{
    		DB: db,
    		FS: fs,
    		table: "migrate",
    	}
    }
    
    func NewWithTable(db *sql.DB, fs fs.FS, table string) *Migrator {
    	return &Migrator{
    		DB: db,
    		FS: fs,
    		table: table,
    	}
    }
    
    m := NewWithTable(db, migrations, "migrate_inator")
    
    • With a Config struct:
    type Config struct {
    	DB *sql.DB
    	FS fs.FS
    	Table string
    }
    
    type Migrator struct {
    	DB *sql.DB
    	FS fs.FS
    	table string
    }
    
    // New Migrator with default options.
    func New(db *sql.DB, fs fs.FS) *Migrator {
    	return NewWithConfig(Config{
    		DB: db,
    		FS: fs,
    		Table: "migrate",
    	})
    }
    
    func NewWithConfig(cfg Config) *Migrator {
    	return &Migrator{
    		DB: cfg.DB,
    		FS: cfg.FS,
    		table: cfg.Table,
    	}
    }
    
    m := NewWithConfig(Config{
    	DB: db,
    	FS: migrations,
    	Table: "migrate_inator"
    })
    
    • With functional options:
    type Migrator struct {
    	DB *sql.DB
    	FS fs.FS
    	table string
    }
    
    type Option func(*Migrator)
    
    // New Migrator with default options.
    func New(db *sql.DB, fs fs.FS, options ...Option) *Migrator {
    	m := &Migrator{
    		DB: db,
    		FS: fs,
    		table: "migrate",
    	}
    
    	for _, opt := range options {
    		opt(m)
    	}
    
    	return m
    }
    
    func WithTable(table string) Option {
    	return func(m *Migrator) {
    		m.table = table
    	}
    }
    
    m := New(db, migrations, WithTable("migrate_inator"))
    
    enhancement 
    opened by ntrrg 5
  • Permit custom migration table name to contain '.'

    Permit custom migration table name to contain '.'

    Currently it is impossible to create a migration table with name master.migrations for example. The reason is the regexp that check the name https://github.com/maragudk/migrate/blob/507b57a50cdcbdaacc8cb3d0a4b0c5fb67187d1a/migrate.go#L18

    This can be useful when a Postgresql DB contains multiple schemas and you want to create migration table in a specific one.

    I suggest to add '.' as a valid character tableMatcher = regexp.MustCompile(^[\w\.]+$)

    opened by mirkoperillo 3
  • callback for sql pre-processing

    callback for sql pre-processing

    👋 hey there, I have an interesting use case that is not solved by most migrations tools, and I was wondering if you would be open to the suggestion. First please allow me to describe the problem.

    I have a database that supports postgres and sqlite, however, the ddl syntax can be slightly different which means I need two different (but very similar) sets of migrations. In the interest of reducing a significant amount of duplicate code, it would be great if I could leverage templates to have a single set of migrations.

    For example:

    CREATE TABLE IF NOT EXISTS person (
     id      {{ if eq .driver "sqlite3" }}INTEGER PRIMARY KEY AUTOINCREMENT{{ else }}SERIAL PRIMARY KEY{{ end }}
    ,name    TEXT
    ,last    TEXT
    ,first   TEXT
    ,address TEXT
    ,dob     DATE
    );
    

    I suspect that templating would be outside the scope of this library, for good reason. Instead, I would propose that this library provides a hook for pre-processing. This would allow me to provide a callback function that executes a template and returns the final sql statement. For example:

    		// Normally we wouldn't just string interpolate the version like this,
    		// but because we know the version has been matched against the regexes, we know it's safe.
    		if _, err := tx.ExecContext(ctx, `update `+m.table+` set version = '`+version+`'`); err != nil {
    			return err
    		}
    
    +		stmt := string(content)
    +		if m.process != nil {
    +			stmt = m.process(ctx, stmt)
    +		}
    +		if _, err := tx.ExecContext(ctx, stmt); err != nil {
    -		if _, err := tx.ExecContext(ctx, string(content)); err != nil {
    			return err
    		}
    

    This would allow me to do something like this:

    driver := "postgres"
    opts.Process = func(before string) (string, error) {
    	t, err := template.New("_").Parse(before)
    	if err != nil {
    		return before, err
    	}
    	var buf bytes.Buffer
    	err := t.Execute(buf, map[string]string{"driver": driver})
    	return buf.String(), err
    }
    

    Alternatively, instead of a pre-processing hook, you could allow the user to override the execution:

    +		if m.exec != nil {
    +			if err := m.exec(ctx, tx, string(content)); err != nil {
    +				return err
    +			}
    +		} else {
    			if _, err := tx.ExecContext(ctx, string(content)); err != nil {
    				return err
    			}
    +		}
    

    I probably prefer the first option but the second option might provide some additional flexibility (for example, maybe I want to log the sql statement on error, or skip execution if the template generates an empty string). I think this would be a pretty small change that would solve a very real world problem for authors that are support multiple database vendors. If you are open to supporting this feature, I would be happy to submit a pull request.

    opened by ashwilliams1 1
  • Expose getCurrentVersion function

    Expose getCurrentVersion function

    https://github.com/maragudk/migrate/blob/b86d7a9b2a9cf1e643280287337756db6bdb96af/migrate.go#L293

    In some scenario a public usage of this function can be interesting, for example for a migration tool based on your library.

    I can propose a PR about this, if the behavior is considered useful.

    opened by mirkoperillo 1
  • Add Before/After migration callback function support

    Add Before/After migration callback function support

    This adds optional functions in the migrate.Options that, if set, are called before and after each migration. The functions are passed the context, the transaction, and the version string. If the callback errors, the current migration is aborted and the transaction rolled back.

    opened by markuswustenberg 0
  • Fix panic not being returned as error in transaction

    Fix panic not being returned as error in transaction

    Because the returned error from Migrator.inTransaction wasn't named, the recover() from within the deferred function didn't actually have an effect setting the err variable.

    opened by markuswustenberg 0
Releases(v0.4.3)
Owner
maragu
 maragu
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
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 Dec 2, 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. 3.2k Nov 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
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 135 Nov 9, 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 845 Dec 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
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
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
A tool to compare if terraform provider migration schema snapshot is equal to schema defined in resource code

migration schema comparer for Terraform When develop Terraform provider sometimes we need do some state migration(not schema migration) via StateUpgra

null 0 Nov 18, 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 29 Sep 26, 2022
entimport is a tool for creating Ent schemas from existing SQL databases.

entimport entimport is a tool for creating Ent schemas from existing SQL databases. Currently, MySQL and PostgreSQL are supported. The tool can import

ariga 111 Nov 26, 2022
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 10.3k Nov 30, 2022
Database migrations. CLI and Golang library.

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

null 27 May 30, 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 134 Dec 5, 2022
goydb, a couchdb compatible embeddable database written in go

goydb, a couchdb compatible embeddable database written in go Getting started (not embedded) Using docker mkdir data docker run -e GOYDB_ADMINS=admin:

goydb 18 Sep 14, 2022