Example of using SQLite with Golang. Create an in-memory DB, with load/save to disk and embedded migrations.

000001_init_schema.down.sql

-- file: migrations/00001_init_schema.down.sql

DROP TABLE users;

000002_create_table_foo.down.sql

-- file: migrations/000002_create_table_foo.down.sql

DROP TABLE foo;

000002_create_table_foo.up.sql

-- file: migrations/000002_create_table_foo.up.sql

CREATE TABLE IF NOT EXISTS foo (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

00001_init_schema.up.sql

-- file: migrations/00001_init_schema.up.sql

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL
);

golang-sqlite-example.go

/*
In-memory SQLite DB.

1. Create new SQLite DB on disk.
2. Run migrations against new DB.
3. Load new DB into memory for runtime operations.
4. Save DB to disk.
5. On next start, load existing DB from disk into memory.

go get github.com/mattn/go-sqlite3

- https://github.com/mattn/go-sqlite3/blob/master/_example/simple/simple.go
- https://github.com/mattn/go-sqlite3/issues/677
- https://github.com/golang/go/issues/29835
- https://go-review.googlesource.com/c/go/+/174182
- https://rbn.im/backing-up-a-SQLite-database-with-Go/backing-up-a-SQLite-database-with-Go.html
- https://www.sqlite.org/uri.html
- https://www.sqlite.org/inmemorydb.html
- https://www.sqlite.org/backup.html
- https://www.sqlite.org/lang_vacuum.html#vacuuminto
*/
package main

import (
	"context"
	"database/sql"
	"embed"
	"errors"
	"fmt"
	"log"
	"os"

	"github.com/golang-migrate/migrate/v4"
	_ "github.com/golang-migrate/migrate/v4/database/sqlite"
	"github.com/golang-migrate/migrate/v4/source/iofs"
	"github.com/mattn/go-sqlite3"
)

var (
	DbName     = "foo"
	DiskDbPath = fmt.Sprintf("%s.db", DbName)
	DiskDbURI  = fmt.Sprintf("sqlite://./%s", DiskDbPath)
	MemDbURI   = fmt.Sprintf("file:%s?mode=memory&cache=shared", DbName)
)

//go:embed migrations/*.sql
var migrationsFS embed.FS

// Run embedded migrations to apply the DB schema.
func migrateDB(dbURI string) {
	dbDriver, err := iofs.New(migrationsFS, "migrations")
	if err != nil {
		log.Println(err)
		return
	}

	migrations, err := migrate.NewWithSourceInstance("iofs", dbDriver, dbURI)
	if err != nil {
		log.Println(err)
		return
	}

	log.Println("Running migrations")
	err = migrations.Up()
	if err != nil && err.Error() != "no change" {
		log.Println(err)
		return
	}
}

// Get a handle to a SQLite DB.
func openDB(dbPath string) *sql.DB {
	log.Println("Open DB handle to:", dbPath)

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		panic(err)
	}

	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}

	return db
}

// Write the in-memory DB to disk using VACUUM INTO.
func saveDB(db *sql.DB, dbDiskPath string) {
	log.Println("Writing DB to file:", dbDiskPath)
	os.Remove(dbDiskPath)
	statement, err := db.Prepare("vacuum main into ?")
	if err != nil {
		log.Fatal(err)
	}
	defer statement.Close()

	result, err := statement.Exec(dbDiskPath)
	if err != nil {
		log.Fatal(err)
	}
	log.Println(result)
}

// Load DB from disk into memory.
func loadDB(dbDiskPath string) *sql.DB {
	log.Println("Loading saved DB file:", dbDiskPath)
	dbDisk := openDB(dbDiskPath)
	db := openDB(MemDbURI)
	err := backupDB(dbDisk, db)
	if err != nil {
		panic(err)
	}
	return db
}

// Use the SQLite backup API to transfer data.
func backupDB(sourceDB, destDB *sql.DB) error {
	destConn, err := destDB.Conn(context.Background())
	if err != nil {
		return err
	}

	srcConn, err := sourceDB.Conn(context.Background())
	if err != nil {
		return err
	}

	// Get the raw Conn instance from the underlying SQLite driver,
	// which exposes the Backup() functions.
	return destConn.Raw(func(destConn interface{}) error {
		return srcConn.Raw(func(srcConn interface{}) error {
			srcSQLiteConn, ok := srcConn.(*sqlite3.SQLiteConn)
			if !ok {
				return fmt.Errorf("can't convert source connection to SQLiteConn")
			}

			destSQLiteConn, ok := destConn.(*sqlite3.SQLiteConn)
			if !ok {
				return fmt.Errorf("can't convert destination connection to SQLiteConn")
			}

			backup, err := destSQLiteConn.Backup("main", srcSQLiteConn, "main")
			if err != nil {
				return fmt.Errorf("error initializing SQLite backup: %w", err)
			}

			done, err := backup.Step(-1)
			if !done {
				return fmt.Errorf("step of -1, but not done")
			}
			if err != nil {
				return fmt.Errorf("error in stepping backup: %w", err)
			}

			err = backup.Finish()
			if err != nil {
				return fmt.Errorf("error finishing backup: %w", err)
			}

			return err
		})
	})
}

// Insert data using a transaction and a prepared statement.
func insertTransactionPrepared(db *sql.DB) {
	// Craete the transaction.
	transaction, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}

	// Craete the prepared statement in the transaction.
	statement, err := transaction.Prepare("insert into foo(id, name) values(?, ?)")
	if err != nil {
		log.Fatal(err)
	}
	defer statement.Close()

	// Execute the prepared statement multiple times.
	for i := 1; i < 10000; i++ {
		// Passing `nil` for ID will auto-increment.
		_, err = statement.Exec(nil, fmt.Sprintf("FooBar%03d", i))
		if err != nil {
			log.Fatal(err)
		}
	}

	// Commit transaction data to DB.
	err = transaction.Commit()
	if err != nil {
		log.Fatal(err)
	}
}

// Query all rows in the table.
func queryAllRows(db *sql.DB) {
	// Query the DB to get a Rows instance.
	rows, err := db.Query("select id, name from foo")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	// Iterate through rows.
	for rows.Next() {
		var id int
		var name string

		// Copy columns into variables and print them.
		err = rows.Scan(&id, &name)
		if err != nil {
			log.Fatal(err)
		}
	}

	// Check for row iteration errors.
	err = rows.Err()
	if err != nil {
		log.Fatal(err)
	}
}

// Query all rows in the table.
func countAllRows(db *sql.DB) int {
	// Query the DB to get a Rows instance.
	rows, err := db.Query("select count(*) as count_foo from foo")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	var count_foo int
	for rows.Next() {
		err = rows.Scan(&count_foo)
		if err != nil {
			log.Fatal(err)
		}
	}
	return count_foo
}

// Query a specific row using a `where` clause.
func querySpecificRecord(db *sql.DB) {
	// Create a prepared statement.
	statement, err := db.Prepare("select name from foo where id = ?")
	if err != nil {
		log.Fatal(err)
	}
	defer statement.Close()

	// Execute the prepared statement, then scan column into variable.
	var name string
	err = statement.QueryRow("3").Scan(&name)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(name)
}

func insertDirect(db *sql.DB) {
	// Passing `null` for ID will auto-increment.
	_, err := db.Exec("insert into foo(id, name) values(null, 'foo'), (null, 'bar'), (null, 'baz')")
	if err != nil {
		log.Fatal(err)
	}
}

func main() {
	log.Println("Starting")

	var db *sql.DB
	_, err := os.ReadFile(DiskDbPath)
	if err == nil {
		db = loadDB(DiskDbPath)
		log.Println("Rows after restore:", countAllRows(db))
	} else if errors.Is(err, os.ErrNotExist) {
		migrateDB(DiskDbURI)
		db = loadDB(DiskDbPath)
		log.Println("Rows after create:", countAllRows(db))
	} else {
		panic(err)
	}
	defer db.Close()

	log.Println("Part 1")
	insertTransactionPrepared(db)
	queryAllRows(db)
	querySpecificRecord(db)
	log.Println("Rows after part 1:", countAllRows(db))

	log.Println("Part 2")
	insertDirect(db)
	queryAllRows(db)
	log.Println("Rows after part 2:", countAllRows(db))

	log.Println("Part 3")
	saveDB(db, DiskDbPath)
	log.Println("Rows after part 3:", countAllRows(db))

	log.Println("Finished")
}