Skip to content

Latest commit

 

History

History
110 lines (78 loc) · 3.23 KB

05.3.md

File metadata and controls

110 lines (78 loc) · 3.23 KB

5.3 SQLite

SQLite is a open source, embedded relational database, it has a self-contained, zero-configuration and affair-supported database engine. Its characteristics are highly portable, easy to use, compact, efficient and reliable. In most of cases, you only need a binary file of SQLite to create, connect and operate database. If you are looking for a embedded database solution, SQLite is worth to consider. You can say SQLite is the open source version of Access.

SQLite drivers

There are many database drivers for SQLite in Go, but many of them are not supporting database/sql interface standards.

The first driver is the only one that supports database/sql interface standards in SQLite drivers, so I use this in my projects and it will be easy to migrate code in the future.

Samples

The create SQL as follows:

CREATE TABLE `userinfo` (
    `uid` INTEGER PRIMARY KEY AUTOINCREMENT,
    `username` VARCHAR(64) NULL,
    `departname` VARCHAR(64) NULL,
    `created` DATE NULL
);

An example:

package main

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

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

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

    res, err := stmt.Exec("astaxie", "研发部门", "2012-12-09")
    checkErr(err)

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

    fmt.Println(id)
    // update
    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
    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
    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)
    }
}

You may notice that the code is almost same as previous section, and we only changed registered driver name, and call sql.Open to connect to SQLite in a different way.

There is a SQLite management tool available: http://sqliteadmin.orbmu2k.de/

Links