go-sqlite3

To enable user authentication in a SQLite database, include the _auth argument in your connection string. When _auth is present, the provided _auth_user is created as an admin user. After the initial creation, the _auth parameter can be omitted.

Required arguments when using _auth:

• _auth_user: The username to be created as admin.
• _auth_pass: The password for the admin user.

Example connection strings:

• Basic authentication: file:test.s3db?_auth&_auth_user=admin&_auth_pass=admin
• Authentication with SHA1 encoding: file:test.s3db?_auth&_auth_user=admin&_auth_pass=admin&_auth_crypt=sha1

The easiest way to cross-compile from macOS is using xgo. Alternatively, you can use musl-cross manually:

1. Install musl-cross: brew install FiloSottile/musl-cross/musl-cross
2. Build using the following command:

CC=x86_64-linux-musl-gcc CXX=x86_64-linux-musl-g++ GOARCH=amd64 GOOS=linux CGO_ENABLED=1 go build -ldflags "-linkmode external -extldflags -static"

Install the go-sqlite3 package using go get.

Requirements:

• This is a CGO enabled package.
• You must have gcc installed and available in your PATH.
• You must set the environment variable CGO_ENABLED=1 during build.

go get github.com/mattn/go-sqlite3

You can enable or disable specific SQLite3 features by using Go build tags during compilation. When using multiple tags, separate them with spaces.

Example command:

go build -tags "icu json1 fts5 secure_delete"

By default, each connection to ":memory:" opens a brand new, isolated in-memory database. To allow multiple connections to point to the same in-memory database, use the following URI format:

file::memory:?cache=shared or file:foobar?mode=memory&cache=shared

Important: If the last connection in the pool closes, the in-memory database is deleted. To prevent this, ensure db.SetMaxIdleConns is > 0 and db.SetConnMaxLifetime is set to an infinite duration.

When opening a database connection, you can provide additional options using a Data Source Name (DSN) string.

Format: filename?option1=value1&option2=value2

Rules:

• Options are separated from the filename by a ?.
• Multiple options are combined using the & character.
• Options should be URL-encoded (use url.QueryEscape in Go) if they contain special characters.
• This applies to both file-based and in-memory databases.

file:test.db?cache=shared&mode=memory

The _auth_crypt key in the connection string allows you to configure the password encoder. If the chosen encoder requires a salt, use the _auth_salt key.

Available Encoders:

• SHA1
• SSHA1 (Salted SHA1)
• SHA256
• SSHA256 (Salted SHA256)
• SHA384
• SSHA384 (Salted SHA384)
• SHA512
• SSHA512 (Salted SHA512)

To use the current locale for time.Time values, use the _loc=auto argument in the SQLite3 filename schema.

Example: file:foo.db?_loc=auto

The sqlite_userauth build tag provides an interface for SQLite user authentication. However, please note that this feature is no longer supported because the underlying userauth extension is no longer supported by the SQLite authors.

If you attempt to use these methods in current versions, they will return errUserAuthNoLongerSupported.

The default sqlite_crypt(X, Y) function used by the UserAuthentication module uses a simple Caesar-cypher which is insecure. You can provide a stronger implementation by using the RegisterFunc method on your connection to overload sqlite_crypt with one of the provided cryptographic encoder functions.

These encoders follow the signature required for SQLite user authentication:

• X (the first argument) is the plaintext password (as a []byte).
• Y (the second argument) is the existing hash/salt from the database (passed as any).

To compute a new hash for a password X, the SQL should call sqlite_crypt(X, NULL). To verify a password, SQLite runs sqlite_user.pw == sqlite_crypt(X, sqlite_user.pw).

You can register Go functions as SQLite extension functions by using the ConnectHook in a custom sqlite3.SQLiteDriver to call conn.RegisterFunc. This allows you to use Go logic directly within SQL queries.

regex = func(re, s string) (bool, error) {
	return regexp.MatchString(re, s)
}

sql.Register("sqlite3_extended",
	&sqlite3.SQLiteDriver{
		ConnectHook: func(conn *sqlite3.SQLiteConn) error {
			return conn.RegisterFunc("regexp", regex, true)
		},
	})

// Usage:
var i int
conn, err := sql.Open("sqlite3_extended", "./foo.db")
// ... handle err
err = conn.QueryRow(`SELECT regexp("foo.*", "seafood")`).Scan(&i)