The first difference in this piece of code is that the “PostgresConfig type” is replacing the long string in the sql.Open() section, as it was in the previous version of the code.
The second and most important difference is that we are using db.Exec().
As far as I understand, it seems that db.Exec is used when the SQL query does not return rows, for example when doing “delete”, “update”, “insert”. On the other hand, when requiring a “select”, then db.Query or db.QueryRows can be used instead.
package main
import (
"database/sql"
"fmt"
_ "github.com/jackc/pgx/v4/stdlib" // stdlib is the package that provides the standard library driver. To be able to use database sql package
)
type PostgresConfig struct {
Host string
Port string
User string
Password string
Database string
SSLMode string
}
func (cfg PostgresConfig) String() string {
return fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=%s", cfg.Host, cfg.Port, cfg.User, cfg.Password, cfg.Database, cfg.SSLMode)
}
func main() {
cfg := PostgresConfig{
Host: "localhost",
Port: "5432",
User: "baloo",
Password: "junglebook",
Database: "lenslocked",
SSLMode: "disable",
}
// pgx = Driver name defined by github.com/jackc/pgx/v4/ It we were using a different library, the driver would be different.
// host, user, password, dbname were the values used in the YAML file for the docker compose.
db, err := sql.Open("pgx", cfg.String())
if err != nil {
panic(err)
}
defer db.Close()
err = db.Ping()
if err != nil {
panic(err)
}
fmt.Println("Connected")
// Create a table
_, err = db.Exec(
`
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name TEXT,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS orders (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
amount INT,
description TEXT
);
`)
if err != nil {
panic(err)
}
fmt.Println("Tables created")
}