SQLite Go: Selecting Data from a Table

Summary: in this tutorial, you will learn to select one or more rows from a table in an SQLite database using Go.

Selecting one row from a table

Step 1. Define a function FindById() in the country.go, which selects a country by id from the countries table:

func FindById(db *sql.DB, id int) (*Country, error) {
    sql := `SELECT * FROM countries WHERE id = ?`
    row := db.QueryRow(sql, id)
    c := &Country{}
    err := row.Scan(&c.Id, &c.Name, &c.Population, &c.Area)
    if err != nil {
        return nil, err
    }
    return c, nil
}Code language: Go (go)

How it works.

First, construct a SELECT statement that retrieves a country by id:

sql := 'SELECT * FROM countries WHERE id = ?'Code language: Go (go)

Next, execute the SELECT statement and return a single row:

row := db.QueryRow(sql, id)Code language: Go (go)

Then, create a new Country struct:

c := &Country{}Code language: Go (go)

Note that we defined the Country struct in the country.go file as follows:

type Country struct {
    Id int
    Name string
    Population int
    Area int
}Code language: Go (go)

After that, scan the result into the Country struct:

err := row.Scan(&c.Id, &c.Name, &c.Population, &c.Area)Code language: Go (go)

In this code, we call the Scan method on the row object to copy the column values from the result into the corresponding fields (Id, Name, and Population) of the Country struct.

Finally, return nil and an error if the scanning operation fails:

if err != nil {
    return nil, err
}Code language: Go (go)

Return the pointer to the Country struct and nil for the error if no error occurs:

return c, nilCode language: Go (go)

Step 2. Modify the main.go to call the FindById function:

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/glebarez/go-sqlite"
)

func main() {
    // connect to the SQLite database
    db, err := sql.Open("sqlite", "./my.db")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()


    country, err := FindById(db, 1)
    if err != nil {
        fmt.Println(err)
        return
    }

    fmt.Println(country.Name)
}Code language: Go (go)

Step 3. Open your terminal and run the main.go program:

go run main.go country.goCode language: Go (go)

Output:

IndiaCode language: Go (go)

Selecting all rows from a table

Step 1. Define a new function findAll() that retrieves all rows from the countries table:

func FindAll(db *sql.DB) ([]Country, error) {
    sql := `SELECT * FROM countries ORDER BY name`
    
    rows, err := db.Query(sql)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var countries []Country
    for rows.Next() {
        c := &Country{}
        err := rows.Scan(&c.Id, &c.Name, &c.Population, &c.Area)
        if err != nil {
            return nil, err
        }
        countries = append(countries, *c)
    }
    return countries, nil
}Code language: Go (go)

How it works.

First, construct a query that returns all rows from the countries table:

sql := `SELECT * FROM countries ORDER BY name`Code language: Go (go)

Second, call the Query() method to execute the SELECT statement and return a set of rows:

rows, err := db.Query(sql)Code language: Go (go)

If an error occurs during the query execution, return nil and the error:

if err != nil {
   return nil, err
}Code language: Go (go)

Third, close the rows object when the function exits using the defer statement:

defer rows.Close()Code language: Go (go)

Fourth, create a slice of Country struct:

var countries []CountryCode language: Go (go)

Fifth, iterate over each row in the result set using a for loop:

for rows.Next() {Code language: Go (go)

Sixth, scan each row and copy the values of the Id, Name, and Population columns into the fields of the Country struct:

c := &Country{}
err := rows.Scan(&c.Id, &c.Name, &c.Population, &c.Area)Code language: Go (go)

Seventh, return nil and error if an error occurs during the Scan operation:

return nil, errCode language: Go (go)

Eighth, add the country to the countries slice:

countries = append(countries, *c)Code language: Go (go)

Finally, return the slice of Country structs and nil for the error after:

return countries, nilCode language: Go (go)

Step 2. Call the FindAll() function in the main() function:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/glebarez/go-sqlite"
)

func main() {
    // connect to the SQLite database
    db, err := sql.Open("sqlite", "./my.db")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()

    // find all countries
    countries, err := FindAll(db)
    if err != nil {
        fmt.Println(err)
        return
    }
    for _, c := range countries {
        fmt.Printf("%s\n", c.Name)
    }        
}Code language: Go (go)

Step 3. Run the Go program:

go run main.go country.goCode language: Go (go)

Output:

Bangladesh
Brazil
China
Ethiopia
India
Indonesia
Nigeria
Pakistan
Russia
United StatesCode language: Go (go)

Summary

• Call the QueryRow() method of the DB instance to execute a SELECT statement that returns a single row.
• Call the Query() method of the DB instance to execute a SELECT statement that returns multiple rows.