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.