How to Use SQLite3 in Go: A Step‑by‑Step Guide

Introduction

SQLite is a lightweight embedded SQL engine; Go provides the database/sql package and the mattn/go-sqlite3 driver to work with SQLite. This article guides through setting up the environment, connecting to a SQLite file, and performing CRUD operations.

Environment preparation

Ensure Go is installed, then install the driver with go get -u github.com/mattn/go-sqlite3. Note that the driver uses cgo and requires a C compiler.

Opening a database

Import "database/sql" and the driver, then call sql.Open("sqlite3", "your_database.db"). If the file does not exist, SQLite creates it. Handle errors and defer db.Close().

Creating a table

Use db.Exec with a CREATE TABLE statement, for example:

createTableSQL := `CREATE TABLE IF NOT EXISTS projects (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    description TEXT
);`
_, err = db.Exec(createTableSQL)
if err != nil { log.Fatal(err) }

Inserting data

Prepare an INSERT statement and execute it with parameters:

stmt, err := db.Prepare("INSERT INTO projects(name, description) VALUES(?, ?)")
if err != nil { log.Fatal(err) }
defer stmt.Close()
_, err = stmt.Exec("My Project", "This is a sample project")
if err != nil { log.Fatal(err) }

Querying data

Query rows and scan each record:

rows, err := db.Query("SELECT id, name, description FROM projects")
if err != nil { log.Fatal(err) }
defer rows.Close()
for rows.Next() {
    var id int
    var name, description string
    if err = rows.Scan(&id, &name, &description); err != nil { log.Fatal(err) }
    fmt.Println(id, name, description)
}
if err = rows.Err(); err != nil { log.Fatal(err) }

Full example

The complete program combines the steps above; see the code block below.

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, err := sql.Open("sqlite3", "./test.db")
    if err != nil { log.Fatal(err) }
    defer db.Close()

    createTableSQL := `CREATE TABLE IF NOT EXISTS projects (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        description TEXT
    );`
    _, err = db.Exec(createTableSQL)
    if err != nil { log.Fatal(err) }

    stmt, err := db.Prepare("INSERT INTO projects(name, description) VALUES(?, ?)")
    if err != nil { log.Fatal(err) }
    defer stmt.Close()
    _, err = stmt.Exec("My Project", "This is a sample project")
    if err != nil { log.Fatal(err) }

    rows, err := db.Query("SELECT id, name, description FROM projects")
    if err != nil { log.Fatal(err) }
    defer rows.Close()
    for rows.Next() {
        var id int
        var name, description string
        if err = rows.Scan(&id, &name, &description); err != nil { log.Fatal(err) }
        fmt.Println(id, name, description)
    }
    if err = rows.Err(); err != nil { log.Fatal(err) }
}

Execution result

An example screenshot shows the printed row:

Conclusion

Using SQLite3 with Go provides a lightweight, efficient storage solution. The steps above enable basic CRUD operations, while real‑world projects should also consider error handling, transactions, and connection pooling to ensure robustness and performance.