Go by Example: Database Basics

Go 1.23

Connect to and interact with SQL databases using Go's `database/sql` package. This example covers the generic interface for database operations, including opening connections, executing queries, and managing transactions safely.

Code

package main

import (
    "database/sql"
    "fmt"
    "log"
    
    // Import driver anonymously so its init() function runs
    _ "github.com/mattn/go-sqlite3" 
)

func main() {
    // Open a database connection
    db, err := sql.Open("sqlite3", "./foo.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create a table
    sqlStmt := `
    create table if not exists foo (id integer not null primary key, name text);
    delete from foo;
    `
    _, err = db.Exec(sqlStmt)
    if err != nil {
        log.Printf("%q: %s\n", err, sqlStmt)
        return
    }

    // Insert data
    tx, err := db.Begin()
    if err != nil {
        log.Fatal(err)
    }
    stmt, err := tx.Prepare("insert into foo(id, name) values(?, ?)")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()
    
    for i := 0; i < 5; i++ {
        _, err = stmt.Exec(i, fmt.Sprintf("user%d", i))
        if err != nil {
            log.Fatal(err)
        }
    }
    tx.Commit()

    // Query data
    rows, err := db.Query("select id, name from foo")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    
    for rows.Next() {
        var id int
        var name string
        err = rows.Scan(&id, &name)
        if err != nil {
            log.Fatal(err)
        }
        fmt.Println(id, name)
    }
}

Explanation

The database/sql package provides a generic interface for SQL databases. It manages a connection pool automatically, which is critical for performance.

Key concepts:

Drivers: You must import a driver (like _ "github.com/mattn/go-sqlite3") to register it.
Lazy Connection: sql.Open only validates arguments. The actual connection happens on the first query. Use db.Ping() to verify connectivity immediately.
Prepared Statements: Use db.Prepare() or db.Exec(query, args...) to prevent SQL injection. Never construct queries using string concatenation with user input.

Code Breakdown

9

Blank import of the sqlite3 driver. This executes the driver's init() function, which registers it with database/sql, making it available for use.

14

sql.Open initializes the database object. Crucially, it does NOT establish a connection immediately; the connection is created lazily when first needed (e.g., by Ping or Exec).

25

db.Exec is used for SQL statements that don't return rows, such as CREATE TABLE, INSERT, UPDATE, and DELETE.

32

db.Begin() starts a new transaction. All subsequent operations on the returned 'tx' object are part of this transaction until Commit() or Rollback() is called.

36

tx.Prepare creates a prepared statement. This is pre-compiled by the database, offering better performance for repeated execution and security against SQL injection.

51

db.Query returns a *Rows iterator. You must close this iterator (defer rows.Close()) to release the connection back to the pool.

59

rows.Scan copies the columns from the current row into the provided variables. The number and types of arguments must match the columns in the query result.

Samplebadu

Go by Example: Database Basics

Code

Explanation

Code Breakdown