ecto_libsql - Comprehensive Developer Guide (Application Usage)

Purpose: Guide for AI agents helping developers USE ecto_libsql in their applications

⚠️ IMPORTANT: This guide is for using ecto_libsql in your applications.
🔧 For developing/maintaining the ecto_libsql library itself, see CLAUDE.md instead.

Welcome to ecto_libsql! This guide provides comprehensive documentation, API reference, and practical examples for building applications with LibSQL/Turso in Elixir using the Ecto adapter.

ℹ️ About This Guide

USAGE.md is the application usage guide for developers building apps with ecto_libsql. It covers:

• How to integrate ecto_libsql into your Elixir/Phoenix application
• Configuration and connection management
• Ecto schemas, migrations, and queries
• Advanced features (vector search, R*Tree spatial indexing, encryption, batching)
• Real-world usage examples and patterns
• Performance optimisation for your applications

If you're working ON the ecto_libsql codebase itself (contributing, fixing bugs, adding features), see CLAUDE.md for internal development documentation.

Table of Contents

• Quick Start
• Connection Management
• Basic Operations
• Advanced Features
  • Transactions
  • Prepared Statements
  • Batch Operations
  • Cursor Streaming
  • Connection Management
  • PRAGMA Configuration
  • Vector Search
  • R*Tree Spatial Indexing
  • Encryption
• Ecto Integration
  • Quick Start with Ecto
  • Schemas and Changesets
  • Migrations
  • Queries
  • Associations
  • Transactions
  • Phoenix Integration
  • Production Deployment
  • Limitations and Known Issues
• API Reference
• Real-World Examples
• Performance Guide
• Error Handling
• Troubleshooting

Quick Start

Installation

Add to your mix.exs:

def deps do
  [
    {:ecto_libsql, "~> 0.8.0"}
  ]
end

Your First Query

# Connect to a local database
{:ok, state} = EctoLibSql.connect(database: "myapp.db")

# Create a table
{:ok, _, _, state} = EctoLibSql.handle_execute(
  "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)",
  [],
  [],
  state
)

# Insert data
{:ok, _, _, state} = EctoLibSql.handle_execute(
  "INSERT INTO users (name, email) VALUES (?, ?)",
  ["Alice", "alice@example.com"],
  [],
  state
)

# Query data
{:ok, _query, result, _state} = EctoLibSql.handle_execute(
  "SELECT * FROM users WHERE name = ?",
  ["Alice"],
  [],
  state
)

IO.inspect(result)
# %EctoLibSql.Result{
#   columns: ["id", "name", "email"],
#   rows: [[1, "Alice", "alice@example.com"]],
#   num_rows: 1
# }

Connection Management

EctoLibSql supports three connection modes, each optimised for different use cases.

Local Mode

Perfect for embedded databases, development, and single-instance applications.

opts = [database: "local.db"]
{:ok, state} = EctoLibSql.connect(opts)

Use cases:

• Development and testing
• Embedded applications
• Single-instance desktop apps
• SQLite migration projects

Remote Mode

Direct connection to Turso for globally distributed databases.

opts = [
  uri: "libsql://my-database.turso.io",
  auth_token: System.get_env("TURSO_AUTH_TOKEN")
]
{:ok, state} = EctoLibSql.connect(opts)

Use cases:

• Cloud-native applications
• Multi-region deployments
• Serverless functions
• High availability requirements

Remote Replica Mode

Best of both worlds: local performance with remote synchronisation.

opts = [
  uri: "libsql://my-database.turso.io",
  auth_token: System.get_env("TURSO_AUTH_TOKEN"),
  database: "replica.db",
  sync: true  # Auto-sync on writes
]
{:ok, state} = EctoLibSql.connect(opts)

Use cases:

• Read-heavy workloads
• Edge computing
• Offline-first applications
• Mobile backends

WebSocket vs HTTP

For lower latency, use WebSocket protocol:

# HTTP (default)
opts = [uri: "https://my-database.turso.io", auth_token: token]

# WebSocket (lower latency, multiplexing)
opts = [uri: "wss://my-database.turso.io", auth_token: token]

WebSocket benefits:

• ~30-50% lower latency
• Better connection pooling
• Multiplexed queries
• Real-time updates

Connection with Encryption

Encrypt local databases and replicas:

opts = [
  database: "secure.db",
  encryption_key: "your-32-char-encryption-key-here"
]
{:ok, state} = EctoLibSql.connect(opts)

Security notes:

• Uses AES-256-CBC encryption
• Encryption key must be at least 32 characters
• Store keys in environment variables or secret managers
• Works with both local and replica modes

Basic Operations

INSERT

# Single insert
{:ok, _, result, state} = EctoLibSql.handle_execute(
  "INSERT INTO users (name, email) VALUES (?, ?)",
  ["Bob", "bob@example.com"],
  [],
  state
)

# Get the inserted row ID
rowid = EctoLibSql.Native.get_last_insert_rowid(state)
IO.puts("Inserted row ID: #{rowid}")

# Check how many rows were affected
changes = EctoLibSql.Native.get_changes(state)
IO.puts("Rows affected: #{changes}")

UPSERT (INSERT ... ON CONFLICT)

EctoLibSql supports all Ecto on_conflict options for upsert operations:

# Ignore conflicts (do nothing on duplicate key)
{:ok, user} = Repo.insert(changeset,
  on_conflict: :nothing,
  conflict_target: [:email]
)

# Replace all fields on conflict
{:ok, user} = Repo.insert(changeset,
  on_conflict: :replace_all,
  conflict_target: [:email]
)

# Replace specific fields only
{:ok, user} = Repo.insert(changeset,
  on_conflict: {:replace, [:name, :updated_at]},
  conflict_target: [:email]
)

# Replace all except specific fields
{:ok, user} = Repo.insert(changeset,
  on_conflict: {:replace_all_except, [:id, :inserted_at]},
  conflict_target: [:email]
)

# Query-based update with keyword list syntax
{:ok, user} = Repo.insert(changeset,
  on_conflict: [set: [name: "Updated Name", updated_at: DateTime.utc_now()]],
  conflict_target: [:email]
)

# Increment counter on conflict
{:ok, counter} = Repo.insert(counter_changeset,
  on_conflict: [inc: [count: 1]],
  conflict_target: [:key]
)

Notes:

• :conflict_target is required for LibSQL/SQLite (unlike PostgreSQL)
• Composite unique indexes work: conflict_target: [:slug, :parent_slug]
• Named constraints (ON CONFLICT ON CONSTRAINT name) are not supported

SELECT

# Simple select
{:ok, _, result, state} = EctoLibSql.handle_execute(
  "SELECT * FROM users",
  [],
  [],
  state
)

Enum.each(result.rows, fn [id, name, email] ->
  IO.puts("User #{id}: #{name} (#{email})")
end)

# Parameterised select
{:ok, _, result, state} = EctoLibSql.handle_execute(
  "SELECT name, email FROM users WHERE id = ?",
  [1],
  [],
  state
)

UPDATE

{:ok, _, result, state} = EctoLibSql.handle_execute(
  "UPDATE users SET email = ? WHERE name = ?",
  ["newemail@example.com", "Alice"],
  [],
  state
)

changes = EctoLibSql.Native.get_changes(state)
IO.puts("Updated #{changes} rows")

DELETE

{:ok, _, result, state} = EctoLibSql.handle_execute(
  "DELETE FROM users WHERE id = ?",
  [1],
  [],
  state
)

changes = EctoLibSql.Native.get_changes(state)
IO.puts("Deleted #{changes} rows")

Advanced Features

Transactions

Basic Transactions

# Begin transaction
{:ok, :begin, state} = EctoLibSql.handle_begin([], state)

# Execute operations
{:ok, _, _, state} = EctoLibSql.handle_execute(
  "INSERT INTO users (name) VALUES (?)",
  ["Charlie"],
  [],
  state
)

{:ok, _, _, state} = EctoLibSql.handle_execute(
  "UPDATE accounts SET balance = balance - 100 WHERE user = ?",
  ["Charlie"],
  [],
  state
)

# Commit
{:ok, _, state} = EctoLibSql.handle_commit([], state)

Transaction Rollback

{:ok, :begin, state} = EctoLibSql.handle_begin([], state)

{:ok, _, _, state} = EctoLibSql.handle_execute(
  "INSERT INTO users (name) VALUES (?)",
  ["Invalid User"],
  [],
  state
)

# Something went wrong, rollback
{:ok, _, state} = EctoLibSql.handle_rollback([], state)

Transaction Behaviours

Control locking and concurrency with transaction behaviours:

# DEFERRED (default) - locks acquired on first write
{:ok, state} = EctoLibSql.Native.begin(state, behavior: :deferred)

# IMMEDIATE - acquires write lock immediately
{:ok, state} = EctoLibSql.Native.begin(state, behavior: :immediate)

# EXCLUSIVE - exclusive lock, blocks all other connections
{:ok, state} = EctoLibSql.Native.begin(state, behavior: :exclusive)

# READ_ONLY - read-only transaction (no locks)
{:ok, state} = EctoLibSql.Native.begin(state, behavior: :read_only)

When to use each behaviour:

• DEFERRED: General-purpose transactions, low contention
• IMMEDIATE: Write-heavy workloads, prevents writer starvation
• EXCLUSIVE: Bulk operations, database migrations
• READ_ONLY: Analytics queries, reports, consistency snapshots

Error Handling in Transactions

defmodule MyApp.Transfer do
  def transfer_funds(from_user, to_user, amount, state) do
    with {:ok, :begin, state} <- EctoLibSql.handle_begin([], state),
         {:ok, _, _, state} <- debit_account(from_user, amount, state),
         {:ok, _, _, state} <- credit_account(to_user, amount, state),
         {:ok, _, state} <- EctoLibSql.handle_commit([], state) do
      {:ok, state}
    else
      {:error, reason, state} ->
        EctoLibSql.handle_rollback([], state)
        {:error, reason}
    end
  end

  defp debit_account(user, amount, state) do
    EctoLibSql.handle_execute(
      "UPDATE accounts SET balance = balance - ? WHERE user = ? AND balance >= ?",
      [amount, user, amount],
      [],
      state
    )
  end

  defp credit_account(user, amount, state) do
    EctoLibSql.handle_execute(
      "UPDATE accounts SET balance = balance + ? WHERE user = ?",
      [amount, user],
      [],
      state
    )
  end
end

Savepoints (Nested Transactions)

Savepoints enable partial rollback within a transaction, perfect for error recovery patterns:

# Begin transaction
{:ok, :begin, state} = EctoLibSql.handle_begin([], state)

# Create savepoint
{:ok, state} = EctoLibSql.Native.create_savepoint(state, "sp1")

{:ok, _, _, state} = EctoLibSql.handle_execute(
  "INSERT INTO users (name) VALUES (?)",
  ["Alice"],
  [],
  state
)

# If something goes wrong, rollback to savepoint (transaction stays active)
{:ok, state} = EctoLibSql.Native.rollback_to_savepoint_by_name(state, "sp1")

# Or release savepoint to commit its changes
{:ok, state} = EctoLibSql.Native.release_savepoint_by_name(state, "sp1")

# Commit the whole transaction
{:ok, _, state} = EctoLibSql.handle_commit([], state)

Use case - Batch import with error recovery:

{:ok, :begin, state} = EctoLibSql.handle_begin([], state)

Enum.reduce(records, state, fn record, state ->
  # Create savepoint for this record
  {:ok, state} = EctoLibSql.Native.create_savepoint(state, "record_#{record.id}")
  
  case insert_record(record, state) do
    {:ok, _, _, state} ->
      # Success - release savepoint
      {:ok, state} = EctoLibSql.Native.release_savepoint_by_name(state, "record_#{record.id}")
      state
    {:error, _, _, state} ->
      # Failure - rollback this record, continue with others
      {:ok, state} = EctoLibSql.Native.rollback_to_savepoint_by_name(state, "record_#{record.id}")
      Logger.warn("Failed to import record #{record.id}")
      state
  end
end)

{:ok, _, state} = EctoLibSql.handle_commit([], state)

Prepared Statements

Prepared statements offer significant performance improvements for repeated queries and prevent SQL injection. As of v0.7.0, statement caching is automatic and highly optimised. Named parameters provide flexible parameter binding with three SQLite syntaxes.

Named Parameters

SQLite supports three named parameter syntaxes for more readable and maintainable queries:

# Syntax 1: Colon prefix (:name)
"SELECT * FROM users WHERE email = :email AND status = :status"

# Syntax 2: At-sign prefix (@name)
"SELECT * FROM users WHERE email = @email AND status = @status"

# Syntax 3: Dollar sign prefix ($name)
"SELECT * FROM users WHERE email = $email AND status = $status"

Execute with map-based parameters:

# Prepared statement with named parameters
{:ok, stmt_id} = EctoLibSql.Native.prepare(
  state,
  "SELECT * FROM users WHERE email = :email AND status = :status"
)

{:ok, result} = EctoLibSql.Native.query_stmt(
  state,
  stmt_id,
  %{"email" => "alice@example.com", "status" => "active"}
)

Direct execution with named parameters:

# INSERT with named parameters
{:ok, _, _, state} = EctoLibSql.handle_execute(
  "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)",
  %{"name" => "Alice", "email" => "alice@example.com", "age" => 30},
  [],
  state
)

# UPDATE with named parameters
{:ok, _, _, state} = EctoLibSql.handle_execute(
  "UPDATE users SET status = :status, updated_at = :now WHERE id = :user_id",
  %{"status" => "inactive", "now" => DateTime.utc_now(), "user_id" => 123},
  [],
  state
)

# DELETE with named parameters
{:ok, _, _, state} = EctoLibSql.handle_execute(
  "DELETE FROM users WHERE id = :user_id AND email = :email",
  %{"user_id" => 123, "email" => "alice@example.com"},
  [],
  state
)

Named parameters in transactions:

{:ok, :begin, state} = EctoLibSql.handle_begin([], state)

{:ok, _, _, state} = EctoLibSql.handle_execute(
  """
  INSERT INTO users (name, email) VALUES (:name, :email)
  """,
  %{"name" => "Alice", "email" => "alice@example.com"},
  [],
  state
)

{:ok, _, _, state} = EctoLibSql.handle_execute(
  "UPDATE users SET verified = 1 WHERE email = :email",
  %{"email" => "alice@example.com"},
  [],
  state
)

{:ok, _, state} = EctoLibSql.handle_commit([], state)

Benefits:

• Readability: Clear parameter names make queries self-documenting
• Maintainability: Easier to refactor when parameter names are explicit
• Type safety: Parameter validation can check required parameters upfront
• Flexibility: Use any of three SQLite syntaxes interchangeably
• Prevention: Prevents SQL injection attacks through proper parameter binding

Backward Compatibility: Positional parameters (?) still work unchanged:

# Positional parameters still work
{:ok, _, result, state} = EctoLibSql.handle_execute(
  "SELECT * FROM users WHERE email = ? AND status = ?",
  ["alice@example.com", "active"],
  [],
  state
)

# Named and positional can coexist in separate queries within the same codebase

Avoiding Mixed Syntax: While SQLite technically permits mixing positional (?) and named (:name) parameters in a single statement, this is discouraged. Named parameters receive implicit numeric indices which can conflict with positional parameters, leading to unexpected binding order. This adapter's map-based approach naturally avoids this issue—pass a list for positional queries, or a map for named queries, but don't mix within a single statement.

How Statement Caching Works

Prepared statements are now cached internally after preparation:

• First call: prepare/2 compiles the statement and caches it
• Subsequent calls: Cached statement is reused with .reset() to clear bindings
• Performance: ~10-15x faster than unprepared queries for repeated execution

# Prepare the statement (compiled and cached internally)
{:ok, stmt_id} = EctoLibSql.Native.prepare(
  state,
  "SELECT * FROM users WHERE email = ?"
)

# Cached statement executed with fresh bindings each time
{:ok, result1} = EctoLibSql.Native.query_stmt(state, stmt_id, ["alice@example.com"])
{:ok, result2} = EctoLibSql.Native.query_stmt(state, stmt_id, ["bob@example.com"])
{:ok, result3} = EctoLibSql.Native.query_stmt(state, stmt_id, ["charlie@example.com"])

# Bindings are automatically cleared between calls - no manual cleanup needed

# Clean up when done
:ok = EctoLibSql.Native.close_stmt(stmt_id)

Performance Comparison

defmodule MyApp.PerfTest do
  # ❌ Slow: Unprepared query executed 100 times (~2.5ms)
  def slow_lookup(state, emails) do
    Enum.each(emails, fn email ->
      {:ok, _, result, _} = EctoLibSql.handle_execute(
        "SELECT * FROM users WHERE email = ?",
        [email],
        [],
        state
      )
      IO.inspect(result)
    end)
  end

  # ✅ Fast: Prepared statement cached and reused (~330µs)
  def fast_lookup(state, emails) do
    {:ok, stmt_id} = EctoLibSql.Native.prepare(
      state,
      "SELECT * FROM users WHERE email = ?"
    )

    Enum.each(emails, fn email ->
      {:ok, result} = EctoLibSql.Native.query_stmt(state, stmt_id, [email])
      IO.inspect(result)
    end)

    EctoLibSql.Native.close_stmt(stmt_id)
  end
end

Prepared Statements with INSERT/UPDATE/DELETE

# Prepare an INSERT statement
{:ok, stmt_id} = EctoLibSql.Native.prepare(
  state,
  "INSERT INTO users (name, email) VALUES (?, ?)"
)

# Execute multiple times with different parameters
# (SQL is re-supplied for sync detection; statement_id reuses the cached statement)
{:ok, rows1} = EctoLibSql.Native.execute_stmt(
  state,
  stmt_id,
  "INSERT INTO users (name, email) VALUES (?, ?)", # Required for sync detection
  ["Alice", "alice@example.com"]
)
IO.puts("Inserted #{rows1} rows")

{:ok, rows2} = EctoLibSql.Native.execute_stmt(
  state,
  stmt_id,
  "INSERT INTO users (name, email) VALUES (?, ?)",
  ["Bob", "bob@example.com"]
)
IO.puts("Inserted #{rows2} rows")

# Clean up
:ok = EctoLibSql.Native.close_stmt(stmt_id)

Statement Introspection (Query Structure Inspection)

Inspect prepared statement structure before execution (v0.7.0+):

# Prepare a statement
{:ok, stmt_id} = EctoLibSql.Native.prepare(
  state,
  "SELECT id, name, email, created_at FROM users WHERE id > ?"
)

# Get parameter count (how many ? placeholders)
{:ok, param_count} = EctoLibSql.Native.stmt_parameter_count(state, stmt_id)
IO.puts("Statement expects #{param_count} parameter(s)")  # Prints: 1

# Get column count (how many columns in result set)
{:ok, col_count} = EctoLibSql.Native.stmt_column_count(state, stmt_id)
IO.puts("Result will have #{col_count} column(s)")  # Prints: 4

# Get column names
col_names =
  Enum.map(0..(col_count - 1), fn i ->
    {:ok, name} = EctoLibSql.Native.stmt_column_name(state, stmt_id, i)
    name
  end)
IO.inspect(col_names)  # Prints: ["id", "name", "email", "created_at"]

:ok = EctoLibSql.Native.close_stmt(stmt_id)

Prepared Statement Best Practices

defmodule MyApp.UserRepository do
  def setup(state) do
    # Prepare commonly used statements at startup
    {:ok, find_by_email} = EctoLibSql.Native.prepare(
      state,
      "SELECT * FROM users WHERE email = ?"
    )

    {:ok, insert_user} = EctoLibSql.Native.prepare(
      state,
      "INSERT INTO users (name, email) VALUES (?, ?)"
    )

    {:ok, update_user} = EctoLibSql.Native.prepare(
      state,
      "UPDATE users SET name = ?, email = ? WHERE id = ?"
    )

    %{
      find_by_email: find_by_email,
      insert_user: insert_user,
      update_user: update_user,
      state: state
    }
  end

  def find_by_email(repo, email) do
    EctoLibSql.Native.query_stmt(repo.state, repo.find_by_email, [email])
  end

  def insert(repo, name, email) do
    EctoLibSql.Native.execute_stmt(
      repo.state,
      repo.insert_user,
      "INSERT INTO users (name, email) VALUES (?, ?)",
      [name, email]
    )
  end

  def cleanup(repo) do
    EctoLibSql.Native.close_stmt(repo.find_by_email)
    EctoLibSql.Native.close_stmt(repo.insert_user)
    EctoLibSql.Native.close_stmt(repo.update_user)
  end
end

Batch Operations

Execute multiple statements efficiently with reduced roundtrips.

Non-Transactional Batch

Each statement executes independently. If one fails, others still complete.

statements = [
  {"INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]},
  {"INSERT INTO users (name, email) VALUES (?, ?)", ["Bob", "bob@example.com"]},
  {"INSERT INTO users (name, email) VALUES (?, ?)", ["Charlie", "charlie@example.com"]},
  {"SELECT COUNT(*) FROM users", []}
]

{:ok, results} = EctoLibSql.Native.batch(state, statements)

Enum.each(results, fn result ->
  IO.inspect(result)
end)

Transactional Batch

All statements execute atomically. If any fails, all are rolled back.

statements = [
  {"UPDATE accounts SET balance = balance - 100 WHERE user = ?", ["Alice"]},
  {"UPDATE accounts SET balance = balance + 100 WHERE user = ?", ["Bob"]},
  {"INSERT INTO transactions (from_user, to_user, amount) VALUES (?, ?, ?)",
   ["Alice", "Bob", 100]}
]

{:ok, results} = EctoLibSql.Native.batch_transactional(state, statements)

Raw SQL Batch Execution

Execute multiple SQL statements as a single string (v0.7.0+):

# Non-transactional: each statement executes independently
sql = """
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');
"""

{:ok, _} = EctoLibSql.Native.execute_batch_sql(state, sql)

# Transactional: all-or-nothing execution
sql = """
INSERT INTO users (name) VALUES ('Charlie');
INSERT INTO users (name) VALUES ('David');
UPDATE users SET name = 'Chuck' WHERE name = 'Charlie';
"""

{:ok, _} = EctoLibSql.Native.execute_transactional_batch_sql(state, sql)

Bulk Insert Example

defmodule MyApp.BulkImporter do
  def import_users(csv_path, state) do
    statements =
      csv_path
      |> File.stream!()
      |> CSV.decode!(headers: true)
      |> Enum.map(fn %{"name" => name, "email" => email} ->
        {"INSERT INTO users (name, email) VALUES (?, ?)", [name, email]}
      end)
      |> Enum.to_list()

    case EctoLibSql.Native.batch_transactional(state, statements) do
      {:ok, results} ->
        IO.puts("Imported #{length(results)} users")
        {:ok, length(results)}

      {:error, reason} ->
        IO.puts("Import failed: #{inspect(reason)}")
        {:error, reason}
    end
  end
end

Cursor Streaming

For large result sets, use cursors to stream data without loading everything into memory.

Basic Cursor Usage

# Start a DBConnection
{:ok, conn} = DBConnection.start_link(EctoLibSql, database: "myapp.db")

# Create a stream
stream = DBConnection.stream(
  conn,
  %EctoLibSql.Query{statement: "SELECT * FROM large_table"},
  []
)

# Process in chunks
stream
|> Enum.each(fn %EctoLibSql.Result{rows: rows, num_rows: count} ->
  IO.puts("Processing batch of #{count} rows")
  Enum.each(rows, &process_row/1)
end)

Cursor with Custom Batch Size

# Fetch 100 rows at a time instead of default 500
stream = DBConnection.stream(
  conn,
  %EctoLibSql.Query{statement: "SELECT * FROM large_table"},
  [],
  max_rows: 100
)

stream
|> Stream.map(fn result -> result.rows end)
|> Stream.concat()
|> Stream.chunk_every(1000)
|> Enum.each(fn chunk ->
  # Process 1000 rows at a time
  MyApp.process_batch(chunk)
end)

Memory-Efficient Data Export

defmodule MyApp.Exporter do
  def export_to_json(conn, output_path) do
    file = File.open!(output_path, [:write])

    DBConnection.stream(
      conn,
      %EctoLibSql.Query{statement: "SELECT * FROM users"},
      [],
      max_rows: 1000
    )
    |> Stream.flat_map(fn %EctoLibSql.Result{rows: rows} -> rows end)
    |> Stream.map(fn [id, name, email] ->
      Jason.encode!(%{id: id, name: name, email: email})
    end)
    |> Stream.intersperse("\n")
    |> Enum.into(file)

    File.close(file)
  end
end

Vector Search

EctoLibSql includes built-in support for vector similarity search, perfect for AI/ML applications.

Creating Vector Tables

# Create a table with a 1536-dimensional vector column (OpenAI embeddings)
vector_col = EctoLibSql.Native.vector_type(1536, :f32)

{:ok, _, _, state} = EctoLibSql.handle_execute(
  """
  CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    content TEXT,
    embedding #{vector_col}
  )
  """,
  [],
  [],
  state
)

Inserting Vectors

# Get embedding from your AI model
embedding = MyApp.OpenAI.get_embedding("Hello, world!")
# Returns: [0.123, -0.456, 0.789, ...]

# Convert to vector format
vec = EctoLibSql.Native.vector(embedding)

# Insert
{:ok, _, _, state} = EctoLibSql.handle_execute(
  "INSERT INTO documents (content, embedding) VALUES (?, vector(?))",
  ["Hello, world!", vec],
  [],
  state
)

Similarity Search

# Query vector
query_text = "greeting messages"
query_embedding = MyApp.OpenAI.get_embedding(query_text)

# Build distance SQL
distance_sql = EctoLibSql.Native.vector_distance_cos("embedding", query_embedding)

# Find most similar documents
{:ok, _, result, state} = EctoLibSql.handle_execute(
  """
  SELECT id, content, #{distance_sql} as distance
  FROM documents
  ORDER BY distance
  LIMIT 10
  """,
  [],
  [],
  state
)

Enum.each(result.rows, fn [id, content, distance] ->
  IO.puts("Document #{id}: #{content} (distance: #{distance})")
end)

Complete RAG Example

defmodule MyApp.RAG do
  @embedding_dimensions 1536

  def setup(state) do
    vector_col = EctoLibSql.Native.vector_type(@embedding_dimensions, :f32)

    EctoLibSql.handle_execute(
      """
      CREATE TABLE IF NOT EXISTS knowledge_base (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        source TEXT,
        content TEXT,
        embedding #{vector_col},
        created_at INTEGER
      )
      """,
      [],
      [],
      state
    )
  end

  def add_document(state, source, content) do
    # Get embedding from OpenAI
    embedding = get_embedding(content)
    vec = EctoLibSql.Native.vector(embedding)

    EctoLibSql.handle_execute(
      """
      INSERT INTO knowledge_base (source, content, embedding, created_at)
      VALUES (?, ?, vector(?), ?)
      """,
      [source, content, vec, System.system_time(:second)],
      [],
      state
    )
  end

  def search(state, query, limit \\ 5) do
    query_embedding = get_embedding(query)
    distance_sql = EctoLibSql.Native.vector_distance_cos("embedding", query_embedding)

    {:ok, _, result, _} = EctoLibSql.handle_execute(
      """
      SELECT source, content, #{distance_sql} as relevance
      FROM knowledge_base
      ORDER BY relevance
      LIMIT ?
      """,
      [limit],
      [],
      state
    )

    Enum.map(result.rows, fn [source, content, relevance] ->
      %{source: source, content: content, relevance: relevance}
    end)
  end

  defp get_embedding(text) do
    # Your OpenAI API call here
    MyApp.OpenAI.create_embedding(text)
  end
end

Vector Search with Metadata Filtering

# Create table with metadata
vector_col = EctoLibSql.Native.vector_type(384, :f32)

{:ok, _, _, state} = EctoLibSql.handle_execute(
  """
  CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    category TEXT,
    price REAL,
    description_embedding #{vector_col}
  )
  """,
  [],
  [],
  state
)

# Search within a category
query_embedding = get_embedding("comfortable running shoes")
distance_sql = EctoLibSql.Native.vector_distance_cos("description_embedding", query_embedding)

{:ok, _, result, state} = EctoLibSql.handle_execute(
  """
  SELECT name, price, #{distance_sql} as similarity
  FROM products
  WHERE category = ? AND price <= ?
  ORDER BY similarity
  LIMIT 10
  """,
  ["shoes", 150.0],
  [],
  state
)

R*Tree Spatial Indexing

R*Tree is a specialised spatial index for efficient multidimensional range queries. Perfect for geospatial data, collision detection, and time-series queries.

Creating R*Tree Tables

R*Tree tables are created as virtual tables by passing rtree: true to the table options in migrations. Two approaches prevent duplicate id columns:

Option 1: Use Ecto's default id (recommended)

defmodule MyApp.Repo.Migrations.CreateLocationsRTree do
  use Ecto.Migration

  def change do
    create table(:geo_regions, options: [rtree: true]) do
      add :min_lat, :float
      add :max_lat, :float
      add :min_lng, :float
      add :max_lng, :float
    end
  end
end

Ecto automatically creates the id column, resulting in: id, min_lat, max_lat, min_lng, max_lng (5 columns, odd ✓).

Option 2: Disable default id and add explicit id

defmodule MyApp.Repo.Migrations.CreateEventsRTree do
  use Ecto.Migration

  def change do
    create table(:events, options: [rtree: true], primary_key: false) do
      add :id, :integer, primary_key: true
      add :min_x, :float
      add :max_x, :float
      add :min_y, :float
      add :max_y, :float
      add :min_time, :integer
      add :max_time, :integer
    end
  end
end

This creates: id, min_x, max_x, min_y, max_y, min_time, max_time (7 columns, odd ✓).

Important R*Tree Requirements:

• First column must be named id (integer primary key)
• Remaining columns come in min/max pairs (1D, 2D, 3D, 4D, or 5D multidimensional)
• Total columns must be odd (3, 5, 7, 9, or 11)
• Minimum 3 columns (id + 1 dimension), maximum 11 columns (id + 5 dimensions)
• R*Tree tables are virtual tables and do not support standard table options like :strict, :random_rowid, or :without_rowid

2D Example: Geographic Boundaries

# Create table for geographic regions
Ecto.Adapters.SQL.query!(
  Repo,
  """
  CREATE VIRTUAL TABLE geo_regions USING rtree(
    id,
    min_lat, max_lat,
    min_lng, max_lng
  )
  """
)

# Insert bounding boxes for regions
# Sydney: -34.0 to -33.8 lat, 151.0 to 151.3 lng
Ecto.Adapters.SQL.query!(
  Repo,
  "INSERT INTO geo_regions VALUES (1, -34.0, -33.8, 151.0, 151.3)"
)

# Melbourne: -38.0 to -37.7 lat, 144.8 to 145.1 lng
Ecto.Adapters.SQL.query!(
  Repo,
  "INSERT INTO geo_regions VALUES (2, -38.0, -37.7, 144.8, 145.1)"
)

# Find regions containing a point (Sydney: -33.87, 151.21)
result = Ecto.Adapters.SQL.query!(
  Repo,
  """
  SELECT id FROM geo_regions
  WHERE min_lat <= -33.87 AND max_lat >= -33.87
    AND min_lng <= 151.21 AND max_lng >= 151.21
  """
)
# Returns: [[1]]

3D Example: Spatial + Time Ranges

# Create table for events with location and time bounds
Ecto.Adapters.SQL.query!(
  Repo,
  """
  CREATE VIRTUAL TABLE events USING rtree(
    id,
    min_x, max_x,      -- X coordinate bounds
    min_y, max_y,      -- Y coordinate bounds
    min_time, max_time -- Time bounds (Unix timestamp)
  )
  """
)

# Insert event: Conference at (100, 200) from Jan 1-3, 2025
start_time = DateTime.to_unix(~U[2025-01-01 00:00:00Z])
end_time = DateTime.to_unix(~U[2025-01-03 23:59:59Z])

Ecto.Adapters.SQL.query!(
  Repo,
  "INSERT INTO events VALUES (1, 100, 100, 200, 200, #{start_time}, #{end_time})"
)

# Find events in area (90-110, 190-210) during Jan 2025
query_start = DateTime.to_unix(~U[2025-01-01 00:00:00Z])
query_end = DateTime.to_unix(~U[2025-01-31 23:59:59Z])

result = Ecto.Adapters.SQL.query!(
  Repo,
  """
  SELECT id FROM events
  WHERE max_x >= 90 AND min_x <= 110
    AND max_y >= 190 AND min_y <= 210
    AND max_time >= #{query_start} AND min_time <= #{query_end}
  """
)

Using with Ecto Schemas

While R*Tree tables are virtual tables, you can still define schemas for them:

defmodule MyApp.GeoRegion do
  use Ecto.Schema

  @primary_key {:id, :integer, autogenerate: false}
  schema "geo_regions" do
    field :min_lat, :float
    field :max_lat, :float
    field :min_lng, :float
    field :max_lng, :float
  end
end

# Insert using Ecto
region = %MyApp.GeoRegion{
  id: 1,
  min_lat: -34.0,
  max_lat: -33.8,
  min_lng: 151.0,
  max_lng: 151.3
}
Repo.insert!(region)

# Query using fragments
import Ecto.Query

# Find regions containing a point
point_lat = -33.87
point_lng = 151.21

query = from r in MyApp.GeoRegion,
  where: fragment("min_lat <= ? AND max_lat >= ?", ^point_lat, ^point_lat),
  where: fragment("min_lng <= ? AND max_lng >= ?", ^point_lng, ^point_lng)

regions = Repo.all(query)

Common Query Patterns

# 1. Point containment: Does bounding box contain this point?
"""
SELECT id FROM rtree_table
WHERE min_x <= ?1 AND max_x >= ?1
  AND min_y <= ?2 AND max_y >= ?2
"""

# 2. Bounding box intersection: Do two boxes overlap?
"""
SELECT id FROM rtree_table
WHERE max_x >= ?1 AND min_x <= ?2  -- Query box: ?1 to ?2
  AND max_y >= ?3 AND min_y <= ?4  -- Query box: ?3 to ?4
"""

# 3. Range query: All items within bounds
"""
SELECT id FROM rtree_table
WHERE min_x >= ?1 AND max_x <= ?2
  AND min_y >= ?3 AND max_y <= ?4
"""

R*Tree vs Vector Search

Use R*Tree when:

• You have bounding box data (geographic regions, time ranges)
• You need exact range queries (all items within bounds)
• Working with 1-5 dimensional coordinate data
• Query performance is critical for range lookups

Use Vector Search when:

• You have high-dimensional embeddings (384-1536+ dimensions)
• You need similarity/distance-based search
• Working with semantic search, recommendations, or ML features
• Approximate nearest neighbors is acceptable

Hybrid Approach:

# Combine both for location-aware semantic search
"""
SELECT p.*, vector_distance_cos(p.embedding, ?1) as similarity
FROM products p
JOIN geo_regions r ON r.id = p.region_id
WHERE r.min_lat <= ?2 AND r.max_lat >= ?2
  AND r.min_lng <= ?3 AND r.max_lng >= ?3
ORDER BY similarity
LIMIT 10
"""

Connection Management

Control connection behaviour and performance with these utilities (v0.7.0+):

Busy Timeout

Configure how long to wait when the database is locked:

# Set timeout to 10 seconds (default is 5 seconds)
{:ok, state} = EctoLibSql.Native.busy_timeout(state, 10_000)

# Now queries will wait up to 10s for locks to release
{:ok, _, result, state} = EctoLibSql.handle_execute(
  "INSERT INTO users (name) VALUES (?)",
  ["Alice"],
  [],
  state
)

Reset Connection

Reset connection state without closing it:

# Reset clears prepared statements, releases locks, rolls back transactions
{:ok, state} = EctoLibSql.Native.reset(state)

Interrupt Long-Running Queries

Cancel a query that's taking too long:

# In one process
Task.async(fn ->
  EctoLibSql.handle_execute("SELECT * FROM huge_table", [], [], state)
end)

# In another process, interrupt it
:ok = EctoLibSql.Native.interrupt(state)

PRAGMA Configuration

Configure SQLite database parameters with the EctoLibSql.Pragma module (v0.7.0+):

Foreign Keys

# Enable foreign key constraints
{:ok, state} = EctoLibSql.Pragma.enable_foreign_keys(state)

# Check if enabled
{:ok, enabled} = EctoLibSql.Pragma.foreign_keys(state)
IO.inspect(enabled)  # true

Journal Mode

# Set to WAL mode for better concurrency
{:ok, state} = EctoLibSql.Pragma.set_journal_mode(state, :wal)

# Check current mode
{:ok, mode} = EctoLibSql.Pragma.journal_mode(state)
IO.inspect(mode)  # :wal

Cache Size

# Set cache to 10MB (negative values = KB)
{:ok, state} = EctoLibSql.Pragma.set_cache_size(state, -10_000)

# Or use pages (positive values)
{:ok, state} = EctoLibSql.Pragma.set_cache_size(state, 2000)

Synchronous Level

# Set synchronous mode (trade durability for speed)
{:ok, state} = EctoLibSql.Pragma.set_synchronous(state, :normal)

# Options: :off, :normal, :full, :extra

Table Introspection

# Get table structure
{:ok, columns} = EctoLibSql.Pragma.table_info(state, "users")
Enum.each(columns, fn col ->
  IO.inspect(col)  # %{name: "id", type: "INTEGER", ...}
end)

# List all tables
{:ok, tables} = EctoLibSql.Pragma.table_list(state)
IO.inspect(tables)  # ["users", "posts", "sqlite_sequence"]

User Version (Schema Versioning)

# Set schema version
{:ok, state} = EctoLibSql.Pragma.set_user_version(state, 5)

# Get current version
{:ok, version} = EctoLibSql.Pragma.user_version(state)
IO.inspect(version)  # 5

Encryption

Protect sensitive data with AES-256-CBC encryption at rest.

Local Encrypted Database

opts = [
  database: "secure.db",
  encryption_key: System.get_env("DB_ENCRYPTION_KEY")
]

{:ok, state} = EctoLibSql.connect(opts)

# Use normally - encryption is transparent
{:ok, _, _, state} = EctoLibSql.handle_execute(
  "INSERT INTO secrets (data) VALUES (?)",
  ["sensitive information"],
  [],
  state
)

Encrypted Remote Replica

opts = [
  uri: "libsql://my-database.turso.io",
  auth_token: System.get_env("TURSO_AUTH_TOKEN"),
  database: "encrypted_replica.db",
  encryption_key: System.get_env("DB_ENCRYPTION_KEY"),
  sync: true
]

{:ok, state} = EctoLibSql.connect(opts)

Key Management Best Practices

defmodule MyApp.DatabaseConfig do
  def get_encryption_key do
    # Option 1: Environment variable
    key = System.get_env("DB_ENCRYPTION_KEY")

    # Option 2: Secret management service (recommended for production)
    # key = MyApp.SecretManager.get_secret("database-encryption-key")

    # Option 3: Vault/KMS
    # key = MyApp.Vault.get_key("database-encryption")

    if byte_size(key) < 32 do
      raise "Encryption key must be at least 32 characters"
    end

    key
  end

  def connection_opts do
    [
      database: "secure.db",
      encryption_key: get_encryption_key()
    ]
  end
end

# Usage
{:ok, state} = EctoLibSql.connect(MyApp.DatabaseConfig.connection_opts())

JSON Schema Helpers

EctoLibSql provides EctoLibSql.JSON module with comprehensive helpers for working with JSON and JSONB data. LibSQL 3.45.1 has JSON1 built into the core with support for both text JSON and efficient JSONB binary format.

JSON Functions

alias EctoLibSql.JSON

# Extract values from JSON
{:ok, theme} = JSON.extract(state, ~s({"user":{"prefs":{"theme":"dark"}}}), "$.user.prefs.theme")
# Returns: {:ok, "dark"}

# Check JSON type
{:ok, type} = JSON.type(state, ~s({"count":42}), "$.count")
# Returns: {:ok, "integer"}

# Validate JSON
{:ok, true} = JSON.is_valid(state, ~s({"valid":true}))
{:ok, false} = JSON.is_valid(state, "not json")

# Create JSON structures
{:ok, array} = JSON.array(state, [1, 2.5, "hello", nil])
# Returns: {:ok, "[1,2.5,\"hello\",null]"}

{:ok, obj} = JSON.object(state, ["name", "Alice", "age", 30, "active", true])
# Returns: {:ok, "{\"name\":\"Alice\",\"age\":30,\"active\":true}"}

Iterating Over JSON

# Iterate over array elements or object members
{:ok, items} = JSON.each(state, ~s([1,2,3]), "$")
# Returns: {:ok, [{0, 1, "integer"}, {1, 2, "integer"}, {2, 3, "integer"}]}

# Recursively iterate all values (flattening)
{:ok, tree} = JSON.tree(state, ~s({"a":{"b":1},"c":[2,3]}), "$")
# Returns: all nested values with their full paths

JSONB Binary Format

JSONB is a more efficient binary encoding of JSON with 5-10% smaller size and faster processing:

# Convert to binary JSONB format
json_string = ~s({"name":"Alice","age":30})
{:ok, jsonb_binary} = JSON.convert(state, json_string, :jsonb)

# All JSON functions work with both text and JSONB
{:ok, value} = JSON.extract(state, jsonb_binary, "$.name")
# Transparently works with binary format

# Convert back to text JSON
{:ok, canonical} = JSON.convert(state, json_string, :json)

Arrow Operators (-> and ->>)

The -> and ->> operators provide concise syntax for JSON access in queries:

# -> returns JSON (always)
fragment = JSON.arrow_fragment("settings", "theme")
# Returns: "settings -> 'theme'"

# ->> returns SQL type (text/int/real/null)
fragment = JSON.arrow_fragment("settings", "theme", :double_arrow)
# Returns: "settings ->> 'theme'"

# Use in Ecto queries - Option 1: Using the helper function
arrow_sql = JSON.arrow_fragment("data", "active", :double_arrow)
from u in User,
  where: fragment(arrow_sql <> " = ?", true)

# Option 2: Direct inline SQL (simpler approach)
from u in User,
  where: fragment("data ->> 'active' = ?", true)

Ecto Integration

JSON helpers work seamlessly with Ecto:

defmodule MyApp.User do
  use Ecto.Schema
  import Ecto.Changeset

  schema "users" do
    field :name, :string
    field :settings, :map  # Stored as JSON/JSONB
    timestamps()
  end
end

# In your repository context
import Ecto.Query

# Query with JSON extraction
from u in User,
  where: fragment("json_extract(?, ?) = ?", u.settings, "$.theme", "dark"),
  select: u.name

# Or using the helpers - Option 1: Arrow fragment helper
arrow_sql = JSON.arrow_fragment("settings", "theme", :double_arrow)
from u in User,
  where: fragment(arrow_sql <> " = ?", "dark")

# Option 2: Direct inline SQL (simpler for static fields)
from u in User,
  where: fragment("settings ->> 'theme' = ?", "dark")

# Update JSON fields
from u in User,
  where: u.id == ^user_id,
  update: [set: [settings: fragment("json_set(?, ?, ?)", u.settings, "$.theme", "light")]]

JSON Modification Functions

Create, update, and manipulate JSON structures:

# Quote a value for JSON
{:ok, quoted} = JSON.json_quote(state, "hello \"world\"")
# Returns: {:ok, "\"hello \\\"world\\\"\""}

# Get JSON array/object length (SQLite 3.9.0+)
{:ok, len} = JSON.json_length(state, ~s([1,2,3,4,5]))
# Returns: {:ok, 5}

# Get JSON structure depth (SQLite 3.9.0+)
{:ok, depth} = JSON.depth(state, ~s({"a":{"b":{"c":1}}}))
# Returns: {:ok, 4}

# Set a value (creates path if not exists)
{:ok, json} = JSON.set(state, ~s({"a":1}), "$.b", 2)
# Returns: {:ok, "{\"a\":1,\"b\":2}"}

# Replace a value (only if path exists)
{:ok, json} = JSON.replace(state, ~s({"a":1,"b":2}), "$.a", 10)
# Returns: {:ok, "{\"a\":10,\"b\":2}"}

# Insert without replacing
{:ok, json} = JSON.insert(state, ~s({"a":1}), "$.b", 2)
# Returns: {:ok, "{\"a\":1,\"b\":2}"}

# Remove keys/paths
{:ok, json} = JSON.remove(state, ~s({"a":1,"b":2,"c":3}), "$.b")
# Returns: {:ok, "{\"a\":1,\"c\":3}"}

# Remove multiple paths
{:ok, json} = JSON.remove(state, ~s({"a":1,"b":2,"c":3}), ["$.a", "$.c"])
# Returns: {:ok, "{\"b\":2}"}

# Apply a JSON Merge Patch (RFC 7396)
# Keys in patch are object keys, not JSON paths
{:ok, json} = JSON.patch(state, ~s({"a":1,"b":2}), ~s({"a":10,"c":3}))
# Returns: {:ok, "{\"a\":10,\"b\":2,\"c\":3}"}

# Remove a key by patching with null
{:ok, json} = JSON.patch(state, ~s({"a":1,"b":2,"c":3}), ~s({"b":null}))
# Returns: {:ok, "{\"a\":1,\"c\":3}"}

# Get all keys from a JSON object (SQLite 3.9.0+)
{:ok, keys} = JSON.keys(state, ~s({"name":"Alice","age":30}))
# Returns: {:ok, "[\"age\",\"name\"]"}  (sorted)

Real-World Example: Settings Management

defmodule MyApp.UserPreferences do
  alias EctoLibSql.JSON

  def get_preference(state, settings_json, key_path) do
    JSON.extract(state, settings_json, "$.#{key_path}")
  end

  def set_preference(state, settings_json, key_path, value) do
    # Build JSON path from key path
    json_path = "$.#{key_path}"
    
    # Use JSON.set instead of raw SQL
    JSON.set(state, settings_json, json_path, value)
  end

  def update_theme(state, settings_json, theme) do
    JSON.set(state, settings_json, "$.theme", theme)
  end

  def toggle_notifications(state, settings_json) do
    # Get current value
    {:ok, current} = JSON.extract(state, settings_json, "$.notifications")
    new_value = not current
    
    # Update it
    JSON.set(state, settings_json, "$.notifications", new_value)
  end

  def remove_preference(state, settings_json, key_path) do
    json_path = "$.#{key_path}"
    JSON.remove(state, settings_json, json_path)
  end

  def validate_settings(state, settings_json) do
    JSON.is_valid(state, settings_json)
  end

  def get_structure_info(state, settings_json) do
    with {:ok, is_valid} <- JSON.is_valid(state, settings_json),
         {:ok, json_type} <- JSON.type(state, settings_json),
         {:ok, depth} <- JSON.depth(state, settings_json) do
      {:ok, %{valid: is_valid, type: json_type, depth: depth}}
    end
  end

  # Build settings from scratch
  def create_default_settings(state) do
    JSON.object(state, [
      "theme", "light",
      "notifications", true,
      "language", "en",
      "timezone", "UTC"
    ])
  end

  # Merge settings with defaults
  def merge_with_defaults(state, user_settings, defaults) do
    with {:ok, user_map} <- JSON.tree(state, user_settings),
         {:ok, defaults_map} <- JSON.tree(state, defaults) do
      # In practice, you'd merge these maps here
      {:ok, user_settings}
    end
  end
end

# Usage
{:ok, state} = EctoLibSql.connect(database: "app.db")
settings = ~s({"theme":"dark","notifications":true,"language":"es"})

# Get a preference
{:ok, theme} = MyApp.UserPreferences.get_preference(state, settings, "theme")
# Returns: {:ok, "dark"}

# Update a preference
{:ok, new_settings} = MyApp.UserPreferences.update_theme(state, settings, "light")

# Toggle notifications
{:ok, new_settings} = MyApp.UserPreferences.toggle_notifications(state, settings)

# Validate settings
{:ok, valid?} = MyApp.UserPreferences.validate_settings(state, settings)
# Returns: {:ok, true}

# Get structure info
{:ok, info} = MyApp.UserPreferences.get_structure_info(state, settings)
# Returns: {:ok, %{valid: true, type: "object", depth: 2}}

Comparison: Set vs Replace vs Insert vs Patch

The modification functions have different behaviours:

json = ~s({"a":1,"b":2})

# SET: Creates or replaces any path (uses JSON paths like "$.key")
{:ok, result} = JSON.set(state, json, "$.c", 3)
# Result: {"a":1,"b":2,"c":3}

{:ok, result} = JSON.set(state, json, "$.a", 100)
# Result: {"a":100,"b":2}

# REPLACE: Only updates existing paths, ignores new paths (uses JSON paths)
{:ok, result} = JSON.replace(state, json, "$.c", 3)
# Result: {"a":1,"b":2}  (c not added)

{:ok, result} = JSON.replace(state, json, "$.a", 100)
# Result: {"a":100,"b":2}  (existing path updated)

# INSERT: Adds new values without replacing existing ones (uses JSON paths)
{:ok, result} = JSON.insert(state, json, "$.c", 3)
# Result: {"a":1,"b":2,"c":3}

{:ok, result} = JSON.insert(state, json, "$.a", 100)
# Result: {"a":1,"b":2}  (existing path unchanged)

# PATCH: Applies JSON Merge Patch (RFC 7396) - keys are object keys, not paths
{:ok, result} = JSON.patch(state, json, ~s({"a":10,"c":3}))
# Result: {"a":10,"b":2,"c":3}

# Use null to remove keys
{:ok, result} = JSON.patch(state, json, ~s({"b":null}))
# Result: {"a":1}

When to use each function:

• SET/REPLACE/INSERT: For path-based updates using JSON paths (e.g., "$.user.name")
• PATCH: For bulk top-level key updates (implements RFC 7396 JSON Merge Patch)

Performance Notes

• JSONB format reduces storage by 5-10% vs text JSON
• JSONB processes in less than half the CPU cycles
• All JSON functions accept both text and JSONB transparently
• For frequent extractions, consider denormalising commonly accessed fields
• Use json_each() and json_tree() for flattening/searching

Ecto Integration

EctoLibSql provides a full Ecto adapter, making it seamless to use with Phoenix and Ecto-based applications. This enables you to use all Ecto features including schemas, migrations, queries, and associations.

Quick Start with Ecto

1. Installation

Add ecto_libsql to your dependencies (it already includes ecto_sql):

def deps do
  [
    {:ecto_libsql, "~> 0.8.0"}
  ]
end