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?

[`(*gorm.DB).Exec`](https://pkg.go.dev/gorm.io/gorm?tab=doc#DB.Exec) does not return an error, if you want to see if your query failed or not read up on [error handling](https://gorm.io/docs/error_handling.html) with gorm. Use `Exec` when you don't care about output, use `Raw` when you *do* care about the output (more [here](https://gorm.io/docs/sql_builder.html)). Also the convention to check if something exists or not in the db is to use `SELECT EXISTS (SELECT 1 FROM ...)` rather than counting. — mkopriva, Sep 03 '20 at 08:45
@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") } } — jkerone, Sep 03 '20 at 22:31
Anytime you need the result from a query you need to **scan** it. Here's how you would do it with the standard library https://stackoverflow.com/questions/49449087/i-want-to-check-if-record-exist-and-if-not-exist-then-i-want-to-insert-that-reco/49449519#49449519, and here's an example with gorm and Raw https://github.com/rl404/point-system/blob/638293159f7c9a069c33f4f09bdcb9c4ae2b033b/internal/config/db.go#L44. — mkopriva, Sep 04 '20 at 03:00
@mkopriva. That was brilliant thanks! Am learning golang and incorporating tdd at the very beginning. — jkerone, Sep 04 '20 at 07:26

score 1 · Accepted Answer · answered Dec 14 '20 at 07:49

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

gorm raw sql query execution

1 Answers1