Home > Software engineering >  How to use Go / GORM to print SELECT query output without pre-defined struct
How to use Go / GORM to print SELECT query output without pre-defined struct

Time:07-21

I am developing an API using Go which connects to MySQL database for some query execution. Am using GORM for database operations. But am stuck at printing the SELECT query output for the tables which I don't have the column names.

My use case is that, I need to run the query on multiple tables where I don't have an idea about what their column names and types are. And so I cannot pre-define a struct for all the current and future tables which might get added.

Is there a way to print/save the SELECT query output without a pre-defined struct ?

I tried do some using empty struct but it didn't help me.

P.S: Am a beginner in Go

    type Testing struct{}

    var test Testing
    dsn := fmt.Sprintf("%v:%v@tcp(%v:%v)/%v", myds.DBuser, myds.DBpassword, myds.DBhost, myds.DBport, myds.DBname)
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        fmt.Println(err)
    }
    tx := db.Raw(query).Scan(&test)
    if tx.Error != nil {
        fmt.Println(tx.Error)
    }
    fmt.Println(test)

CodePudding user response:

You can use an anonymous struct

Let's say you have a struct:

type User struct{
  FirstName string
  LastName string
}

Query:

SELECT CONCAT(first_name,last_name) AS full_name from users;

Notice the new column full_name

you can simply do

var fullName = struct{FullName string}{}

Notice how I use pascal case & FullName has to be the field name
A capital letter in between will represent a _
Field is public so it can be accessed outside.

full_name(query) = FullName(field)

pass this fullName object as a bucket to your Scan and it should work.

db.Raw(query).Scan(&fullName)

EDIT:

Your query will have some result right?
Let me assume that you have

column_one,column_two... column_n

Now, to get the data from all the columns or selected ones if you want, you simply have to define fields (in anonymous struct) with specific names. In our case:

struct{ColumnOne,ColumnTwo,..ColumnN interface{}}{}

P.S. I have used interface{}, you can use types depending on the data your column returns.

CodePudding user response:

It worked for me by using a map type with interface. This helped me to save the SELECT query results without pre-defined struct or the column names.

    dsn := fmt.Sprintf("%v:%v@tcp(%v:%v)/%v", myds.DBuser, myds.DBpassword, myds.DBhost, myds.DBport, myds.DBname)

    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        fmt.Println(err)
    }

    var result []map[string]interface{}

    tx := db.Raw(query).Scan(&result)
    if tx.Error != nil {
        fmt.Println(tx.Error)
        return
    }
    bytes, _ := json.Marshal(result)
    fmt.Println(string(bytes))
  • Related