@riligar/elysia-sqlite

An open source project by RiLiGar

A powerful Elysia plugin for SQLite database management with a beautiful built-in UI dashboard. Designed for Bun runtime.

✨ Features

• 📊 Smart DataGrid — Interactive table with pagination, sorting, and inline editing
• 🔗 Foreign Key Preview — Intelligently resolves and displays foreign key relationships
• 🔐 Secure Admin — Built-in authentication with session management and 2FA/TOTP
• ⚡ Zero Config — Auto-detects database schema and provides instant CRUD interface
• 📤 Easy Export — Export your data to CSV or JSON with a single click
• 🧭 Guided Onboarding — Simple setup wizard for initial configuration
• 🤖 AI SQL — Generate SQL queries using natural language (requires OpenRouter key)

📦 Installation

bun add @riligar/elysia-sqlite

Peer Dependencies

bun add elysia

🚀 Quick Start

import { Elysia } from "elysia";
import { sqliteAdmin } from "@riligar/elysia-sqlite";

const app = new Elysia()
  .use(
    sqliteAdmin({
      dbPath: "demo.db",
      prefix: "/sqlite", // Optional: defaults to /sqlite
    })
  )
  .listen(3000);

console.log("🦊 Server running at http://localhost:3000");
console.log("📊 Admin Dashboard at http://localhost:3000/sqlite");

On first run, navigate to /sqlite (or your configured prefix) to start the onboarding wizard and configure your admin credentials.

⚙️ Configuration

Plugin Options

These options are passed to the sqliteAdmin plugin at initialization.

Option	Type	Default	Description
dbPath	string	Required	Path to the SQLite database file
prefix	string	"/sqlite"	URL prefix for the admin dashboard and API
configPath	string	Same directory as dbPath	Path to save the runtime authentication config (JSON). Defaults to sqlite-config.json in the same directory as your database file

Runtime Configuration (via UI)

The following settings are managed via the Settings tab in the dashboard and stored in the JSON file defined by configPath.

Option	Description
username	Admin username for accessing the dashboard
password	Admin password (stored in plain text in config file - protect this file!)
totpSecret	Secret key for Two-Factor Authentication (managed automatically)

Note: The configuration file contains sensitive credentials. Ensure it is included in your .gitignore if necessary or secured appropriately in production environments.

Cloud Deployment (Fly.io, Railway, etc.)

When deploying to cloud platforms with ephemeral filesystems, ensure your database and config files are stored on a persistent volume.

Fly.io Example:

# fly.toml
[mounts]
  source = "data"
  destination = "/data"

// Your app
sqliteAdmin({
  dbPath: "/data/app.db",
  // configPath automatically uses /data/sqlite-config.json
});

The config file is automatically stored alongside your database, so both will persist across deployments when using the same volume.

🔌 API Endpoints

The plugin adds the following routes under your configured prefix (default /sqlite):

Authentication

Method	Path	Description
POST	/auth/login	Authenticate user (username, password, 2FA code)
POST	/auth/logout	End session
GET	/auth/status	Check if system is configured and user authenticated
POST	/api/setup	Initial setup (create admin credentials)

Data Operations

Method	Path	Description
GET	/api/tables	List all tables in database
GET	/api/table/:name/rows	Get rows for a table (pagination support)
POST	/api/table/:name/insert	Insert new record
POST	/api/table/:name/update	Update record (inline edit)
POST	/api/table/:name/delete	Delete record
GET	/api/table/:name	Get table schema (columns and foreign keys)

Advanced Features

Method	Path	Description
POST	/api/query	Execute raw SQL query
POST	/api/ai/sql	Generate SQL from natural language prompt
POST	/api/resolve-fk	Resolve IDs to display labels for foreign keys
GET	/api/meta/schema	Get full database schema for ERD

🤝 Contributing

Contributions are welcome! See our GitHub repository for more information.

📄 License

MIT © RiLiGar

Made with ❤️ by RiLiGar