Use the SQLite Backend

go get github.com/glincker/theauth-go/storage/sqlite

​

What it covers

​

Open the database

db, err := sql.Open("sqlite",
    "file:app.db?_pragma=foreign_keys(1)&_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_txlock=immediate")

​

Standalone: let the package migrate

import sqlitestore "github.com/glincker/theauth-go/storage/sqlite"

if err := sqlitestore.Migrate(ctx, db); err != nil { ... }
store, err := sqlitestore.New(db)

​

Hosted: fold the SQL into your own migrator

files, _ := fs.Glob(sqlitestore.Migrations(), "*.sql")

​

Table prefix

​

Expiry sweeps

go func() {
    for range time.Tick(15 * time.Minute) {
        if _, err := store.SweepExpired(ctx, time.Now()); err != nil {
            slog.Error("auth sweep failed", "err", err)
        }
    }
}()

​

Importing existing data in one transaction

if err := sqlite.Migrate(ctx, db); err != nil { // before the transaction
    return err
}
tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback() // no-op after Commit

st, err := sqlite.NewTx(tx)
if err != nil {
    return err
}
u, err := theauth.ImportUserTo(ctx, st, theauth.ImportedUser{
    Email: "ada@example.com", PasswordHash: legacyArgon2idHash, CreatedAt: legacyCreated,
})
if err != nil && !errors.Is(err, theauth.ErrImportDuplicate) {
    return err
}
_, err = theauth.ImportAPITokenTo(ctx, st, theauth.ImportedToken{
    OwnerID: u.ID, Name: "ci", Abilities: []string{"read"}, TokenHash: sha256OfRawToken[:],
})
if err == nil {
    err = theauth.ImportTOTPSecretTo(ctx, st, encryptionKey, theauth.ImportedTOTP{
        UserID: u.ID, Secret: "JBSWY3DPEHPK3PXP",
    })
}
if err == nil {
    _, err = theauth.ImportWebAuthnCredentialTo(ctx, st, theauth.ImportedWebAuthnCredential{
        UserID: u.ID, CredentialID: credID, PublicKey: cosePub, SignCount: 7, Transports: []string{"internal"},
    })
}
if err != nil {
    return err
}
return tx.Commit()

• Build a short-lived Store with NewTx and use the package-level Import*To helpers. Do not build a TheAuth on it: a TheAuth outlives the transaction. (*TheAuth).ImportUser, ImportTOTPSecret, ImportWebAuthnCredential and ImportAPIToken exist for the case where you import through a live instance.
• ImportTOTPSecretTo takes the plaintext base32 secret and encrypts it with the same key as enrollment, so pass Config.EncryptionKey. The secret is never logged.
• Recovery codes are not importable. Their hashes are salted by the library, so imported users must regenerate them.
• Password hashes are stored verbatim. Bcrypt hashes verify only when PasswordPolicy.AllowLegacyBcrypt is set.
• Every helper validates input and returns ErrImportInvalid (wrapped) for bad data and ErrImportDuplicate when the record exists, so a re-run can skip what is already imported.
• Migrate takes a *sql.DB and cannot run inside a transaction. SweepExpired works on a transaction-bound Store but belongs on a normal Store after the commit. Stop using the Store once the transaction ends.

​

Shared login throttle

Config.LoginThrottle = &theauth.LoginThrottleConfig{Store: store.ThrottleStore()}

​

Upgrading

​

Behavior notes

• Email uniqueness is case-insensitive (COLLATE NOCASE, ASCII folding). A duplicate returns the driver’s constraint error wrapped with context.
• IDs are stored as 26 character ULID text, timestamps as UTC unix microseconds.
• Consuming a magic link, reset token or session link, and claiming a device code, is one UPDATE ... RETURNING, so concurrent consumers produce exactly one winner. AdvanceTOTPStep is one conditional upsert.
• Audit rows carry no foreign keys: the log is append-only and survives deletion of the user it describes.
• UpdateWebAuthnSignCount returns ErrReplayDetected for a non-increasing count.