130 lines
2.5 KiB
Go
130 lines
2.5 KiB
Go
package main
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"log"
|
|
"net/smtp"
|
|
"os"
|
|
|
|
_ "github.com/jackc/pgx/v5/stdlib"
|
|
)
|
|
|
|
func main() {
|
|
// connect to a database
|
|
conn, err := sql.Open("pgx", fmt.Sprintf("host=localhost port=5432 dbname=test_connect user=%s password=%s", os.Getenv("PGUSER"), os.Getenv("PGPWD")))
|
|
if err != nil {
|
|
log.Fatalf("Unable to connect to the database: %v\n", err)
|
|
}
|
|
defer conn.Close()
|
|
|
|
// Send test email
|
|
from := "me@here.com"
|
|
auth := smtp.PlainAuth("", from, "", "localhost")
|
|
_ = smtp.SendMail("localhost:1025", auth, from, []string{"you@there.com"}, []byte("Hello world"))
|
|
|
|
log.Println("Connected to database!")
|
|
|
|
// test my connection
|
|
err = conn.Ping()
|
|
if err != nil {
|
|
log.Fatal("Cannot ping database!")
|
|
}
|
|
|
|
log.Println("Pinged database!")
|
|
|
|
// get rows from table
|
|
err = getAllRows(conn)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
// insert a row
|
|
query := `insert into users (first_name, last_name) values ($1, $2)`
|
|
_, err = conn.Exec(query, "Jack", "Brown")
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
log.Println("Inserted a row!")
|
|
|
|
// get rows from table again
|
|
err = getAllRows(conn)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
// update a row
|
|
stmt := `update users set first_name = $1 where first_name = $2`
|
|
_, err = conn.Exec(stmt, "Jackie", "Jack")
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
log.Println("Updated one or more rows")
|
|
|
|
// get rows from table again
|
|
err = getAllRows(conn)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
// get one row by id
|
|
var firstName, lastName string
|
|
var id int
|
|
|
|
query = `select id, first_name, last_name from users where id = $1`
|
|
row := conn.QueryRow(query, 1)
|
|
|
|
err = row.Scan(&id, &firstName, &lastName)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
log.Println("Query row returns", id, firstName, lastName)
|
|
|
|
// delete a row
|
|
query = `delete from users where id = $1`
|
|
_, err = conn.Exec(query, 3)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
log.Println("Delete a row")
|
|
|
|
// get rows again
|
|
err = getAllRows(conn)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
}
|
|
|
|
func getAllRows(conn *sql.DB) error {
|
|
rows, err := conn.Query("select id, first_name, last_name from users")
|
|
if err != nil {
|
|
log.Println(err)
|
|
return err
|
|
}
|
|
defer rows.Close() // IMPORTANT!!
|
|
|
|
var firstName, lastName string
|
|
var id int
|
|
|
|
for rows.Next() {
|
|
err := rows.Scan(&id, &firstName, &lastName)
|
|
if err != nil {
|
|
log.Println(err)
|
|
return err
|
|
}
|
|
fmt.Println("Record is", id, firstName, lastName)
|
|
}
|
|
|
|
if err = rows.Err(); err != nil {
|
|
log.Fatalf("Error scanning rows: %v", err)
|
|
}
|
|
|
|
fmt.Println("----------------------------")
|
|
|
|
return nil
|
|
}
|