module DB

Overview

The DB module is a unified interface to database access. Database dialects is supported by custom database driver shards. Check crystal-lang/crystal-sqlite3 for example.

Drivers implementors check Driver class.

DB manage a connection pool. The connection pool can be configured by URI query. See 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:

require "db"
require "sqlite3"

DB.open "sqlite3:./file.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

  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:

Constant Summary

TYPES = [Nil, String, Bool, Int32, Int64, Float32, Float64, Time, Bytes]

Types supported to interface with database driver. These can be used in any ResultSet#read or any Database#query related method to be used as query parameters

VERSION = "0.5.1"

Class Method Summary

• .connect(uri : URI | String)

  Opens a connection using the specified uri.

• .connect(uri : URI | String, &block)

  Opens a connection using the specified uri.

• .open(uri : URI | String)

  Opens a database using the specified uri.

• .open(uri : URI | String, &block)

  Same as #open but the database is yielded and closed automatically.

• .register_driver(driver_name, driver_class : Driver.class)

  Registers a driver class for a given driver_name.

Macro Summary

• mapping(properties, strict = true)

  The DB.mapping macro defines how an object is built from a DB::ResultSet.

• mapping

Class Method Detail

Macro Detail

require "db"

class Employee
  DB.mapping({
    title: String,
    name:  String,
  })
end

employees = Employee.from_rs(db.query("SELECT title, name FROM employees"))
employees[0].title # => "Manager"
employees[0].name  # => "John"

class Employee
  DB.mapping({
    title: String,
    name:  {
      type:    String,
      nilable: true,
      key:     "firstname",
    },
  })
end