crystal-sqlite3 v0.21.0

SQLite3 bindings for Crystal

crystal-sqlite3 Build Status

SQLite3 bindings for Crystal.

Check crystal-db for general db driver documentation. crystal-sqlite3 driver is registered under sqlite3:// uri.

Installation

Add this to your application's shard.yml:

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

Usage

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
end

DB::Any

  • Time is implemented as TEXT column using SQLite3::DATE_FORMAT_SUBSECOND format (or SQLite3::DATE_FORMAT_SECOND if the text does not contain a dot).
  • Bool is implemented as INT column mapping 0/1 values.

Setting PRAGMAs

You can adjust certain SQLite3 PRAGMAs automatically when the connection is created by using the query parameters:

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

The following is the list of supported options:

Name Connection key
Busy Timeout busy_timeout
Cache Size cache_size
Foreign Keys foreign_keys
Journal Mode journal_mode
Synchronous synchronous
WAL autocheckoint wal_autocheckpoint

Please note there values passed using these connection keys are passed directly to SQLite3 without check or evaluation. Using incorrect values result in no error by the library.

Repository

crystal-sqlite3

Owner
Statistic
  • 131
  • 28
  • 23
  • 297
  • 1
  • 4 months ago
  • March 12, 2015
License

MIT License

Links
Synced at

Sun, 14 Apr 2024 15:11:06 GMT

Languages