shifter

package module
v0.0.0-...-b5dfc24 Latest Latest
Warning

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

 Go to latest Published: May 4, 2020 License: MIT Imports: 19 Imported by: 3

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().

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

Source Files

View all Source files

Directories

Path Synopsis
cli
cmd

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.