SQLite: CRUD, Migrations & Prepared Statements | Go Tutorial #33

SQLite: CRUD, Migrations & Prepared Statements in Go

This tutorial demonstrates how to use Go's database/sql package with a pure Go SQLite driver to perform full CRUD operations, manage schema migrations, and execute prepared statements efficiently. It covers connecting to SQLite, executing queries safely with placeholders, handling nullable fields, and running transactions for reliable database updates.

Code

package main

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

  _ "modernc.org/sqlite" // Pure Go SQLite driver, no CGO needed
)

func main() {
  // Connect to SQLite database file
  db, err := sql.Open("sqlite", "file:test.db?cache=shared&mode=rwc")
  if err != nil {
    log.Fatal(err)
  }
  defer db.Close()

  // Create table if not exists
  _, err = db.Exec(`
    CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      price REAL NOT NULL,
      description TEXT
    )
  `)
  if err != nil {
    log.Fatal(err)
  }

  // Insert a product using prepared statement to prevent SQL injection
  stmt, err := db.Prepare("INSERT INTO products(name, price, description) VALUES (?, ?, ?)")
  if err != nil {
    log.Fatal(err)
  }
  defer stmt.Close()

  res, err := stmt.Exec("Gadget", 19.99, "Useful gadget")
  if err != nil {
    log.Fatal(err)
  }

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

  // Query single row
  var name string
  var price float64
  var description sql.NullString // nullable field handling
  err = db.QueryRow("SELECT name, price, description FROM products WHERE id = ?", lastID).
    Scan(&name, &price, &description)
  if err != nil {
    log.Fatal(err)
  }
  desc := "NULL"
  if description.Valid {
    desc = description.String
  }
  fmt.Printf("Product: %s, Price: %.2f, Description: %s\n", name, price, desc)

  // Update product price with verification
  res, err = db.Exec("UPDATE products SET price = ? WHERE id = ?", 24.99, lastID)
  if err != nil {
    log.Fatal(err)
  }
  rowsAffected, _ := res.RowsAffected()
  fmt.Printf("Updated rows: %d\n", rowsAffected)

  // Delete product
  res, err = db.Exec("DELETE FROM products WHERE id = ?", lastID)
  if err != nil {
    log.Fatal(err)
  }
  rowsAffected, _ = res.RowsAffected()
  fmt.Printf("Deleted rows: %d\n", rowsAffected)

  // Schema migration example: set user_version and create index inside a transaction
  tx, err := db.Begin()
  if err != nil {
    log.Fatal(err)
  }
  _, err = tx.Exec("PRAGMA user_version = 1")
  if err != nil {
    tx.Rollback()
    log.Fatal(err)
  }
  _, err = tx.Exec("CREATE INDEX IF NOT EXISTS idx_products_name ON products(name)")
  if err != nil {
    tx.Rollback()
    log.Fatal(err)
  }
  err = tx.Commit()
  if err != nil {
    log.Fatal(err)
  }
  fmt.Println("Migration completed successfully")
}

Key Points

• Use sql.Open("sqlite", "file.db") to connect to SQLite databases without CGO.
• Perform CRUD operations with db.Exec and verify changes using RowsAffected() and LastInsertId().
• Prevent SQL injection by using ? placeholders and prepared statements.
• Manage schema versions and migrations safely using PRAGMA user_version and transactional BEGIN/COMMIT.
• Handle nullable columns with types like sql.NullString and use transactions for atomic batch operations.