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.