> ## Documentation Index > Fetch the complete documentation index at: https://resources.devweekends.com/llms.txt > Use this file to discover all available pages before exploring further. # Database Operations > Working with SQL databases, ORMs, and NoSQL solutions in Go # Database Operations in Go Go provides excellent support for database operations through the `database/sql` package. This chapter covers SQL databases, ORMs, and NoSQL solutions. ## The database/sql Package The `database/sql` package provides a generic interface for SQL databases. Think of `database/sql` as a universal remote control: it provides the same buttons (Query, Exec, Begin) regardless of which TV (database) you are talking to. The driver is the infrared translator that converts your button presses into the specific protocol your database understands. The package manages a connection pool, handles reconnection, and provides a consistent API regardless of which database you use. You need a driver for your specific database -- the driver registers itself via an `init` function (this is one of the good uses of `init` and blank imports). **Critical Pitfall**: `sql.Open` does not actually establish a connection to the database. It only validates the driver name and prepares the connection pool. The first actual connection happens when you execute a query. Always call `db.Ping()` or `db.PingContext()` after `sql.Open` to verify connectivity: ```go theme={null} db, err := sql.Open("postgres", connStr) if err != nil { return err // Driver name is wrong, or DSN is malformed } if err := db.Ping(); err != nil { return err // Actually cannot connect to the database } ``` ### Setting Up a Connection ```go theme={null} import ( "database/sql" _ "github.com/lib/pq" // PostgreSQL // _ "github.com/go-sql-driver/mysql" // MySQL // _ "github.com/mattn/go-sqlite3" // SQLite ) func main() { // Connection string format varies by driver connStr := "host=localhost port=5432 user=postgres password=secret dbname=myapp sslmode=disable" db, err := sql.Open("postgres", connStr) if err != nil { log.Fatal(err) } defer db.Close() // Verify connection if err := db.Ping(); err != nil { log.Fatal(err) } // Configure connection pool -- these settings are critical for production! // Without them, defaults can either exhaust database connections or be too conservative. db.SetMaxOpenConns(25) // Max open connections (prevents overwhelming the DB) db.SetMaxIdleConns(5) // Max idle connections (keeps warm connections in the pool) db.SetConnMaxLifetime(5 * time.Minute) // Max connection lifetime (prevents stale connections) db.SetConnMaxIdleTime(1 * time.Minute) // Max idle time (frees unused connections) } ``` **Connection Pool Sizing**: A common rule of thumb is to set `MaxOpenConns` to 2-3x the number of CPU cores for your database server, but never exceed the database's own max connections limit. For cloud databases (RDS, Cloud SQL), check your instance's connection limit and leave headroom for monitoring and admin connections. Setting `MaxOpenConns` too high can actually *decrease* performance due to connection thrashing. ```` ### Environment-Based Configuration ```go type DBConfig struct { Host string Port int User string Password string DBName string SSLMode string } func NewDBConfig() *DBConfig { return &DBConfig{ Host: getEnv("DB_HOST", "localhost"), Port: getEnvInt("DB_PORT", 5432), User: getEnv("DB_USER", "postgres"), Password: getEnv("DB_PASSWORD", ""), DBName: getEnv("DB_NAME", "myapp"), SSLMode: getEnv("DB_SSLMODE", "disable"), } } func (c *DBConfig) ConnectionString() string { return fmt.Sprintf( "host=%s port=%d user=%s password=%s dbname=%s sslmode=%s", c.Host, c.Port, c.User, c.Password, c.DBName, c.SSLMode, ) } ```` *** ## Basic CRUD Operations ### Inserting Data ```go theme={null} type User struct { ID int64 Name string Email string CreatedAt time.Time } func (r *UserRepository) Create(ctx context.Context, user *User) error { query := ` INSERT INTO users (name, email, created_at) VALUES ($1, $2, $3) RETURNING id` err := r.db.QueryRowContext(ctx, query, user.Name, user.Email, time.Now()). Scan(&user.ID) if err != nil { return fmt.Errorf("failed to create user: %w", err) } return nil } // Without RETURNING (MySQL style) func (r *UserRepository) CreateMySQL(ctx context.Context, user *User) error { query := `INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)` result, err := r.db.ExecContext(ctx, query, user.Name, user.Email, time.Now()) if err != nil { return err } id, err := result.LastInsertId() if err != nil { return err } user.ID = id return nil } ``` ### Querying Single Row ```go theme={null} func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) { query := `SELECT id, name, email, created_at FROM users WHERE id = $1` var user User err := r.db.QueryRowContext(ctx, query, id). Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt) if err == sql.ErrNoRows { return nil, ErrNotFound } if err != nil { return nil, fmt.Errorf("failed to get user: %w", err) } return &user, nil } ``` ### Querying Multiple Rows ```go theme={null} func (r *UserRepository) List(ctx context.Context, limit, offset int) ([]*User, error) { query := ` SELECT id, name, email, created_at FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2` rows, err := r.db.QueryContext(ctx, query, limit, offset) if err != nil { return nil, err } defer rows.Close() // Always close rows -- leaked rows exhaust connection pool var users []*User for rows.Next() { var u User if err := rows.Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt); err != nil { return nil, err } users = append(users, &u) } // CRITICAL: Check for errors that occurred during iteration. // rows.Next() returns false on both exhaustion AND error. // Without rows.Err(), you silently swallow iteration errors. if err := rows.Err(); err != nil { return nil, err } return users, nil } ``` ### Updating Data ```go theme={null} func (r *UserRepository) Update(ctx context.Context, user *User) error { query := ` UPDATE users SET name = $1, email = $2, updated_at = $3 WHERE id = $4` result, err := r.db.ExecContext(ctx, query, user.Name, user.Email, time.Now(), user.ID) if err != nil { return err } rowsAffected, err := result.RowsAffected() if err != nil { return err } if rowsAffected == 0 { return ErrNotFound } return nil } ``` ### Deleting Data ```go theme={null} func (r *UserRepository) Delete(ctx context.Context, id int64) error { query := `DELETE FROM users WHERE id = $1` result, err := r.db.ExecContext(ctx, query, id) if err != nil { return err } rowsAffected, err := result.RowsAffected() if err != nil { return err } if rowsAffected == 0 { return ErrNotFound } return nil } ``` *** ## Transactions Transactions are like an "all-or-nothing" switch: either every operation within the transaction succeeds and is committed together, or any failure rolls everything back as if nothing happened. In Go, the pattern is to `defer tx.Rollback()` immediately after beginning the transaction. If `Commit()` is called successfully, the deferred `Rollback()` becomes a no-op. If any error occurs, the deferred `Rollback()` cleans up. ### Basic Transaction ```go theme={null} func (r *OrderRepository) CreateOrder(ctx context.Context, order *Order) error { tx, err := r.db.BeginTx(ctx, nil) if err != nil { return err } // Idiomatic Go: defer Rollback immediately after BeginTx. // If Commit() succeeds, Rollback() is a safe no-op. // If any error path returns early, Rollback() cleans up. defer tx.Rollback() // Insert order query := `INSERT INTO orders (user_id, total, status) VALUES ($1, $2, $3) RETURNING id` err = tx.QueryRowContext(ctx, query, order.UserID, order.Total, "pending"). Scan(&order.ID) if err != nil { return err } // Insert order items itemQuery := `INSERT INTO order_items (order_id, product_id, quantity, price) VALUES ($1, $2, $3, $4)` for _, item := range order.Items { _, err = tx.ExecContext(ctx, itemQuery, order.ID, item.ProductID, item.Quantity, item.Price) if err != nil { return err } } // Update inventory inventoryQuery := `UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1` for _, item := range order.Items { result, err := tx.ExecContext(ctx, inventoryQuery, item.Quantity, item.ProductID) if err != nil { return err } affected, _ := result.RowsAffected() if affected == 0 { return errors.New("insufficient stock") } } // Commit return tx.Commit() } ``` ### Transaction Helper ```go theme={null} func WithTransaction(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error { tx, err := db.BeginTx(ctx, nil) if err != nil { return err } defer func() { if p := recover(); p != nil { tx.Rollback() panic(p) } }() if err := fn(tx); err != nil { tx.Rollback() return err } return tx.Commit() } // Usage err := WithTransaction(ctx, db, func(tx *sql.Tx) error { if _, err := tx.ExecContext(ctx, query1, args...); err != nil { return err } if _, err := tx.ExecContext(ctx, query2, args...); err != nil { return err } return nil }) ``` **Pitfall -- Using db.Query Inside a Transaction**: If you call `r.db.QueryContext()` instead of `tx.QueryContext()` inside a transaction block, the query runs on a different connection outside the transaction. This is a subtle bug that can cause phantom reads, missed updates, and data inconsistency. Always use `tx.QueryContext`, `tx.ExecContext`, and `tx.QueryRowContext` within a transaction -- never the `db.*` methods. **Pitfall -- Long-Running Transactions**: Holding a transaction open for too long (e.g., waiting for an external API call within a transaction) blocks the database row or table and can cause timeouts for other queries. Keep transactions short: do all non-database work before beginning the transaction, then execute only the SQL statements inside it. *** ## Prepared Statements ### Using Prepared Statements ```go theme={null} type UserRepository struct { db *sql.DB stmtGet *sql.Stmt stmtList *sql.Stmt stmtCreate *sql.Stmt } func NewUserRepository(db *sql.DB) (*UserRepository, error) { repo := &UserRepository{db: db} var err error repo.stmtGet, err = db.Prepare(`SELECT id, name, email FROM users WHERE id = $1`) if err != nil { return nil, err } repo.stmtList, err = db.Prepare(`SELECT id, name, email FROM users LIMIT $1 OFFSET $2`) if err != nil { return nil, err } repo.stmtCreate, err = db.Prepare(`INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id`) if err != nil { return nil, err } return repo, nil } func (r *UserRepository) Close() error { var errs []error if err := r.stmtGet.Close(); err != nil { errs = append(errs, err) } if err := r.stmtList.Close(); err != nil { errs = append(errs, err) } if err := r.stmtCreate.Close(); err != nil { errs = append(errs, err) } if len(errs) > 0 { return fmt.Errorf("failed to close statements: %v", errs) } return nil } func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) { var user User err := r.stmtGet.QueryRowContext(ctx, id).Scan(&user.ID, &user.Name, &user.Email) if err == sql.ErrNoRows { return nil, ErrNotFound } return &user, err } ``` *** ## Handling NULL Values ### Using sql.Null Types ```go theme={null} type User struct { ID int64 Name string Email sql.NullString // Nullable Phone sql.NullString // Nullable DeletedAt sql.NullTime // Nullable } func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) { query := `SELECT id, name, email, phone, deleted_at FROM users WHERE id = $1` var user User err := r.db.QueryRowContext(ctx, query, id). Scan(&user.ID, &user.Name, &user.Email, &user.Phone, &user.DeletedAt) return &user, err } // Convert to application struct type UserDTO struct { ID int64 `json:"id"` Name string `json:"name"` Email *string `json:"email,omitempty"` Phone *string `json:"phone,omitempty"` } func (u *User) ToDTO() *UserDTO { dto := &UserDTO{ ID: u.ID, Name: u.Name, } if u.Email.Valid { dto.Email = &u.Email.String } if u.Phone.Valid { dto.Phone = &u.Phone.String } return dto } ``` ### Using Pointers ```go theme={null} type User struct { ID int64 Name string Email *string // nil if NULL Phone *string // nil if NULL } func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) { query := `SELECT id, name, email, phone FROM users WHERE id = $1` var user User err := r.db.QueryRowContext(ctx, query, id). Scan(&user.ID, &user.Name, &user.Email, &user.Phone) return &user, err } ``` *** ## Query Builder Pattern ```go theme={null} type QueryBuilder struct { table string columns []string conditions []string args []interface{} orderBy string limit int offset int } func NewQueryBuilder(table string) *QueryBuilder { return &QueryBuilder{table: table} } func (qb *QueryBuilder) Select(columns ...string) *QueryBuilder { qb.columns = columns return qb } func (qb *QueryBuilder) Where(condition string, args ...interface{}) *QueryBuilder { qb.conditions = append(qb.conditions, condition) qb.args = append(qb.args, args...) return qb } func (qb *QueryBuilder) OrderBy(column string, desc bool) *QueryBuilder { direction := "ASC" if desc { direction = "DESC" } qb.orderBy = fmt.Sprintf("%s %s", column, direction) return qb } func (qb *QueryBuilder) Limit(limit int) *QueryBuilder { qb.limit = limit return qb } func (qb *QueryBuilder) Offset(offset int) *QueryBuilder { qb.offset = offset return qb } func (qb *QueryBuilder) Build() (string, []interface{}) { query := fmt.Sprintf("SELECT %s FROM %s", strings.Join(qb.columns, ", "), qb.table, ) if len(qb.conditions) > 0 { query += " WHERE " + strings.Join(qb.conditions, " AND ") } if qb.orderBy != "" { query += " ORDER BY " + qb.orderBy } if qb.limit > 0 { query += fmt.Sprintf(" LIMIT %d", qb.limit) } if qb.offset > 0 { query += fmt.Sprintf(" OFFSET %d", qb.offset) } return query, qb.args } // Usage qb := NewQueryBuilder("users"). Select("id", "name", "email"). Where("status = $1", "active"). Where("created_at > $2", time.Now().AddDate(0, -1, 0)). OrderBy("created_at", true). Limit(10) query, args := qb.Build() rows, err := db.QueryContext(ctx, query, args...) ``` *** ## GORM ORM GORM is the most popular ORM for Go. ### Setup ```go theme={null} import ( "gorm.io/gorm" "gorm.io/driver/postgres" ) type User struct { gorm.Model // ID, CreatedAt, UpdatedAt, DeletedAt Name string `gorm:"size:100;not null"` Email string `gorm:"uniqueIndex;not null"` Age int `gorm:"default:0"` Profile Profile `gorm:"constraint:OnDelete:CASCADE"` Orders []Order } type Profile struct { ID uint UserID uint Bio string Avatar string } type Order struct { ID uint UserID uint Total float64 Status string } func main() { dsn := "host=localhost user=postgres password=secret dbname=myapp port=5432 sslmode=disable" db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{ Logger: logger.Default.LogMode(logger.Info), }) if err != nil { log.Fatal(err) } // Auto migrate db.AutoMigrate(&User{}, &Profile{}, &Order{}) } ``` ### CRUD with GORM ```go theme={null} // Create user := User{Name: "John", Email: "john@example.com"} result := db.Create(&user) if result.Error != nil { return result.Error } fmt.Println("Created ID:", user.ID) // Read var user User db.First(&user, 1) // By primary key db.First(&user, "email = ?", "john@example.com") // By condition // Read multiple var users []User db.Where("age > ?", 18).Find(&users) db.Where("name LIKE ?", "%john%").Order("created_at desc").Limit(10).Find(&users) // Update db.Model(&user).Update("name", "Jane") db.Model(&user).Updates(User{Name: "Jane", Age: 25}) db.Model(&user).Updates(map[string]interface{}{"name": "Jane", "age": 25}) // Delete (soft delete with gorm.Model) db.Delete(&user, 1) // Hard delete db.Unscoped().Delete(&user, 1) ``` ### Relationships with GORM ```go theme={null} // Eager loading var users []User db.Preload("Profile").Preload("Orders").Find(&users) // Nested preloading db.Preload("Orders.Items").Find(&users) // Conditional preloading db.Preload("Orders", "status = ?", "completed").Find(&users) // Create with associations user := User{ Name: "John", Profile: Profile{Bio: "Developer"}, Orders: []Order{ {Total: 100.00, Status: "pending"}, }, } db.Create(&user) ``` ### GORM Transactions ```go theme={null} func createOrderWithGORM(db *gorm.DB, order *Order) error { return db.Transaction(func(tx *gorm.DB) error { if err := tx.Create(order).Error; err != nil { return err } for _, item := range order.Items { result := tx.Model(&Product{}). Where("id = ? AND stock >= ?", item.ProductID, item.Quantity). Update("stock", gorm.Expr("stock - ?", item.Quantity)) if result.RowsAffected == 0 { return errors.New("insufficient stock") } } return nil }) } ``` *** ## sqlx Package sqlx extends database/sql with helpful features. ```go theme={null} import "github.com/jmoiron/sqlx" type User struct { ID int64 `db:"id"` Name string `db:"name"` Email string `db:"email"` CreatedAt time.Time `db:"created_at"` } func main() { db, err := sqlx.Connect("postgres", connectionString) if err != nil { log.Fatal(err) } } // Get single row var user User err := db.GetContext(ctx, &user, "SELECT * FROM users WHERE id = $1", 1) // Get multiple rows var users []User err := db.SelectContext(ctx, &users, "SELECT * FROM users WHERE age > $1", 18) // Named queries user := User{Name: "John", Email: "john@example.com"} result, err := db.NamedExecContext(ctx, "INSERT INTO users (name, email) VALUES (:name, :email)", user) // Named query with IN clause query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", []int{1, 2, 3}) query = db.Rebind(query) var users []User err = db.SelectContext(ctx, &users, query, args...) ``` *** ## Redis ```go theme={null} import "github.com/redis/go-redis/v9" func main() { rdb := redis.NewClient(&redis.Options{ Addr: "localhost:6379", Password: "", DB: 0, }) ctx := context.Background() // String operations err := rdb.Set(ctx, "key", "value", time.Hour).Err() val, err := rdb.Get(ctx, "key").Result() // Hash operations rdb.HSet(ctx, "user:1", map[string]interface{}{ "name": "John", "email": "john@example.com", }) name, err := rdb.HGet(ctx, "user:1", "name").Result() user, err := rdb.HGetAll(ctx, "user:1").Result() // List operations rdb.LPush(ctx, "queue", "task1", "task2") task, err := rdb.RPop(ctx, "queue").Result() // Set operations rdb.SAdd(ctx, "tags", "go", "programming", "backend") tags, err := rdb.SMembers(ctx, "tags").Result() // Sorted set rdb.ZAdd(ctx, "leaderboard", redis.Z{Score: 100, Member: "player1"}) top, err := rdb.ZRevRangeWithScores(ctx, "leaderboard", 0, 9).Result() // Pub/Sub pubsub := rdb.Subscribe(ctx, "channel") ch := pubsub.Channel() for msg := range ch { fmt.Println(msg.Channel, msg.Payload) } } ``` ### Redis Caching Pattern ```go theme={null} type CacheRepository struct { db *sql.DB cache *redis.Client } func (r *CacheRepository) GetUser(ctx context.Context, id int64) (*User, error) { key := fmt.Sprintf("user:%d", id) // Try cache first data, err := r.cache.Get(ctx, key).Bytes() if err == nil { var user User if err := json.Unmarshal(data, &user); err == nil { return &user, nil } } // Cache miss - query database user, err := r.getUserFromDB(ctx, id) if err != nil { return nil, err } // Cache result data, _ = json.Marshal(user) r.cache.Set(ctx, key, data, time.Hour) return user, nil } func (r *CacheRepository) InvalidateUser(ctx context.Context, id int64) error { key := fmt.Sprintf("user:%d", id) return r.cache.Del(ctx, key).Err() } ``` *** ## MongoDB ```go theme={null} import ( "go.mongodb.org/mongo-driver/mongo" "go.mongodb.org/mongo-driver/mongo/options" "go.mongodb.org/mongo-driver/bson" "go.mongodb.org/mongo-driver/bson/primitive" ) type User struct { ID primitive.ObjectID `bson:"_id,omitempty"` Name string `bson:"name"` Email string `bson:"email"` CreatedAt time.Time `bson:"created_at"` } func main() { ctx := context.Background() client, err := mongo.Connect(ctx, options.Client().ApplyURI("mongodb://localhost:27017")) if err != nil { log.Fatal(err) } defer client.Disconnect(ctx) db := client.Database("myapp") users := db.Collection("users") // Insert user := User{Name: "John", Email: "john@example.com", CreatedAt: time.Now()} result, err := users.InsertOne(ctx, user) user.ID = result.InsertedID.(primitive.ObjectID) // Find one var found User err = users.FindOne(ctx, bson.M{"email": "john@example.com"}).Decode(&found) // Find many cursor, err := users.Find(ctx, bson.M{"name": bson.M{"$regex": "^J"}}) var results []User err = cursor.All(ctx, &results) // Update _, err = users.UpdateOne(ctx, bson.M{"_id": user.ID}, bson.M{"$set": bson.M{"name": "Jane"}}, ) // Delete _, err = users.DeleteOne(ctx, bson.M{"_id": user.ID}) } ``` *** ## Interview Questions * `Query`/`QueryContext`: Returns rows, use for SELECT * `Exec`/`ExecContext`: Returns Result (LastInsertId, RowsAffected), use for INSERT/UPDATE/DELETE Not closing rows causes: * Connection leaks (connection stays open) * Resource exhaustion (max connections reached) * Memory leaks Always use `defer rows.Close()` immediately after QueryContext. * Use parameterized queries (`$1`, `?` placeholders) * Never concatenate user input into SQL strings * Use prepared statements * Use an ORM with proper escaping * `MaxOpenConns`: Max simultaneous connections (prevents overwhelming DB) * `MaxIdleConns`: Connections kept in pool (reduces connection overhead) * `ConnMaxLifetime`: How long a connection can be reused * `ConnMaxIdleTime`: How long idle connections stay open Proper settings prevent connection exhaustion and improve performance. *** ## Summary | Package/Tool | Use Case | | -------------- | --------------------------------- | | `database/sql` | Standard SQL interface | | `sqlx` | Extended sql with struct scanning | | `GORM` | Full-featured ORM | | `go-redis` | Redis client | | `mongo-driver` | MongoDB client | *** ## Interview Deep-Dive **Strong Answer:** * `database/sql` maintains a pool of database connections internally. When you call `QueryContext` or `ExecContext`, it grabs a connection from the pool (or opens a new one), executes the query, and returns the connection to the pool. * `MaxOpenConns` limits the total number of connections (both in-use and idle). If all connections are in use and a new query arrives, it blocks until one is returned. Set this too high, and you overwhelm the database server. Set it too low, and your application blocks during traffic spikes. * `MaxIdleConns` controls how many connections stay open in the pool when not in use. Idle connections avoid TCP handshake overhead. Set too high, and you waste database resources. Set too low, and you repeatedly open/close connections. * `ConnMaxLifetime` forces connections to be closed and replaced after a duration. This prevents stale connections from database firewalls, DNS changes, and connection-level state accumulation. A typical value is 5 minutes. * Misconfiguration example: leaving defaults (`MaxOpenConns = 0`, meaning unlimited) in a service handling 10,000 concurrent requests. The pool opens 10,000 connections, PostgreSQL rejects them (default max is 100), and you get a flood of "too many connections" errors. **Follow-up: You forgot to call `rows.Close()` after a QueryContext. What happens?** The connection is never returned to the pool. It is "in use" forever. As more queries leak connections, the pool reaches `MaxOpenConns`, and subsequent queries block indefinitely -- classic connection pool exhaustion. The fix is always `defer rows.Close()` immediately after `QueryContext`. **Strong Answer:** * Define a repository interface at the service layer: `type UserRepository interface { GetByID(ctx context.Context, id int64) (*User, error); Create(ctx context.Context, user *User) error }`. * The concrete implementation uses actual SQL in a `postgres` package. The service constructor takes the interface: `func NewUserService(repo UserRepository) *UserService`. * For testing, a mock implements the same interface with in-memory operations. Integration tests inject the real PostgreSQL implementation against a test database. * Key design decisions: the interface uses domain types (`*User`), not database types (`sql.NullString`). Error types are domain-specific (`ErrNotFound`). Context propagates as the first parameter. * The pattern also makes caching transparent: a `CachedUserRepo` wraps a `UserRepository`, checks Redis first, falls back to the wrapped repo. Same interface, invisible to the service. **Follow-up: `sql.Open` does not actually connect to the database. Why was it designed this way?** `sql.Open` creates the pool configuration but opens no TCP connections. The pool is lazy, creating connections on demand. This allows the application to start before the database is ready (common in container orchestration). To verify connectivity, call `db.PingContext(ctx)` after `sql.Open`. **Strong Answer:** * Start with `tx, err := db.BeginTx(ctx, nil)`. Then `defer tx.Rollback()` immediately -- rollback on an already-committed transaction is a no-op, so this is safe. Execute queries with `tx.ExecContext`. If any step fails, return the error (deferred rollback fires). On success, call `tx.Commit()`. * This idiom handles all failure paths: early returns, panics, and context cancellation. You never need manual rollback in error branches. * If context is cancelled mid-transaction, the current query fails. The deferred rollback sends ROLLBACK to the database. If the connection was lost, the database server rolls back after its idle timeout. * For 50 line items: use batch inserts rather than 50 individual INSERTs. This reduces round trips from 50 to 1. * Edge case: if `tx.Commit()` fails (network error), the transaction might or might not have committed server-side. For critical operations, use idempotency keys. **Follow-up: What is the difference between `QueryContext` and `ExecContext`?** `QueryContext` returns `*sql.Rows` for SELECT queries. `ExecContext` returns `sql.Result` (with `LastInsertId()` and `RowsAffected()`) for INSERT, UPDATE, DELETE. Using `QueryContext` for an INSERT works but you must still call `rows.Close()` or you leak a connection. Using `ExecContext` for a SELECT discards the data. Rule: `QueryContext` for reading, `ExecContext` for writing.