Use SQLite database in Go language

Use SQLite database in Go language

1. Drive

Go support SQLite driver is also more, but many are not support Database/sql interface

• Https://github.com/mattn/go-sqlite3 supports DATABASE/SQL interfaces, based on CGO (see the official documentation or the chapters later in this book for information on CGO)
• Https://github.com/feyeleanor/gosqlite3 does not support Database/sql interface, based on CGO write
• Https://github.com/phf/go-sqlite3 does not support Database/sql interface, based on CGO write

Currently support Database/sql's SQLite database driver only the first one, I am currently using it to develop projects. The use of standard interfaces facilitates future migration when better drivers are present.

2. Instance Code

The database table structure for the example is as follows, and the corresponding SQL is built for the table:

 
CREATE TABLE `userinfo` (
    `uid` INTEGER PRIMARY KEY AUTOINCREMENT,
    `username` VARCHAR(64) NULL,
    `departname` VARCHAR(64) NULL,
    `created` DATE NULL ); CREATE TABLE `userdeatail` (
    `uid` INT(10) NULL,
    `intro` TEXT NULL,
    `profile` TEXT NULL, PRIMARY KEY (`uid`)
);

Look at the following go program is how to manipulate database table data: additions and deletions

package main

import (
    "database / sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
)

func main () {
    db, err: = sql.Open ("sqlite3", "./foo.db")
    checkErr (err)

    // Insert data
    stmt, err: = db.Prepare ("INSERT INTO userinfo (username, departname, created) values (?,?,?)")
    checkErr (err)

    res, err: = stmt.Exec ("astaxie", "R & D", "2012-12-09")
    checkErr (err)

    id, err: = res.LastInsertId ()
    checkErr (err)

    fmt.Println (id)
    //update data
    stmt, err = db.Prepare ("update userinfo set username =? where uid =?")
    checkErr (err)

    res, err = stmt.Exec ("astaxieupdate", id)
    checkErr (err)

    affect, err: = res.RowsAffected ()
    checkErr (err)

    fmt.Println (affect)

    //Query data
    rows, err: = db.Query ("SELECT * FROM userinfo")
    checkErr (err)

    for rows.Next () {
        var uid int
        var username string
        var department string
        var created string
        err = rows.Scan (& uid, & username, & department, & created)
        checkErr (err)
        fmt.Println (uid)
        fmt.Println (username)
        fmt.Println (department)
        fmt.Println (created)
    }

    //delete data
    stmt, err = db.Prepare ("delete from userinfo where uid =?")
    checkErr (err)

    res, err = stmt.Exec (id)
    checkErr (err)

    affect, err = res.RowsAffected ()
    checkErr (err)

    fmt.Println (affect)

    db.Close ()

}

func checkErr (err error) {
    if err! = nil {
        panic (err)
    }
} 

We can see that the code above is almost exactly the same as the code in the MySQL example, and the only change is that the imported driver is changed, and then the callsql.Openis opened in SQLite mode.

Use SQLite database in Go language