Skip to content

Latest commit

 

History

History
369 lines (256 loc) · 8.23 KB

USAGE.md

File metadata and controls

369 lines (256 loc) · 8.23 KB

Usage Guide

This guide explains how to use libsqlite in practice, from first deployment to daily operations.

1. What you are running

You are running a self-hosted control plane that manages:

  • SQLite files on disk
  • existing .db files imported from your server
  • .db files discovered from a mounted directory
  • remote libsql databases registered by URL and token

The control plane stores metadata, permissions, sessions, audit logs and migration history.

2. First deployment

Backend

  1. Copy backend/.env.example to backend/.env.
  2. Set MASTER_KEY with 64 hex characters.
  3. Set the storage and discovery paths.
  4. Start the backend.

Example:

cd backend
npm install
npm run build
npm run start

Frontend

  1. Copy frontend/.env.example to frontend/.env.
  2. Set NEXT_PUBLIC_API_URL.
  3. Start the frontend.

Example:

cd frontend
npm install
npm run build
npm run start

3. Create the first administrator

The backend exposes:

  • POST /api/v1/auth/register

Create the first user by sending email and password.

Example:

curl -X POST http://localhost:3000/api/v1/auth/register \
  -H "Content-Type: application/json" \
  -d '{"email":"admin@example.com","password":"StrongPassword123!"}'

The response returns:

  • user information
  • accessToken
  • refreshToken

Save the access token in the frontend or in your client.

4. Login

Use the same endpoint for normal authentication:

  • POST /api/v1/auth/login

Example:

curl -X POST http://localhost:3000/api/v1/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"admin@example.com","password":"StrongPassword123!"}'

5. Create a project

Projects are the ownership boundary for databases.

Endpoint:

  • POST /api/v1/projects

Example:

curl -X POST http://localhost:3000/api/v1/projects \
  -H "Authorization: Bearer <accessToken>" \
  -H "Content-Type: application/json" \
  -d '{"name":"Billing"}'

Why projects matter:

  • keep databases grouped by application or client
  • simplify permissions
  • simplify backup and filesystem layout

6. Create a new SQLite database

Endpoint:

  • POST /api/v1/databases

Example:

curl -X POST http://localhost:3000/api/v1/databases \
  -H "Authorization: Bearer <accessToken>" \
  -H "Content-Type: application/json" \
  -d '{
    "projectId":"<projectId>",
    "name":"billing-db",
    "type":"libsql",
    "primaryMode":"runtime"
  }'

What happens:

  • the control plane creates a metadata record
  • a dedicated libsql-server runtime or shared sqld namespace is provisioned
  • a subdomain and libsql:// URL are generated
  • the database becomes active and returns its JWT

With LIBSQL_ALLOW_LOCAL_FILE=false, type=sqlite is rejected. Legacy LOCAL-FILE databases can be migrated with migrate:local-to-primary.

Managed path examples:

# Dedicated runtime
data/sqlite/projects/<projectId>/databases/<databaseId>/dbs/default/data

# Shared sqld namespace
/app/data/sqld/data/dbs/<namespace>/data

7. Import an existing SQLite database

Use this when you already have a .db file on the server.

Endpoint:

  • POST /api/v1/databases/import-sqlite

Example:

curl -X POST http://localhost:3000/api/v1/databases/import-sqlite \
  -H "Authorization: Bearer <accessToken>" \
  -H "Content-Type: application/json" \
  -d '{
    "projectId":"<projectId>",
    "name":"legacy-db",
    "sourcePath":"/srv/uploads/legacy.db",
    "storageMode":"runtime"
  }'

Use storageMode=namespace for the shared sqld primary, or auto to follow LIBSQL_PRIMARY_MODE. Under the libSQL-first policy, imports without a primary fail instead of creating a legacy LOCAL-FILE database.

What happens:

  • the file is copied into a libSQL runtime or namespace
  • uploaded files use a direct copy; live server paths use VACUUM INTO
  • the database is registered with a libsql:// URL and JWT
  • the original source file is left untouched
  • public route health is checked in the background after the primary is ready

For large databases, prefer the Server path mode. Uploading depends on your connection speed and is limited by MAX_UPLOAD_SIZE_BYTES (default 500 MB). The panel compresses the file in the browser before uploading when the browser supports CompressionStream; SQLite files usually shrink significantly. The backend enforces the limit against the decompressed size and rejects invalid gzip streams. Uploads are staged under IMPORT_STAGING_DIR (default <data>/tmp) so they do not cross filesystems.

8. Discover existing .db files from a mounted folder

Under the libSQL-first policy, discovery is skipped because it would create legacy LOCAL-FILE records. Import files with storageMode=auto instead, or use the hidden LIBSQL_ALLOW_LOCAL_FILE=true escape hatch.

Use this when Coolify or your server mounts a folder containing SQLite files.

Configure:

  • SQLITE_DISCOVERY_PATH
  • SQLITE_DISCOVERY_PROJECT_ID
  • optional SQLITE_DISCOVERY_ADOPT=true

Endpoint:

  • POST /api/v1/discovery/sqlite/scan

Example:

curl -X POST http://localhost:3000/api/v1/discovery/sqlite/scan \
  -H "Authorization: Bearer <accessToken>" \
  -H "Content-Type: application/json" \
  -d '{
    "projectId":"<projectId>",
    "rootPath":"/app/data/sqlite",
    "adopt":false
  }'

Two modes exist:

Discover only

  • records the file in metadata
  • keeps the file where it already is

Discover and adopt

  • copies the file into the managed storage tree
  • keeps all managed databases in one consistent hierarchy

Endpoint for adoption:

  • POST /api/v1/discovery/sqlite/adopt

Example:

curl -X POST http://localhost:3000/api/v1/discovery/sqlite/adopt \
  -H "Authorization: Bearer <accessToken>" \
  -H "Content-Type: application/json" \
  -d '{
    "projectId":"<projectId>",
    "rootPath":"/app/data/sqlite"
  }'

9. Register a libsql remote database

Use this for a remote libsql or Turso-compatible endpoint.

Endpoint:

  • POST /api/v1/provisioning/libsql

Example:

curl -X POST http://localhost:3000/api/v1/provisioning/libsql \
  -H "Authorization: Bearer <accessToken>" \
  -H "Content-Type: application/json" \
  -d '{
    "projectId":"<projectId>",
    "name":"remote-app-db",
    "url":"libsql://example.turso.io",
    "token":"<libsql-token>"
  }'

10. Inspect schema

Endpoint:

  • GET /api/v1/databases/:id/schema

This shows:

  • table names
  • columns
  • foreign keys

11. Run queries

Endpoint:

  • POST /api/v1/databases/:id/query

Example:

curl -X POST http://localhost:3000/api/v1/databases/<dbId>/query \
  -H "Authorization: Bearer <accessToken>" \
  -H "Content-Type: application/json" \
  -d '{
    "sql":"SELECT * FROM users LIMIT 10",
    "params":[]
  }'

Rules:

  • read queries are allowed for SQLite through the read path
  • writes are allowed through the managed API with permission checks

12. Apply migrations

Endpoint:

  • POST /api/v1/databases/:id/migrations

Example:

curl -X POST http://localhost:3000/api/v1/databases/<dbId>/migrations \
  -H "Authorization: Bearer <accessToken>" \
  -H "Content-Type: application/json" \
  -d '{
    "name":"create-users-table",
    "sql":"CREATE TABLE users (id TEXT PRIMARY KEY, email TEXT NOT NULL);"
  }'

This is the recommended way to run schema changes from your codebase or CI.

13. Rotate tokens

Use:

  • PATCH /api/v1/databases/:id/rotate-token

This is useful when:

  • a credential may have been exposed
  • a database is moving between environments
  • you want to refresh access regularly

14. Audit and monitoring

Use:

  • GET /api/v1/audit
  • GET /health
  • GET /ready

These endpoints help you verify:

  • what changed
  • whether the API is alive
  • whether the database layer is ready

15. Recommended operational workflow

  1. Create a project.
  2. Create a SQLite database or import an existing one.
  3. Or register a libsql endpoint.
  4. Apply migrations from your code or CI.
  5. Inspect schema and run queries when needed.
  6. Use the audit log for traceability.
  7. Backup the control-plane database and managed SQLite files.

16. Common mistakes

  • forgetting to set MASTER_KEY
  • mounting a directory but not setting SQLITE_DISCOVERY_PATH
  • trying to manage a remote database without registering URL and token
  • assuming the backend can guess arbitrary files on another machine