@aizvi/auth-sqlite

A SQLite storage adapter for @aizvi/auth. Point it at a .sqlite file, or a database connection you already have, and your auth system has somewhere to store users and sessions. No separate database server needed.

It creates its own users and mobile_auth_sessions tables the first time it runs, and never touches anything else in your database.

Install

npm install @aizvi/auth @aizvi/auth-sqlite
pnpm add @aizvi/auth @aizvi/auth-sqlite
yarn add @aizvi/auth @aizvi/auth-sqlite
bun add @aizvi/auth @aizvi/auth-sqlite

Quick start

The easiest way to use this package: give it a file path, and it opens (or creates) the database for you.

import { createAuthRouter } from '@aizvi/auth';
import { sqliteAdapter } from '@aizvi/auth-sqlite';

app.use(
  '/auth',
  createAuthRouter({
    adapter: sqliteAdapter({ file: './data.sqlite' }),
    mailer: myEmailSender,
    jwtSecret: process.env.JWT_SECRET!,
  })
);

The file, and any missing parent directories, is created automatically if it doesn't already exist, with journal_mode = WAL and foreign_keys = ON set for you.

Which driver does this use?

sqliteAdapter() picks the right driver automatically based on where your code is running, so you never have to think about it:

• Running under Bun: uses Bun's built in bun:sqlite.
• Running under Node.js: uses better-sqlite3.

Same function, same behavior, either way. Nothing in your code needs to change if you switch runtimes.

Already have a SQLite connection? Use that instead

If your app already opens its own SQLite connection, for example with Node's built in node:sqlite, or a better-sqlite3 instance configured with your own options, you don't need sqliteAdapter() to open a second one. Hand your existing connection straight to createSqliteAuthAdapter() instead:

import { DatabaseSync } from 'node:sqlite';
import { createAuthRouter } from '@aizvi/auth';
import { createSqliteAuthAdapter } from '@aizvi/auth-sqlite';

const db = new DatabaseSync('./data.sqlite'); // the connection your app already uses
db.exec('PRAGMA journal_mode = WAL');
db.exec('PRAGMA foreign_keys = ON');

app.use(
  '/auth',
  createAuthRouter({
    adapter: createSqliteAuthAdapter(db),
    mailer: myEmailSender,
    jwtSecret: process.env.JWT_SECRET!,
  })
);

This works with any driver that can exec() SQL, prepare() a statement, and run() or get() it, which covers node:sqlite, better-sqlite3, and bun:sqlite. That way you only ever have one open connection to your database, shared between your auth tables and everything else your app stores.

If you're adding auth to an app that already has its own users table with the same columns this package expects (see Schema below), createSqliteAuthAdapter() simply uses it as is. It only creates the tables if they don't already exist.

More examples

Using better-sqlite3 with your own options, instead of the defaults sqliteAdapter() picks:

import Database from 'better-sqlite3';
import { createSqliteAuthAdapter } from '@aizvi/auth-sqlite';

const db = new Database('./data.sqlite', { timeout: 5000, readonly: false });
db.pragma('journal_mode = WAL');

const adapter = createSqliteAuthAdapter(db);

Sharing one connection between auth and the rest of your app:

import { DatabaseSync } from 'node:sqlite';
import { createSqliteAuthAdapter } from '@aizvi/auth-sqlite';

// Your app's single, shared database connection, used everywhere.
export const db = new DatabaseSync('./data.sqlite');
db.exec('PRAGMA journal_mode = WAL');
db.exec('PRAGMA foreign_keys = ON');

// Your own tables, created however you already do it.
db.exec('CREATE TABLE IF NOT EXISTS posts (id TEXT PRIMARY KEY, title TEXT NOT NULL)');

// The auth adapter reuses the exact same connection and file.
export const authAdapter = createSqliteAuthAdapter(db);

Running the migration yourself, ahead of time:

import { DatabaseSync } from 'node:sqlite';
import { migrate, createSqliteAuthAdapter } from '@aizvi/auth-sqlite';

const db = new DatabaseSync('./data.sqlite');
migrate(db); // creates users / mobile_auth_sessions if they don't exist yet

// ...later, once you're ready to build the router:
const adapter = createSqliteAuthAdapter(db);

Schema

On first use, this creates:

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  is_verified INTEGER NOT NULL DEFAULT 0,
  verification_code TEXT,
  verification_expires TEXT,
  reset_code TEXT,
  reset_expires TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS mobile_auth_sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id),
  refresh_token_hash TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  revoked_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

Only the SHA-256 hash of a refresh token is ever stored, never the raw token itself.

API

sqliteAdapter(options)

| Option | Required | Description | | ------ | -------- | ----------- | | file | yes | Path to the SQLite file. Created automatically, including parent directories, if it doesn't exist. |

Returns an adapter ready to pass to createAuthRouter. Also has a .close() method if you need to close the connection manually. Most apps never need to call this.

createSqliteAuthAdapter(db)

Takes an already open database connection (anything implementing exec, prepare().run()/.get(), and close(); see src/driver.ts for the exact minimal interface) and returns the same kind of adapter.

migrate(db)

Runs the table creation step on its own, if you want to control exactly when it happens rather than letting sqliteAdapter/createSqliteAuthAdapter run it for you automatically.

Code of Conduct

See CODE_OF_CONDUCT.md.

License

MIT (see license.txt)