How to create a Postgres database using GORM

14
votes

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.

2
goormgo-gorm
This should be the answer. Creating Database with gorm API including password. - Mario

2 Answers

2
votes

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

0
votes

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
    }
}