In software development, it's often necessary to test and validate logic using a lightweight and easily manageable database system. SQLite, with its minimal setup and zero configuration requirements, is an excellent choice for such scenarios. Leveraging the simplicity and efficiency of SQLite for testing purposes can significantly streamline the development process. In this guide, we'll explore how to seamlessly connect to SQLite using Go on Windows, empowering developers to efficiently test their code with a reliable and straightforward database solution.

Install SQLIte3 Packages

Go has built-in support for SQLite3, so you don't need to install any additional packages. However, if you want to use a specific SQLite3 driver, you can install it using the go get command. 

go get github.com/mattn/go-sqlite3

It may have error reported while trying to install the packages. 

$ go get github.com/mattn/go-sqlite3
go: downloading github.com/mattn/go-sqlite3 v1.14.22
# github.com/mattn/go-sqlite3
cgo: exec gcc: exec: "gcc": executable file not found in %PATH%

To get this fixed, gcc needs to be installed. Can follow the official download page to download and install gcc. Post the installation, can verify it's installed successfully.

$ gcc -v
Using built-in specs.
COLLECT_GCC=C:\TDM-GCC-64\bin\gcc.exe
COLLECT_LTO_WRAPPER=C:/TDM-GCC-64/bin/../libexec/gcc/x86_64-w64-mingw32/10.3.0/lto-wrapper.exe
Target: x86_64-w64-mingw32
Configured with: ../../../src/gcc-git-10.3.0/configure --build=x86_64-w64-mingw32 --enable-targets=all --enable-languages=ada,c,c++,fortran,jit,lto,objc,obj-c++ --enable-libgomp --enable-lto --enable-graphite --enable-cxx-flags=-DWINPTHREAD_STATIC --disable-build-with-cxx --disable-build-poststage1-with-cxx --enable-libstdcxx-debug --enable-threads=posix 
--enable-version-specific-runtime-libs --enable-fully-dynamic-string --enable-libstdcxx-filesystem-ts=yes --disable-libstdcxx-pch --enable-libstdcxx-threads --enable-libstdcxx-time=yes --enable-mingw-wildcard --with-gnu-ld --disable-werror --enable-nls --disable-win32-registry --enable-large-address-aware --disable-rpath --disable-symvers --prefix=/mingw64tdm --with-local-prefix=/mingw64tdm --with-pkgversion=tdm64-1 --with-bugurl=https://github.com/jmeubank/tdm-gcc/issues
Thread model: posix
Supported LTO compression algorithms: zlib zstd
gcc version 10.3.0 (tdm64-1)

Write Your Go Code

Here's a basic example of Go code that connects to an SQLite3 database, creates a table, and inserts some data:

package main

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

func main() {
    // Open SQLite database
    db, err := sql.Open("sqlite3", "test.db")
    if err != nil {
        fmt.Println("Error opening database:", err)
        return
    }
    defer db.Close()

    // Create a table if not exists
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT
        )
    `)
    if err != nil {
        fmt.Println("Error creating table:", err)
        return
    }

    // Insert some data
    _, err = db.Exec("INSERT INTO users (name) VALUES (?)", "Alice")
    if err != nil {
        fmt.Println("Error inserting data:", err)
        return
    }

    fmt.Println("Data inserted successfully")
}

This code creates an SQLite3 database named test.db, creates a table named users, and inserts a record with the name Alice.

Run Your Code

Save the code in a file, e.g., main.go, and then navigate to the directory containing your code in the command prompt. Run the following command to execute your Go program:

go run main.go

This will compile and execute your Go program, connecting to the SQLite3 database and performing the specified operations.

To query the database, it is also not difficult.

package main

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

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

type User struct {
	ID   int
	Name string
}

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

	// Query data
	rows, err := db.Query("SELECT id, name FROM users")
	if err != nil {
		log.Fatal("Error querying database:", err)
	}
	defer rows.Close()

	// Iterate over the rows
	var users []User
	for rows.Next() {
		var user User
		err := rows.Scan(&user.ID, &user.Name)
		if err != nil {
			log.Fatal("Error scanning row:", err)
		}
		users = append(users, user)
	}

	// Check for errors from iterating over rows
	if err := rows.Err(); err != nil {
		log.Fatal("Error iterating over rows:", err)
	}

	// Print the retrieved data
	for _, user := range users {
		fmt.Printf("ID: %d, Name: %s\n", user.ID, user.Name)
	}
}

By following these steps, you can connect to an SQLite3 database using Go on Windows. This basic example can be expanded to perform more complex database operations as needed.