SQLite3 Crystal Shard

Crystal shard providing self-contained SQLite3 bindings with comprehensive extension support.

Features

Self-Contained: No system SQLite dependencies required. The entire SQLite library is embedded and compiled statically into your application.

Extensions: All major SQLite extensions are compiled in and ready to use:

• 🔍 FTS5 - Full-text search engine
• 📊 Vec - Vector similarity search for AI/ML applications
• 🌐 RTree - Spatial indexing for geographic data
• 📐 GEOPOLY - Geometry processing
• 🧮 Math Functions - Complete math library
• 📦 JSON1 - JSON support (built-in to SQLite 3.38+)

Production Ready: 100% test coverage with 52 comprehensive tests ensuring reliability.

Easy to Use: Follows the standard Crystal DB API for familiar database operations.

Installation

1. Add the dependency to your shard.yml:

dependencies:
  sqlite3:
    git: https://vski.sh/x/sqlite3
    version: ~> 0.1.0

2. Install dependencies:

shards install

That's it! The SQLite library will be automatically built with all extensions enabled during installation. The resulting static library will be linked to your application.

Quick Start

require "sqlite3"

# In-memory database for testing
DB.open "sqlite3://%3Amemory%3A" do |db|
  # Create a table
  db.exec "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"

  # Insert data with parameters
  db.exec "INSERT INTO users (name, email) VALUES (?, ?)", "Alice", "alice@example.com"

  # Query data
  users = db.query_all "SELECT name, email FROM users", as: {String, String}
  users.each do |name, email|
    puts "#{name} <#{email}>"
  end

  # Use scalar queries
  count = db.scalar("SELECT COUNT(*) FROM users").as(Int64)
  puts "Total users: #{count}"
end

Configuration

Connection Options

Configure SQLite behavior through URI parameters:

# Performance-optimized configuration
DB.open "sqlite3://./app.db?journal_mode=wal&synchronous=normal&cache_size=-64000" do |db|
  # WAL mode for better concurrency
  # Normal synchronous for faster writes
  # 64MB cache
end

Available options:

• journal_mode - WAL, DELETE, TRUNCATE, PERSIST, MEMORY, OFF
• synchronous - FULL, NORMAL, OFF
• cache_size - Number of pages (negative = KB)
• busy_timeout - Milliseconds to wait for locks
• foreign_keys - Enable foreign key constraints (ON/OFF)
• wal_autocheckpoint - WAL checkpoint threshold

Building

Prerequisites

• Crystal >= 1.0.0
• C compiler (gcc or clang)
• make

Build Commands

make              # Build static library
make clean        # Remove build artifacts
make rebuild      # Clean and rebuild
make show-config  # Display build configuration
make test-compile # Verify compilation

Build Output

ext/sqlite3.a  - Static library (1.9MB)
├── sqlite3.o              - SQLite amalgamation
├── sqlite-vec.o           - Vec extension
└── sqlite3_vec_wrapper.o  - Vec auto-initialization

Testing

# Run all tests
crystal spec

# Run specific test suites
crystal spec spec/driver_spec.cr      # Core functionality
crystal spec spec/extension_spec.cr   # Extension tests

# Current test results
# 52 examples, 0 failures, 0 errors, 0 pending
# 100% pass rate