DOCSEARCHbyAlgolia
Submit Docs

crystal-sqlite3

repository·master·Indexed 41 days ago

https://github.com/crystal-lang/crystal-sqlite3

SQLite3 bindings for the Crystal programming language. It provides a driver that integrates with the crystal-db ecosystem, allowing developers to interact with SQLite databases using the sqlite3:// URI scheme. Features include support for executing SQL commands, fetching scalar values, and iterating over result sets, as well as the ability to configure SQLite3 PRAGMAs via connection URIs.

Tokens
1.7K
Snippets
7
Records
8
Agent score
1%

What's inside crystal-sqlite3

  1. How DB::Any handles Time and Bool types

    master

    When using DB::Any for parameter binding, the driver implements types as follows:

    • Time: Implemented as a TEXT column using SQLite3::DATE_FORMAT_SUBSECOND (or SQLite3::DATE_FORMAT_SECOND if no subsecond component is present).
    • Bool: Implemented as an INT column mapping to 0 or 1 values.

  3. Compile SQLite from source

    master

    If you need a specific SQLite feature not available in your distribution or want to enable custom compile-time features, you can compile SQLite from the amalgamation source code.

    Prerequisites (Ubuntu)

    Install build-essential:

    sudo apt update
sudo apt install build-essential

    Download and Extract

    Download the "C source code as an amalgamation" from the SQLite Download Page.

    # Example for version 3.37.0
wget https://sqlite.org/2021/sqlite-amalgamation-3370000.zip
unzip sqlite-amalgamation-3370000.zip
cd sqlite-amalgamation-3370000

    Compilation Steps

    1. Compile the sqlite3 command line tool:
    gcc shell.c sqlite3.c -lpthread -ldl -o sqlite3
./sqlite3 --version
    1. Compile the libsqlite3.so.0 shared library:
    gcc -lpthread -ldl -shared -o libsqlite3.so.0 -fPIC sqlite3.c

  4. Use crystal-sqlite3 to execute queries and fetch data

    master

    To use the driver, require sqlite3 and open a connection using the sqlite3:// URI scheme. You can execute SQL commands with exec, fetch a single scalar value with scalar, iterate over rows with query, or fetch all results as an array of hashes with query_all.

    require "sqlite3"

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

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

  # Fetch a single value
  puts db.scalar "select max(age) from contacts"

  # Iterate over a result set
  db.query "select name, age from contacts order by age desc" do |rs|
    puts "#{rs.column_name(0)} (#{rs.column_name(1)})"
    rs.each do
      puts "#{rs.read(String)} (#{rs.read(Int32)})"
    end
  end

  # Fetch all results as an array of hashes
  contacts = db.query_all "SELECT name, age FROM contacts", as: {name: String, age: Int64}
  puts contacts
end

  5. Link a custom SQLite library to your Crystal application

    master

    You can use a custom-compiled version of SQLite with your Crystal application using two different methods: specifying the library at runtime or hardcoding the path at compile time.

    Method 1: Specify library location at runtime (Recommended)

    This method allows you to point to a different library location without rebuilding the binary. Use the LD_LIBRARY_PATH environment variable.

    To run directly:

    LD_LIBRARY_PATH=/path/to/sqlite_dir crystal run src/app.cr

    To build a release binary that uses the custom library at runtime:

    crystal build --release --link-flags -L"$(realpath /path/to/sqlite_dir/libsqlite3.so.0)" src/app.cr
LD_LIBRARY_PATH=/path/to/sqlite_dir ./app

    You can verify which library is being linked using ldd:

    LD_LIBRARY_PATH=/path/to/sqlite_dir ldd ./app

    Method 2: Specify absolute path at compile time

    This method embeds the specific path into the binary. Note that the resulting binary will only work if the library remains at that exact location.

    To run directly:

    crystal run --link-flags "$(realpath /path/to/sqlite_dir/libsqlite3.so.0)" src/app.cr

    To build a release binary:

    crystal build --release --link-flags "$(realpath /path/to/sqlite_dir/libsqlite3.so.0)" src/app.cr
./app

    Verify the link with:

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

  7. Configure SQLite3 PRAGMAs via connection URI

    master

    You can automatically adjust SQLite3 PRAGMAs when creating a connection by appending query parameters to the sqlite3:// URI. Note that values are passed directly to SQLite3 without validation; incorrect values will not trigger a library error.

    require "sqlite3"

# Example: setting journal_mode to WAL and synchronous to normal
DB.open "sqlite3://./data.db?journal_mode=wal&synchronous=normal" do |db|
end

  8. Supported SQLite3 PRAGMA connection keys

    master

    The following query parameters can be used in the connection URI to set SQLite3 PRAGMAs:

    | Name | Connection key |
| :--- | :--- |
| Busy Timeout | `busy_timeout` |
| Cache Size | `cache_size` |
| Foreign Keys | `foreign_keys` |
| Journal Mode | `journal_mode` |
| Synchronous | `synchronous` |
| WAL autocheckpoint | `wal_autocheckpoint` |