[{"Id":"202012090815100632","ParentId":"20201209081451316946","Type":"Text","Sequence":1,"Attributes":{"Text":"Working with the standard database functions in GoLang can be quite repetative."}},{"Id":"202012090815570657","ParentId":"20201209081451316946","Type":"Text","Sequence":2,"Attributes":{"Text":"Here are some helper function to make this easier"}},{"Id":"202012090816330209","ParentId":"20201209081451316946","Type":"Heading","Sequence":3,"Attributes":{"Level":"2","Text":"DSN String from Environment"}},{"Id":"202012090816560485","ParentId":"20201209081451316946","Type":"Code","Sequence":4,"Attributes":{"Language":"XML","Code":"func dsn() string {\r\n\t//dbDrvr := os.Getenv("DB_DRIVER")\r\n\tdbHost := os.Getenv("DB_HOST")\r\n\tdbPort := os.Getenv("DB_PORT")\r\n\tdbName := os.Getenv("DB_DATABASE")\r\n\tdbUser := os.Getenv("DB_USERNAME")\r\n\tdbPass := os.Getenv("DB_PASSWORD")\r\n\tdbHost += ":" + dbPort\r\n\treturn fmt.Sprintf("%s:%s@tcp(%s)/%s", dbUser, dbPass, dbHost, dbName)\r\n}"}},{"Id":"202012090818280787","ParentId":"20201209081451316946","Type":"Heading","Sequence":5,"Attributes":{"Level":"2","Text":"Open Database Connection"}},{"Id":"202012090818530813","ParentId":"20201209081451316946","Type":"Code","Sequence":6,"Attributes":{"Language":"XML","Code":"var databasePointer *sql.DB\r\n\r\nfunc openDatabase() {\r\n\tdbDrvr := os.Getenv("DB_DRIVER")\r\n\tdsn := dsn()\r\n\r\n\t// Opening a driver typically will not attempt to connect to the database.\r\n\tdb, err := sql.Open(dbDrvr, dsn)\r\n\tif err != nil {\r\n\t\tlog.Fatal(err)\r\n\t\tpanic(err)\r\n\t}\r\n\r\n\tdb.SetConnMaxLifetime(time.Minute * 3)\r\n\tdb.SetMaxOpenConns(10)\r\n\tdb.SetMaxIdleConns(10)\r\n\tlog.Println("Pinging ...")\r\n\terr = db.Ping()\r\n\tif err != nil {\r\n\t\tpanic(err.Error()) // proper error handling instead of panic in your app\r\n\t}\r\n\r\n\tdatabasePointer = db\r\n}"}},{"Id":"202012090824590126","ParentId":"20201209081451316946","Type":"Heading","Sequence":7,"Attributes":{"Level":"2","Text":"Export Database Pointer To Be Re-Used"}},{"Id":"202012090825430990","ParentId":"20201209081451316946","Type":"Code","Sequence":8,"Attributes":{"Language":"XML","Code":"// GetDb returns a pointer to the database\r\nfunc GetDb() *sql.DB {\r\n\treturn databasePointer\r\n}"}},{"Id":"202012090821410403","ParentId":"20201209081451316946","Type":"Heading","Sequence":9,"Attributes":{"Level":"2","Text":"Execute an SQL without Result"}},{"Id":"202012090822310899","ParentId":"20201209081451316946","Type":"Code","Sequence":10,"Attributes":{"Language":"XML","Code":"// Exec executes a query\r\nfunc Exec(sql string, args ...interface{}) bool {\r\n\tresult, err := GetDb().Exec(sql, args...)\r\n\r\n\tif err != nil {\r\n\t\tlog.Println("QUERY ERROR: " + sql)\r\n\t\tlog.Fatal(err)\r\n\t}\r\n\r\n\trowsAffected, _ := result.RowsAffected()\r\n\r\n\tlog.Println("Rows affected: " + string(rowsAffected))\r\n\r\n\treturn true\r\n}"}},{"Id":"202012090823480095","ParentId":"20201209081451316946","Type":"Heading","Sequence":11,"Attributes":{"Level":"2","Text":"Query Tables with Result"}},{"Id":"202012090824150267","ParentId":"20201209081451316946","Type":"Code","Sequence":12,"Attributes":{"Language":"XML","Code":"// QuerySingle queries the Db and returns a map\r\nfunc QuerySingle(sql string, args ...interface{}) map[string]string {\r\n\trows, err := GetDb().Query(sql, args...)\r\n\r\n\tif err != nil {\r\n\t\tlog.Println("QUERY ERROR: " + sql)\r\n\t\tlog.Fatal(err)\r\n\t}\r\n\r\n\tmappedRows := rowsToMap(rows)\r\n\r\n\tif len(mappedRows) > 0 {\r\n\t\treturn mappedRows[0]\r\n\t}\r\n\r\n\treturn nil\r\n}\r\n\r\n// Query queries the Db and returns a map\r\nfunc Query(sql string, args ...interface{}) []map[string]string {\r\n\trows, err := GetDb().Query(sql, args...)\r\n\r\n\tif err != nil {\r\n\t\tlog.Println("QUERY ERROR: " + sql)\r\n\t\tlog.Fatal(err)\r\n\t}\r\n\r\n\treturn rowsToMap(rows)\r\n}"}},{"Id":"202012090826480655","ParentId":"20201209081451316946","Type":"Heading","Sequence":13,"Attributes":{"Level":"2","Text":"Convert Query Results to Map"}},{"Id":"202012090826530070","ParentId":"20201209081451316946","Type":"Code","Sequence":15,"Attributes":{"Language":"XML","Code":"func rowsToMap(rows *sql.Rows) []map[string]string {\r\n\tcols, _ := rows.Columns()\r\n\tmappedRows := []map[string]string{}\r\n\t// Create a slice of interface{}'s to represent each column,\r\n\t// and a second slice to contain pointers to each item in the columns slice.\r\n\tcolumns := make([]interface{}, len(cols))\r\n\tcolumnPointers := make([]interface{}, len(cols))\r\n\tfor rows.Next() {\r\n\t\tfor i := range columns {\r\n\t\t\tcolumnPointers[i] = &columns[i]\r\n\t\t}\r\n\r\n\t\t// Scan the result into the column pointers...\r\n\t\tif err := rows.Scan(columnPointers...); err != nil {\r\n\t\t\tcontinue\r\n\t\t}\r\n\r\n\t\t// Create our map, and retrieve the value for each column from the pointers slice,\r\n\t\t// storing it in the map with the name of the column as the key.\r\n\t\tmappedRow := make(map[string]string)\r\n\t\tfor i, colName := range cols {\r\n\t\t\tval := columnPointers[i].(*interface{})\r\n\t\t\tif *val == nil {\r\n\t\t\t\tmappedRow[colName] = ""\r\n\t\t\t} else {\r\n\t\t\t\tstr := fmt.Sprintf("%s", *val)\r\n\t\t\t\tmappedRow[colName] = str\r\n\t\t\t}\r\n\t\t}\r\n\r\n\t\t// Outputs: map[columnName:value columnName2:value2 columnName3:value3 ...]\r\n\t\tmappedRows = append(mappedRows, mappedRow)\r\n\r\n\t}\r\n\treturn mappedRows\r\n}\r\n"}}]