Working with the standard database functions in GoLang can be quite repetative.
Here are some helper function to make this easier
DSN String from Environment
Language: XML
func dsn() string {
//dbDrvr := os.Getenv("DB_DRIVER")
dbHost := os.Getenv("DB_HOST")
dbPort := os.Getenv("DB_PORT")
dbName := os.Getenv("DB_DATABASE")
dbUser := os.Getenv("DB_USERNAME")
dbPass := os.Getenv("DB_PASSWORD")
dbHost += ":" + dbPort
return fmt.Sprintf("%s:%s@tcp(%s)/%s", dbUser, dbPass, dbHost, dbName)
}Open Database Connection
Language: XML
var databasePointer *sql.DB
func openDatabase() {
dbDrvr := os.Getenv("DB_DRIVER")
dsn := dsn()
// Opening a driver typically will not attempt to connect to the database.
db, err := sql.Open(dbDrvr, dsn)
if err != nil {
log.Fatal(err)
panic(err)
}
db.SetConnMaxLifetime(time.Minute * 3)
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(10)
log.Println("Pinging ...")
err = db.Ping()
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
databasePointer = db
}Export Database Pointer To Be Re-Used
Language: XML
// GetDb returns a pointer to the database
func GetDb() *sql.DB {
return databasePointer
}Execute an SQL without Result
Language: XML
// Exec executes a query
func Exec(sql string, args ...interface{}) bool {
result, err := GetDb().Exec(sql, args...)
if err != nil {
log.Println("QUERY ERROR: " + sql)
log.Fatal(err)
}
rowsAffected, _ := result.RowsAffected()
log.Println("Rows affected: " + string(rowsAffected))
return true
}Query Tables with Result
Language: XML
// QuerySingle queries the Db and returns a map
func QuerySingle(sql string, args ...interface{}) map[string]string {
rows, err := GetDb().Query(sql, args...)
if err != nil {
log.Println("QUERY ERROR: " + sql)
log.Fatal(err)
}
mappedRows := rowsToMap(rows)
if len(mappedRows) > 0 {
return mappedRows[0]
}
return nil
}
// Query queries the Db and returns a map
func Query(sql string, args ...interface{}) []map[string]string {
rows, err := GetDb().Query(sql, args...)
if err != nil {
log.Println("QUERY ERROR: " + sql)
log.Fatal(err)
}
return rowsToMap(rows)
}Convert Query Results to Map
Language: XML
func rowsToMap(rows *sql.Rows) []map[string]string {
cols, _ := rows.Columns()
mappedRows := []map[string]string{}
// Create a slice of interface{}'s to represent each column,
// and a second slice to contain pointers to each item in the columns slice.
columns := make([]interface{}, len(cols))
columnPointers := make([]interface{}, len(cols))
for rows.Next() {
for i := range columns {
columnPointers[i] = &columns[i]
}
// Scan the result into the column pointers...
if err := rows.Scan(columnPointers...); err != nil {
continue
}
// Create our map, and retrieve the value for each column from the pointers slice,
// storing it in the map with the name of the column as the key.
mappedRow := make(map[string]string)
for i, colName := range cols {
val := columnPointers[i].(*interface{})
if *val == nil {
mappedRow[colName] = ""
} else {
str := fmt.Sprintf("%s", *val)
mappedRow[colName] = str
}
}
// Outputs: map[columnName:value columnName2:value2 columnName3:value3 ...]
mappedRows = append(mappedRows, mappedRow)
}
return mappedRows
}