SQLite Go: Deleting Data

Summary: in this tutorial, you will learn how to delete one or more rows from a table in Go.

How to delete data from a table

To delete from 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 a DELETE statement that deletes one or more rows from a table:

sql := "DELETE FROM table ...";Code language: Go (go)

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

DELETE FROM table
WHERE id = ?Code language: SQL (Structured Query Language) (sql)

Third, call the Exec() method of the DB struct to execute the DELETE statement:

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

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

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

The Exec() method returns two values:

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

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

Deleting a row from the countries table

Step 1. Define an Delete() function in the country.go file, which deletes a country by an ID from the countries table:

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

How it works.

First, construct a DELETE statement that deletes a row in the countries table:

sql := `DELETE FROM countries WHERE id = ?`Code language: Go (go)

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

result, err := db.Exec(sql, 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 deleted by the DELETE statement:

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

Step 3. Call the Delete() function in the main function to delete 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()
    // Delete the country with id 1
    _, err = Delete(db, 1)
    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:

Code language: Shell Session (shell)

The output shows an empty set indicating that the program has deleted the country with id 1

Step 4. Quit the sqlite3 tool:

.quitCode language: Go (go)

Summary

• Call the Exec() method of the DB struct to execute an DELETE statement to delete a row from a table.