How to use SQLite in your Go project

cbrake · August 29, 2022, 4:01pm

There are two popular options for using SQLite in your Go project:

sqlite3 package - github.com/mattn/go-sqlite3 - Go Packages (requires CGO)
sqlite package - modernc.org/sqlite - Go Packages (does not require CGO)

Beyond that, there are a number of options when using SQLite which affect how it handles concurrency, WAL or not, etc. The Pocketbase source provides good defaults to start with. Start with these defaults (which you append to the filename when opening the database) – it will save you a lot strange DB errors when getting started – especially if you have concurrent accesses, which you will likely have in Go.

Pocketbase also shows how to use build tags to use one of the above implementations depending if CGO is enabled or not.

github.com

pocketbase/pocketbase/blob/master/core/db_cgo.go

//go:build cgo

package core

import (
	"fmt"

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

func connectDB(dbPath string) (*dbx.DB, error) {
	pragmas := "_foreign_keys=1&_journal_mode=WAL&_synchronous=NORMAL&_busy_timeout=8000"

	db, openErr := dbx.MustOpen("sqlite3", fmt.Sprintf("%s?%s", dbPath, pragmas))
	if openErr != nil {
		return nil, openErr
	}

	// additional pragmas not supported through the dsn string

This file has been truncated. show original

github.com

pocketbase/pocketbase/blob/master/core/db_nocgo.go

//go:build !cgo

package core

import (
	"fmt"

	"github.com/pocketbase/dbx"
	_ "modernc.org/sqlite"
)

func connectDB(dbPath string) (*dbx.DB, error) {
	pragmas := "_pragma=foreign_keys(1)&_pragma=journal_mode(WAL)&_pragma=synchronous(NORMAL)&_pragma=busy_timeout(8000)&_pragma=journal_size_limit(100000000)"

	return dbx.MustOpen("sqlite", fmt.Sprintf("%s?%s", dbPath, pragmas))
}