How to dynamically map a database table in Golang?

Question

I have to create a JSON from a MS-SQL Database through Go. The problem is that some database fields are created dynamically in backend system (not Go).

There could be:

type Demotable struct{
Id int
Name string
Adress int
Z_somedynamicfield string
Z_anotherfield int
}

All fields prefixed with "Z_" are dynamic, so struct could also be:

type Demotable struct{
Id int
Name string
Adress int
Z_completedifferent int
Z_notlikefirst string
Z_evenmorefields string
}

I also have a helper table where every "dynamic field" is described:

| tableName | fieldName          | fieldType |
+-----------+--------------------+-----------+
| Demotable | Z_somedynamicfield | string    |
+-----------+--------------------+-----------+
| Demotable | Z_anotherfield     | int       |
...

Ideally I would dynamically "extend" struct with infos from helper table - but I don't have an idea how or if this even is possible?

You cannot extend structs at runtime. You'll have to build a map that you can then JSON encode. sql.Rows.Columns and .ColumnTypes return column names and types of a result set, respectively. You shouldn't need to query that other table. — Peter
– Peter, Commented Apr 16, 2018 at 21:24

oxshxll · Accepted Answer · 2018-04-17 08:16:51Z

1

I would sugges first querying the current tables of your table through a prepared statement:

SELECT * FROM [DatabaseName].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable'

Store the results of the query in a string slice. After doing so, create a map with string keys and *interface{} values.

 results := make(map[string](*interface{})

While iterating over results of your actual query, you can finally assign the values from your previous query to map the results.

Kyle Banks offers a great explanation on receiving dynamic results from Query results on this article.

If for any reason you need to actually specify the type of the results, the reflect package will be of a lot of help on auto-resolving the value type for your needs.

answered Apr 17, 2018 at 8:16

oxshxll

261 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to dynamically map a database table in Golang?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related