Introductory tutorial to SQLite in Go (Golang)

To make use of SQLite in Go, you want a database driver – a shopper that lets you hook up with and carry out operations on the database. Go doesn’t present any official driver, nevertheless it does have a typical driver interface for SQL-like databases, outlined within the database/sql package deal. Most SQL databases implementations use it, which permits the identical capabilities for use for various databases. Probably the most standard such drivers for SQLite is mattn/go-sqlite3, and this driver is used on this tutorial to exhibit easy methods to make fundamental operations on an SQLite database in Go.

Click on right here to go on to the complete code instance.

Repository sample

When exchanging information with a database, it’s a good suggestion to not combine utility logic with database logic. This may be executed by abstracting the information entry layer right into a separate construction, liable for storing and retrieving information from the database. This sample is named a Repository. It permits for:

Discount of code duplication – you outline an information entry layer for a given area object solely as soon as.
Bettering readability of your code – enterprise logic will not be blended with information entry logic.
Simple addition of latest options and modifications to the information entry code, and even simple alternative of the complete database, because the code that operates straight on the DB is hidden in a particular repository implementation.

We strongly advocate utilizing the Repository to change information with the database. This tutorial demonstrates easy methods to create a easy repository and use this sample to create, learn, replace and delete information from an SQLite database.

Venture construction

In our instance, we need to create a Web site repository – a repository that shops details about a selected web site – its identify, URL, and rating. Let’s begin with our undertaking construction tree:

sqlite-intro/
├── fundamental.go
└── web site
    ├── sqlite_repository.go
    └── web site.go

There are two fundamental elements to this undertaking:

fundamental.go file – the entry level to our utility that initializes the database connection and makes fundamental operations on a database by the repository. We are going to current what’s inside this file after defining the area object – Web site, and implementing the repository.
web site package deal – the package deal liable for the Web site area. It comprises a definition of the Web site struct and the SQLite repository implementation to retailer Web site objects within the SQLite database.

Observe that we use packaging by function in our undertaking to have all of the constructions associated to the Web site in a single place.

To make use of the repository, we have to outline it first, so within the subsequent part, we’ll begin by defining the contents of the web site package deal.

The `web site` package deal

web site
├── sqlite_repository.go
└── web site.go

Area object

Within the web site/web site.go file, we outline our area object, a Web site, which is a struct that comprises information a couple of given web site. Objects of this sort will likely be saved within the database and retrieved from it.

package deal web site

sort Web site struct {
    ID   int64
    Identify string
    URL  string
    Rank int64
}

Repository – constructor, errors and Migrate() operate

The subsequent step is to create an SQLite implementation of the Web site repository. To do that, we initilize a web site/sqlite_repository.go file, the place we outline an SQLiteRepository struct that can work together with the SQLite database. This struct may have the next strategies:

Migrate() error
Create(web site Web site) (*Web site, error)
All() ([]Web site, error)
GetByName(identify string) (*Web site, error)
Replace(id int64, up to date Web site) (*Web site, error)
Delete(id int64) error

Observe that the strategy definitions don’t rely on SQLite in any respect. That is the aim of the Repository sample – hiding database implementation particulars and offering a easy API to work together with any database. Sooner or later, you’ll be able to outline a Repository interface:

sort Repository interface {
    Migrate() error
    Create(web site Web site) (*Web site, error)
    All() ([]Web site, error)
    GetByName(identify string) (*Web site, error)
    Replace(id int64, up to date Web site) (*Web site, error)
    Delete(id int64) error
}

and add new databases repository implementations, for instance, MySQLRepository, PostgresRepository, and so forth., if you wish to change the database in your utility. With the Repository sample, utilizing a unique database is only a matter of calling a constructor of a unique repository implementation.

Let’s begin with the code of the SQLiteRepository constructor, the repository errors definition, and the Migrate() technique 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 deal web site

import (
    "database/sql"
    "errors"

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

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

sort SQLiteRepository struct {
    db *sql.DB
}

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

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

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

In traces 17-25, we outline the SQLiteRepository struct and its constructor. Observe that it requires an occasion of sql.DB sort as a dependency. The sql.DB is an object representing a pool of DB connections for all drivers suitable with the database/sql interface.
In traces 10-15, we outline all errors that may be returned by strategies of this repository. It’s a good observe to return your individual outlined errors as a substitute of the errors returned by capabilities of database/sql package deal or driver-specific errors. This can make the repository driver-independent and simpler to change sooner or later.
In traces 27-39, we create the Migrate() technique, which is liable for migrating the repository. Migration, on this case, is creating an SQL desk and initializing all the information essential to function on the repository. When engaged on a recent database occasion, this operate ought to be referred to as first, earlier than studying or writing information by the repository. The logic of the Migrate() is easy – it executes the CREATE TABLE SQL question utilizing DB.Exec() technique and returns the error.

Create a brand new report within the SQLite database

After defining SQLiteRepository, its constructor, and the Migrate() technique, we need to create a operate to write down data to the database. That is the aim of the Create() technique, 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(web site Web site) (*Web site, error) {
    res, err := r.db.Exec("INSERT INTO web sites(identify, url, rank) values(?,?,?)", web site.Identify, web site.URL, web site.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
    }
    web site.ID = id

    return &web site, nil
}

Like Migrate(), this operate makes use of the DB.Exec() technique to execute an SQL INSERT question. If there’s an error, we test whether it is an occasion of sqlite3.Error and if its code signifies an SQLite distinctive constraint violation. It signifies that a report with the identical UNIQUE subject (the identical identify within the web sites desk) already exists, so we are able to map this error to ErrDuplicate, which we outlined earlier than. Within the final half, we take the inserted report ID and assign it to the returned object in order that it displays the state within the database.

Many DB strategies, like DB.Exec() or DB.Question(), take a question and arguments as enter parameters. As you’ll be able to see within the instance above, it’s worthwhile to use the ? character to point the place the following arguments ought to be inserted into the question.

Learn from SQLite database

To learn Web site data from the repository, we use two strategies:

All() ([]Web site, error) which returns all obtainable data within the Web site repository
GetByName(identify string) (*Web site, error) that offers again a Web site with the required identify

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() ([]Web site, error) {
    rows, err := r.db.Question("SELECT * FROM web sites")
    if err != nil {
        return nil, err
    }
    defer rows.Shut()

    var all []Web site
    for rows.Subsequent() {
        var web site Web site
        if err := rows.Scan(&web site.ID, &web site.Identify, &web site.URL, &web site.Rank); err != nil {
            return nil, err
        }
        all = append(all, web site)
    }
    return all, nil
}

func (r *SQLiteRepository) GetByName(identify string) (*Web site, error) {
    row := r.db.QueryRow("SELECT * FROM web sites WHERE identify = ?", identify)

    var web site Web site
    if err := row.Scan(&web site.ID, &web site.Identify, &web site.URL, &web site.Rank); err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, ErrNotExists
        }
        return nil, err
    }
    return &web site, nil
}

The All() technique makes use of the DB.Question() to return rows for the SQL SELECT question. The distinction between the DB.Question() and DB.Exec() strategies is that the previous is used for the queries that return rows, the latter for the queries that don’t. As the results of the DB.Question(), we get an sql.Rows struct that represents a cursor to SQL rows. Discover that it ought to be closed on the finish of the operate. Utilizing two strategies: Subsequent() returning true if there are extra rows within the outcome, and Scan() that copies successive values of the outcome set into the given variables, we are able to create a slice of all web sites within the database desk.

The GetByName() technique works in an analogous means, however as a substitute of DB.Question() it makes use of DB.QueryRow() that returns at most one row. This eliminates the necessity to shut the construction. To repeat values to a Web site object, we use the identical Scan() technique as earlier than. We additionally test if the Scan() returns a typical sql.ErrNoRows error if there is no such thing as a report within the outcome. In such a case, we map this error to our repository ErrNotExists error.

Replace a row of the SQLite database

The Replace() technique will not be considerably completely different from the earlier ones. It makes use of the DB.Exec() to execute the SQL UPDATE question that replaces values for a report with a given ID. It then checks what number of rows have been affected by this replace. If zero, we think about 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) Replace(id int64, up to date Web site) (*Web site, error) {
    if id == 0 {
        return nil, errors.New("invalid up to date ID")
    }
    res, err := r.db.Exec("UPDATE web sites SET identify = ?, url = ?, rank = ? WHERE id = ?", up to date.Identify, up to date.URL, up to date.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 &up to date, nil
}

Delete a row from the SQLite database

The Delete() technique works equally to Replace(). It executes the SQL DELETE question to delete the row with the required 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 web sites WHERE id = ?", id)
    if err != nil {
        return err
    }

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

    if rowsAffected == 0 {
        return ErrDeleteFailed
    }

    return err
}

On this means, we now have created a whole SQLite repository with CRUD operations. Now it’s time to check it by making a easy app that demonstrates how its capabilities work.

The `fundamental()` operate

Connect with SQLite

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



package deal fundamental

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

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

const fileName = "sqlite.db"

func fundamental() {
    os.Take away(fileName)

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

To hook up with an SQLite database utilizing the mattn/go-sqlite3 driver, it’s essential to register it because the database/sql driver. It’s executed by importing the driving force package deal in line 10 (we do that utilizing a clean identifier, to import the package deal, regardless that it isn’t utilized by the present program). As soon as imported, it calls the init() operate, which registers the driving force within the database/sql interface below the identify sqlite3.
Utilizing the sql.Open() operate with the registered sqlite3 driver identify, you’ll be able to hook up with a brand new SQLite database. The second argument is the information supply identify which within the case of SQLite is a path to the database file. In our instance, we need to run this system with a recent occasion every time, so the primary line of fundamental() deletes the previous 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 := web site.NewSQLiteRepository(db)

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

gosamples := web site.Web site{
    Identify: "GOSAMPLES",
    URL:  "https://gosamples.dev",
    Rank: 2,
}
golang := web site.Web site{
    Identify: "Golang official web site",
    URL:  "https://golang.org",
    Rank: 1,
}

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

In traces 23-27, we create a brand new web site SQLite repository and migrate the information, which in our case signifies that we create a brand new SQL web sites desk.
In traces 29-47, we create new Web site objects and insert them into the database utilizing the Create() technique.

Learn, replace, delete within 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.Deadly(err)
}

fmt.Printf("get by identify: %+vn", gotGosamples)

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

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

fmt.Printf("nAll web sites:n")
for _, web site := vary all {
    fmt.Printf("web site: %+vn", web site)
}

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

all, err = websiteRepository.All()
if err != nil {
    log.Deadly(err)
}
fmt.Printf("nAll web sites:n")
for _, web site := vary all {
    fmt.Printf("web site: %+vn", web site)
}

In traces 49-54, we retrieve the report with the GOSAMPLES identify from the database and print it to the console.
In traces 56-69, we make a rating replace of the retrieved report after which get all data from the desk to make sure that the replace was carried out efficiently.
In traces 71-82, we delete the second row by ID, and likewise get and print all data to make sure that the database state is appropriate.

Comply with the output beneath to test the outcomes of those operations:

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

All web sites:
web site: {ID:1 Identify:GOSAMPLES URL:https://gosamples.dev Rank:1}
web site: {ID:2 Identify:Golang official web site URL:https://golang.org Rank:1}

All web sites:
web site: {ID:1 Identify:GOSAMPLES URL:https://gosamples.dev Rank:1}

As you’ll be able to see, utilizing SQLite in Go is basically easy and no completely different than utilizing MySQL, Postgres, or every other SQL database, because of the frequent database/sql interface. Through the use of the Repository sample, you can too make the code clear and straightforward to know, the place the enterprise and information entry logic will not be blended.

Full instance

The instance can also be obtainable on Github right here.

web site/web site.go

1
2
3
4
5
6
7
8



package deal web site

sort Web site struct {
    ID   int64
    Identify string
    URL  string
    Rank int64
}

web site/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 deal web site

import (
    "database/sql"
    "errors"

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

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

sort SQLiteRepository struct {
    db *sql.DB
}

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

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

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

func (r *SQLiteRepository) Create(web site Web site) (*Web site, error) {
    res, err := r.db.Exec("INSERT INTO web sites(identify, url, rank) values(?,?,?)", web site.Identify, web site.URL, web site.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
    }
    web site.ID = id

    return &web site, nil
}

func (r *SQLiteRepository) All() ([]Web site, error) {
    rows, err := r.db.Question("SELECT * FROM web sites")
    if err != nil {
        return nil, err
    }
    defer rows.Shut()

    var all []Web site
    for rows.Subsequent() {
        var web site Web site
        if err := rows.Scan(&web site.ID, &web site.Identify, &web site.URL, &web site.Rank); err != nil {
            return nil, err
        }
        all = append(all, web site)
    }
    return all, nil
}

func (r *SQLiteRepository) GetByName(identify string) (*Web site, error) {
    row := r.db.QueryRow("SELECT * FROM web sites WHERE identify = ?", identify)

    var web site Web site
    if err := row.Scan(&web site.ID, &web site.Identify, &web site.URL, &web site.Rank); err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, ErrNotExists
        }
        return nil, err
    }
    return &web site, nil
}

func (r *SQLiteRepository) Replace(id int64, up to date Web site) (*Web site, error) {
    if id == 0 {
        return nil, errors.New("invalid up to date ID")
    }
    res, err := r.db.Exec("UPDATE web sites SET identify = ?, url = ?, rank = ? WHERE id = ?", up to date.Identify, up to date.URL, up to date.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 &up to date, nil
}

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

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

    if rowsAffected == 0 {
        return ErrDeleteFailed
    }

    return err
}

fundamental.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 deal fundamental

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

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

const fileName = "sqlite.db"

func fundamental() {
    os.Take away(fileName)

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

    websiteRepository := web site.NewSQLiteRepository(db)

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

    gosamples := web site.Web site{
        Identify: "GOSAMPLES",
        URL:  "https://gosamples.dev",
        Rank: 2,
    }
    golang := web site.Web site{
        Identify: "Golang official web site",
        URL:  "https://golang.org",
        Rank: 1,
    }

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

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

    fmt.Printf("get by identify: %+vn", gotGosamples)

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

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

    fmt.Printf("nAll web sites:n")
    for _, web site := vary all {
        fmt.Printf("web site: %+vn", web site)
    }

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

    all, err = websiteRepository.All()
    if err != nil {
        log.Deadly(err)
    }
    fmt.Printf("nAll web sites:n")
    for _, web site := vary all {
        fmt.Printf("web site: %+vn", web site)
    }
}

Introductory tutorial to SQLite in Go (Golang)

Repository sample

Venture construction

The `web site` package deal

Area object

Repository – constructor, errors and Migrate() operate

Create a brand new report within the SQLite database

Learn from SQLite database

Replace a row of the SQLite database

Delete a row from the SQLite database

The `fundamental()` operate

Connect with SQLite

Init the SQLite repository

Learn, replace, delete within the repository

Full instance

Greatest Practices for Dealing with Many Optionally available Flags – Getting Assist

Rust’s Protection Towards Use After Free & Use After Transfer

JS Social gathering 319: The boring JavaScript stack

LEAVE A REPLY Cancel reply

Most Popular

Creating Coloration Palettes with the CSS color-mix() Perform

Examine if variable is Null or Empty — LazyAdmin

Greatest Practices for Dealing with Many Optionally available Flags – Getting Assist

Rust’s Protection Towards Use After Free & Use After Transfer

Recent Comments

ABOUT US

POPULAR POSTS

Creating Coloration Palettes with the CSS color-mix() Perform

Examine if variable is Null or Empty — LazyAdmin

Greatest Practices for Dealing with Many Optionally available Flags – Getting Assist

POPULAR CATEGORY

Introductory tutorial to SQLite in Go (Golang)

Repository sample

Venture construction

The web site package deal

Area object

Repository – constructor, errors and Migrate() operate

Create a brand new report within the SQLite database

Learn from SQLite database

Replace a row of the SQLite database

Delete a row from the SQLite database

The fundamental() operate

Connect with SQLite

Init the SQLite repository

Learn, replace, delete within the repository

Full instance

LEAVE A REPLY Cancel reply

Most Popular

Recent Comments

ABOUT US

POPULAR POSTS

POPULAR CATEGORY

The `web site` package deal

The `fundamental()` operate