# How to use SQLite

## 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> 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.

### Samples

We create the following SQL:

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

An example:

```go
    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)
        var uid int
        var username string
        var department string
        var created time.Time

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

        rows.Close() //good habit to close

        // 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.

Note that sometimes you can't use the `for` statement because you don't have more than one row, then you can use the `if` statement

```go
    if rows.Next() {
        err = rows.Scan(&uid, &username, &department, &created)
        checkErr(err)
        fmt.Println(uid)
        fmt.Println(username)
        fmt.Println(department)
        fmt.Println(created)
    }
```

Also you have to do a `rows.Next()`, without using that you can't fetch data in the `Scan` function.

## Transactions

The above example shows how you fetch data from the database, but when you want to write a web application then it will not only be necessary to fetch data from the db but it will also be required to write data into it. For that purpose, you should use transactions because for various reasons, such as having multiple go routines which access the database, the database might get locked. This is undesirable in your web application and the use of transactions is effective in ensuring your database activities either pass or fail completely depending on circumstances. It is clear that using transactions can prevent a lot of things from going wrong with the web app.

```go
    trashSQL, err := database.Prepare("update task set is_deleted='Y',last_modified_at=datetime() where id=?")
    if err != nil {
        fmt.Println(err)
    }
    tx, err := database.Begin()
    if err != nil {
        fmt.Println(err)
    }
    _, err = tx.Stmt(trashSQL).Exec(id)
    if err != nil {
        fmt.Println("doing rollback")
        tx.Rollback()
    } else {
        tx.Commit()
    }
```

As it is clear from the above block of code, you first prepare a statement, after which you execute it, depending on the output of that execution then you either roll it back or commit it.

As a final note on this section, there is a useful SQLite management tool available: <http://sqlitebrowser.org>

### Links

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://golang.repodevs.com/05.0/05.3.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.