xmigrate

package module
v0.0.0-...-6939f6d Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 28, 2019 License: Apache-2.0 Imports: 16 Imported by: 0

README

xmigrate/pgmigrate

Schema first DB migration tool for PostgreSQL.

Build Status Go Report Card codecov

Getting Started

Prerequisites
  • Go 1.12+
Installing
$ go get -u github.com/akito0107/xmigrate/cmd/pgmigrate
How To Use
  1. Prepare Database.
# psql -U postgres
psql (9.6.10)

postgres=# create database xmigrate_tutorial;
CREATE DATABASE
  1. Create shcema file.
CREATE TABLE ACCOUNT (
    id serial primary key,
    email varchar unique not null,
    name varchar,
    created_at timestamp with time zone default current_timestamp
);
  1. Call sync command with -f option.
$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/xmigrate_tutorial
dry-run mode (with --apply flag will be exec below queries)
applying: CREATE TABLE ACCOUNT (id serial PRIMARY KEY, email character varying UNIQUE NOT NULL, name character varying, created_at timestamp with timezone DEFAULT current_timestamp)
  1. Recall sync with --apply option.
$ pgmigrate -f schema.sql --apply postgres://[USER_NAME]:[PASSWORD]@localhost:5432/xmigrate_tutorial
applying: CREATE TABLE ACCOUNT (id serial PRIMARY KEY, email character varying UNIQUE NOT NULL, name character varying, created_at timestamp with time zone DEFAULT current_timestamp)
  1. Check DB Stats
# psql -U postgres --dbname xmigrate_tutorial
psql (9.6.10)
Type "help" for help.

xmigrate_tutorial=# \d
               List of relations
 Schema |      Name      |   Type   |  Owner
--------+----------------+----------+----------
 public | account        | table    | postgres
 public | account_id_seq | sequence | postgres
(2 rows)

xmigrate_tutorial=# \d account
                                    Table "public.account"
   Column   |           Type           |                      Modifiers

------------+--------------------------+------------------------------------------------------
 id         | integer                  | not null default nextval('account_id_seq'::regclass)
 email      | character varying        | not null
 name       | character varying        |
 created_at | timestamp with time zone | default now()
Indexes:
    "account_pkey" PRIMARY KEY, btree (id)
    "account_email_key" UNIQUE CONSTRAINT, btree (email)
  1. Modify Schema
CREATE TABLE ACCOUNT (
     id serial primary key,
     email varchar unique not null,
     name varchar,
+    address varchar not null,
     created_at timestamp with time zone default current_timestamp
 );
  1. Preview & Apply
$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: ALTER TABLE account ADD COLUMN address character varying NOT NULL

$ pgmigrate -f schema.sql --apply postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
applying: ALTER TABLE account ADD COLUMN address character varying NOT NULL
  1. Check DB stats
xmigrate_tutorial=# \d account
                                    Table "public.account"
   Column   |           Type           |                      Modifiers
------------+--------------------------+------------------------------------------------------
 id         | integer                  | not null default nextval('account_id_seq'::regclass)
 email      | character varying        | not null
 name       | character varying        |
 created_at | timestamp with time zone | default now()
 address    | character varying        | not null
Indexes:
    "account_pkey" PRIMARY KEY, btree (id)
    "account_email_key" UNIQUE CONSTRAINT, btree (email)

Supported Operation

Create Table

given:

+CREATE TABLE ITEM (
+    ID serial primary key,
+    NAME varchar not null,
+    PRICE int not null
+);

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: CREATE TABLE ITEM (ID serial PRIMARY KEY, NAME character varying NOT NULL, PRICE int NOT NULL)
Drop Table

given:

-CREATE TABLE ITEM (
-    ID serial primary key,
-    NAME varchar not null,
-    PRICE int not null
-);

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: DROP TABLE IF EXISTS item
Add Column

given:

CREATE TABLE ACCOUNT (
     EMAIL varchar unique not null,
     NAME varchar,
     ADDRESS varchar not null,
+    ADDRESS2 varchar,
     CREATED_AT timestamp with time zone default current_timestamp
 );

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: ALTER TABLE account ADD COLUMN ADDRESS2 character varying
Drop Column

given:

CREATE TABLE ACCOUNT (
     EMAIL varchar unique not null,
     NAME varchar,
     ADDRESS varchar not null,
-    ADDRESS2 varchar,
     CREATED_AT timestamp with time zone default current_timestamp
 );

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: ALTER TABLE account DROP COLUMN address2
Modify Column
Change Data Type

given:

CREATE TABLE ACCOUNT (
     ID serial primary key,
     EMAIL varchar unique not null,
     NAME varchar,
-    ADDRESS varchar not null,
+    ADDRESS text not null,
     CREATED_AT timestamp with time zone default current_timestamp
 );

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: ALTER TABLE ACCOUNT ALTER COLUMN ADDRESS TYPE text
Add Column Constraint (NOT NULL)

given:

 CREATE TABLE ACCOUNT (
     ID serial primary key,
     EMAIL varchar unique not null,
-    NAME varchar,
+    NAME varchar not null,
     ADDRESS text not null,
     CREATED_AT timestamp with time zone default current_timestamp
 );

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: ALTER TABLE ACCOUNT ALTER COLUMN NAME SET NOT NULL
Remove Column Constraint (NOT NULL)

given:

 CREATE TABLE ACCOUNT (
     ID serial primary key,
     EMAIL varchar unique not null,
-    NAME varchar not null,
+    NAME varchar,
     ADDRESS text not null,
     CREATED_AT timestamp with time zone default current_timestamp
 );

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: ALTER TABLE ACCOUNT ALTER COLUMN NAME DROP NOT NULL
Add Table Constraint

given:

CREATE TABLE ACCOUNT (
     EMAIL varchar unique not null,
     NAME varchar not null,
     ADDRESS text not null,
     CREATED_AT timestamp with time zone default current_timestamp,
+    CONSTRAINT unique_name_address UNIQUE (NAME, ADDRESS)
 );

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: ALTER TABLE ACCOUNT ADD CONSTRAINT unique_name_address UNIQUE(NAME, ADDRESS)

CAUTION ONLY SUPPORTS NAMED CONSTRAINT. ex) the following case is not handled correctly.

CREATE TABLE ACCOUNT (
     EMAIL varchar unique not null,
     NAME varchar not null,
     ADDRESS text not null,
     CREATED_AT timestamp with time zone default current_timestamp,
+    UNIQUE (NAME, ADDRESS)
 );
Remove Table Constraint

given:

CREATE TABLE ACCOUNT (
     EMAIL varchar unique not null,
     NAME varchar not null,
     ADDRESS text not null,
     CREATED_AT timestamp with time zone default current_timestamp
-    CONSTRAINT unique_name_address UNIQUE (NAME, ADDRESS)
 );

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: ALTER TABLE ACCOUNT DROP CONSTRAINT unique_name_address
Create Index

given:

+ CREATE INDEX name_idx ON account (name);

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
applying: CREATE INDEX cat_name_idx ON item (category_id, name)
Drop Index

given:

- CREATE INDEX name_idx ON account (name);

then:

$ pgmigrate -f schema.sql postgres://[USER_NAME]:[PASSWORD]@localhost:5432/
dry-run mode (with --apply flag will be exec below queries)
DROP INDEX name_idx
options
NAME:
   pgmigrate - postgres db migration utility

USAGE:
   pgmigrate [GLOBAL OPTIONS] [db url]

VERSION:
   0.0.0

COMMANDS:
     help, h  Shows a list of commands or help for one command

GLOBAL OPTIONS:
   --schemapath value, -f value  schema sql path (default: "schema.sql")
   --apply, -a                   apply migration
   --help, -h                    show help
   --version, -v                 print the version

License

This project is licensed under the Apache License 2.0 License - see the LICENSE file for details

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func CalcGraph 

func CalcGraph(diffs []*SchemaDiff) *toposort.Graph

Calculate Diff's dependencies

Types

type AddColumnSpec 

type AddColumnSpec struct {
	TableName string
	ColumnDef *sqlast.SQLColumnDef
}

func (*AddColumnSpec) ToSQLString 

func (a *AddColumnSpec) ToSQLString() string

type AddIndexSpec 

type AddIndexSpec struct {
	Def *sqlast.SQLCreateIndex
}

func (*AddIndexSpec) ToSQLString 

func (a *AddIndexSpec) ToSQLString() string

type AddTableConstraintSpec 

type AddTableConstraintSpec struct {
	TableName     string
	ConstraintDef *sqlast.TableConstraint
}

func (*AddTableConstraintSpec) ToSQLString 

func (a *AddTableConstraintSpec) ToSQLString() string

type AddTableSpec 

type AddTableSpec struct {
	SQL *sqlast.SQLCreateTable
}

func (*AddTableSpec) ToSQLString 

func (a *AddTableSpec) ToSQLString() string

type DiffNode 

type DiffNode struct {
	Diff *SchemaDiff
	// contains filtered or unexported fields
}

func (*DiffNode) GetDeps 

func (d *DiffNode) GetDeps() []string

func (*DiffNode) Symbol 

func (d *DiffNode) Symbol() string

type DiffSpec 

type DiffSpec interface {
	ToSQLString() string
}

type DiffType 

type DiffType int
const (
	AddColumn DiffType = iota
	DropColumn
	AddTable
	DropTable
	EditColumn
	AddTableConstraint
	DropTableConstraint
	AddIndex
	RemoveIndex
)

type DropColumnSpec 

type DropColumnSpec struct {
	TableName  string
	ColumnName string
}

func (*DropColumnSpec) ToSQLString 

func (d *DropColumnSpec) ToSQLString() string

type DropIndexSpec 

type DropIndexSpec struct {
	IndexName string
}

func (*DropIndexSpec) ToSQLString 

func (d *DropIndexSpec) ToSQLString() string

type DropTableConstraintSpec 

type DropTableConstraintSpec struct {
	TableName       string
	ConstraintsName string
}

func (*DropTableConstraintSpec) ToSQLString 

func (d *DropTableConstraintSpec) ToSQLString() string

type DropTableSpec 

type DropTableSpec struct {
	TableName string
}

func (*DropTableSpec) ToSQLString 

func (d *DropTableSpec) ToSQLString() string

type EditColumnAction 

type EditColumnAction interface {
	ToSQLString() string
}

type EditColumnSpec 

type EditColumnSpec struct {
	Type       EditColumnType
	TableName  string
	ColumnName string
	SQL        *sqlast.SQLAlterTable
}

func (*EditColumnSpec) ToSQLString 

func (e *EditColumnSpec) ToSQLString() string

type EditColumnType 

type EditColumnType int
const (
	EditType EditColumnType = iota
	SetNotNull
	DropNotNull
	SetDefault
	DropDefault
)

type PGConf 

type PGConf struct {
	DBName     string `env:"DB_NAME"`
	DBHost     string `env:"DB_HOST"`
	DBPort     string `env:"DB_PORT"`
	DBPassword string `env:"DB_PASSWORD,allow-empty"`
	UserName   string `env:"DB_USER_NAME"`
	SSLMode    bool   `env:"DB_SSL_MODE,allow-empty"`
}

func (*PGConf) Src 

func (p *PGConf) Src() string

type PGDump 

type PGDump struct {
	// contains filtered or unexported fields
}

func NewPGDump 

func NewPGDump(conf *PGConf) *PGDump

func NewPGDumpFromURL 

func NewPGDumpFromURL(dburl *dburl.URL) *PGDump

func (*PGDump) Dump 

func (p *PGDump) Dump(ctx context.Context) ([]*TableDef, error)

type SchemaDiff 

type SchemaDiff struct {
	Type DiffType
	Spec DiffSpec
}

func DSLToDiff 

func DSLToDiff(stmts []sqlast.SQLStmt) ([]*SchemaDiff, error)

func Diff 

func Diff(targ *TargetTable, currentTable []*TableDef) ([]*SchemaDiff, error)

func Inverse 

func Inverse(diff *SchemaDiff, currentTable []*TableDef) (*SchemaDiff, error)

type TableDef 

type TableDef struct {
	Name       string
	Columns    map[string]*sqlast.SQLColumnDef
	Constrains []*sqlast.TableConstraint
	Indexes    map[string]*sqlast.SQLCreateIndex
}

type TargetTable 

type TargetTable struct {
	TableDef []*sqlast.SQLCreateTable
	IndexDef []*sqlast.SQLCreateIndex
}

Source Files

View all Source files

Directories

Path Synopsis
cmd
pginverse
pgmigrate
xmigrate

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.