Golang. Database. Easy Working with Standard Database/sql

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

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

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

// GetDb returns a pointer to the database
func GetDb() *sql.DB {
	return databasePointer
}

Execute an SQL without Result

// 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

// 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

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
}