sqlite

README

SQLite

SQLite for MyGo, with Go connections and the @mygo-plugins/sqlite TypeScript client. The plugin ships prebuilt SQLite libraries and loads them through purego, so apps build without cgo or a C compiler.

Register mygo.Use(sqlite.Plugin) only when the web frontend uses @mygo-plugins/sqlite. Go and native UI apps call sqlite.Open directly; they need no plugin registration and close their own connections.

import (
    "github.com/egoist/mygo"
    "github.com/egoist/mygo/plugins/sqlite"
)

func main() {
    mygo.Use(sqlite.Plugin)
    // Create windows, then call mygo.App.Run().
}

import { open } from "@mygo-plugins/sqlite";

const db = await open("app.db");
await db.execute("CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY, title TEXT, done INTEGER)");
const inserted = await db.execute("INSERT INTO tasks(title, done) VALUES (?, ?)", ["Learn MyGo", false]);
const { columns, rows } = await db.query("SELECT id, title, done FROM tasks WHERE done = ?", [false]);
await db.transaction([
  { sql: "UPDATE tasks SET done = ? WHERE id = ?", params: [true, inserted.lastInsertId] },
  { sql: "INSERT INTO tasks(title, done) VALUES (?, ?)", params: ["Ship the app", false] },
]);
await db.close();

Use bun add @mygo-plugins/sqlite once the npm package is published. In a checkout, bun install && bun run build installs and builds the workspace package. See the full guide for the Go API, types, cancellation, options and packaging.

Building the native libraries

Prebuilt libraries are published in sqlite-3.53.4-1. CI and unpackaged programs download the matching library and verify its SHA-256. Testing and building apps require no Zig installation.

To update SQLite or its C shim, with Zig 0.16.0 on the path:

go generate ./plugins/sqlite
# Or build just this machine, from plugins/sqlite:
go run ./internal/libbuild -target host

The generator downloads SQLite 3.53.4 from sqlite.org and checks its published SHA3-256 before extracting sqlite3.c and sqlite3.h. It builds macOS, Linux and Windows for amd64 and arm64, writes SHA-256 and release URLs to mygo-plugin.json, and populates the CLI's native-library cache. mygo dev and mygo build discover the manifest and bundle the matching library; macOS universal builds combine both architectures. Linux targets glibc 2.28 or later; macOS targets 12 or later. Binaries in build/ are ignored by git.

New binaries must be published before a fresh checkout or app build can download them. The default asset release is sqlite-3.53.4-1:

gh release create sqlite-3.53.4-1 plugins/sqlite/build/* --latest=false \
  --title "SQLite 3.53.4 for MyGo" --notes "SQLite compiled with Zig 0.16.0 and the MyGo C shim."

Change the asset tag (or pass -release <URL>) when the shim, compiler or compile options change; regenerate and publish the manifest and binaries together. -src <directory> uses a local amalgamation instead of the verified download, intended for development.

MYGO_SQLITE_LIBRARY overrides the library path. Otherwise the plugin looks in app resources, next to the executable, then the CLI's cache. Unpackaged programs download a missing library and verify its SHA-256; packaged apps never download native code at run time.

SQLite is public domain; the plugin and C shim use the repository's MIT license. FTS5, RTree and JSON are enabled, with foreign keys on by default. Loadable extensions, shared cache and deprecated APIs are omitted.

Documentation

Overview

Package sqlite is MyGo's SQLite plugin. SQLite's C amalgamation and a small C shim are compiled with Zig and loaded through purego: no cgo, Zig, Bun or compiler is needed at application run time.

Register the plugin only when the web frontend uses @mygo-plugins/sqlite:

mygo.Use(sqlite.Plugin)

The frontend uses open from @mygo-plugins/sqlite. It opens named databases in the app's user-data directory, executes parameterized SQL, queries rows and runs batches in transactions. Go and native UI apps call Open directly and close their own connections; no mygo.Use is needed.

Index

• Variables
• func LibraryPath() (string, error)
• func Load() error
• func New(opts Options) mygo.Plugin
• type DB
  • func Open(ctx context.Context, path string, opts OpenOptions) (*DB, error)
  • func (d *DB) Close() error
  • func (d *DB) Execute(ctx context.Context, sql string, args ...any) (result Result, err error)
  • func (d *DB) Query(ctx context.Context, sql string, args ...any) (rows Rows, err error)
  • func (d *DB) Transaction(ctx context.Context, statements []Statement) (results []Result, err error)
• type Error
  • func (e *Error) Error() string
• type OpenOptions
• type Options
• type Result
• type Rows
• type Statement

Variables

var ErrClosed = errors.New("sqlite: database is closed")

ErrClosed is returned when an operation uses a closed database.

var Plugin = New(Options{})

Plugin is the page plugin with default options. It loads the native library only when a page opens its first database.

Functions

func LibraryPath

func LibraryPath() (string, error)

LibraryPath finds the library named by MYGO_SQLITE_LIBRARY, the app's resources, or the executable's directory, then the CLI's SHA-256-checked cache. Unpackaged programs download a missing library into that cache; packaged apps require the library that mygo dev/build put into them.

func Load

func Load() error

Load loads the plugin's SQLite library once. Open calls it lazily, so binding the plugin and generating clients require no native library.

func New

func New(opts Options) mygo.Plugin

New returns a plugin with options. Connections belong to their page, close when it navigates away or its window closes, and close on app quit.

Types

type DB

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

DB is a connection to the plugin's SQLite library. It is safe from any goroutine; operations serialize on the connection and may be canceled while waiting for it. Use Transaction for atomic groups of statements.

func Open

func Open(ctx context.Context, path string, opts OpenOptions) (*DB, error)

Open opens path (or ":memory:") with the SQLite C library compiled by Zig, without cgo. It creates a missing file unless ReadOnly. The parent directory must already exist. Close the returned connection when done.

func (*DB) Close

func (d *DB) Close() error

Close cancels in-flight operations, waits for their statements to be finalized, and closes the connection. Repeated calls are harmless.

func (*DB) Execute

func (d *DB) Execute(ctx context.Context, sql string, args ...any) (result Result, err error)

Execute runs exactly one statement with positional parameters. Supported values are nil, bool, integers, finite floats, strings, []byte and json.Number. SQL text may not contain NUL. Transaction control, ATTACH and VACUUM INTO are rejected; use Transaction for groups of statements.

func (*DB) Query

func (d *DB) Query(ctx context.Context, sql string, args ...any) (rows Rows, err error)

Query runs exactly one statement and copies its rows. It also supports statements with RETURNING. For large results, select a bounded page.

func (*DB) Transaction

func (d *DB) Transaction(ctx context.Context, statements []Statement) (results []Result, err error)

Transaction runs statements under BEGIN IMMEDIATE, without interleaving other operations on this connection. Any error, including cancellation, rolls back the batch. Results are returned only after COMMIT succeeds.

type Error

type Error struct {
	Code         int
	ExtendedCode int
	Message      string
}

Error is an SQLite error. Code is the primary result code; ExtendedCode distinguishes such errors as unique, foreign-key and not-null constraints.

func (*Error) Error

func (e *Error) Error() string

type OpenOptions

type OpenOptions struct {
	// ReadOnly opens an existing database without allowing writes.
	ReadOnly bool
	// BusyTimeout is how long an operation waits for another connection's
	// lock: five seconds when zero, no wait when negative. Cancellation
	// stops the wait early.
	BusyTimeout time.Duration
}

OpenOptions configure a connection. Foreign keys are enabled by default.

type Options

type Options struct {
	// Directory holds the page's database files, mygo.PathUserData when
	// empty. Pages may open file names here, or ":memory:", not paths.
	Directory string
	// BusyTimeout follows OpenOptions.BusyTimeout.
	BusyTimeout time.Duration
}

Options configure the page plugin. Go's Open accepts its own path and OpenOptions and does not use these options.

type Result

type Result struct {
	Changes      int64 `json:"changes"`
	LastInsertID int64 `json:"lastInsertId"`
}

Result describes a statement. Changes counts directly affected rows; LastInsertID is SQLite's last inserted rowid on this connection.

type Rows

type Rows struct {
	Columns []string `json:"columns"`
	Values  [][]any  `json:"rows"`
}

Rows holds columns and their values in order, preserving duplicate column names. Values are nil, int64, float64, string or []byte. BLOBs are copied before the next step, so they remain valid after the statement closes.

type Statement

type Statement struct {
	SQL  string
	Args []any
}

Statement is one parameterized statement in a transaction.