How to use database sql

Use the database/sql package as a generic interface to connect to any SQL database, then register a specific driver (like pgx for PostgreSQL or mysql for MySQL) to handle the actual connection details. You open a connection pool with sql.Open, execute queries using Query or QueryRow for reading and Exec for writing, and always ensure resources are closed or deferred to prevent leaks.

Here is a practical example connecting to a PostgreSQL database and fetching a user record:

package main

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

	_ "github.com/lib/pq" // Register the PostgreSQL driver
)

type User struct {
	ID   int
	Name string
}

func main() {
	// Open a connection pool; this does not immediately connect
	dsn := "host=localhost user=postgres password=secret dbname=mydb sslmode=disable"
	db, err := sql.Open("postgres", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Verify the connection works immediately
	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}

	// Execute a query with parameters to prevent SQL injection
	var user User
	err = db.QueryRow("SELECT id, name FROM users WHERE id = $1", 1).Scan(&user.ID, &user.Name)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("Found user: %d - %s\n", user.ID, user.Name)
}

For write operations, use Exec which returns a Result containing the last insert ID and number of rows affected. Always use parameterized queries (placeholders like ? for MySQL or $1 for PostgreSQL) instead of string concatenation to avoid SQL injection vulnerabilities.

// Example of an insert operation
result, err := db.Exec("INSERT INTO users (name, email) VALUES ($1, $2)", "Alice", "alice@example.com")
if err != nil {
	log.Fatal(err)
}

id, err := result.LastInsertId()
if err != nil {
	log.Fatal(err)
}
fmt.Printf("Inserted user with ID: %d\n", id)

Remember that sql.Open returns a connection pool, not a single connection. The pool manages multiple underlying connections automatically, so you should reuse the *sql.DB instance throughout your application lifecycle rather than opening and closing it for every query. If you need to run multiple queries in a single transaction, use db.Begin() to start a transaction, execute your statements on the transaction object, and then Commit() or Rollback() based on the outcome.

SQL is a language used to talk to databases, but Go doesn't speak it natively. You need to install a translator (a driver) that connects Go code to your specific database software. Think of it like needing a specific adapter to plug a laptop into a different country's power outlet.