SQLite Go: Deleting a Row from a Table

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

Second, construct a DELETE statement that deletes one or more rows from a table:

sql := "DELETE FROM table ...";

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

DELETE FROM table
WHERE id = ?

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

result, err = db.Exec(sql)

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

result, err = db.Exec(sql, id)

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

How it works.

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

sql := `DELETE FROM countries WHERE id = ?`

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

result, err := db.Exec(sql, id)

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

if err != nil {
   return 0, err
}

Finally, return the number of rows deleted by the DELETE statement:

return result.RowsAffected()

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

Step 4. Run the Go program

go run main.go country.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.db

Step 2. Format the query output:

.header on
.mode column
.nullvalue null

Step 3. Retrieve data from the countries table:

select * from countries where id = 1;

Output:

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

Step 4. Quit the sqlite3 tool:

.quit

Summary #

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

Was this tutorial helpful?