🗂️ Introductory tutorial to SQLite in Go

Table of Contents

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:

Reduction of code duplication - you define a data access layer for a given domain object only once.
Improving readability of your code - business logic is not mixed with data access logic.
Easy addition of new features and modifications to the data access code, and even easy replacement of the entire database, as the code that operates directly on the DB is hidden in a specific repository implementation.

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:

main.go file - the entry point to our application that initializes the database connection and makes basic operations on a database through the repository. We will present what is inside this file after defining the domain object - Website, and implementing the repository.
website package - the package responsible for the Website domain. It contains a definition of the Website struct and the SQLite repository implementation to store Website objects in the SQLite database.

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
}

In lines 17-25, we define the SQLiteRepository struct and its constructor. Note that it requires an instance of sql.DB type as a dependency. The sql.DB is an object representing a pool of DB connections for all drivers compatible with the database/sql interface.
In lines 10-15, we define all errors that can be returned by methods of this repository. It is a good practice to return your own defined errors instead of the errors returned by functions of database/sql package or driver-specific errors. This will make the repository driver-independent and easier to modify in the future.
In lines 27-39, we create the Migrate() method, which is responsible for migrating the repository. Migration, in this case, is creating an SQL table and initializing all the data necessary to operate on the repository. When working on a fresh database instance, this function should be called first, before reading or writing data through the repository. The logic of the Migrate() is simple - it executes the CREATE TABLE SQL query using DB.Exec() method and returns the error.

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:

All() ([]Website, error) which returns all available records in the Website repository
GetByName(name string) (*Website, error) that gives back a Website with the specified name

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

To connect to an SQLite database using the mattn/go-sqlite3 driver, it is necessary to register it as the database/sql driver. It is done by importing the driver package in line 10 (we do this using a blank identifier, to import the package, even though it is not used by the current program). Once imported, it calls the init() function, which registers the driver in the database/sql interface under the name sqlite3.
Using the sql.Open() function with the registered sqlite3 driver name, you can connect to a new SQLite database. The second argument is the data source name which in the case of SQLite is a path to the database file. In our example, we want to run the program with a fresh instance each time, so the first line of main() deletes the old database file if it exists.

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)
}

In lines 23-27, we create a new website SQLite repository and migrate the data, which in our case means that we create a new SQL websites table.
In lines 29-47, we create new Website objects and insert them into the database using the Create() method.

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)
}

In lines 49-54, we retrieve the record with the GOSAMPLES name from the database and print it to the console.
In lines 56-69, we make a ranking update of the retrieved record and then get all records from the table to ensure that the update was performed successfully.
In lines 71-82, we delete the second row by ID, and also get and print all records to ensure that the database state is correct.

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)
    }
}