# 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,
	    `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/](http://sqliteadmin.orbmu2k.de/)

## Links

- [Directory](preface.md)
- Previous section: [MySQL](05.2.md)
- Next section: [PostgreSQL](05.4.md)