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
}

How it works.

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

sql := 'SELECT * FROM countries WHERE id = ?'

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

row := db.QueryRow(sql, id)

Then, create a new Country struct:

c := &Country{}

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
}

After that, scan the result into the Country struct:

err := row.Scan(&c.Id, &c.Name, &c.Population, &c.Area)

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
}

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

return c, nil

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)
}

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

go run main.go country.go

Output:

India

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
}

How it works.

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

sql := `SELECT * FROM countries ORDER BY name`

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

rows, err := db.Query(sql)

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

if err != nil {
   return nil, err
}

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

defer rows.Close()

Fourth, create a slice of Country struct:

var countries []Country

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

for rows.Next() {

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)

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

return nil, err

Eighth, add the country to the countries slice:

countries = append(countries, *c)

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

return countries, nil

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)
    }        
}

Step 3. Run the Go program:

go run main.go country.go

Output:

Bangladesh
Brazil
China
Ethiopia
India
Indonesia
Nigeria
Pakistan
Russia
United States

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.

Was this tutorial helpful?