@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

``bash
bun add @riligar/elysia-sqlite
`

$3

`bash
bun add elysia
`

🚀 Quick Start

`javascript
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

$3

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 |

$3

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.

$3

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

Fly.io Example:

`toml


fly.toml


[mounts]
source = "data"
destination = "/data"
`

`javascript
// 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):

$3

| 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) |

$3

| 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) |

$3

| 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