{"id":4175,"date":"2024-10-31T16:11:58","date_gmt":"2024-10-31T09:11:58","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=4175"},"modified":"2024-10-31T16:57:03","modified_gmt":"2024-10-31T09:57:03","slug":"select","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-go\/select\/","title":{"rendered":"SQLite Go: Selecting Data from a Table"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn to select one or more rows from a table in an SQLite database using Go.<\/p>\n\n\n\n

Selecting one row from a table<\/h2>\n\n\n\n

Step 1. Define a function FindById()<\/code> in the country.go<\/code>, which selects a country by id from the countries<\/code> table:<\/p>\n\n\n

func<\/span> FindById<\/span>(db *sql.DB, id int<\/span>)<\/span> (*Country, error)<\/span><\/span> {\n    sql := `SELECT * FROM countries WHERE id = ?`<\/span>\n    row := db.QueryRow(sql, id)\n    c := &Country{}\n    err := row.Scan(&c.Id, &c.Name, &c.Population, &c.Area)\n    if<\/span> err != nil<\/span> {\n        return<\/span> nil<\/span>, err\n    }\n    return<\/span> c, nil<\/span>\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, construct a SELECT<\/a> statement that retrieves a country by id:<\/p>\n\n\n
sql := 'SELECT * FROM countries WHERE id = ?'<\/span><\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, execute the SELECT<\/code> statement and return a single row:<\/p>\n\n\n
row := db.QueryRow(sql, id)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, create a new Country<\/code> struct:<\/p>\n\n\n
c := &Country{}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that we defined the Country<\/code> struct in the country.go<\/code> file as follows:<\/p>\n\n\n
type<\/span> Country struct<\/span> {\n    Id int<\/span>\n    Name string<\/span>\n    Population int<\/span>\n    Area int<\/span>\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, scan the result into the Country<\/code> struct:<\/p>\n\n\n
err := row.Scan(&c.Id, &c.Name, &c.Population, &c.Area)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
In this code, we call the Scan<\/code> method on the row<\/code> object to copy the column values from the result into the corresponding fields (Id<\/code>, Name<\/code>, and  Population<\/code>) of the Country<\/code> struct.<\/p>\n\n\n\n
Finally, return nil<\/code> and an error if the scanning operation fails:<\/p>\n\n\n
if<\/span> err != nil<\/span> {\n    return<\/span> nil<\/span>, err\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Return the pointer to the Country<\/code> struct and nil<\/code> for the error if no error occurs:<\/p>\n\n\n
return<\/span> c, nil<\/span><\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the main.go<\/code> to call the FindById<\/code> function:<\/p>\n\n\n
package<\/span> main\n\nimport<\/span> (\n    \"database\/sql\"<\/span>\n    \"fmt\"<\/span>\n\n    _ \"github.com\/glebarez\/go-sqlite\"<\/span>\n)\n\nfunc<\/span> main<\/span>()<\/span><\/span> {\n    \/\/ connect to the SQLite database<\/span>\n    db, err := sql.Open(\"sqlite\"<\/span>, \".\/my.db\"<\/span>)\n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        return<\/span>\n    }\n    defer<\/span> db.Close()\n\n\n    country, err := FindById(db, 1<\/span>)\n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        return<\/span>\n    }\n\n    fmt.Println(country.Name)\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Open your terminal and run the main.go<\/code> program:<\/p>\n\n\n
go<\/span> run main.go<\/span> country.go<\/span><\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
India<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Selecting all rows from a table<\/h2>\n\n\n\n
Step 1. Define a new function findAll()<\/code> that retrieves all rows from the countries<\/code> table:<\/p>\n\n\n
func<\/span> FindAll<\/span>(db *sql.DB)<\/span> ([]Country, error)<\/span><\/span> {\n    sql := `SELECT * FROM countries ORDER BY name`<\/span>\n    \n    rows, err := db.Query(sql)\n    if<\/span> err != nil<\/span> {\n        return<\/span> nil<\/span>, err\n    }\n    defer<\/span> rows.Close()\n\n    var<\/span> countries []Country\n    for<\/span> rows.Next() {\n        c := &Country{}\n        err := rows.Scan(&c.Id, &c.Name, &c.Population, &c.Area)\n        if<\/span> err != nil<\/span> {\n            return<\/span> nil<\/span>, err\n        }\n        countries = append<\/span>(countries, *c)\n    }\n    return<\/span> countries, nil<\/span>\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, construct a query that returns all rows from the countries<\/code> table:<\/p>\n\n\n
sql := `SELECT * FROM countries ORDER BY name`<\/span><\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, call the Query()<\/code> method to execute the SELECT<\/code> statement and return a set of rows:<\/p>\n\n\n
rows, err := db.Query(sql)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
If an error occurs during the query execution, return nil<\/code> and the error:<\/p>\n\n\n
if<\/span> err != nil<\/span> {\n   return<\/span> nil<\/span>, err\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, close the rows object when the function exits using the defer<\/code> statement:<\/p>\n\n\n
defer<\/span> rows.Close()<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, create a slice of Country<\/code> struct:<\/p>\n\n\n
var<\/span> countries []Country<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, iterate over each row in the result set using a for<\/a> loop:<\/p>\n\n\n
for<\/span> rows.Next() {<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, scan each row and copy the values of the Id<\/code>, Name<\/code>, and Population<\/code> columns into the fields of the Country<\/code> struct:<\/p>\n\n\n
c := &Country{}\nerr := rows.Scan(&c.Id, &c.Name, &c.Population, &c.Area)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, return nil<\/code> and error if an error occurs during the Scan<\/code> operation:<\/p>\n\n\n
return<\/span> nil<\/span>, err<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Eighth, add the country to the countries<\/code> slice:<\/p>\n\n\n
countries = append<\/span>(countries, *c)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, return the slice of Country<\/code> structs and nil<\/code> for the error after:<\/p>\n\n\n
return<\/span> countries, nil<\/span><\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Call the FindAll()<\/code> function in the main()<\/code> function:<\/p>\n\n\n
package<\/span> main\n\nimport<\/span> (\n    \"database\/sql\"<\/span>\n    \"fmt\"<\/span>\n    _ \"github.com\/glebarez\/go-sqlite\"<\/span>\n)\n\nfunc<\/span> main<\/span>()<\/span><\/span> {\n    \/\/ connect to the SQLite database<\/span>\n    db, err := sql.Open(\"sqlite\"<\/span>, \".\/my.db\"<\/span>)\n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        return<\/span>\n    }\n    defer<\/span> db.Close()\n\n    \/\/ find all countries<\/span>\n    countries, err := FindAll(db)\n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        return<\/span>\n    }\n    for<\/span> _, c := range<\/span> countries {\n        fmt.Printf(\"%s\\n\"<\/span>, c.Name)\n    }        \n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Run the Go program:<\/p>\n\n\n
go<\/span> run main.go<\/span> country.go<\/span><\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Bangladesh\nBrazil\nChina\nEthiopia\nIndia\nIndonesia\nNigeria\nPakistan\nRussia\nUnited States<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Call the QueryRow()<\/code> method of the DB instance to execute a SELECT<\/code> statement that returns a single row.<\/li>\n\n\n\n
  • Call the Query()<\/code> method of the DB instance to execute a SELECT<\/code> statement that returns multiple rows.<\/li>\n<\/ul>\n\n\n\n
    <\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t