gorm raw sql query execution

Question

Am running a query to check if a table exists or not using the gorm orm for golang. Below is my code.

package main

import (
    "fmt"
    "log"

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

    _ "github.com/lib/pq"
)

// App sets up and runs the app
type App struct {
    DB *gorm.DB
}

`const tableCreationQuery = `SELECT count (*) 
FROM information_schema.TABLES 
WHERE (TABLE_SCHEMA = 'api_test') AND (TABLE_NAME = 'Users')`

func ensureTableExists() {
    if err := a.DB.Exec(tableCreationQuery); err != nil {
        log.Fatal(err)
    }
}`

The expected response should be either 1 or 0. I got this from another SO answer. Instead I get this

2020/09/03 00:27:18 &{0xc000148900 1 0xc000119ba0 0} exit status 1 FAIL go-auth 0.287s

My untrained mind says its a pointer but how do I reference the returned values to determine what was contained within?

Solution

If you want to check if your SQL statement was successfully executed in GORM you can use the following:

tx := DB.Exec(sqlStr, args...)

if tx.Error != nil {
    return false
}

return true

However in your example are using a SELECT statement then you need to check the result, which will be better suited to use the DB.Raw() method like below

var exists bool
DB.Raw(sqlStr).Row().Scan(&exists)
return exists

Comments

[mkopriva]

(*gorm.DB).Exec does not return an error, if you want to see if your query failed or not read up on error handling with gorm. Use Exec when you don't care about output, use Raw when you do care about the output (more here). Also the convention to check if something exists or not in the db is to use SELECT EXISTS (SELECT 1 FROM ...) rather than counting.

[jkerone]

@mkopriva, thanks for your answer. Your suggested command does run and I have added it below. Am still unclear how to check the returned result. Did I receive a 1 or 0 or empty array. Would be great if you could help with that. Many thanks again. func ensureTableExists() { dbname := "api_test" tablename := "User" err := a.DB.Raw("SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_schema=? AND table_name=?)", dbname, tablename).Error if err != nil { fmt.Print("no error") } }

[mkopriva]

Anytime you need the result from a query you need to scan it. Here's how you would do it with the standard library stackoverflow.com/questions/49449087/…, and here's an example with gorm and Raw github.com/rl404/point-system/blob/….

[jkerone]

@mkopriva. That was brilliant thanks! Am learning golang and incorporating tdd at the very beginning.