GoLang. Database. Easy Working with Standard Database


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
}