SQLite is an open source, embedded relational database. It has a self-contained, zero-configuration and transaction-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 a database. If you are looking for an embedded database solution, SQLite is worth considering. You can say SQLite is the open source version of Access.
There are many database drivers for SQLite in Go, but many of them do not support the database/sql
interface standards.
- https://github.com/mattn/go-sqlite3 supports
database/sql
, based on cgo. - https://github.com/feyeleanor/gosqlite3 doesn't support
database/sql
, based on cgo. - https://github.com/phf/go-sqlite3 doesn't support
database/sql
, based on cgo.
The first driver is the only one that supports the database/sql
interface standard in its SQLite driver, so I use this in my projects -it will make it easy to migrate my code in the future if I need to.
We create the following SQL:
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"
"time"
_ "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 time.Time
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 have noticed that the code is almost the same as in the previous section, and that we only changed the name of the registered driver and called sql.Open
to connect to SQLite in a different way.
As a final note on this secton, there is a useful SQLite management tool available: http://sqliteadmin.orbmu2k.de/
- Directory
- Previous section: MySQL
- Next section: PostgreSQL