How to create a Postgres database using GORM

Question

This is primarily focused towards having setup() and teardown() methods for a test suite that I'm planning on writing that involves creation of a DB.

I've figured out how to create a DB using GORM. However, I'm not sure if this is the best approach.

package main

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

func main() {
    db, err := gorm.Open("postgres", "host=127.0.0.1 port=5432 user=superuser dbname=postgres password='' sslmode=disable")
    capture(err)
    db = db.Exec("CREATE DATABASE test_db;")
    if db.Error != nil {
        fmt.Println("Unable to create DB test_db, attempting to connect assuming it exists...")
        db, err = gorm.Open("postgres", "host=127.0.0.1 port=5432 user=superuser dbname=test_db password='' sslmode=disable")
        if err != nil {
           fmt.Println("Unable to connect to test_db")
           capture(err)
        }
    }
    defer db.Close()
}

func capture(err error) {
    if err != nil {
        log.Fatalf("%s", err)
   }
}

I'm connecting to the default postgres DB first, after which I'm creating a second test DB which I'm planning on using.

Is this the best approach ? Or is there a way to connect to Postgres without having a pre-existing DB.

NOTE: I've already looked up answers where people have used SQL driver to connect to a DB using only the connection string user:password@/. That has not worked in my case.(like here)

I've alse tried a connection string without having a DB name, that results in the driver trying to connect to a DB with the same name as the user. Which fails since such a DB does not exist.

Ancillary

This should be the answer. Creating Database with gorm API including password. – Wentz

Answer 1

Here is how I achieved creating a postgreSQL database using Gorm, the key is to connect to postgreSQL only, a connection to "database" is not required to create a database, only connecting to database engine is enough. Just don't pass the database base in connection string.

In main.go

package main

import (
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "fmt"
    "github.com/joho/godotenv"
)

func createDatabase() {
    dsn := fmt.Sprintf("host=%s port=%s user=%s password=%s sslmode=disable TimeZone=%s", Config("DB_HOST"), Config("DB_PORT"), Config("DB_USER"), Config("DB_PASSWORD"), Config("DB_TIMEZONE"))
    DB, _ := gorm.Open(postgres.Open(dsn), &gorm.Config{})

    createDatabaseCommand := fmt.Sprintf("CREATE DATABASE %s", Config("DB_NAME"))
    DB.Exec(createDatabaseCommand)
}
 
func main() {
    createDatabase()
}

Now simply just run go get -d ./... && go run main.go

Keppel

Answer 2

The way I went about it was to avoid creating the db first with the expectation of an error and then using that as an indication db already exists. I find below to be more graceful IMO. This is with using GORM btw

connStr := fmt.Sprintf("user=%s password=%s host=%s port=%s dbname=%s sslmode=disable",
    "user",
    "password",
    "host",
    "port",
    "postgres")

// connect to the postgres db just to be able to run the create db statement
db, err := gorm.Open(postgres.Open(connStr), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Silent)})
if err != nil {
    return err
}

// check if db exists
stmt := fmt.Sprintf("SELECT * FROM pg_database WHERE datname = '%s';", client.Name)
rs := db.Raw(stmt)
if rs.Error != nil {
    return rs.Error
}

// if not create it
var rec = make(map[string]interface{})
if rs.Find(rec); len(rec) == 0 {
    stmt := fmt.Sprintf("CREATE DATABASE %s;", dbName)
    if rs := db.Exec(stmt); rs.Error != nil {
        return rs.Error
    }

    // close db connection
    sql, err := db.DB()
    defer func() {
        _ = sql.Close()
    }()
    if err != nil {
        return err
    }
}

Windowpane

Answer 3

Your method seems valid enough. You could also use postgres' createdb utility to create the DB before you connect to it. For example:

import (
    "log"
    "os/exec"
    "bytes"
)

func createPgDb() {
    cmd := exec.Command("createdb", "-p", "5432", "-h", "127.0.0.1", "-U", "superuser", "-e", "test_db")
    var out bytes.Buffer
    cmd.Stdout = &out
    if err := cmd.Run(); err != nil {
        log.Printf("Error: %v", err)
    }
    log.Printf("Output: %q\n", out.String())
}

This example is paraphrased from the Command / Run examples in the Go manual https://golang.org/pkg/os/exec/#Command

Fideliafidelio

The suggested answer in the posted question works better: It is a native gorm Api solution does not depend on whether createdb is in the path or engaging in process forking from a server. – Wentz

Answer 4

I suggest using database/psql package for creating the database , not gorm

be sure you imported these packages

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

and use below codes to create database

url := fmt.Sprintf("host=%s port=%s user=%s password=%s sslmode=disable",
        DBHost, DBPort, DBUsername, DBPassword)
db, err := sql.Open("postgres", url)
if err != nil {
    panic(err)
}
defer db.Close()

_, err = db.Exec(fmt.Sprintf("CREATE DATABASE %s;", DBName))
if err != nil {
    panic(err)
}

Cuckooflower

Answer 5

This is my impl

var DB * gorm.DB

func ConnectToDb(params *types.AppParameters) {
    var err error
    dbName := os.Getenv("DB_NAME")
    conn_url := fmt.Sprintf("user=%s password=%s host=%s port=%s sslmode=disable",
        os.Getenv("DB_USER"),
        os.Getenv("DB_PASSWORD"),
        os.Getenv("DB_HOST"),
        os.Getenv("DB_PORT"),
    )
    conn_db_url := fmt.Sprintf("%s dbname=%s", conn_url, dbName)
    DB, err = gorm.Open(postgres.Open(conn_url), &gorm.Config{})
    count  := 0
    DB.Raw("SELECT count(*) FROM pg_database WHERE datname = ?", dbName).Scan(&count)
    if count == 0 {
        sql :=fmt.Sprintf("CREATE DATABASE %s", dbName)
        result := DB.Exec(sql)
        utils.CheckError(result.Error)
    }

    DB, err = gorm.Open(postgres.Open(conn_db_url), &gorm.Config{})
    utils.CheckError(err)
    
}

Octennial