SQLite Go: Updating Data

Summary: in this tutorial, you will learn how to update data in a table from an SQLite database in Go.

How to update data in a table

To update a row in a table, you follow these steps:

First, connect to the SQLite database file:

db, err := sql.Open("sqlite", "./my.db")Code language: Go (go)

Second, construct an UPDATE statement that updates one or more columns in a table:

sql := "UPDATE table SET ...";Code language: Go (go)

If you want to pass data to the UPDATE statement, you can use the ? placeholders. For example:

UPDATE countries
SET area = ? population = ? name = ?
WHERE id = ?Code language: Go (go)

Third, call the Exec() method of the DB struct’s instance to execute the CREATE TABLE statement:

result, err = db.Exec(sql)Code language: Go (go)

If the UPDATE statement includes placeholders (?), you need to pass the corresponding values in the Exec() method:

result, err = db.Exec(sql, area, population, name, id)Code language: Go (go)

The result is an instance of the struct that implements the Result interface.

The Result interface includes the RowsAffected() method that returns the number of rows affected by the update statement.

Updating data in the countries table

Step 1. Define an Update() function that updates the population of a country in the countries table:

func Update(db *sql.DB, id int, population int) (int64, error) {
    sql := `UPDATE countries SET population = ? WHERE id = ?;`
    result, err := db.Exec(sql, population, id)
    if err != nil {
        return 0, err
    }
    return result.RowsAffected()
}Code language: Go (go)

How it works.

First, construct an UPDATE statement that updates data in the countries table:

sql := `UPDATE countries SET population = ? WHERE id = ?;`Code language: Go (go)

Second, call the Exec() method to execute the UPDATE statement:

result, err := db.Exec(sql, population, id)Code language: Go (go)

Third, return 0 and Error if an error occurs during the update:

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

Finally, return the number of rows updated by the UPDATE statement:

return result.RowsAffected()Code language: Go (go)

Step 3. Call the Update() function in the main function to update the population of the country id 1:

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

    // Update the population of a country
    _, err = Update(db, 1, 346037975)
    if err != nil {
        fmt.Println(err)
        return
    }    
    
}Code language: Go (go)

Step 4. Run the Go program

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

Verifying data

Step 1. Open your terminal and navigate to the project directory.

Step 2. Connect to the my.db using the sqlite3 shell:

sqlite3 my.dbCode language: Shell Session (shell)

Step 2. Format the query output:

.header on
.mode column
.nullvalue nullCode language: Shell Session (shell)

Step 3. Retrieve data from the countries table:

select * from countries where id = 1;Code language: Go (go)

Output:

id  name           population   area
--  -------------  -----------  ---------
1   United States  346037975.0  9826675.0Code language: Shell Session (shell)

The output shows that the population has updated successfully.

Step 4. Quit the sqlite3 tool:

.quitCode language: Go (go)

Summary

• Call the Exec() method of the DB struct to execute an UPDATE statement to update a new row in a table.