Go library that automatically updates PostgreSQL schema without touching existing data. Find the documentation on godoc.
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.
go get https://github.com/apratheek/schemamagic
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.
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
.
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
}
table := schemamagic.NewTable(schemamagic.Table{Name: "temp_table", DefaultSchema: "public", Database: database, Tx: tx})
-
Append(col Column):
This method appends a column to the table -
AddConstraint(constraint Constraint):
This method appends a constraint to the table -
DropTable(ctx):
This method drops the table from the database -
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.
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
}
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"})
table.Append(c1)
table.Append(c2)
table.Append(c3)
table.Append(c4)
table.Append(c5)
table.Append(c6)
table.Append(c7)
type Constraint struct {
Name string // This stores the name of the constrant
Value string // This stores the constrant that needs to be applied
}
compositeKey := schemamagic.Constraint{
Name: "new_id",
Value: "PRIMARY KEY (action, version_description)",
}
compositeKey2 := schemamagic.Constraint{
Name: "uniq_version",
Value: "UNIQUE (created_at, version_new)",
}
table.AddConstraint(compositeKey)
table.AddConstraint(anotherConstraint)
Check out a minimal example here.
Haven't yet implemented addition of foreign keys. This wasn't something I required.This has now been implemented via constraints.
- If a column has
DefaultExists
set totrue
and a correspondingDefaultValue
, and itsDefaultValue
is changed at the next iteration, then all the columns in the table are updated with the newDefaultValue
. 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. - You can pass along an individual
Tx
object to update each table, or you could use the sameTx
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!).