CODEWITHSUNDEEP
databasegodatabase-connectiongo-gorm
Alex Colussi
Alex Colussi

Reputation: 123

how set a specifc database schema with gorm?

I'm developing an CRUD app. I'm reading an JSON from an api and I want to write this JSON in a database with "database/sql" and GORM in a spcific schema the struct:

type Veiculo struct {
gorm.Model
Codigo                int       `json:"vei_codigo"`
Placa                 string    `json:"vei_placa"`
Nome                  string    `json:"vei_nome"`
}

the endpoint function:

func CreateVeiculo(c *gin.Context) {

var veiculo model.Veiculo
//id := c.Params.ByName("id")

c.BindJSON(&veiculo)
c.JSON(200, veiculo)

psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+" password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
dbVeiculosGorm, err := gorm.Open("postgres", psqlInfo)
if err != nil {
    panic(err)
}
defer dbVeiculosGorm.Close()

dbVeiculosGorm.AutoMigrate(&model.Veiculo{})

//t := time.Now()
//ts := t.Format("2006-01-02 15:04:05")

dbVeiculosGorm.Create(&model.Veiculo{Placa: veiculo.Placa, Nome: veiculo.Nome}

but the database is untouchable. There is multiple schemas. Do i have to set the specific schema?? What am i doing wrong?

Upvotes: 11

Views: 21786

Answers (9)

dev-nnb
dev-nnb

Reputation: 1

Please check with this code my testing is on

package main

import ( "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/postgres" )

type User struct { ID int Username string }

func main() { db, err := gorm.Open("postgres", "host=localhost port=5432 sslmode=disable dbname=aman") if err != nil { panic(err) } defer db.Close() db.LogMode(true)

// set schema here.
gorm.DefaultTableNameHandler = func(db *gorm.DB, tableName string) string {
    return "s1." + tableName
}

db.CreateTable(&User{})

}

Upvotes: -1

Amr Ali
Amr Ali

Reputation: 187

Using GORM 2.0 This will work. Note the . has to be added for the TablePrefix.

func ConnectDB() (db *gorm.DB, err error) {
    db, err = gorm.Open(postgres.New(postgres.Config{
        DSN:                  `user=test password=test dbname=DB-NAME port=5432 sslmode=disable`,
        PreferSimpleProtocol: true,
    }), &gorm.Config{
        NamingStrategy: schema.NamingStrategy{
            TablePrefix:   "YOUR_SCHEMA_NAME.", // schema name
            SingularTable: false,
        }})
    return
}

Upvotes: 13

Amr Ali
Amr Ali

Reputation: 187

The Answer of @hbswift also worked for me but I tried something before which is a little bit different inspired by latest gorm Close idea.

func ConnectDB() (db *gorm.DB, err error) {
    db, err = gorm.Open(postgres.New(postgres.Config{
        DSN:                  `user=postgres password=a12345 dbname=YOUR-DB-NAME port=5432 sslmode=disable`,
        PreferSimpleProtocol: true, // disables implicit prepared statement usage. By default pgx automatically uses the extended protocol
    }), &gorm.Config{})
    return
}


func main() {
    var sqlDB *sql.DB
    var db *gorm.DB
    {
        var err error
        db, err = ConnectDB()
        if err != nil {
            os.Exit(-1)
        }
        sqlDB, _ = db.DB()
        sqlDB.Exec(`set search_path='Schema-Name'`)
    }
    defer sqlDB.Close()

}

Upvotes: 3

hbswift
hbswift

Reputation: 39

If you are using the latest version of gorm this WILL not work:

gorm.DefaultTableNameHandler = func(dbVeiculosGorm *gorm.DB, defaultTableName string) string {
    return "your schema name." + defaultTableName
}

dbVeiculosGorm.AutoMigrate(&model.Veiculo{})

Instead define your naming convention in the gorm config. Example:

    dsn := "host=localhost user=myuser dbname=golang_tutorial port=5432"
    db,err := gorm.Open(postgres.Open(dsn), &gorm.Config{
            NamingStrategy: schema.NamingStrategy{
                TablePrefix: "golang_crud.", // schema name  
                SingularTable: true,
            },
    })

Upvotes: -1

Bill Zelenko
Bill Zelenko

Reputation: 2858

To specify specific schema in your create statement modify your code like so:

dbVeiculosGorm.Table("schema.tablename").Create(&model.Veiculo{Placa: veiculo.Placa, Nome: veiculo.Nome}

To specify schema for your migrate action, add this method:

type Veiculo struct {
    gorm.Model
    Codigo int    `json:"vei_codigo"`
    Placa  string `json:"vei_placa"`
    Nome   string `json:"vei_nome"`
}

func (u *Veiculo) TableName() string {
    // custom table name, this is default
    return "schema.veiculo"
}

Upvotes: 6

Ethan Pailes
Ethan Pailes

Reputation: 81

I don't have the reputation to comment yet or I would comment on Yurifull's post. It is worth mentioning that singularizing is a big more complex than chopping off the last character. Fortunately, jinzhu has split the singularization logic out into its own package so we can update Yurifull's code to look like:

import (
    "github.com/jinzhu/inflection"
)

...

gorm.DefaultTableNameHandler = func(db *gorm.DB, table string) string {
    fmt.Printf(table)
    return "schema_name." + inflection.Singular(table)
}

Upvotes: 0

Yurifull
Yurifull

Reputation: 391

I use this configuration with my database with singular names with postgresql.

gorm.DefaultTableNameHandler = func(db *gorm.DB, table string) string {
    fmt.Printf(table)
    return "schema_name." + table[:len(table)-1]
}

Upvotes: 0

Thushara Buddhika
Thushara Buddhika

Reputation: 1820

In Addition to Alan.WCR to Capitalize Table name,

gorm.DefaultTableNameHandler = func(db *gorm.DB, table string) string {
    var tableName string
    names := strings.Split(table, "_")
    for _, name := range names {
        tableName = tableName + strings.Title(name)
    }
    return "Users." + tableName
}

Upvotes: 1

Alan.WCR
Alan.WCR

Reputation: 31

before automigrate do

gorm.DefaultTableNameHandler = func(dbVeiculosGorm *gorm.DB, defaultTableName string) string {
    return "your schema name." + defaultTableName
}

dbVeiculosGorm.AutoMigrate(&model.Veiculo{})

Upvotes: 3

Related Questions