If you want to work with a database, you can use either a ORM like GORM or raw SQL Statements. Both have pros and cons.
ORM will make you worry less about structs since it automatically converts the results of the query to objects.
Raw SQL on the other hands has the advantage if you want to build more complex queries, since you don’t have to translate them into ORM functions.
What better suits you, is quite a personal decision.
I will concentrate on raw SQL for now.
Connect to PostgreSQL
You will need two packages for that.database/sql from the standard library and pg. We will only need to install the pg package.
go get github.com/go-pg/pg/v10
go get -u github.com/lib/pq
Let’s create a database-package to manage our connection.
package database
import (
"database/sql"
_ "github.com/lib/pq"
)
func Connect() *sql.DB {
connStr := "user=<db-user> password=<password> host=<db-host> dbname=<database-name> sslmode=disable"
db, err := sql.Open("postgres", connStr)
if err != nil {
log.Panic(err)
}
return db
}
Notice the Underscore in front of the import of pg? This is necessary since we don’t interact with the package directly and go will see it as unused. With the Underscore we prevent this check.
If you want to use ssl to encrypt your connection set sslmode to either verify-ca, verify-full or simply require. For more detailed information, habe a look at the official documentation.
Execute Queries
Select Data
Next we will query some data.
func GetUsers() {
// Create database connection
db := database.Connect()
// Close it once it is no longe in use
defer db.Close()
// define raw SQL Query
// Parameter are done like $1, $2, $3...
query := `SELECT
"id",
"username",
"age"
FROM "users"
WHERE
"username" = $1
AND
age > $2
;`
// Execute Query
// Give parameters as args
rows, err := db.Query(query, 'Steve', 18)
// Check for errors
if err != nil {
log.Fatal(err)
}
// Free rows-object once not longer in user
defer rows.Close()
// Create empty arry of type User
var users = make([]User, 0)
// Iterate over all rows
for rows.Next() {
// Create empty user object
var u User
// Read data from SQL-Query into User-object
rows.Scan(
&u.Id,
&u.Username,
&u.Age
)
// Add user to array
users = append(users, u)
}
}
Insert or Update Data
If you want to insert or Update data you might want to get the number of affected rows back. This can be done like so.
func InsertUser() int {
// Connect to database
db := database.Connect()
// Close Connection later
defer db.Close()
// Define raw SQL query
query := `INSERT INTO "users" ("username", "age")
VALUES ($1, $2);`
// Execute Query with paramters
res, err := db.Exec(query, 'Jeany', 21)
// Error handling
if err != nil {
log.Fatal(error)
}
// Get the number of affected rows
rowCount, err := res.RowsAffected()
if err != nil {
log.Fatal(err)
}
// return number
return rowCount
}
