schemamagic

README

schemamagic

Go library that automatically updates PostgreSQL schema without touching existing data. Find the documentation on godoc.

Motivation

This library aims to update SQL (PostgreSQL) schema changes painlessly, bridging the gap between SQL and NoSQL. I've been using this in production in a personal project of mine, and it has made my life a million times easier. No more messy table updates or setting defaults.

Installation

go get https://github.com/apratheek/schemamagic

Usage

import "github.com/apratheek/schemamagic"

The above command should import the package into your code, assuming that your $GOPATH is set. To connect to the database, the following function needs to be called.

schemamagic.SetupDB(host string, port uint16, database string, username string, password string)

which returns a connection to the underlying database and an error. schemamagic uses pgx as the PostgreSQL driver, so all the methods available on *pgxpool.Pool are valid on this connection.

Logging

The package uses ulogger to log the output. The default log is set at "info". To set it to either warn or debug or info, the following API is provided.

schemamagic.SetLogLevel(level string)

where level is either warn, info or debug.

Table (struct)

type Table struct {
	Name          string // This denotes the name of the PostgreSQL table
	DefaultSchema string // This is the default schema (usually "public")
	Database      string // This is the name of the database
	Tx            pgx.Tx // This is pgx.Tx
	Autocommit    bool // Denotes if the operation on each table needs to be autocommitted (default is False)
	Columns       []Column // Stores all the columns in this table
}

Create table

table := schemamagic.NewTable(schemamagic.Table{Name: "temp_table", DefaultSchema: "public", Database: database, Tx: tx})

Available methods

1. Append(col Column): This method appends a column to the table

2. AddConstraint(constraint Constraint): This method appends a constraint to the table

3. DropTable(ctx): This method drops the table from the database

4. Begin(ctx): This method creates the table (along with all the columns) if it does not exist, or updates the schema if it has changed.

Column (Struct)

type Column struct {
	Name            string // Name of the column
	Datatype        string // Datatype of the column (bigint, bigserial, text, jsonb, bigint[], etc)
	PseudoDatatype  string // This is the name of the datatype that is used by PostgreSQL to store the mentioned Datatype. Eg.: time --> time without/with time zone, timestamp --> timestamp with/without time zone, etc.
	Action          string // Default is "Add", does not support anything else as of this moment
	DefaultExists   bool // Default is false. Stores if a default value needs to be assigned to this column
        DefaultValue    string // This is the default value that will be set to the column if DefaultExists is true. Eg.: 400 (integer/bigint), 'Hello' (text), array[]::bigint[] (bigint[]), date_part('epoch'::text, now())::bigint (timestamp)
	IsUnique        bool // Default is false. If true, the unique key contraint is added
	IsPrimary       bool // Default is false. If true, the primary key constraint is added
	IsNotNull       bool // Default is false. If true, the 'NOT NULL' constraint is added
	IndexRequired   bool // Default is false. If true, an index is created on this column
	Comment         string // This is just a comment regarding this column. Does not affect the execution of the library
	SequenceRestart int64 // In case the Datatype is either bigserial or serial, a number can be mentioned here to restart the sequence
}

Create Column

c1 := schemamagic.NewColumn(schemamagic.Column{Name: "action", Datatype: "text", IsNotNull: true, IsUnique: true})
c2 := schemamagic.NewColumn(schemamagic.Column{Name: "created_at", Datatype: "bigint", DefaultExists: true, DefaultValue: "400"})
c3 := schemamagic.NewColumn(schemamagic.Column{Name: "version_description", Datatype: "text", DefaultExists: true, DefaultValue: "'Hello'", IndexRequired: true})
c4 := schemamagic.NewColumn(schemamagic.Column{Name: "version_new", Datatype: "bigserial"})
c5 := schemamagic.NewColumn(schemamagic.Column{Name: "arr", Datatype: "bigint[]", DefaultExists: true, DefaultValue: "array[]::bigint[]"})
c6 := schemamagic.NewColumn(schemamagic.Column{Name: "timestamp", Datatype: "bigint", DefaultExists: true, DefaultValue: "date_part('epoch'::text, now())::bigint", IsPrimary: true, IsUnique: true})
c7 := schemamagic.NewColumn(schemamagic.Column{Name: "timestamp2", Datatype: "timestamp", DefaultExists: true, DefaultValue: "current_timestamp", PseudoDatatype: "timestamp without time zone"})

Add columns to a table

table.Append(c1)
table.Append(c2)
table.Append(c3)
table.Append(c4)
table.Append(c5)
table.Append(c6)
table.Append(c7)

Constraint (struct)

type Constraint struct {
	Name  string // This stores the name of the constrant
	Value string // This stores the constrant that needs to be applied
}

Create Constraint

compositeKey := schemamagic.Constraint{
		Name:  "new_id",
		Value: "PRIMARY KEY (action, version_description)",
	}
compositeKey2 := schemamagic.Constraint{
		Name:  "uniq_version",
		Value: "UNIQUE (created_at, version_new)",
	}

Add constraints to a table

table.AddConstraint(compositeKey)
table.AddConstraint(anotherConstraint)

Example

Check out a minimal example here.

Things not implemented

1. Haven't yet implemented addition of foreign keys. This wasn't something I required. This has now been implemented via constraints.

Gotchas

1. If a column has DefaultExists set to true and a corresponding DefaultValue, and its DefaultValue is changed at the next iteration, then all the columns in the table are updated with the new DefaultValue. This was deliberate, as in case you want to update the default value, you'd probably want to update the value in all the columns.
2. You can pass along an individual Tx object to update each table, or you could use the same Tx object to update all the tables at once. The choice is left to the developer. Of course, the changes will have to be explicitly committed by the developer (in case Autocommit is set to false). Otherwise, none of the changes would reflect (duh!).

Documentation

Index

• func SetLogLevel(level string)
• func SetupDB(ctx context.Context, dbHost string, port uint16, database string, ...) (*pgxpool.Pool, error)
• type Column
  • func NewColumn(c Column) Column
• type Constraint
• type Table
  • func NewTable(t Table) *Table
  • func (t *Table) AddConstraint(constraint Constraint)
  • func (t *Table) Append(col Column)
  • func (t *Table) Begin(ctx context.Context)
  • func (t *Table) DropTable(ctx context.Context)

Functions

func SetLogLevel

func SetLogLevel(level string)

SetLogLevel sets the log level of the output log

func SetupDB

func SetupDB(ctx context.Context, dbHost string, port uint16, database string, username string, password string) (*pgxpool.Pool, error)

SetupDB establishes the connection between the postgres server

Types

type Column

type Column struct {
	Name           string
	Datatype       string
	PseudoDatatype string // This is the name of the datatype that is used by PostgreSQL to store the mentioned Datatype. Eg.: time --> time without/with time zone, timestamp --> timestamp with/without time zone, etc.
	Action         string
	DefaultExists  bool
	DefaultValue   string
	IsUnique       bool
	IsPrimary      bool
	IsNotNull      bool
	IndexRequired  bool
	// Stores the Index Type: GIN, etc. Default will be empty, which is B-Tree (default index type in postgres)
	IndexType       string
	Comment         string
	SequenceRestart int64
}

Column stores all the parameters of each column inside a table

func NewColumn

func NewColumn(c Column) Column

NewColumn initializes the Column with the default parameters

type Constraint

type Constraint struct {
	Name  string // This stores the name of the constrant
	Value string // This stores the constrant that needs to be applied
}

Constraint stores the applicable constraint on a table

type Table

type Table struct {
	Name          string
	DefaultSchema string
	Database      string
	Tx            pgx.Tx
	Autocommit    bool
	Columns       []Column
	// contains filtered or unexported fields
}

Table holds the table details as well as all the columns inside the table

func NewTable

func NewTable(t Table) *Table

NewTable creates and returns an instance of a postgres table

func (*Table) AddConstraint

func (t *Table) AddConstraint(constraint Constraint)

AddConstraint accepts a constraint and appends it to the list of constraints that need to be applied on the table

func (*Table) Append

func (t *Table) Append(col Column)

Append method accepts a column and appends it to the list of columns of the table

func (*Table) Begin

func (t *Table) Begin(ctx context.Context)

Begin method initiates a DB transaction and checks if table (Name) exists in the DB. If it does, then it calls updateTable(). If it doesn't, it calls createTable(), and then updateTable()"""

func (*Table) DropTable

func (t *Table) DropTable(ctx context.Context)

DropTable method drops the table from the DB