Create DAO Layer with ORM to facilitate DB queries

[agalue]

The ReST API provides lots of information, but sometimes its limitations prevents to implement certain use cases. For this reason it would be useful to have access to the database on a easy manner.

In the world of GO, the following is one of the most used ORMs: https://gorm.io

Here is an example:

package main

import (
	"fmt"
	"encoding/json"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/postgres"
)

// OnmsNode an OpenNMS node object
type OnmsNode struct {
	ID            uint              `gorm:"column:nodeid;primary_key"`
	Label         string            `gorm:"column:nodelabel"`
	LabelSource   string            `gorm:"column:nodelabelsource"`
	ForeignSource string            `gorm:"column:foreignsource"`
	ForeignID     string            `gorm:"column:foreignid"`
	Location      string            `gorm:"column:location"`
	SysObjID      string            `gorm:"column:nodesysoid"`
	SysName       string            `gorm:"column:nodesysname"`
	SysDescr      string            `gorm:"column:nodesysdescription"`
	SysLocation   string            `gorm:"column:nodesyslocation"`
	SysContact    string            `gorm:"column:nodesyscontact"`
	Interfaces    []OnmsIPInterface `gorm:"foreignKey:NodeID;AssociationForeignKey:nodeid" json:",omitempty"`
}

// TableName the name of the OpenNMS Node Table
func (OnmsNode) TableName() string {
	return "node"
}

// OnmsIPInterface the IP Interface Object
type OnmsIPInterface struct {
	ID          uint   `gorm:"column:id;primary_key"`
	IPAddress   string `gorm:"column:ipaddr"`
	Hostname    string `gorm:"column:iphostname"`
	IsManaged   string `gorm:"column:ismanaged"`
	SnmpPrimary string `gorm:"column:issnmpprimary"`
	NodeID      uint   `gorm:"column:nodeid"`
}

// TableName the name of the OpenNMS IP Interface Table
func (OnmsIPInterface) TableName() string {
	return "ipinterface"
}

func main() {
	db, err := gorm.Open("postgres", "host=localhost port=5432 sslmode=disable dbname=opennms user=opennms password=opennms")
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	// Get One
	node := &OnmsNode{}
	db.Preload("Interfaces").First(node, 10)
	bytesArray, err := json.MarshalIndent(node, "", "  ")
	fmt.Println(string(bytesArray))

	// Get all Nodes
	nodes := []OnmsNode{}
	db.Find(&nodes)
	bytesArray, err = json.MarshalIndent(nodes, "", "  ")
	fmt.Println(string(bytesArray))

	// Get a sub-set of Nodes
	linuxNodes := []OnmsNode{}
	db.Where("nodeSysOID like ?", ".1.3.6.1.4.1.8072.%").Find(& linuxNodes)
	bytesArray, err = json.MarshalIndent(linuxNodes, "", "  ")
	fmt.Println(string(bytesArray))
}