### Install Build Essentials (Ubuntu)

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/compile_and_link_sqlite.md

Installs the necessary build tools on Ubuntu systems. This is a prerequisite for compiling software from source.

```sh
sudo apt update
sudo apt install build-essential
```

--------------------------------

### Compile and link custom SQLite version

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Instructions for compiling SQLite from source and linking it with the Crystal runtime using `LD_LIBRARY_PATH` or `--link-flags`. This example shows downloading and unzipping the SQLite amalgamation source.

```shell
# Download amalgamation source
wget https://sqlite.org/2021/sqlite-amalgamation-3370000.zip
unzip sqlite-amalgamation-3370000.zip
cd sqlite-amalgamation-3370000
```

--------------------------------

### Install crystal-sqlite3 Shard

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/README.md

Add the crystal-sqlite3 dependency to your application's shard.yml file to include it in your project.

```yaml
dependencies:
  sqlite3:
    github: crystal-lang/crystal-sqlite3
```

--------------------------------

### Query and Iterate Over Result Rows

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Use db.query to get a DB::ResultSet for iterating over rows. rs.each processes rows, and rs.read retrieves columns by type. Column names and count are accessible.

```crystal
require "sqlite3"

DB.open "sqlite3://./app.db" do |db|
  db.exec "create table if not exists products (sku text, price float, stock integer)"
  db.exec "insert into products values (?, ?, ?)", "A100", 9.99, 50
  db.exec "insert into products values (?, ?, ?)", "B200", 19.95, 10

  db.query "select sku, price, stock from products order by price desc" do |rs|
    # Print header using column names
    puts (0...rs.column_count).map { |i| rs.column_name(i) }.join(" | ")
    # => sku | price | stock

    rs.each do
      sku   = rs.read(String)
      price = rs.read(Float64)
      stock = rs.read(Int32)
      puts "#{sku} | #{price} | #{stock}"
      # => B200 | 19.95 | 10
      # => A100 | 9.99  | 50
    end
  end
end
```

--------------------------------

### Basic SQLite3 Database Operations in Crystal

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/README.md

Demonstrates connecting to an SQLite database, creating a table, inserting data with placeholders and arguments, querying for a single value, and iterating over query results. Ensure the database file path is correct.

```crystal
require "sqlite3"

DB.open "sqlite3://./data.db" do |db|
  db.exec "create table contacts (name text, age integer)"
  db.exec "insert into contacts values (?, ?)", "John Doe", 30

  args = [] of DB::Any
  args << "Sarah"
  args << 33
  db.exec "insert into contacts values (?, ?)", args: args

  puts "max age:"
  puts db.scalar "select max(age) from contacts" # => 33

  puts "contacts:"
  db.query "select name, age from contacts order by age desc" do |rs|
    puts "#{rs.column_name(0)} (#{rs.column_name(1)})"
    # => name (age)
    rs.each do
      puts "#{rs.read(String)} (#{rs.read(Int32)})"
      # => Sarah (33)
      # => John Doe (30)
    end
  end

  contacts = db.query_all "SELECT name, age FROM contacts", as: {name: String, age: Int64}
  puts contacts # => [{name: "John Doe", age: 30}, {name: "Sarah", age: 33}]
end
```

--------------------------------

### Verify SQLite Version at Runtime

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Open an in-memory SQLite database and query the SQLite version using SQL. This snippet demonstrates how to interact with the database and retrieve information.

```crystal
DB.open "sqlite3://:memory:" do |db|
  puts db.scalar("select sqlite_version()")
end
```

--------------------------------

### PRAGMA configuration via URI query parameters

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Configures connection-level SQLite3 PRAGMAs by appending query parameters to the connection URI. Supported PRAGMAs include busy_timeout, cache_size, foreign_keys, journal_mode, synchronous, and wal_autocheckpoint.

```APIDOC
## PRAGMA configuration via URI query parameters

Connection-level SQLite3 PRAGMAs can be set by appending query parameters to the `sqlite3://` URI. Supported keys: `busy_timeout`, `cache_size`, `foreign_keys`, `journal_mode`, `synchronous`, `wal_autocheckpoint`. Values are passed directly to SQLite without validation.

```crystal
require "sqlite3"

# WAL mode + normal fsync + foreign-key enforcement + 5-second busy timeout
DB.open "sqlite3://./production.db?journal_mode=wal&synchronous=normal&foreign_keys=1&busy_timeout=5000" do |db|
  # Verify the PRAGMAs were applied
  puts db.scalar("PRAGMA journal_mode")       # => "wal"
  puts db.scalar("PRAGMA synchronous")        # => 1  (NORMAL)
  puts db.scalar("PRAGMA foreign_keys")       # => 1
  puts db.scalar("PRAGMA busy_timeout")       # => 5000

  db.exec "create table if not exists parent (id integer primary key)"
  db.exec "create table if not exists child  (pid integer references parent(id))"

  # This will raise SQLite3::Exception because FK enforcement is on
  begin
    db.exec "insert into child values (999)"
  rescue ex : SQLite3::Exception
    puts "FK violation: #{ex.message}"
  end
end
```
```

--------------------------------

### Configure PRAGMAs via URI

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Set connection-level SQLite3 PRAGMAs by appending query parameters to the `sqlite3://` URI. Supported keys include `busy_timeout`, `cache_size`, `foreign_keys`, `journal_mode`, `synchronous`, and `wal_autocheckpoint`. Values are passed directly to SQLite.

```crystal
require "sqlite3"

# WAL mode + normal fsync + foreign-key enforcement + 5-second busy timeout
DB.open "sqlite3://./production.db?journal_mode=wal&synchronous=normal&foreign_keys=1&busy_timeout=5000" do |db|
  # Verify the PRAGMAs were applied
  puts db.scalar("PRAGMA journal_mode")       # => "wal"
  puts db.scalar("PRAGMA synchronous")        # => 1  (NORMAL)
  puts db.scalar("PRAGMA foreign_keys")       # => 1
  puts db.scalar("PRAGMA busy_timeout")       # => 5000

  db.exec "create table if not exists parent (id integer primary key)"
  db.exec "create table if not exists child  (pid integer references parent(id))"

  # This will raise SQLite3::Exception because FK enforcement is on
  begin
    db.exec "insert into child values (999)"
  rescue ex : SQLite3::Exception
    puts "FK violation: #{ex.message}"
  end
end
```

--------------------------------

### Configure SQLite3 PRAGMAs on Connection

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/README.md

Connect to an SQLite database with specific PRAGMA settings applied automatically using query parameters in the connection URI. Incorrect values may not raise errors.

```crystal
require "sqlite3"

DB.open "sqlite3://./data.db?journal_mode=wal&synchronous=normal" do |db|
  # this database now uses WAL journal and normal synchronous mode
  # (defaults were `delete` and `full`, respectively)
end
```

--------------------------------

### Blob (binary data) support

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Supports binding `Bytes` values as `BLOB` columns using `sqlite3_bind_blob` and reading them back as `Bytes`. Requires importing the `sqlite3` library.

```crystal
require "sqlite3"

DB.open "sqlite3://%3Amemory%3A" do |db|
  db.exec "create table files (name text, data blob)"

  raw = Bytes[0x89, 0x50, 0x4E, 0x47]  # PNG magic bytes
  db.exec "insert into files values (?, ?)", "header.bin", raw

  db.query "select name, data from files" do |rs|
    rs.each do
      name = rs.read(String)
      data = rs.read(Bytes)
      puts name                    # => "header.bin"
      puts data.inspect            # => Bytes[137, 80, 78, 71]
      puts data.size               # => 4
    end
  end
end
```

--------------------------------

### Database backup using connection dump

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Copies an entire source database to a target `SQLite3::Connection` using the SQLite online backup API. This method works for any combination of file-based and in-memory databases. Requires importing the `sqlite3` library.

```crystal
require "sqlite3"

# Backup a file database to an in-memory database and back
DB.open "sqlite3://./source.db" do |src_db|
  DB.open "sqlite3://%3Amemory%3A" do |mem_db|
    src_db.exec "create table if not exists items (val integer)"
    src_db.exec "insert into items values (?)", 42

    # Dump file → memory
    src_db.using_connection do |src_conn|
      mem_db.using_connection do |mem_conn|
        src_conn.as(SQLite3::Connection).dump(mem_conn.as(SQLite3::Connection))
      end
    end

    puts mem_db.scalar("select val from items") # => 42

    # Modify the in-memory copy, then dump back
    mem_db.exec "insert into items values (?)", 99
    mem_db.using_connection do |mem_conn|
      src_db.using_connection do |src_conn|
        mem_conn.as(SQLite3::Connection).dump(src_conn.as(SQLite3::Connection))
      end
    end

    puts src_db.scalar("select count(*) from items") # => 2
  end
end
```

--------------------------------

### Fetch All Rows with DB#query_all

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Use `db.query_all` to fetch all query results into typed named tuples or single types. This is convenient when all rows are needed at once. Ensure the database and table are set up before querying.

```crystal
require "sqlite3"

DB.open "sqlite3://./app.db" do |db|
  db.exec "create table if not exists contacts (name text, age integer)"
  db.exec "insert into contacts values (?, ?)", "John Doe", 30
  db.exec "insert into contacts values (?, ?)", "Sarah",    33

  # Named-tuple form
  contacts = db.query_all "SELECT name, age FROM contacts ORDER BY age",
    as: {name: String, age: Int64}
  contacts.each { |c| puts "#{c[:name]} is #{c[:age]}" }
  # => John Doe is 30
  # => Sarah is 33

  # Single-column form
  names = db.query_all "SELECT name FROM contacts", as: String
  puts names.inspect # => ["John Doe", "Sarah"]
end
```

--------------------------------

### Build Crystal App with Runtime Library Path

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/compile_and_link_sqlite.md

Builds a Crystal application, linking against a specific SQLite library located at runtime. The `realpath` command ensures the correct absolute path is used.

```sh
crystal build --release --link-flags -L"$(realpath ../sqlite-amalgamation-3370000/libsqlite3.so.0)" src/app.cr
LD_LIBRARY_PATH=../sqlite-amalgamation-3370000 ./app
```

--------------------------------

### Download and Extract SQLite Source

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/compile_and_link_sqlite.md

Downloads the SQLite amalgamation source code and extracts it. Ensure you replace the version number with the desired release.

```sh
wget https://sqlite.org/2021/sqlite-amalgamation-3370000.zip
unzip sqlite-amalgamation-3370000.zip
cd sqlite-amalgamation-3370000
```

--------------------------------

### Verify Linked SQLite Library

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/compile_and_link_sqlite.md

Uses the `ldd` command to inspect the dynamic dependencies of an executable, showing which version of libsqlite is being linked.

```sh
LD_LIBRARY_PATH=../sqlite-amalgamation-3370000 ldd ./app
```

--------------------------------

### Execute SQL Statements with Parameters

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Use db.exec to run DML/DDL statements. Supports positional parameters via splat args or the 'args:' named parameter. Handles various integer types.

```crystal
require "sqlite3"

DB.open "sqlite3://./app.db" do |db|
  db.exec "create table if not exists users (id integer primary key, name text not null, active integer)"

  # Splat-style parameters
  result = db.exec "insert into users (name, active) values (?, ?)", "Carol", true
  puts result.last_insert_id  # => 1
  puts result.rows_affected   # => 1

  # Array-style parameters (DB::Any)
  args = [] of DB::Any
  args << "Dave"
  args << false
  result2 = db.exec "insert into users (name, active) values (?, ?)", args: args
  puts result2.last_insert_id # => 2

  # Passing all integer widths
  db.exec "insert into users (name, active) values (?, ?)", "Eve", 1_u8
end
```

--------------------------------

### Verify Linked SQLite Library (Compile-Time)

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/compile_and_link_sqlite.md

Uses `ldd` to verify the linked SQLite library after building the Crystal application with a compile-time specified path.

```sh
ldd ./app
```

--------------------------------

### Build Crystal App with Compile-Time Library Path

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/compile_and_link_sqlite.md

Builds a Crystal application, embedding the absolute path to the SQLite library at compile time. This creates an executable that relies on the library being present at the exact specified location.

```sh
crystal build --release --link-flags "$(realpath ../sqlite-amalgamation-3370000/libsqlite3.so.0)" src/app.cr
./app
```

--------------------------------

### Open SQLite3 Database Connection

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Use DB.open with a 'sqlite3://' URI to establish a connection pool. The block form ensures the database is closed automatically. Supports file-based and in-memory databases.

```crystal
require "sqlite3"

# File-based database (created if it does not exist)
DB.open "sqlite3://./data.db" do |db|
  db.exec "create table if not exists contacts (name text, age integer)"
  db.exec "insert into contacts values (?, ?)", "Alice", 28
  puts db.scalar("select count(*) from contacts") # => 1
end

# In-memory database (URL-encoded colon required in URI host position)
DB.open "sqlite3://%3Amemory%3A" do |db|
  db.exec "create table contacts (name text, age integer)"
  db.exec "insert into contacts values (?, ?)", "Bob", 35
  puts db.scalar("select name from contacts") # => "Bob"
end
```

--------------------------------

### Run Crystal App with LD_LIBRARY_PATH

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/compile_and_link_sqlite.md

Executes a Crystal application, specifying the path to the custom SQLite library at runtime using LD_LIBRARY_PATH. This is useful for testing or when the library is not in a standard system location.

```sh
# directory of your crystal app
cd ../app

# Crystal run
LD_LIBRARY_PATH=../sqlite-amalgamation-3370000 crystal run src/app.cr
```

--------------------------------

### Check SQLite Version from Crystal

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/compile_and_link_sqlite.md

A Crystal code snippet demonstrating how to connect to an in-memory SQLite database and query its version using SQL.

```crystal
# src/app.cr

DB_URI = "sqlite3://:memory:"

DB.open DB_URI do |db|
    db_version = db.scalar "select sqlite_version();"
    puts "SQLite #{db_version}"
end
```

--------------------------------

### Run Crystal App with Custom SQLite Library

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Execute a Crystal application, specifying the path to the custom SQLite3 shared library using LD_LIBRARY_PATH. This is useful for development or testing with a specific library version.

```bash
cd ../myapp
LD_LIBRARY_PATH=../sqlite-amalgamation-3370000 crystal run src/app.cr
```

--------------------------------

### Support for Time and Bool columns

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Handles `Time` values stored as TEXT in `"%F %H:%M:%S.%L"` or `"%F %H:%M:%S"` format (converted to UTC) and `Bool` values stored as `INT` (`1`/`0`). Requires importing the `sqlite3` library.

```crystal
require "sqlite3"

DB.open "sqlite3://%3Amemory%3A" do |db|
  db.exec "create table events (title text, occurred_at text, published integer)"

  now = Time.utc(2024, 6, 15, 10, 30, 45, nanosecond: 123_000_000)
  db.exec "insert into events values (?, ?, ?)", "Launch", now, true

  db.query "select title, occurred_at, published from events" do |rs|
    rs.each do
      title      = rs.read(String)
      occurred   = rs.read(Time)   # parsed back from TEXT automatically
      published  = rs.read(Bool)   # 1 → true

      puts title                                 # => "Launch"
      puts occurred.to_s(SQLite3::DATE_FORMAT_SUBSECOND)
      # => "2024-06-15 10:30:45.123"
      puts published                             # => true
    end
  end
end
```

--------------------------------

### Compile SQLite Command-Line Shell

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/compile_and_link_sqlite.md

Compiles the SQLite command-line shell executable. This command links the necessary source files and libraries.

```sh
gcc shell.c sqlite3.c -lpthread -ldl -o sqlite3
./sqlite3 --version
```

--------------------------------

### Compile SQLite Shared Library

Source: https://github.com/crystal-lang/crystal-sqlite3/blob/master/compile_and_link_sqlite.md

Compiles the SQLite shared library (libsqlite3.so.0). This command creates a position-independent code suitable for dynamic linking.

```sh
gcc -lpthread -ldl -shared -o libsqlite3.so.0 -fPIC sqlite3.c
```

--------------------------------

### DB.open

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Opens a connection pool to a SQLite3 database. Supports file-based and in-memory databases. The block form ensures the database is closed automatically.

```APIDOC
## DB.open — Open a database connection

`DB.open(uri)` opens a connection pool to the specified SQLite3 database. The URI scheme must be `sqlite3://`. Pass a file path for a persistent database or the special values `:memory:` / `%3Amemory%3A` for an in-memory database. The block form automatically closes the database when the block exits.

```crystal
require "sqlite3"

# File-based database (created if it does not exist)
DB.open "sqlite3://./data.db" do |db|
  db.exec "create table if not exists contacts (name text, age integer)"
  db.exec "insert into contacts values (?, ?)", "Alice", 28
  puts db.scalar("select count(*) from contacts") # => 1
end

# In-memory database (URL-encoded colon required in URI host position)
DB.open "sqlite3://%3Amemory%3A" do |db|
  db.exec "create table contacts (name text, age integer)"
  db.exec "insert into contacts values (?, ?)", "Bob", 35
  puts db.scalar("select name from contacts") # => "Bob"
end
```
```

--------------------------------

### DB#query_all

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Fetches all rows from a query result and materializes them into typed structs or tuples. This is efficient for retrieving all data at once.

```APIDOC
## DB#query_all — Fetch all rows as typed structs or tuples

`db.query_all(sql, as: T)` materializes every result row into a named tuple or a single type. It is the most convenient API when you need all rows at once.

```crystal
require "sqlite3"

DB.open "sqlite3://./app.db" do |db|
  db.exec "create table if not exists contacts (name text, age integer)"
  db.exec "insert into contacts values (?, ?)", "John Doe", 30
  db.exec "insert into contacts values (?, ?)", "Sarah",    33

  # Named-tuple form
  contacts = db.query_all "SELECT name, age FROM contacts ORDER BY age",
    as: {name: String, age: Int64}
  contacts.each { |c| puts "#{c[:name]} is #{c[:age]}" }
  # => John Doe is 30
  # => Sarah is 33

  # Single-column form
  names = db.query_all "SELECT name FROM contacts", as: String
  puts names.inspect # => ["John Doe", "Sarah"]
end
```
```

--------------------------------

### SQLite3::Exception for error handling

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Handles `SQLite3::Exception` which is raised on invalid operations. The exception carries a human-readable `message` and an integer `code` matching `LibSQLite3::Code` error codes. Requires importing the `sqlite3` library.

```crystal
require "sqlite3"

DB.open "sqlite3://%3Amemory%3A" do |db|
  db.exec "create table t (id integer primary key, val text not null)"

  begin
    db.exec "insert into t (id, val) values (?, ?)", 1, "hello"
    db.exec "insert into t (id, val) values (?, ?)", 1, "duplicate"  # PK conflict
  rescue ex : SQLite3::Exception
    puts ex.message  # => "UNIQUE constraint failed: t.id"
    puts ex.code     # => 19  (SQLITE_CONSTRAINT)
  end

  begin
    db.query "select not_a_column from t" do |rs|; end
  rescue ex : SQLite3::Exception
    puts ex.message  # => "no such column: not_a_column"
  end
end
```

--------------------------------

### Embed SQLite Library Path at Compile Time

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Build a release version of your Crystal application, embedding the path to the SQLite3 shared library using the --link-flags option. This ensures the application links against the specified library at build time.

```bash
crystal build --release \
  --link-flags "$(realpath ../sqlite-amalgamation-3370000/libsqlite3.so.0)" \
  src/app.cr
./app
```

--------------------------------

### DB#query / ResultSet

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Executes a query and returns a DB::ResultSet for iterating over rows. Supports reading individual columns by type and accessing column names and count.

```APIDOC
## DB#query / ResultSet — Iterate over rows

`db.query(sql, *args)` returns a `DB::ResultSet`. Use `rs.each` to iterate rows and `rs.read(Type)` to read individual columns in order. `rs.column_name(index)` returns the column name. `rs.column_count` returns the total number of columns.

```crystal
require "sqlite3"

DB.open "sqlite3://./app.db" do |db|
  db.exec "create table if not exists products (sku text, price float, stock integer)"
  db.exec "insert into products values (?, ?, ?)", "A100", 9.99, 50
  db.exec "insert into products values (?, ?, ?)", "B200", 19.95, 10

  db.query "select sku, price, stock from products order by price desc" do |rs|
    # Print header using column names
    puts (0...rs.column_count).map { |i| rs.column_name(i) }.join(" | ")
    # => sku | price | stock

    rs.each do
      sku   = rs.read(String)
      price = rs.read(Float64)
      stock = rs.read(Int32)
      puts "#{sku} | #{price} | #{stock}"
      # => B200 | 19.95 | 10
      # => A100 | 9.99  | 50
    end
  end
end
```
```

--------------------------------

### Use REGEXP operator in SQLite queries

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Enables the REGEXP operator in SQL queries by registering a `regexp` function backed by Crystal's `Regex` class. Usage requires importing the `sqlite3` library.

```crystal
require "sqlite3"

DB.open "sqlite3://%3Amemory%3A" do |db|
  db.exec "create table logs (message text)"
  db.exec "insert into logs values (?)", "ERROR: disk full"
  db.exec "insert into logs values (?)", "INFO: started"
  db.exec "insert into logs values (?)", "ERROR: timeout"
  db.exec "insert into logs values (?)", "WARN: slow query"

  errors = db.query_all "select message from logs where message REGEXP ?",
    "^ERROR", as: String
  puts errors.inspect
  # => ["ERROR: disk full", "ERROR: timeout"]

  # Scalar usage: returns 1 (match) or 0 (no match)
  puts db.scalar("select 'hello world' REGEXP 'wor.d'") # => 1
  puts db.scalar("select 'hello world' REGEXP '^wor'")  # => 0
end
```

--------------------------------

### DB#scalar

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Executes a query and returns the first column of the first row. The result is returned as DB::Any and can be cast to the expected Crystal type.

```APIDOC
## DB#scalar — Read a single value

`db.scalar(sql, *args)` executes a query and returns the first column of the first row as `DB::Any`. Cast or read the return value to the expected Crystal type.

```crystal
require "sqlite3"

DB.open "sqlite3://./app.db" do |db|
  db.exec "create table if not exists orders (amount float)"
  db.exec "insert into orders values (?)", 120.50
  db.exec "insert into orders values (?)", 85.00
  db.exec "insert into orders values (?)", 200.75

  total  = db.scalar("select sum(amount)  from orders").as(Float64)
  max    = db.scalar("select max(amount)  from orders").as(Float64)
  count  = db.scalar("select count(*)     from orders").as(Int64)
  sqlite_ver = db.scalar("select sqlite_version()").as(String)

  puts "total=#{total} max=#{max} count=#{count} sqlite=#{sqlite_ver}"
  # => total=406.25 max=200.75 count=3 sqlite=3.x.y
end
```
```

--------------------------------

### Explicit Transactions with DB#transaction

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Manage database transactions using `db.transaction` which internally handles `BEGIN`, `COMMIT`, and `ROLLBACK`. Nested calls support savepoints. Ensure proper error handling with `begin...rescue` for transaction failures.

```crystal
require "sqlite3"

DB.open "sqlite3://./bank.db" do |db|
  db.exec "create table if not exists accounts (id integer primary key, balance float)"
  db.exec "insert into accounts values (1, 1000.0)"
  db.exec "insert into accounts values (2, 500.0)"

  begin
    db.transaction do |tx|
      cnn = tx.connection
      cnn.exec "update accounts set balance = balance - ? where id = ?", 200.0, 1
      cnn.exec "update accounts set balance = balance + ? where id = ?", 200.0, 2

      # Nested savepoint
      tx.savepoint "sp1" do |sp|
        sp.connection.exec "update accounts set balance = balance - ? where id = ?", 50.0, 2
        # Rollback only the savepoint, keeping the outer transfer
        sp.rollback
      end
    end
  rescue ex : SQLite3::Exception
    puts "Transaction failed: #{ex.message} (code #{ex.code})"
  end

  puts db.scalar("select balance from accounts where id = 1") # => 800.0
  puts db.scalar("select balance from accounts where id = 2") # => 700.0
end
```

--------------------------------

### DB#exec

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Executes a DML or DDL statement that does not return rows. It returns a DB::ExecResult containing rows affected and the last inserted ID. Supports positional parameters using '?'.

```APIDOC
## DB#exec — Execute a statement (no result rows)

`db.exec(sql, *args)` executes a DML or DDL statement and returns a `DB::ExecResult` with `rows_affected` and `last_insert_id`. Use `?` as the positional parameter placeholder. Arguments can be passed as splat args or via the `args:` named parameter.

```crystal
require "sqlite3"

DB.open "sqlite3://./app.db" do |db|
  db.exec "create table if not exists users (id integer primary key, name text not null, active integer)"

  # Splat-style parameters
  result = db.exec "insert into users (name, active) values (?, ?)", "Carol", true
  puts result.last_insert_id  # => 1
  puts result.rows_affected   # => 1

  # Array-style parameters (DB::Any)
  args = [] of DB::Any
  args << "Dave"
  args << false
  result2 = db.exec "insert into users (name, active) values (?, ?)", args: args
  puts result2.last_insert_id # => 2

  # Passing all integer widths
  db.exec "insert into users (name, active) values (?, ?)", "Eve", 1_u8
end
```
```

--------------------------------

### Read Single Value with DB#scalar

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Use `db.scalar` to execute a query and retrieve the first column of the first row as `DB::Any`. Cast the result to the expected Crystal type. This is useful for aggregate functions or single-value queries.

```crystal
require "sqlite3"

DB.open "sqlite3://./app.db" do |db|
  db.exec "create table if not exists orders (amount float)"
  db.exec "insert into orders values (?)", 120.50
  db.exec "insert into orders values (?)", 85.00
  db.exec "insert into orders values (?)", 200.75

  total  = db.scalar("select sum(amount)  from orders").as(Float64)
  max    = db.scalar("select max(amount)  from orders").as(Float64)
  count  = db.scalar("select count(*)     from orders").as(Int64)
  sqlite_ver = db.scalar("select sqlite_version()").as(String)

  puts "total=#{total} max=#{max} count=#{count} sqlite=#{sqlite_ver}"
  # => total=406.25 max=200.75 count=3 sqlite=3.x.y
end
```

--------------------------------

### DB#transaction

Source: https://context7.com/crystal-lang/crystal-sqlite3/llms.txt

Manages explicit database transactions, including support for nested savepoints. Calls BEGIN, COMMIT, or ROLLBACK on the underlying SQLite3 connection.

```APIDOC
## DB#transaction — Explicit transactions and savepoints

crystal-db exposes `db.transaction` which internally calls `BEGIN` / `COMMIT` / `ROLLBACK` on the underlying `SQLite3::Connection`. Savepoints are also supported via nested transaction calls.

```crystal
require "sqlite3"

DB.open "sqlite3://./bank.db" do |db|
  db.exec "create table if not exists accounts (id integer primary key, balance float)"
  db.exec "insert into accounts values (1, 1000.0)"
  db.exec "insert into accounts values (2, 500.0)"

  begin
    db.transaction do |tx|
      cnn = tx.connection
      cnn.exec "update accounts set balance = balance - ? where id = ?", 200.0, 1
      cnn.exec "update accounts set balance = balance + ? where id = ?", 200.0, 2

      # Nested savepoint
      tx.savepoint "sp1" do |sp|
        sp.connection.exec "update accounts set balance = balance - ? where id = ?", 50.0, 2
        # Rollback only the savepoint, keeping the outer transfer
        sp.rollback
      end
    end
  rescue ex : SQLite3::Exception
    puts "Transaction failed: #{ex.message} (code #{ex.code})"
  end

  puts db.scalar("select balance from accounts where id = 1") # => 800.0
  puts db.scalar("select balance from accounts where id = 2") # => 700.0
end
```
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.