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:

• crystal-lang/crystal-sqlite3 for SQLite
• crystal-lang/crystal-mysql for MySQL and MariaDB
• will/crystal-pg for PostgreSQL
• kaukas/crystal-cassandra for Cassandra

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:

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.9.0"

Class Method Summary

• .connect(uri : URI | String)

  Opens a connection using the specified uri.

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

  ditto

• .open(uri : URI | String)

  Creates a Database pool and opens initial connection(s) as specified in the connection uri.

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

  Same as #open but the database is yielded and closed automatically at the end of the block.

• .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 ResultSet.

• mapping(**properties)

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