How do I create an index #125
Comments
|
No, go-pg does support creating table schemas and/or indexes. I usually create schema and indexes manually: |
|
that's a pity 😭 |
|
If it helps someone, I am able to auto generate an index on a column when creating my tables with the following code: Please let me know if you have a better solution ❤️ func createSchema(db *pg.DB) (err error) {
for _, model := range []interface{}{(*APIChannel)(nil), (*Mgmt)(nil), (*PlatformAccount)(nil)} {
err = db.CreateTable(model, &orm.CreateTableOptions{
IfNotExists: true,
})
if err != nil {
return err
}
// Extract the table name from the model
var tableName string
_, err := db.Model(model).Query(&tableName, "SELECT '?TableName'")
if err != nil {
panic(err)
}
// Trim the quotes which by default will look like -> "table_name"
tableName = tableName[1 : len(tableName)-1]
// Create an index on "deleted_at" column of each table to support the soft delete functionality of the BaseModel
_, err = db.Exec(fmt.Sprintf("CREATE INDEX %s_deleted_at ON %s (deleted_at)", tableName, tableName))
if err != nil {
panic(err)
}
}
return nil
} |
|
@saginadir thank you very much for sharing your code. Based on your code, I created mine and works very well. The following is my code: type Base struct {
ID int64
CreatedAt time.Time `sql:"default:now()"`
UpdatedAt time.Time `sql:"default:now()"`
}
// BeforeUpdate ...
func (b *Base) BeforeUpdate(ctx context.Context) (context.Context, error) {
b.UpdatedAt = time.Now()
return ctx, nil
}
var _ orm.BeforeUpdateHook = (*Base)(nil)
// Order ...
type Order struct {
Base
Name string
Items []Item `pg:"many2many:order_to_items"`
}
// Item ...
type Item struct {
Base
Name string
}
// OrderToItem ...
type OrderToItem struct {
OrderID int64
ItemID int64
}
func createSchema(db *pg.DB) error {
// Register many to many model so ORM can better recognize m2m relation.
orm.RegisterTable((*OrderToItem)(nil))
models := []interface{}{
&Order{},
&Item{},
&OrderToItem{},
}
// Drop Tables For Testing (Don't do it in production)
err := DropTable(db, models)
if err != nil {
return err
}
// Create Tables (will create created_at index automatically)
err = CreateTable(db, models)
if err != nil {
return err
}
err = CreateIndex(db, (*OrderToItem)(nil), "btree", "order_id")
if err != nil {
return err
}
err = CreateIndex(db, (*OrderToItem)(nil), "btree", "item_id")
if err != nil {
return err
}
return nil
}
// CreateTable ...
func CreateTable(db *pg.DB, models []interface{}) error {
for _, model := range models {
err := db.CreateTable(model, &orm.CreateTableOptions{
IfNotExists: true,
})
if err != nil {
return err
}
// Create btree index on created_at column if exists
if reflect.ValueOf(model).Elem().FieldByName("CreatedAt").IsValid() {
err := CreateIndex(db, model, "btree", "created_at")
if err != nil {
return err
}
}
}
return nil
}
// DropTable ...
func DropTable(db *pg.DB, models []interface{}) error {
for _, model := range models {
err := db.DropTable(model, &orm.DropTableOptions{
IfExists: true,
})
if err != nil {
return err
}
}
return nil
}
// GetTableName ...
func GetTableName(db *pg.DB, model interface{}) (string, error) {
var tableName string
// Extract the table name from the model
_, err := db.Model(model).Query(&tableName, "SELECT '?TableName'")
if err != nil {
return "", err
}
// I am paranoid and check if tableName is quoted string
if tableName[0:1] != `"` || tableName[len(tableName)-1:len(tableName)] != `"` {
return "", errors.New("tableName format returned from postgres is not correct")
}
// Trim the quotes which by default will look like -> "table_name"
tableName = tableName[1 : len(tableName)-1]
return tableName, nil
}
// CreateIndex ...
func CreateIndex(db *pg.DB, model interface{}, indexType string, indexColumn string) error {
tableName, err := GetTableName(db, model)
if err != nil {
return err
}
_, err = db.Model(model).Exec(
fmt.Sprintf("CREATE INDEX IF NOT EXISTS %s_%s_idx ON %s USING %s (%s)", tableName, indexColumn, tableName, indexType, indexColumn),
)
if err != nil {
return err
}
return nil
} |
|
Hi, just FYI. Hope it can help others. I improved my index creation version by creating custom tags for example type User struct {
ID int64
Name string
Emails []string
}
type Story struct {
ID int64
Title string
AuthorID int64 `cpg:"index:btree"`
Author *User
}
func createSchema(db *pg.DB) error {
models := []interface{}{
&User{},
&Story{},
}
// Create Tables
err = CreateTable(db, models)
if err != nil {
return err
}
return nil
}
// CreateTable ...
func CreateTable(db *pg.DB, models []interface{}) error {
for _, model := range models {
err := db.CreateTable(model, &orm.CreateTableOptions{
IfNotExists: true,
})
if err != nil {
return err
}
err = handleTagCpg(db, model, nil)
if err != nil {
return err
}
}
return nil
}
// rootModel is for handling nested anonymous structs inside a table struct
func handleTagCpg(db *pg.DB, model interface{}, rootModel interface{}) error {
if rootModel == nil {
rootModel = model
}
t := reflect.TypeOf(model).Elem()
for i := 0; i < t.NumField(); i++ {
field := t.Field(i)
if field.Anonymous {
err := handleTagCpg(db, reflect.New(field.Type).Interface(), rootModel)
if err != nil {
return err
}
continue
}
tag := field.Tag.Get("cpg")
if tag != "" {
tagSplit := strings.Split(tag, ":")
if tagSplit[0] == "index" {
err := CreateIndex(db, rootModel, tagSplit[1], Underscore(field.Name))
if err != nil {
return err
}
}
}
}
return nil
}
// Underscore converts "CamelCasedString" to "camel_cased_string".
func Underscore(s string) string {
r := make([]byte, 0, len(s)+5)
for i := 0; i < len(s); i++ {
c := s[i]
if IsUpper(c) {
if i > 0 && i+1 < len(s) && (IsLower(s[i-1]) || IsLower(s[i+1])) {
r = append(r, '_', ToLower(c))
} else {
r = append(r, ToLower(c))
}
} else {
r = append(r, c)
}
}
return string(r)
}
// IsUpper ...
func IsUpper(c byte) bool {
return c >= 'A' && c <= 'Z'
}
// IsLower ...
func IsLower(c byte) bool {
return c >= 'a' && c <= 'z'
}
// ToUpper ...
func ToUpper(c byte) byte {
return c - 32
}
// ToLower ...
func ToLower(c byte) byte {
return c + 32
}
// GetTableName ...
func GetTableName(db *pg.DB, model interface{}) (string, error) {
var tableName string
// Extract the table name from the model
_, err := db.Model(model).Query(&tableName, "SELECT '?TableName'")
if err != nil {
return "", err
}
// I am paranoid and check if tableName is quoted string
if tableName[0:1] != `"` || tableName[len(tableName)-1:len(tableName)] != `"` {
return "", errors.New("tableName format returned from postgres is not correct")
}
// Trim the quotes which by default will look like -> "table_name"
tableName = tableName[1 : len(tableName)-1]
return tableName, nil
}
// CreateIndex ...
func CreateIndex(db *pg.DB, model interface{}, indexType string, indexColumn string) error {
tableName, err := GetTableName(db, model)
if err != nil {
return err
}
_, err = db.Model(model).Exec(
fmt.Sprintf("CREATE INDEX IF NOT EXISTS %s_%s_idx ON %s USING %s (%s)", tableName, indexColumn, tableName, indexType, indexColumn),
)
if err != nil {
return err
}
return nil
} |
|
That is a pity you all have to write custom code, but with tool like https://github.com/go-pg/migrations creating an index is a matter of creating a file create index concurrently your_index_name on your_table_name (foo, bar);And you have all the power of SQL with support of all index types PostgreSQL has in its arsenal... I just don't see what assistance go-pg can provide here... |
@vmihailenco First of all, thank you for your hard work on go-pg, the tool has been valuable to me as I'm sure it's valuable to others as well. But regarding your suggestion, I don't think you see the issue we are trying to tackle. The module you've provided in your suggestion is NOT what I was looking for. If I've wanted something similar I could have easily picked one of the dozens of similar tools written in various other languages. What I am looking for is a tool that will just enable me to create an index on an already existing We are not asking for a complete indexing solution that will provide features like a support for multi-column indexes - but having the ability to just simply add a tag In fact, it seems like our buddy @sgon00 has already created a solution for that. So I assume, and of course if you also think it'll be useful, adding this option to the core of go-pg won't cause too much trouble. I'll be happy to assist with the PR if it's something you will welcome. |
|
@vmihailenco is this still true in v10? Sorry, I'm brand new to Golang and just wading in here as a v10 first timer, and you now have https://pkg.go.dev/github.com/go-pg/pg/v10#example-DB.Model-CreateTable - which I've worked my app init around, however if I can't mark the index on the struct that defines a table, that might be a show stopper for that very handy utility.... |
@Routhinator if you are new to Golang perhpas try the Gorm ORM, it supports more feature and it's better documented. go-pg is lower level and as you see it's missing indexes tags ;) |
I can't find any doc said about how to create an index with pg.Haven't support it?
The text was updated successfully, but these errors were encountered: