How do I create a table in database only if it does not exists

Question

I need to create a table in Go using gorm only if the table does not exists, I have the following. Generally this is used for REST API and used to make REST Calls

package database

import (
    "fmt"
    "log"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"

    "github.com/armadasystems/armada-remote-asst-mgmt/pkg/models"
    _ "github.com/lib/pq"
    "github.com/spf13/viper"
)

type Config struct {
    DBUser        string `mapstructure:"DB_USER"`
    DBPass        string `mapstructure:"DB_PASS"`
    DBHost        string `mapstructure:"DB_HOST"`
    DBPort        string `mapstructure:"DB_PORT"`
    DBName        string `mapstructure:"DB_NAME"`
    SSLMode       string `mapstructure:"SSL_MODE"`
}

var DB *gorm.DB
var err error

func LoadAppConfig(path string) (config Config, err error) {
    viper.AddConfigPath(path)
    viper.SetConfigName("app")
    viper.SetConfigType("env")
    viper.AutomaticEnv()
    err = viper.ReadInConfig()
    if err != nil {
        return
    }

    err = viper.Unmarshal(&config)
    return
}

func InitDB() {
    config, err := LoadAppConfig("./")
    if err != nil {
        fmt.Printf("Cannot load app.env file: %v", err)
    }

    dbHost := config.DBHost
    dbPort := config.DBPort
    dbUser := config.DBUser
    dbPass := config.DBPass
    dbName := config.DBName
    dsn := fmt.Sprintf("host=%s port=%s user=%s dbname=%s password=%s sslmode=disable",
        dbHost,
        dbPort,
        dbUser,
        dbName,
        dbPass,
    )
    fmt.Println(dsn)
    DB, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})
    DB.AutoMigrate(&models.Users{})

    if err != nil {
        log.Fatal("Error connecting to the database...", err)
        panic(err.Error())
    }

    fmt.Println("Successfully connected to database")
}

Above is used for db connection and below is the struct for users

package models

import (
    "gorm.io/gorm"
)

type Users struct {
    gorm.Model
    user  string `json:"USER"`
    addr  string `json:"ADDR"`
    Id    string `json:"ID"`
}

I tried the above code and I was expecting it would create a table if none existed but it throws an error

score 0 · Answer 1 · answered Jun 27 '23 at 03:13

GORM's AutoMigrate function will automatically create tables if they don't exists in the database. Also it will performs adding or modifying columns, indexes,or constraints based on your model.

you can enable the logging to see the queries GORM executed behind

DB, err = gorm.Open(postgres.Open(dsn), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
})

A struct should be having the syntax as type <name> struct {}. type Asset Users this is invalid in here. You should use like type Users struct{}.

type Users struct {
    gorm.Model
    User string `json:"USER"`
    Addr string `json:"ADDR"`
    Id   string `json:"ID"`
}

The fields should be capitalised so that can be exported and recognised by the GORM package.

Hope this helps

Thanks @praththeesh - that mistake you pointed was my typo, fixed it, but thanks for clarification — Lethargic_Sun, Jun 27 '23 at 18:27

How do I create a table in database only if it does not exists

1 Answers1