README

Godocs Go Report Card Open Source Helpers Release

pg-shifter

Golang struct to postgres table shifter. go1.9+ required.

The main objective is to keep the table's complete schema details in golang table struct only.
If any column is missing is struct which is their in the database table then that column will be dropped.
If any column is extra in struct which is not their in the database table then that column will be added.
If any modification in column type/constraint in struct which is not matching with database then that will be modified.
So, to make sure that your go table struct tells you the actual schema of table and to make alter easy we have created this migrator.

You are most welcome to contribute :)

Install

  1. go get github.com/mayur-tolexo/pg-shifter
  2. go get github.com/tools/godep
  3. cd $GOPATH/src/github.com/mayur-tolexo/pg-shifter/
  4. godep restore -v

Features

  1. Create Table
  2. Create Enum
  3. Upsert Enum
  4. Create Index
  5. Create Unique Key
  6. Upsert Unique Key
  7. Create All Tables
  8. Alter Table
  9. Alter All Tables
  10. Drop Table
  11. Drop All Tables
  12. Create Table Struct
  13. Create history table
  14. Add trigger

Alter table supported operations:

  1. Add New Column
  2. Remove existing column
  3. Modify existing column
    1. Modify datatype
    2. Modify data length (e.g. varchar(255) to varchar(100))
    3. Add/Drop default value
    4. Add/Drop Not Null Constraint
    5. Add/Drop constraint (Unique/Foreign Key)
    6. Modify constraint
      1. Set constraint deferrable
        1. Initially deferred
        2. Initially immediate
      2. Set constraint not deferrable
      3. Add/Drop FOREIGN KEY ON DELETE DEFAULT/NO ACTION/RESTRICT/CASCADE/SET NULL
      4. Add/Drop FOREIGN KEY ON UPDATE DEFAULT/NO ACTION/RESTRICT/CASCADE/SET NULL

Create Table

CreateTable(conn *pg.DB, model interface{}) (err error)

This will create table if not exists from go struct. Also, if any enum associated to the table struct then that will be created as well.
All the unique keys and index associated to the table struct will be created as well.

i) Directly passing struct model  
ii) Passing table name after setting model  
i) Directly passing struct model
type TestAddress struct {
	tableName struct{} `sql:"test_address"`
	AddressID int      `sql:"address_id,type:serial NOT NULL PRIMARY KEY"`
	Address   string   `sql:"address,type:text"`
	City      string   `sql:"city,type:varchar(25) NULL"`
}

s := shifter.NewShifter()
err := s.CreateTable(conn, &TestAddress{})
ii) Passing table name after setting model
s := shifter.NewShifter()
s.SetTableModel(&TestAddress{})
err := s.CreateTable(conn, "test_address")

Create Enum

CreateAllEnum(conn *pg.DB, model interface{}) (err error)

This will create all the enum associated to the given table.
To define enum on table struct you need to create a method with following signature:

func (tableStruct) Enum() map[string][]string

Here returned map's key is enum name and value is slice of enum values.
If enum already exist in database then it will not create enum again.

i) Directly passing struct model   
ii) Passing table name after setting model  
i) Directly passing struct model
type TestAddress struct {
	tableName struct{} `sql:"test_address"`
	AddressID int      `sql:"address_id,type:serial NOT NULL PRIMARY KEY"`
	Address   string   `sql:"address,type:text"`
	City      string   `sql:"city,type:varchar(25) NULL"`
	Status    string   `sql:"status,type:address_status"`
}

//Enum of the table.
func (TestAddress) Enum() map[string][]string {
	enm := map[string][]string{
		"address_status": {"enable", "disable"},
	}
	return enm
}

s := shifter.NewShifter()
err := s.CreateAllEnum(conn, &TestAddress{})
ii) Passing table name after setting model
s := shifter.NewShifter()
s.SetTableModel(&TestAddress{})
err = s.CreateAllEnum(conn, "test_address")

CreateEnum(conn *pg.DB, model interface{}, enumName string) (err error)

This will create given enum if associated to given table

i) Directly passing struct model   
ii) Passing table name after setting model  
i) Directly passing struct model
s := shifter.NewShifter()
err := s.CreateEnum(conn, &TestAddress{}, "address_status")
ii) Passing table name after setting model
s := shifter.NewShifter()
s.SetTableModel(&TestAddress{})
err = s.CreateEnum(conn, "test_address", "address_status")

Upsert Enum

UpsertAllEnum(conn *pg.DB, model interface{}) (err error)

This will create/update all the enum associated to the given table. To define enum on table struct you need to create a method with following signature:

func (tableStruct) Enum() map[string][]string

Here returned map's key is enum name and value is slice of enum values.
If enum already exist in database then it will update the enum value which are missing in the database.

i) Directly passing struct model   
ii) Passing table name after setting model  
i) Directly passing struct model
type TestAddress struct {
	tableName struct{} `sql:"test_address"`
	AddressID int      `sql:"address_id,type:serial NOT NULL PRIMARY KEY"`
	Address   string   `sql:"address,type:text"`
	City      string   `sql:"city,type:varchar(25) NULL"`
	Status    string   `sql:"status,type:address_status"`
}

//Enum of the table.
func (TestAddress) Enum() map[string][]string {
	enm := map[string][]string{
		"address_status": {"enable", "disable"},
	}
	return enm
}

s := shifter.NewShifter()
err := s.UpsertAllEnum(conn, &TestAddress{})
ii) Passing table name after setting model
s := shifter.NewShifter()
s.SetTableModel(&TestAddress{})
err = s.UpsertAllEnum(conn, "test_address")

UpsertEnum(conn *pg.DB, model interface{}, enumName string) (err error)

This will create/update given enum if associated to given table

i) Directly passing struct model   
ii) Passing table name after setting model  
i) Directly passing struct model
s := shifter.NewShifter()
err := s.UpsertEnum(conn, &TestAddress{}, "address_status")
ii) Passing table name after setting model
s := shifter.NewShifter()
s.SetTableModel(&TestAddress{})
err = s.UpsertEnum(conn, "test_address", "address_status")

Create Index

CreateAllIndex(conn *pg.DB, model interface{}, skipPrompt ...bool) (err error)

This will create all the index associated to the given table.
If skipPrompt is enabled then it won't ask for confirmation before creating index. Default is disable.
To define index on table struct you need to create a method with following signature:

func (tableStruct) Index() map[string]string  

Here returned map's key is column which need to index and value is the type of data structure to user for indexing. Default is btree. For composite index you can add column comma seperated.

i) Directly passing struct model   
ii) Passing table name after setting model  
i) Directly passing struct model
type TestAddress struct {
	tableName struct{}    `sql:"test_address"`
	AddressID int         `json:"address_id,omitempty" sql:"address_id,type:serial PRIMARY KEY"`
	City      string      `json:"city" sql:"city,type:varchar(25) UNIQUE"`
	Status    string      `json:"status,omitempty" sql:"status,type:address_status"`
	Info      interface{} `sql:"info,type:jsonb"`
}

//Index of the table. For composite index use ,
//Default index type is btree. For gin index use gin
func (TestAddress) Index() map[string]string {
	idx := map[string]string{
		"status":            shifter.BtreeIndex,
		"info":              shifter.GinIndex,
		"address_id,status": shifter.BtreeIndex,
	}
	return idx
}

s := shifter.NewShifter()
err := s.CreateAllIndex(conn, &TestAddress{})
ii) Passing table name after setting model
s := shifter.NewShifter()
s.SetTableModel(&TestAddress{})
err = s.CreateAllIndex(conn, "test_address")

Create Unique Key

CreateAllUniqueKey(conn *pg.DB, model interface{}, skipPrompt ...bool) (err error)

This will create all the composite unique key associated to the given table.
If skipPrompt is enabled then it won't ask for confirmation before creating unique key. Default is disable.
To define composite unique key on table struct you need to create a method with following signature:

func (tableStruct) UniqueKey() []string

Here returned slice is the columns comma seperated.
If single column need to create unique key then use UNIQUE sql tag for column.

i) Directly passing struct model   
ii) Passing table name after setting model  
i) Directly passing struct model
type TestAddress struct {
	tableName struct{}  `sql:"test_address"`
	AddressID int       `json:"address_id,omitempty" sql:"address_id,type:serial PRIMARY KEY"`
	City      string    `json:"city" sql:"city,type:varchar(25) UNIQUE"`
	Status    string    `json:"status,omitempty"
}

//UniqueKey of the table. This is for composite unique keys
func (TestAddress) UniqueKey() []string {
	uk := []string{
		"address_id,status,city",
	}
	return uk
}

s := shifter.NewShifter()
err := s.CreateAllUniqueKey(conn, &TestAddress{})
ii) Passing table name after setting model
s := shifter.NewShifter()
s.SetTableModel(&TestAddress{})
err = s.CreateAllUniqueKey(conn, "test_address")

Upsert Unique Key

UpsertAllUniqueKey(conn *pg.DB, model interface{}, skipPrompt ...bool) (err error)

This will create all the composite unique key associated to the given table.
Modify composite unique key which are not matching with table and struct.
Drop composite unique key which exists in table but not in struct.
If skipPrompt is enabled then it won't ask for confirmation before upserting unique key. Default is disable.
To define composite unique key on table struct you need to create a method with following signature:

func (tableStruct) UniqueKey() []string

Here returned slice is the columns comma seperated.
If single column need to create unique key then use UNIQUE sql tag for column.

i) Directly passing struct model   
ii) Passing table name after setting model  
i) Directly passing struct model
type TestAddress struct {
	tableName struct{}  `sql:"test_address"`
	AddressID int       `json:"address_id,omitempty" sql:"address_id,type:serial PRIMARY KEY"`
	City      string    `json:"city" sql:"city,type:varchar(25) UNIQUE"`
	Status    string    `json:"status,omitempty"
}

//UniqueKey of the table. This is for composite unique keys
func (TestAddress) UniqueKey() []string {
	uk := []string{
		"address_id,status,city",
	}
	return uk
}

s := shifter.NewShifter()
err := s.UpsertAllUniqueKey(conn, &TestAddress{})
ii) Passing table name after setting model
s := shifter.NewShifter()
s.SetTableModel(&TestAddress{})
err = s.UpsertAllUniqueKey(conn, "test_address")

Create All Tables

CreateAllTable(conn *pg.DB) (err error)

This will create table if not exists from go struct which are set in shifter. Also, if any enum associated to the table struct then that will be created as well.
All the unique keys and index associated to the table struct will be created as well.

db := []interface{}{&TestAddress{}, &TestUser{}, &TestAdminUser{}}

s := shifter.NewShifter()
s.SetTableModels(db)
err := s.CreateAllTable(conn)

Alter Table

AlterTable(conn *pg.DB, model interface{}, skipPrompt ...bool) (err error)

This will alter table.
If skipPrompt is enabled then it won't ask for confirmation before upserting unique key. Default is disable.

i) Directly passing struct model   
ii) Passing table name after setting model  
i) Directly passing struct model
type TestAddress struct {
	tableName struct{}  `sql:"test_address"`
	AddressID int       `json:"address_id,omitempty" sql:"address_id,type:serial PRIMARY KEY"`
	City      string    `json:"city" sql:"city,type:varchar(25) UNIQUE"`
	Status    string    `json:"status,omitempty"
}

s := shifter.NewShifter()
err := s.AlterTable(conn, &TestAddress{})
ii) Passing table name after setting model
s := shifter.NewShifter()
s.SetTableModel(&TestAddress{})
err = s.AlterTable(conn, "test_address")

Alter All Tables

AlterAllTable(conn *pg.DB, skipPrompt ...bool) (err error)
This will alter all tables added in shifter using SetTableModels().
If skipPrompt is enabled then it won't ask for confirmation before upserting unique key. Default is disable.

db := []interface{}{&TestAddress{}, &TestUser{}, &TestAdminUser{}}

s := shifter.NewShifter()
s.SetTableModels(db)
err := s.AlterAllTable(conn)

Drop Table

DropTable(conn *pg.DB, model interface{}, cascade bool) (err error)

This will drop the table from database if exists. Also, if history table associated to this table exists then that will be dropped as well. If cascade is true then it will drop table with cascade.

i) Directly passing struct model
s := shifter.NewShifter()
err := s.DropTable(conn, &TestAddress{}, true)
ii) Passing table name
s := shifter.NewShifter()
err := s.DropTable(conn, "test_address", true)

Drop All Tables

DropAllTable(conn *pg.DB, cascade bool) (err error)

This will drop all the table from database if exists which are set in shifter. So, before calling it you need to SetTableModels() on shifter. Also, if history table associated to this table exists then that will be dropped as well. If cascade is true then it will drop tables with cascade.

db := []interface{}{&TestAddress{}, &TestUser{}, &TestAdminUser{}}

s := shifter.NewShifter()
s.SetTableModels(db)
err := s.DropAllTable(conn, true)

Create Table Struct

CreateStruct(conn *pg.DB, tableName string, filePath string) (err error)

if conn, err := psql.Conn(true); err == nil {
	shifter.NewShifter().CreateStruct(conn, "address", "")
}
OUTPUT

Screenshot 2019-12-08 at 10 09 43 PM

Add New Column

Just add new field in the table struct and run AlterTable().

Remove Existing Column

Remove field from the table struct which you want to remove and run AlterTable().

Expand ▾ Collapse ▴

Documentation

Index

Constants

View Source
const (
	TriggerTag = "trigger" //use to create triggers on table.
	HistoryTag = "history" //use to create history table. Default table_history if after trigger given

)

    constants used

    View Source
    const (
    	BtreeIndex  = "btree"   //btree index type
    	GinIndex    = "gin"     //gin index type
    	GistIndex   = "gist"    //gist index type
    	HashIndex   = "hash"    //hash index type
    	BrinIndex   = "brin"    //brin index type
    	SPGistIndex = "sp-gist" //sp-gist index type
    )

      index type const

      Variables

      This section is empty.

      Functions

      This section is empty.

      Types

      type Shifter

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

        Shifter model contains all the methods to migrate go struct to postgresql

        func NewShifter

        func NewShifter(tables ...interface{}) *Shifter

          NewShifter will return shifter model

          func (*Shifter) AlterAllTable

          func (s *Shifter) AlterAllTable(conn *pg.DB, skipPromt ...bool) (err error)

            AlterAllTable will alter all tables before calling it you need to set the table model in shifter using SetTableModels()

            func (*Shifter) AlterTable

            func (s *Shifter) AlterTable(conn *pg.DB, model interface{}, skipPrompt ...bool) (err error)

              AlterTable will alter table.

              Parameters

              conn: postgresql connection
              model: struct pointer or string (table name)
              skipPrompt: bool (default false | if false then before execution sql it will prompt for confirmation)
              

              func (*Shifter) CreateAllEnum

              func (s *Shifter) CreateAllEnum(conn *pg.DB, model interface{}) (err error)

                CreateAllEnum will create all enums of the given table.

                Parameters

                conn: postgresql connection
                model: struct pointer or string (table name)
                

                if model is table name then need to set shifter SetTableModel() before calling CreateAllEnum()

                func (*Shifter) CreateAllIndex

                func (s *Shifter) CreateAllIndex(conn *pg.DB, model interface{}, skipPrompt ...bool) (err error)

                  CreateAllIndex will create all index of the given table.

                  Parameters

                  conn: postgresql connection
                  model: struct pointer or string (table name)
                  skipPrompt: bool (default false | if false then before execution sql it will prompt for confirmation)
                  

                  if model is table name then need to set shifter SetTableModel() before calling CreateAllIndex()

                  func (*Shifter) CreateAllTable

                  func (s *Shifter) CreateAllTable(conn *pg.DB) (err error)

                    CreateAllTable will create all tables before calling it you need to set the table model in shifter using SetTableModels()

                    func (*Shifter) CreateAllUniqueKey

                    func (s *Shifter) CreateAllUniqueKey(conn *pg.DB, model interface{}, skipPrompt ...bool) (err error)

                      CreateAllUniqueKey will create table all composite unique key.

                      Parameters

                      conn: postgresql connection
                      model: struct pointer or string (table name)
                      skipPrompt: bool (default false | if false then before execution sql it will prompt for confirmation)
                      

                      if model is table name then need to set shifter SetTableModel() before calling CreateAllUniqueKey()

                      func (*Shifter) CreateEnum

                      func (s *Shifter) CreateEnum(conn *pg.DB, model interface{}, enumName string) (err error)

                        CreateEnum will create enum by enum name.

                        Parameters

                        conn: postgresql connection
                        model: struct pointer or string (table name)
                        enumName: enum which you want to create
                        

                        if model is table name then need to set shifter SetTableModel() before calling CreateEnum()

                        func (*Shifter) CreateStruct

                        func (s *Shifter) CreateStruct(conn *pg.DB, tableName string,
                        	filePath string) (err error)

                          CreateStruct will create golang structure from postgresql table

                          func (*Shifter) CreateStructFromStruct

                          func (s *Shifter) CreateStructFromStruct(conn *pg.DB, filePath string) (
                          	err error)

                            CreateStructFromStruct will create structure from shifter structures which are set in shifter map before calling it you need to set all the table models in shifter using SetTableModels()

                            func (*Shifter) CreateTable

                            func (s *Shifter) CreateTable(conn *pg.DB, model interface{}) (err error)

                              CreateTable will create table if not exists.

                              Parameters

                              conn: postgresql connection
                              model: struct pointer or string (table name)
                              

                              if model is table name then need to set shifter SetTableModel() before calling CreateTable()

                              func (*Shifter) CreateTrigger

                              func (s *Shifter) CreateTrigger(conn *pg.DB, tableName string) (err error)

                                CreateTrigger will create triggers mentioned on struct before calling it you need to set the table model in shifter using SetTableModel()

                                func (*Shifter) Debug

                                func (s *Shifter) Debug(conn *pg.DB)

                                  Debug : Print postgresql query on terminal

                                  func (*Shifter) DropAllEnum

                                  func (s *Shifter) DropAllEnum(conn *pg.DB, model interface{}, skipPrompt bool) (err error)

                                    DropAllEnum will drop all enums of the given table.

                                    Parameters

                                    conn: postgresql connection
                                    model: struct pointer or string (table name)
                                    skipPrompt: bool (default false | if false then before execution sql it will prompt for confirmation)
                                    

                                    if model is table name then need to set shifter SetTableModel() before calling DropAllEnum()

                                    func (*Shifter) DropAllTable

                                    func (s *Shifter) DropAllTable(conn *pg.DB, cascade bool) (err error)

                                      DropAllTable will drop all tables before calling it you need to set the table model in shifter using SetTableModels()

                                      func (*Shifter) DropTable

                                      func (s *Shifter) DropTable(conn *pg.DB, model interface{}, cascade bool) (err error)

                                        DropTable will drop table if exists in database Parameters

                                        conn: postgresql connection
                                        model: struct pointer or string (table name)
                                        cascade: if enable then it will drop with cascade
                                        

                                        func (*Shifter) GetStructSchema

                                        func (s *Shifter) GetStructSchema(tableName string) (sSchema map[string]model.ColSchema)

                                          GetStructSchema will return struct schema

                                          func (*Shifter) GetTrigger

                                          func (s *Shifter) GetTrigger(tableName string) (trigger string)

                                            GetTrigger : Get triggers by table name

                                            func (*Shifter) SetEnum

                                            func (s *Shifter) SetEnum(enum map[string][]string) (err error)

                                              SetEnum will set global enum list

                                              func (*Shifter) SetLogPath

                                              func (s *Shifter) SetLogPath(logPath string) *Shifter

                                                SetLogPath will set logpath where alter struct log will be created.

                                                deafult path is pwd/log/

                                                func (*Shifter) SetTableModel

                                                func (s *Shifter) SetTableModel(table interface{}) (err error)

                                                  SetTableModel will set table struct pointer to shifter

                                                  func (*Shifter) SetTableModels

                                                  func (s *Shifter) SetTableModels(tables []interface{}) (err error)

                                                    SetTableModels will set multiple table struct pointer to shifter. You can set all the table struct pointers and then perform operation by table name only

                                                    func (*Shifter) UpsertAllEnum

                                                    func (s *Shifter) UpsertAllEnum(conn *pg.DB, model interface{}) (err error)

                                                      UpsertAllEnum will create/update all enums of the given table.

                                                      Parameters

                                                      conn: postgresql connection
                                                      model: struct pointer or string (table name)
                                                      

                                                      if model is table name then need to set shifter SetTableModel() before calling UpsertAllEnum()

                                                      func (*Shifter) UpsertAllUniqueKey

                                                      func (s *Shifter) UpsertAllUniqueKey(conn *pg.DB, model interface{}, skipPrompt ...bool) (err error)

                                                        UpsertAllUniqueKey will create/alter/drop composite unique keys of table.

                                                        Parameters

                                                        conn: postgresql connection
                                                        model: struct pointer or string (table name)
                                                        skipPrompt: bool (default false | if false then before execution sql it will prompt for confirmation)
                                                        

                                                        If model is table name then need to set shifter SetTableModel() before calling CreateAllUniqueKey(). If composite unique key is modified then also it will update. If composite unique key exists in table but doesn't exists in struct UniqueKey method then that will be dropped.

                                                        func (*Shifter) UpsertEnum

                                                        func (s *Shifter) UpsertEnum(conn *pg.DB, model interface{}, enumName string) (err error)

                                                          UpsertEnum will create/update enum by enum name.

                                                          Parameters

                                                          conn: postgresql connection
                                                          model: struct pointer or string (table name)
                                                          enumName: enum which you want to upsert
                                                          

                                                          if model is table name then need to set shifter SetTableModel() before calling UpsertEnum()

                                                          func (*Shifter) Verbose

                                                          func (s *Shifter) Verbose(enable bool) *Shifter

                                                            Verbose will enable executed sql printing in console

                                                            Directories

                                                            Path Synopsis
                                                            cli
                                                            cmd