More About SQL With Go

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")


	newName := "Lionel"
	newEmail := "lmessi@thegoat.example"

	row := db.QueryRow(`
	INSERT INTO users (name, email)
	VALUES ($1, $2) RETURNING ID;`, newName, newEmail)
	var id int
	err = row.Scan(&id)
	if err != nil {
		panic(err)
	}

	fmt.Println("User created id=", id)

}

go run main.go
Connected
Tables created
User created id= 8

docker compose exec -it db psql -U baloo -d lenslocked
psql (15.2 (Debian 15.2-1.pgdg110+1))
Type "help" for help.

lenslocked=# select * from users;
 id |      name       |         email
----+-----------------+------------------------
  1 | Alex Rabocse    | alex@fakemail.com
  3 | Dario Salazar   | dario@fakemail.com
  5 | Dar Sal         | dar@fakemail.com
  6 | Bastian Escobar | bastian@fakemail.com
  8 | Lionel          | lmessi@thegoat.example
(5 rows