DOCSEARCHbyAlgolia
Submit Docs

SQLite AI Extension

repository·main·Indexed 40 days ago

https://github.com/sqliteai/sqlite-ai

An extension that brings on-device LLM inference, embeddings, audio transcription (Whisper), and vision capabilities directly into SQLite. It allows developers to run AI models via GGUF locally and privately using standard SQL queries. Supports Node.js (@sqliteai/sqlite-ai v0.7.56), Python (sqlite-ai), Flutter/Dart (sqlite_ai), Android, and Swift.

Tokens
12.2K
Snippets
48
Records
55
Agent score
46%

What's inside SQLite AI

  1. What is SQLite-AI?

    main
    SQLite-AI is an extension for SQLite that brings artificial intelligence capabilities directly into the database. It allows developers to run, fine-tune, and serve AI models using standard SQL queries. This is specifically designed for on-device and edge applications where low-latency and offline inference are required.

  2. Understand the SQLite-AI lifecycle and resource management

    main

    A SQLite-AI connection manages four independent resources: a model, an inference context (which owns the KV cache), a sampler, and a chat (the message history). Releasing one does not necessarily release the others, allowing for granular control over memory.

    Resource Release Mapping

    Function CallModelContext / KV CacheSamplerChat History
    llm_model_load() (again)replacedfreedfreedkept
    llm_model_free()freedfreedfreedkept
    llm_context_free()keptfreedkeptkept
    llm_chat_free()keptcache clearedkeptfreed
    closing the connectionfreedfreedfreedfreed

    Key Behaviors

    • Conversations survive model switches: Because chat history is not tied to a model, loading a new model via llm_model_load() will cause the next llm_chat_respond() to replay the entire transcript through the new model's template. To start a fresh conversation when switching models, call llm_chat_free() first.
    • Samplers are tied to models: A sampler cannot outlive a model. If you load a new model, you must rebuild your sampler chain. If you do not configure one, a default is created automatically.
    -- Example: Switching models while clearing old history and cache
SELECT llm_chat_save('before switching model'); -- optional: keep a copy
SELECT llm_chat_free();                         -- drop history and clear the cache
SELECT llm_model_load('/path/to/other-model.gguf');
SELECT llm_context_create_chat('context_size=4096');
SELECT llm_sampler_create();                    -- rebuild sampler after model load
SELECT llm_sampler_init_temp(0.8);
SELECT llm_sampler_init_dist(42);
SELECT llm_chat_respond('Hello');

  3. Understand the difference between token-level and sentence-level embeddings

    main

    The number and type of embeddings returned depend on the pooling_type configured in llama_context_params:

    1. Token-level embeddings (LLAMA_POOLING_TYPE_NONE):

      • Returns an embedding for every token in the input.
      • Retrieve using llama_get_embeddings(ctx).
      • Resulting shape is effectively a matrix of (tokens × dimension).

    2. Sentence-level (pooled) embeddings (LLAMA_POOLING_TYPE_MEAN, LLAMA_POOLING_TYPE_CLS, or LLAMA_POOLING_TYPE_LAST):

      • Returns exactly one embedding per sequence ID (seq_id).
      • Retrieve using llama_get_embeddings_seq(ctx, seq_id).
      • Useful for semantic search and sentence similarity tasks.

  4. Load the sqlite-ai extension in Python

    main

    To use AI capabilities in your SQL queries, you must load the sqlite-ai extension into your SQLite connection. This involves enabling extension loading, locating the binary via importlib.resources, and then loading it.

    Note: If you encounter issues, some operating systems disable extension loading by default. You may need to follow platform-specific instructions to enable it.

    import importlib.resources
import sqlite3

# Connect to your SQLite database
conn = sqlite3.connect("example.db")

# Load the sqlite-ai extension
# Use 'sqliteai.binaries.cpu' or 'sqliteai.binaries.gpu'
ext_path = importlib.resources.files("sqliteai.binaries.cpu") / "ai"

conn.enable_load_extension(True)
conn.load_extension(str(ext_path))
conn.enable_load_extension(False)

# Verify installation
print(conn.execute("SELECT ai_version();").fetchone())

  5. Load the SQLite AI extension in Node.js

    main

    To use the AI capabilities, you must load the extension into your SQLite database instance. Use getExtensionPath() from @sqliteai/sqlite-ai to retrieve the correct binary path for your current platform, then pass it to your SQLite driver's extension loading method (e.g., db.loadExtension() in better-sqlite3).

    import { getExtensionPath } from '@sqliteai/sqlite-ai';
import Database from 'better-sqlite3';

const db = new Database(':memory:');
db.loadExtension(getExtensionPath());

// Verify installation by checking the AI extension version
const version = db.prepare('SELECT ai_version()').pluck().get();
console.log('AI extension version:', version);

  6. Install the sqlite-ai Python package

    main

    The sqlite-ai Python package provides prebuilt binaries for the SQLite-AI extension across multiple platforms and architectures. When you install the package via pip, it automatically selects the correct binary package for your platform and architecture. You can choose between the CPU or GPU variant depending on your hardware requirements.

    pip install sqlite-ai

  9. Recover from a full context error

    main

    When a chat fills its context, llm_chat_respond() returns Context size exceeded. The conversation remains usable, but you must make room to continue. You have three options:

    1. Start over: Call llm_chat_free() to drop the history and clear the cache.
    2. Enlarge the context: Call llm_context_free() followed by llm_context_create_chat('context_size=...') with a larger window. The history is preserved and replayed into the new context.
    3. Compact the history: Since history is stored in standard tables, you can use SQL to trim old messages and then restore the history.

    Compaction Example

    To keep only the most recent 8 messages:

    SELECT llm_chat_save('before compaction');
DELETE FROM ai_chat_messages
 WHERE id NOT IN (SELECT id FROM ai_chat_messages ORDER BY id DESC LIMIT 8);
SELECT llm_chat_restore((SELECT uuid FROM ai_chat_history ORDER BY id DESC LIMIT 1));

  10. Install SQLite-AI

    main

    SQLite-AI can be installed via pre-built binaries or platform-specific packages:

    Pre-built Binaries

    Download binaries for Linux (x86/ARM), macOS (x86/ARM), Windows (x86), Android, or iOS from the official Releases page.

    Platform-Specific Installation

    • Python: Install via PyPI using pip install sqlite-ai.
    • Flutter/Dart: Use flutter pub add sqlite_ai or dart pub add sqlite_ai.
    • Android: Add ai.sqlite:ai:0.7.55 to your Gradle dependencies.
    • Swift: Add the repository as a package dependency in Xcode.
    pip install sqlite-ai

  11. Load the SQLite-AI extension

    main

    Once the binary is available, you must load the extension into your SQLite session. You can do this via the SQLite CLI dot-command or via a SQL statement.

    -- In SQLite CLI
.load ./ai

-- In SQL
SELECT load_extension('./ai');

  12. How to generate sentence-level embeddings in llama.cpp

    main

    By default, llama.cpp can produce embeddings per token. To force the generation of a single embedding vector for an entire text input (sentence embedding), you must enable a pooling mode in the llama_context_params when creating the context.

    When pooling is enabled, you must use llama_get_embeddings_seq(ctx, seq_id) to retrieve the resulting vector instead of llama_get_embeddings(ctx).

    struct llama_context_params cparams = llama_context_default_params();

// Enable pooling to get one embedding per sequence
cparams.pooling_type = LLAMA_POOLING_TYPE_MEAN; 

struct llama_context *ctx = llama_new_context_with_model(model, cparams);

// ... after llama_encode() or llama_decode() ...

// Retrieve the single sentence-level embedding
const float *embedding = llama_get_embeddings_seq(ctx, 0);