sqlite3memvfs

README

sqlite3memvfs

codeberg.org/piman/sqlite3memvfs implements support for reading data from in-memory SQLite database files. This can be helpful if you want to…

• … distribute an SQLite database with your program using embed.
• … load a non-local DB in a situation where memory isn’t more precious than disk storage (e.g. many containers).
• … do other things I didn’t think of.

This is based on

• github.com/mattn/go-sqlite3 for a SQLite driver;
• github.com/psanford/sqlite3vfs for mapping the VFS layer.

If this sounds interesting, but your database outstrips your memory, but you can keep an immutable copy on an external object store, github.com/psanford/sqlite3vfshttp may be more appropriate.

License

Copyright 2022 Joe Wreschnig

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

Documentation

Overview

Package sqlite3memvfs implements support for reading data from in-memory SQLite database files, keyed in a VFS which can be accessed via database/sql.Open using the “vfs” query parameter. Multiple VFSs may be registered under different names; “files” may also be created or removed from each VFS independently.

This is subtly different than the :memory: filename which is used to create an in-memory database scoped to a single connection. This package is for when you already have static data and don’t want to use a real filesystem at all.

This uses the API provided by sqlite3vfs and the SQLite OS interface, but you don’t need to know the details of either to use this.

Example (Embed)

package main

import (
	"database/sql"
	_ "embed"
	"fmt"

	"codeberg.org/piman/sqlite3memvfs"
)

//go:embed testdata/test.db
var dbfile string

func init() {
	sqlite3memvfs.Must(
		sqlite3memvfs.RegisteredAs("example"),
		sqlite3memvfs.WithEntryData("test.db", dbfile),
	)
}

func main() {
	db, _ := sql.Open("sqlite3", "test.db?vfs=example")
	defer db.Close()
	row := db.QueryRow("select value from test where id = 1")

	var result string
	if row.Scan(&result) == nil {
		fmt.Println(result)
	}
}

Output:

first row

Index

• type Entry
• type Option
  • func RegisteredAs(name string, opts ...sqlite3vfs.Option) Option
  • func WithEntry(name string, r Entry) Option
  • func WithEntryData[T ~[]byte | ~string](name string, data T) Option
• type VFS
  • func Must(opts ...Option) *VFS
  • func New(opts ...Option) (*VFS, error)
  • func (v *VFS) Access(name string, flag sqlite3vfs.AccessFlag) (bool, error)
  • func (v *VFS) Delete(name string, dirSync bool) error
  • func (v *VFS) Entry(name string, r Entry) error
  • func (*VFS) FullPathname(name string) string
  • func (v *VFS) Open(name string, flags sqlite3vfs.OpenFlag) (sqlite3vfs.File, sqlite3vfs.OpenFlag, error)

Examples

• Package (Embed)

Types

type Entry

type Entry interface {
	io.ReaderAt
	Size() int64
}

An Entry implements the features needed for an immutable SQLite3 file; that is, reading from a specified offset and having a size.

If implementing your own Entry see the parallel use requirements of io.ReaderAt.ReadAt. Also note the lack of Close; a VFS may stop referencing an Entry but will never do anything to dispose of one.

type Option

type Option func(*VFS) error

An Option can be passed to Must or New to prepare a VFS during its creation.

func RegisteredAs

func RegisteredAs(name string, opts ...sqlite3vfs.Option) Option

RegisteredAs will register this VFS with the provided name and options during creation. (If you want to do this later instead, use sqlite3vfs.RegisterVFS.)

It’s not possible to unregister a VFS, so registering a VFS once at startup and adding/removing entries is recommended if you need to handle changing datasets over time.

func WithEntry

func WithEntry(name string, r Entry) Option

WithEntry creates the VFS with the provided entry.

func WithEntryData

func WithEntryData[T ~[]byte | ~string](name string, data T) Option

WithEntryData creates the VFS with an entry containing the provided data.

Prefer string data when it’s embedded at compile-time (e.g. via embed). Prefer bytes when you already have immutable bytes, which is usual when you generate or load data at runtime.

type VFS

type VFS struct {
	// contains filtered or unexported fields
}

A VFS is a SQLite virtual filesystem with some particular contents.

The data in the VFS should be treated immutably, both within and externally to SQLite operations. Attempting to modify the contents via SQLite will fail with an error. Modifying the VFS directly comes with similar caveats as an on-disk filesystem:

• Changing entries not used by any open database/sql.Conn is fine.

• Replacing an entry used by an open connection may be fine but different connections will end up with different views of the “same” database. This may also cause more subtle problems if you’re using shared-cache mode.

• Changing the underlying contents of an entry (e.g. the original []byte passed) while a connection is open will have undefined results. Don’t do this. If you’re lucky SQLite will report the database is corrupted, but it may also return incorrect or garbage data.

func Must

func Must(opts ...Option) *VFS

Must is like New but panics on errors.

func New

func New(opts ...Option) (*VFS, error)

New returns a new in-memory virtual filesystem for SQLite. Each VFS’s contents can be managed independently, and registered under a different SQLite VFS name.

As the data is static and VFSs cannot be unregistered, any errors during creation are probably unrecoverable. Panicking on error is recommended; see Must.

func (*VFS) Access

func (v *VFS) Access(name string, flag sqlite3vfs.AccessFlag) (bool, error)

Access implements sqlite3vfs.VFS.

func (*VFS) Delete

func (v *VFS) Delete(name string, dirSync bool) error

Delete removes the entry from the VFS. This can be used to free resources you no longer need after closing all sql.Conn using the deleted file.

func (*VFS) Entry

func (v *VFS) Entry(name string, r Entry) error

Entry adds a new file entry to the VFS. After doing this, it is generally unsafe to do anything which could change the data returned by that Entry. If you need to change the data, replace it with a different Entry (with some caveats; see New).

Entry(name, nil) is equivalent to VFS.Delete(name, false).

func (*VFS) FullPathname

func (*VFS) FullPathname(name string) string

FullPathname implements sqlite3vfs.VFS.

func (*VFS) Open

func (v *VFS) Open(name string, flags sqlite3vfs.OpenFlag) (sqlite3vfs.File, sqlite3vfs.OpenFlag, error)

Open implements sqlite3vfs.VFS.

In the SQLite VFS model, files on immutable devices may still be opened “read-write”, and fail only when a write is attempted.