SQLite Go: Inserting Data

Summary: in this tutorial, you will learn how to insert data into a table of a SQLite database in Go.

How to insert a row into a table

To insert a new row into a table, you follow these steps:

First, connect to the SQLite database file:

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

Second, construct an INSERT statement:

sql := "INSERT INTO table...";Code language: Go (go)

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

INSERT INTO countries(name, population, area) VALUES(?, ?, ?)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 INSERT statement includes placeholders (?), you need to pass the corresponding values in the Exec() method:

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

When executing the INSERT statement, the program will substitute the placeholders with the value1, value2, …

The result is an instance of the struct that implements the Result interface. The Result interface has two methods:

• LastInsertId() returns an ID generated by the SQLite for an auto-increment column.
• RowsAffected() returns the number of rows affected by an update, insert, or delete statement.

Inserting a row into the countries table

Step 1. Define a new struct called Country in the country.go file:

type Country struct { Id int Name string Population int Area int }Code language: Go (go)

Step 2. Define an Insert() function that inserts a new country into the countries table:

func Insert(db *sql.DB, c *Country) (int64, error) { sql := `INSERT INTO countries (name, population, area) VALUES (?, ?, ?);` result, err := db.Exec(sql, c.Name, c.Population, c.Area) if err != nil { return 0, err } return result.LastInsertId() }Code language: Go (go)

How it works.

First, construct an INSERT statement that inserts a row into the countries table:

sql := `INSERT INTO countries (name, population, area) VALUES (?, ?, ?);`Code language: Go (go)

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

result, err := db.Exec(sql, c.Name, c.Population, c.Area)Code language: Go (go)

Third, return 0 and Error if an error occurs:

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

Finally, return the last inserted id and error by calling the LastInsertId() method:

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

Step 3. Call the Insert() function in the main function to insert a new country into the countries table:

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?_pragma=foreign_keys(1)") if err != nil { fmt.Println(err) return } defer db.Close() // create a new country country := &Country{ Name: "United States", Population: 329064917, Area: 9826675, } // insert the country countryId, err := Insert(db, country) if err != nil { fmt.Println(err) return } // print the inserted country fmt.Printf( "The country %s was inserted with ID:%d\n", country.Name, countryId, ) }Code language: Go (go)

Step 4. Run the Go program

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

Output:

The country United States was inserted with ID:1Code 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: Go (go)

Step 3. Retrieve data from the countries table:

countriesCode language: Go (go)

Output:

id name population area -- ------------- ----------- --------- 1 United States 329064917 9826675Code language: Go (go)

The output shows the that program has inserted the country successfully.

Step 4. Quit the sqlite3 tool:

.quitCode language: Go (go)

Summary

• Call the Exec() method of the DB struct to execute an INSERT statement to insert a new row into a table.