🗂️ Introduction to SQLite in Go

db sql sqlite

To use SQLite in Go, you need a database driver - a client that allows you to connect to and perform operations on the database. Go does not provide any official driver, but it does have a common driver interface for SQL-like databases, defined in the database/sql package. Most SQL databases implementations use it, which allows the same functions to be used for different databases. One of the most popular such drivers for SQLite is mattn/go-sqlite3, and this driver is used in this tutorial to demonstrate how to make basic operations on an SQLite database in Go.

Click here to go directly to the full code example.

Repository pattern

When exchanging data with a database, it is a good idea not to mix application logic with database logic. This can be done by abstracting the data access layer into a separate structure, responsible for storing and retrieving data from the database. This pattern is known as a Repository. It allows for:

We strongly advocate using the Repository to exchange data with the database. This tutorial demonstrates how to create a simple repository and use this pattern to create, read, update and delete data from an SQLite database.

Project structure

In our example, we want to create a Website repository - a repository that stores information about a particular website - its name, URL, and ranking. Let’s start with our project structure tree:

sqlite-intro/
├── main.go
└── website
    ├── sqlite_repository.go
    └── website.go

There are two main parts to this project:

Note that we use packaging by feature in our project to have all the structures related to the Website in one place.

To use the repository, we need to define it first, so in the next section, we will start by defining the contents of the website package.

The website package

website
├── sqlite_repository.go
└── website.go

Domain object

In the website/website.go file, we define our domain object, a Website, which is a struct that contains data about a given website. Objects of this type will be stored in the database and retrieved from it.

package website

type Website struct {
    ID   int64
    Name string
    URL  string
    Rank int64
}

Repository - constructor, errors and Migrate() function

The next step is to create an SQLite implementation of the Website repository. To do this, we initilize a website/sqlite_repository.go file, where we define an SQLiteRepository struct that will interact with the SQLite database. This struct will have the following methods:

Migrate() error
Create(website Website) (*Website, error)
All() ([]Website, error)
GetByName(name string) (*Website, error)
Update(id int64, updated Website) (*Website, error)
Delete(id int64) error

Note that the method definitions do not depend on SQLite at all. This is the purpose of the Repository pattern - hiding database implementation details and providing a simple API to interact with any database. In the future, you can define a Repository interface:

type Repository interface {
    Migrate() error
    Create(website Website) (*Website, error)
    All() ([]Website, error)
    GetByName(name string) (*Website, error)
    Update(id int64, updated Website) (*Website, error)
    Delete(id int64) error
}

and add new databases repository implementations, for example, MySQLRepository, PostgresRepository, etc., if you want to change the database in your application. With the Repository pattern, using a different database is just a matter of calling a constructor of a different repository implementation.

Let’s start with the code of the SQLiteRepository constructor, the repository errors definition, and the Migrate() method implementation:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package website

import (
    "database/sql"
    "errors"

    "github.com/mattn/go-sqlite3"
)

var (
    ErrDuplicate    = errors.New("record already exists")
    ErrNotExists    = errors.New("row not exists")
    ErrUpdateFailed = errors.New("update failed")
    ErrDeleteFailed = errors.New("delete failed")
)

type SQLiteRepository struct {
    db *sql.DB
}

func NewSQLiteRepository(db *sql.DB) *SQLiteRepository {
    return &SQLiteRepository{
        db: db,
    }
}

func (r *SQLiteRepository) Migrate() error {
    query := `
    CREATE TABLE IF NOT EXISTS websites(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        url TEXT NOT NULL,
        rank INTEGER NOT NULL
    );
    `

    _, err := r.db.Exec(query)
    return err
}

Create a new record in the SQLite database

After defining SQLiteRepository, its constructor, and the Migrate() method, we want to create a function to write records to the database. This is the purpose of the Create() method, which takes a row to create and returns the row after insertion or an error if the operation fails.

41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
func (r *SQLiteRepository) Create(website Website) (*Website, error) {
    res, err := r.db.Exec("INSERT INTO websites(name, url, rank) values(?,?,?)", website.Name, website.URL, website.Rank)
    if err != nil {
        var sqliteErr sqlite3.Error
        if errors.As(err, &sqliteErr) {
            if errors.Is(sqliteErr.ExtendedCode, sqlite3.ErrConstraintUnique) {
                return nil, ErrDuplicate
            }
        }
        return nil, err
    }

    id, err := res.LastInsertId()
    if err != nil {
        return nil, err
    }
    website.ID = id

    return &website, nil
}

Like Migrate(), this function uses the DB.Exec() method to execute an SQL INSERT query. If there is an error, we check if it is an instance of sqlite3.Error and if its code indicates an SQLite unique constraint violation. It means that a record with the same UNIQUE field (the same name in the websites table) already exists, so we can map this error to ErrDuplicate, which we defined before. In the last part, we take the inserted record ID and assign it to the returned object so that it reflects the state in the database.

Many DB methods, like DB.Exec() or DB.Query(), take a query and arguments as input parameters. As you can see in the example above, you need to use the ? character to indicate where the subsequent arguments should be inserted into the query.

Read from SQLite database

To read Website records from the repository, we use two methods:

62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
func (r *SQLiteRepository) All() ([]Website, error) {
    rows, err := r.db.Query("SELECT * FROM websites")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var all []Website
    for rows.Next() {
        var website Website
        if err := rows.Scan(&website.ID, &website.Name, &website.URL, &website.Rank); err != nil {
            return nil, err
        }
        all = append(all, website)
    }
    return all, nil
}

func (r *SQLiteRepository) GetByName(name string) (*Website, error) {
    row := r.db.QueryRow("SELECT * FROM websites WHERE name = ?", name)

    var website Website
    if err := row.Scan(&website.ID, &website.Name, &website.URL, &website.Rank); err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, ErrNotExists
        }
        return nil, err
    }
    return &website, nil
}

The All() method uses the DB.Query() to return rows for the SQL SELECT query. The difference between the DB.Query() and DB.Exec() methods is that the former is used for the queries that return rows, the latter for the queries that do not. As the result of the DB.Query(), we get an sql.Rows struct that represents a cursor to SQL rows. Notice that it should be closed at the end of the function. Using two methods: Next() returning true if there are more rows in the result, and Scan() that copies successive values of the result set into the given variables, we can create a slice of all websites in the database table.

The GetByName() method works in a similar way, but instead of DB.Query() it uses DB.QueryRow() that returns at most one row. This eliminates the need to close the structure. To copy values to a Website object, we use the same Scan() method as before. We also check if the Scan() returns a standard sql.ErrNoRows error if there is no record in the result. In such a case, we map this error to our repository ErrNotExists error.

Update a row of the SQLite database

The Update() method is not significantly different from the previous ones. It uses the DB.Exec() to execute the SQL UPDATE query that replaces values for a record with a given ID. It then checks how many rows were affected by this update. If zero, we consider it failed and return the ErrUpdateFailed error.

 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
func (r *SQLiteRepository) Update(id int64, updated Website) (*Website, error) {
    if id == 0 {
        return nil, errors.New("invalid updated ID")
    }
    res, err := r.db.Exec("UPDATE websites SET name = ?, url = ?, rank = ? WHERE id = ?", updated.Name, updated.URL, updated.Rank, id)
    if err != nil {
        return nil, err
    }

    rowsAffected, err := res.RowsAffected()
    if err != nil {
        return nil, err
    }

    if rowsAffected == 0 {
        return nil, ErrUpdateFailed
    }

    return &updated, nil
}

Delete a row from the SQLite database

The Delete() method works similarly to Update(). It executes the SQL DELETE query to delete the row with the specified ID from the database. If no row is affected, it returns the ErrUpdateFailed error.

114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
func (r *SQLiteRepository) Delete(id int64) error {
    res, err := r.db.Exec("DELETE FROM websites WHERE id = ?", id)
    if err != nil {
        return err
    }

    rowsAffected, err := res.RowsAffected()
    if err != nil {
        return err
    }

    if rowsAffected == 0 {
        return ErrDeleteFailed
    }

    return err
}

In this way, we have created a complete SQLite repository with CRUD operations. Now it’s time to test it by creating a simple app that demonstrates how its functions work.

The main() function

Connect to SQLite

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"

    "github.com/gosamples-dev/samples/sqlite-intro/website"
    _ "github.com/mattn/go-sqlite3"
)

const fileName = "sqlite.db"

func main() {
    os.Remove(fileName)

    db, err := sql.Open("sqlite3", fileName)
    if err != nil {
        log.Fatal(err)
    }
    // ...

Init the SQLite repository

23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
websiteRepository := website.NewSQLiteRepository(db)

if err := websiteRepository.Migrate(); err != nil {
    log.Fatal(err)
}

gosamples := website.Website{
    Name: "GOSAMPLES",
    URL:  "https://gosamples.dev",
    Rank: 2,
}
golang := website.Website{
    Name: "Golang official website",
    URL:  "https://golang.org",
    Rank: 1,
}

createdGosamples, err := websiteRepository.Create(gosamples)
if err != nil {
    log.Fatal(err)
}
createdGolang, err := websiteRepository.Create(golang)
if err != nil {
    log.Fatal(err)
}

Read, update, delete in the repository

49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
gotGosamples, err := websiteRepository.GetByName("GOSAMPLES")
if err != nil {
    log.Fatal(err)
}

fmt.Printf("get by name: %+v\n", gotGosamples)

createdGosamples.Rank = 1
if _, err := websiteRepository.Update(createdGosamples.ID, *createdGosamples); err != nil {
    log.Fatal(err)
}

all, err := websiteRepository.All()
if err != nil {
    log.Fatal(err)
}

fmt.Printf("\nAll websites:\n")
for _, website := range all {
    fmt.Printf("website: %+v\n", website)
}

if err := websiteRepository.Delete(createdGolang.ID); err != nil {
    log.Fatal(err)
}

all, err = websiteRepository.All()
if err != nil {
    log.Fatal(err)
}
fmt.Printf("\nAll websites:\n")
for _, website := range all {
    fmt.Printf("website: %+v\n", website)
}

Follow the output below to check the results of these operations:

get by name: &{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:2}

All websites:
website: {ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:1}
website: {ID:2 Name:Golang official website URL:https://golang.org Rank:1}

All websites:
website: {ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:1}

As you can see, using SQLite in Go is really simple and no different than using MySQL, Postgres, or any other SQL database, thanks to the common database/sql interface. By using the Repository pattern, you can also make the code clean and easy to understand, where the business and data access logic are not mixed.

Full example

The example is also available on Github here.

website/website.go

1
2
3
4
5
6
7
8
package website

type Website struct {
    ID   int64
    Name string
    URL  string
    Rank int64
}

website/sqlite_repository.go

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
package website

import (
    "database/sql"
    "errors"

    "github.com/mattn/go-sqlite3"
)

var (
    ErrDuplicate    = errors.New("record already exists")
    ErrNotExists    = errors.New("row not exists")
    ErrUpdateFailed = errors.New("update failed")
    ErrDeleteFailed = errors.New("delete failed")
)

type SQLiteRepository struct {
    db *sql.DB
}

func NewSQLiteRepository(db *sql.DB) *SQLiteRepository {
    return &SQLiteRepository{
        db: db,
    }
}

func (r *SQLiteRepository) Migrate() error {
    query := `
    CREATE TABLE IF NOT EXISTS websites(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        url TEXT NOT NULL,
        rank INTEGER NOT NULL
    );
    `

    _, err := r.db.Exec(query)
    return err
}

func (r *SQLiteRepository) Create(website Website) (*Website, error) {
    res, err := r.db.Exec("INSERT INTO websites(name, url, rank) values(?,?,?)", website.Name, website.URL, website.Rank)
    if err != nil {
        var sqliteErr sqlite3.Error
        if errors.As(err, &sqliteErr) {
            if errors.Is(sqliteErr.ExtendedCode, sqlite3.ErrConstraintUnique) {
                return nil, ErrDuplicate
            }
        }
        return nil, err
    }

    id, err := res.LastInsertId()
    if err != nil {
        return nil, err
    }
    website.ID = id

    return &website, nil
}

func (r *SQLiteRepository) All() ([]Website, error) {
    rows, err := r.db.Query("SELECT * FROM websites")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var all []Website
    for rows.Next() {
        var website Website
        if err := rows.Scan(&website.ID, &website.Name, &website.URL, &website.Rank); err != nil {
            return nil, err
        }
        all = append(all, website)
    }
    return all, nil
}

func (r *SQLiteRepository) GetByName(name string) (*Website, error) {
    row := r.db.QueryRow("SELECT * FROM websites WHERE name = ?", name)

    var website Website
    if err := row.Scan(&website.ID, &website.Name, &website.URL, &website.Rank); err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, ErrNotExists
        }
        return nil, err
    }
    return &website, nil
}

func (r *SQLiteRepository) Update(id int64, updated Website) (*Website, error) {
    if id == 0 {
        return nil, errors.New("invalid updated ID")
    }
    res, err := r.db.Exec("UPDATE websites SET name = ?, url = ?, rank = ? WHERE id = ?", updated.Name, updated.URL, updated.Rank, id)
    if err != nil {
        return nil, err
    }

    rowsAffected, err := res.RowsAffected()
    if err != nil {
        return nil, err
    }

    if rowsAffected == 0 {
        return nil, ErrUpdateFailed
    }

    return &updated, nil
}

func (r *SQLiteRepository) Delete(id int64) error {
    res, err := r.db.Exec("DELETE FROM websites WHERE id = ?", id)
    if err != nil {
        return err
    }

    rowsAffected, err := res.RowsAffected()
    if err != nil {
        return err
    }

    if rowsAffected == 0 {
        return ErrDeleteFailed
    }

    return err
}

main.go

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"

    "github.com/gosamples-dev/samples/sqlite-intro/website"
    _ "github.com/mattn/go-sqlite3"
)

const fileName = "sqlite.db"

func main() {
    os.Remove(fileName)

    db, err := sql.Open("sqlite3", fileName)
    if err != nil {
        log.Fatal(err)
    }

    websiteRepository := website.NewSQLiteRepository(db)

    if err := websiteRepository.Migrate(); err != nil {
        log.Fatal(err)
    }

    gosamples := website.Website{
        Name: "GOSAMPLES",
        URL:  "https://gosamples.dev",
        Rank: 2,
    }
    golang := website.Website{
        Name: "Golang official website",
        URL:  "https://golang.org",
        Rank: 1,
    }

    createdGosamples, err := websiteRepository.Create(gosamples)
    if err != nil {
        log.Fatal(err)
    }
    createdGolang, err := websiteRepository.Create(golang)
    if err != nil {
        log.Fatal(err)
    }

    gotGosamples, err := websiteRepository.GetByName("GOSAMPLES")
    if err != nil {
        log.Fatal(err)
    }

    fmt.Printf("get by name: %+v\n", gotGosamples)

    createdGosamples.Rank = 1
    if _, err := websiteRepository.Update(createdGosamples.ID, *createdGosamples); err != nil {
        log.Fatal(err)
    }

    all, err := websiteRepository.All()
    if err != nil {
        log.Fatal(err)
    }

    fmt.Printf("\nAll websites:\n")
    for _, website := range all {
        fmt.Printf("website: %+v\n", website)
    }

    if err := websiteRepository.Delete(createdGolang.ID); err != nil {
        log.Fatal(err)
    }

    all, err = websiteRepository.All()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("\nAll websites:\n")
    for _, website := range all {
        fmt.Printf("website: %+v\n", website)
    }
}