module DB

Overview

The DB module is a unified interface for database access. Individual database systems are supported by specific database driver shards.

Available drivers include:

For basic instructions on implementing a new database driver, check Driver and the existing drivers.

DB manages a connection pool. The connection pool can be configured by query parameters to the connection URI as described in Database.

Usage

Assuming crystal-sqlite3 is included a SQLite3 database can be opened with #open.

db = DB.open "sqlite3:./path/to/db/file.db"
db.close

If a block is given to #open the database is closed automatically

DB.open "sqlite3:./file.db" do |db|
  # work with db
end # db is closed

In the code above db is a Database. Methods available for querying it are described in QueryMethods.

Three kind of statements can be performed:

  1. Database#exec waits no response from the database.
  2. Database#scalar reads a single value of the response.
  3. Database#query returns a ResultSet that allows iteration over the rows in the response and column information.

All of the above methods allows parametrised query. Either positional or named arguments.

Check a full working version:

The following example uses SQLite where ? indicates the arguments. If PostgreSQL is used $1, $2, etc. should be used. crystal-db does not interpret the statements.

require "db"
require "sqlite3"

DB.open "sqlite3:./file.db" do |db|
  # When using the pg driver, use $1, $2, etc. instead of ?
  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

Defined in:

lib/db/src/db.cr
lib/db/src/db/begin_transaction.cr
lib/db/src/db/connection.cr
lib/db/src/db/connection_context.cr
lib/db/src/db/database.cr
lib/db/src/db/disposable.cr
lib/db/src/db/driver.cr
lib/db/src/db/enumerable_concat.cr
lib/db/src/db/error.cr
lib/db/src/db/mapping.cr
lib/db/src/db/pool.cr
lib/db/src/db/pool_prepared_statement.cr
lib/db/src/db/pool_statement.cr
lib/db/src/db/pool_unprepared_statement.cr
lib/db/src/db/query_methods.cr
lib/db/src/db/result_set.cr
lib/db/src/db/serializable.cr
lib/db/src/db/session_methods.cr
lib/db/src/db/statement.cr
lib/db/src/db/string_key_cache.cr
lib/db/src/db/transaction.cr
avram/pool_statement_logging.cr