ใช้ Go ต่อ SQL Database

go get github.com/lib/pq

package main

import (
  "database/sql"
  "fmt"
  "log"

  _ "github.com/lib/pq"
)

const (
  host     = "localhost"  // or the Docker service name if running in another container
  port     = 5432         // default PostgreSQL port
  user     = "myuser"     // as defined in docker-compose.yml
  password = "mypassword" // as defined in docker-compose.yml
  dbname   = "mydatabase" // as defined in docker-compose.yml
)

func main() {
  // Connection string
  psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
    "password=%s dbname=%s sslmode=disable",
    host, port, user, password, dbname)

  // Open a connection
  db, err := sql.Open("postgres", psqlInfo)
  if err != nil {
    log.Fatal(err)
  }
  defer db.Close()

  // Check the connection
  err = db.Ping()
  if err != nil {
    log.Fatal(err)
  }

  fmt.Println("Successfully connected!")
}

func createProduct(db *sql.DB, name string, price int, category string, quantity int) (int, error) {
    var id int
    err := db.QueryRow(`INSERT INTO products(name, price, category, quantity) VALUES($1, $2, $3, $4) RETURNING id;`, name, price, category, quantity).Scan(&id)
    if err != nil {
        return 0, err
    }
    return id, nil
}

type Product struct {
  ID       int
  Name     string
  Price    int
  Category string
  Quantity int
}

func getProduct(db *sql.DB, id int) (Product, error) {
  var p Product
  row := db.QueryRow(`SELECT id, name, price, category, quantity FROM products WHERE id = $1;`, id)
  err := row.Scan(&p.ID, &p.Name, &p.Price, &p.Category, &p.Quantity)
  if err != nil {
    return Product{}, err
  }
  return p, nil
}

func updateProduct(db *sql.DB, id int, name string, price int, category string, quantity int) error {
  _, err := db.Exec(`UPDATE products SET name = $1, price = $2, category = $3, quantity = $4 WHERE id = $5;`, name, price, category, quantity, id)
  return err
}

func deleteProduct(db *sql.DB, id int) error {
  _, err := db.Exec(`DELETE FROM products WHERE id = $1;`, id)
  return err
}

type Product struct {
  ID       int
  Name     string
  Price    int
  Category string
  Quantity int
}

func getProducts(db *sql.DB) ([]Product, error) {
  rows, err := db.Query("SELECT id, name, price, category, quantity FROM products")
  if err != nil {
    return nil, err
  }
  defer rows.Close()

  var products []Product
  for rows.Next() {
    var p Product
    err := rows.Scan(&p.ID, &p.Name, &p.Price, &p.Category, &p.Quantity)
    if err != nil {
      return nil, err
    }
    products = append(products, p)
  }

  // Check for errors from iterating over rows
  if err = rows.Err(); err != nil {
    return nil, err
  }

  return products, nil
}

func main() {
  fmt.Println(test())
  fmt.Println("Successfully connected!")
}

func test() string {
  defer fmt.Println("Before!")
  fmt.Println("After!")
  return "test"
}

After!
Before!
test
Successfully connected!

type ProductWithSupplier struct {
  ProductID        int
  ProductName      string
  Price            int
  SupplierName     string
  SupplierLocation string
}

func getProductsAndSuppliers(db *sql.DB) ([]ProductWithSupplier, error) {
  // SQL JOIN query
  query := `
      SELECT
          p.id AS product_id,
          p.name AS product_name,
          p.price,
          s.name AS supplier_name,
          s.location AS supplier_location
      FROM
          products p
      INNER JOIN suppliers s
          ON p.supplier_id = s.id;`

  rows, err := db.Query(query)
  if err != nil {
    return nil, err
  }
  defer rows.Close()

  var products []ProductWithSupplier
  for rows.Next() {
    var p ProductWithSupplier
    err := rows.Scan(&p.ProductID, &p.ProductName, &p.Price, &p.SupplierName, &p.SupplierLocation)
    if err != nil {
      return nil, err
    }
    products = append(products, p)
  }

  if err = rows.Err(); err != nil {
    return nil, err
  }

  return products, nil
}

type Product struct {
  // define your product fields
  Name       string
  Price      int
  Category   string
  Quantity   int
  SupplierID int
}

type Supplier struct {
  // define your supplier fields
  Name     string
  Location string
}

func addProductAndSupplier(db *sql.DB, product Product, supplier Supplier) error {
  // Start a transaction
  tx, err := db.Begin()
  if err != nil {
    return err
  }

  // Rollback the transaction in case of a panic
  defer tx.Rollback()

  // Insert into the supplier table
  supplierResult, err := tx.Exec("INSERT INTO suppliers (name, location) VALUES ($1, $2) RETURNING id", supplier.Name, supplier.Location)
  if err != nil {
    return err
  }

  // Get the last inserted ID for the supplier
  supplierID, err := supplierResult.LastInsertId()
  if err != nil {
    return err
  }

  // Insert into the product table
  _, err = tx.Exec("INSERT INTO products (name, price, category, quantity, supplier_id) VALUES ($1, $2, $3, $4, $5)", product.Name, product.Price, product.Category, product.Quantity, supplierID)
  if err != nil {
    return err
  }

  // Commit the transaction
  return tx.Commit()
}

func main() {
  product := Product{
    Name:     "Example Product",
    Price:    100,
    Category: "Electronics",
    Quantity: 50,
  }

  supplier := Supplier{
    Name:     "Example Supplier",
    Location: "Example Location",
  }

  err = addProductAndSupplier(db, product, supplier)
  if err != nil {
    log.Fatalf("Error adding product and supplier: %v", err)
  } else {
    fmt.Println("Product and supplier added successfully")
  }
}