112 lines
3.4 KiB
Markdown
112 lines
3.4 KiB
Markdown
# 5.3 SQLite
|
|
|
|
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.
|
|
|
|
## SQLite drivers
|
|
|
|
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](https://github.com/mattn/go-sqlite3) supports `database/sql`, based on cgo.
|
|
- [https://github.com/feyeleanor/gosqlite3](https://github.com/feyeleanor/gosqlite3) doesn't support `database/sql`, based on cgo.
|
|
- [https://github.com/phf/go-sqlite3](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.
|
|
|
|
## Samples
|
|
|
|
We create the following SQL:
|
|
|
|
CREATE TABLE `userinfo` (
|
|
`uid` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`username` VARCHAR(64) NULL,
|
|
`department` 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, department, 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/](http://sqliteadmin.orbmu2k.de/)
|
|
|
|
## Links
|
|
|
|
- [Directory](preface.md)
|
|
- Previous section: [MySQL](05.2.md)
|
|
- Next section: [PostgreSQL](05.4.md)
|