Alumna SQLite Adapter

The official SQLite database adapter for the Alumna Backend Framework.

This adapter provides a seamless, high-performance bridge between Alumna's strict Schemas and SQLite. It features zero-allocation JSON streaming for nested fields, native dot-notation querying for JSON columns, and absolute security against SQL injection through strict compile-time and runtime schema validation.

---

Table of Contents

1. Installation
2. Quick Start (Step-by-Step)
3. Type Mapping
4. Querying and Filtering
5. Querying Nested JSON Fields
6. Security & Performance
7. Testing
8. License

---

1. Installation

Add the dependency to your shard.yml:

dependencies:
  alumna:
    github: alumna/backend
  alumna-sqlite:
    github: alumna/sqlite

Run shards install.

---

2. Quick Start (Step-by-Step)

Let's build a simple API for a Products catalogue.

Step 1: Create your SQLite Table

Alumna requires an auto-incrementing primary key named id. For nested objects or arrays, use a standard TEXT column to store JSON.

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  price REAL NOT NULL,
  is_published INTEGER DEFAULT 0,
  tags TEXT,      -- Will store a JSON Array
  metadata TEXT   -- Will store a JSON Object
);

Step 2: Define your Alumna Schema

Your schema acts as the absolute source of truth. The adapter will use it to automatically cast types back and forth between SQLite and Crystal.

require "alumna"
require "alumna-sqlite"
require "db"

ProductSchema = Alumna::Schema.new
  .str("title", min_length: 2)
  .float("price")
  .bool("is_published", required: false)
  .array("tags", of: :str, required: false)
  .hash("metadata", required: false) do |m|
    m.str("manufacturer")
    m.int("warranty_years")
  end

Step 3: Mount the Adapter

Connect to your database and mount the service to your Alumna app.

# Open the SQLite database connection
DB_CONNECTION = DB.open("sqlite3://./my_database.db")

app = Alumna::App.new

# Instantiate the adapter with the DB, table name, and schema
product_service = Alumna::SqliteAdapter.new(DB_CONNECTION, "products", ProductSchema)

# Mount it to the router
app.use("/products", product_service)

app.listen(3000)

That's it! You now have a fully functional REST API with GET, POST, PUT, PATCH, and DELETE wired directly to your SQLite database.

---

3. Type Mapping

The adapter automatically translates Alumna FieldTypes to SQLite column types. You do not need to write manual parsers.

Alumna Schema Type	SQLite Column Type	Notes
.str	TEXT
.int	INTEGER
.float	REAL
.bool	INTEGER	SQLite lacks a native boolean. Stores as 1 or 0.
.time	TEXT	Stores as strict ISO8601 strings (RFC 3339).
.bytes	BLOB	Native binary blob streaming.
.hash / .array	TEXT	Automatically serialized/deserialized to JSON strings.

---

4. Querying and Filtering

Because the adapter is deeply integrated with Alumna's Query engine, you get advanced filtering via URL query parameters out of the box.

Exact Match:

GET /products?is_published=true

Comparison Operators ($gt, $gte, $lt, $lte, $ne):

GET /products?price[$gt]=50.00&price[$lte]=199.99

Lists ($in, $nin):

GET /products?title[$in]=Laptop,Mouse,Keyboard

Sorting, Pagination, and Field Selection:

GET /products?$sort=price:-1&$limit=10&$skip=20&$select=id,title,price

---

5. Querying Nested JSON Fields

SQLite has incredibly powerful native JSON functions, and this adapter takes full advantage of them. You can filter and sort by deeply nested data using dot-notation, directly in your URL.

Given our ProductSchema above, which has a nested metadata hash:

Filter by a nested field:

GET /products?metadata.manufacturer=AcmeCorp

Sort by a nested field:

GET /products?$sort=metadata.warranty_years:-1

Filter inside a JSON Array:

GET /products?tags=electronics

(The adapter automatically detects that tags is an array in your schema and securely compiles an EXISTS (SELECT 1 FROM json_each(...) ) SQLite query behind the scenes!)

---

6. Security & Performance

100% SQL Injection Proof

Most frameworks are vulnerable to SQL injection if you allow dynamic column sorting or nested JSON querying.

Alumna::SqliteAdapter prevents this natively. Every single field name, dot-notation path, and sort directive is strictly validated against your Alumna Schema definition before it ever touches the SQL builder. If a client sends a malicious query like ?metadata.invalid_field'; DROP TABLE...=1, the adapter instantly rejects it because invalid_field doesn't exist in the schema.

Zero-Allocation JSON Streaming

When reading or writing complex nested Hash or Array fields, the adapter bypasses intermediate memory allocations by using Alumna's core JsonHelper. Data streams directly from the SQLite driver into Alumna's native types, keeping your memory footprint flat even under heavy load.

Pre-compiled Queries

Static queries (GET /:id, PUT, DELETE) are pre-compiled into strings exactly once when the server boots. This completely eliminates SQL string-builder allocations on standard endpoints.

---

7. Testing

If you are developing your own Alumna Database Adapter (like Postgres or MySQL) based on this one, Alumna provides an automated Adapter Compliance Suite.

It runs dozens of edge-cases against your database to guarantee it behaves identically to the built-in MemoryAdapter.

require "alumna/testing"
require "alumna-sqlite"
require "db"

SHARED_DB = DB.open("sqlite3://./test.db")

# This one macro runs the entire Alumna compliance test suite 
# against your custom database!
Alumna::Testing::AdapterSuite.run("Alumna::SqliteAdapter") do
  
  SHARED_DB.exec("DROP TABLE IF EXISTS adapter_test")
  SHARED_DB.exec("CREATE TABLE adapter_test ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, price REAL )")

  schema = Alumna::Schema.new(strict: false)
    .str("title")
    .float("price")

  Alumna::SqliteAdapter.new(SHARED_DB, "adapter_test", schema)
end

---

8. License

MIT