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:

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

Constant Summary

Log = ::Log.for(self)
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.10.1"

Class Method Summary

Macro Summary

Class Method Detail

def self.connect(uri : URI | String) #

Opens a connection using the specified uri. The scheme of the uri determines the driver to use. Returned connection must be closed by Connection#close. If a block is used the connection is yielded and closed automatically.


[View source]
def self.connect(uri : URI | String, &) #

Opens a connection using the specified uri. The scheme of the uri determines the driver to use. Returned connection must be closed by Connection#close. If a block is used the connection is yielded and closed automatically.


[View source]
def self.open(uri : URI | String) #

Creates a Database pool and opens initial connection(s) as specified in the connection uri. Use DB#connect to open a single connection.

The scheme of the uri determines the driver to use. Connection parameters such as hostname, user, database name, etc. are specified according to each database driver's specific format.

The returned database must be closed by Database#close.


[View source]
def self.open(uri : URI | String, &) #

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


[View source]
def self.register_driver(driver_name, driver_class : Driver.class) #

Registers a driver class for a given driver_name. Should be called by drivers implementors only.


[View source]

Macro Detail

macro mapping(properties, strict = true) #

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

It takes hash literal as argument, in which attributes and types are defined. Once defined, ResultSet#read(t) populates properties of the class from the ResultSet.

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"

Attributes not mapped with DB.mapping are not defined as properties. Also, missing attributes raise a DB::MappingException.

You can also define attributes for each property.

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

Available attributes:

  • type (required) defines its type. In the example above, title: String is a shortcut to title: {type: String}.
  • nilable defines if a property can be a Nil.
  • default: value to use if the property is missing in the result set, or if it's null and nilable was not set to true. If the default value creates a new instance of an object (for example [1, 2, 3] or SomeObject.new), a different instance will be used each time a row is parsed.
  • key defines which column to read from a ResultSet. It defaults to the name of the property.
  • converter takes an alternate type for parsing. It requires a #from_rs method in that class, and returns an instance of the given type.

The mapping also automatically defines Crystal properties (getters and setters) for each of the keys. It doesn't define a constructor accepting those arguments, but you can provide an overload.

The macro basically defines a constructor accepting a ResultSet that reads from it and initializes this type's instance variables.

This macro also declares instance variables of the types given in the mapping.


[View source]
macro mapping(**properties) #

[View source]